How to read statspack report
1) Wait Events :-- Look for excessive waits and wait times and check for specific problems
2) SQL Ordered by Buffer Gets,Physical Reads and Rows Processed :-- Figure out which SQL statements to tune
3) Instance Activity statistics :-- Compare with baseline report,compute additional statistics
4) Tablespace and File I/O :-- Investigate I/O bottlenecks,identify files and tablespaces with heavy I/O
5) Buffer Pool :-- Identify buffer pool with high I/O
6) Buffer wait statistics :-- Identify buffer type with large waits
7) Enqueue Activity :- Identify the locks which are causing most waits
8) Rollback Segment statistics and Storage :-- Investigate waits for rollback segments
9) Latch Activity,Latch Sleep Breakdown ,Latch Miss Sources :-- Identify latch bottlenacks
10) Non-default init.ora -- Look for init.ora parameter definitions
Look for statspack report in this order
1) Top 5 wait events
2) Load profile
3) Instance Efficiency Hit Ratios
4) Wait Events / Wait Event Histograms
5) Latch Waits
6) Top SQL
7) Instance Activity/Time Model Stats/ O/S Stats
8) File I/O / File Read Histogram / Undo Stats
9) Memory Allocations
10) Undo
Defintion
Top 5 wait events
Load Profile :-- use the Load Profile to verify the validity of comparing a report generated during a problem period with a report generated during a baseline period
Example of Load Profile
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 351,530.67 7,007.37
Logical reads: 5,449.81 108.64 --Key Metric
Block changes: 1,042.08 20.77
Physical reads: 37.71 0.75 --Key Metric
Physical writes: 134.68 2.68 --Key Metric
User calls: 1,254.72 25.01
Parses: 4.92 0.10
Hard parses: 0.02 0.00
Sorts: 15.73 0.31
Logons: -0.01 0.00
Executes: 473.73 9.44 --Key Metric
Transactions: 50.17 --Key Metric
% Blocks changed per Read: 19.12 Recursive Call %: 4.71
Rollback per transaction %: 2.24 Rows per Sort: 20.91
1) Wait Events :-- Look for excessive waits and wait times and check for specific problems
2) SQL Ordered by Buffer Gets,Physical Reads and Rows Processed :-- Figure out which SQL statements to tune
3) Instance Activity statistics :-- Compare with baseline report,compute additional statistics
4) Tablespace and File I/O :-- Investigate I/O bottlenecks,identify files and tablespaces with heavy I/O
5) Buffer Pool :-- Identify buffer pool with high I/O
6) Buffer wait statistics :-- Identify buffer type with large waits
7) Enqueue Activity :- Identify the locks which are causing most waits
8) Rollback Segment statistics and Storage :-- Investigate waits for rollback segments
9) Latch Activity,Latch Sleep Breakdown ,Latch Miss Sources :-- Identify latch bottlenacks
10) Non-default init.ora -- Look for init.ora parameter definitions
Look for statspack report in this order
1) Top 5 wait events
2) Load profile
3) Instance Efficiency Hit Ratios
4) Wait Events / Wait Event Histograms
5) Latch Waits
6) Top SQL
7) Instance Activity/Time Model Stats/ O/S Stats
8) File I/O / File Read Histogram / Undo Stats
9) Memory Allocations
10) Undo
Defintion
Top 5 wait events
Load Profile :-- use the Load Profile to verify the validity of comparing a report generated during a problem period with a report generated during a baseline period
Example of Load Profile
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 351,530.67 7,007.37
Logical reads: 5,449.81 108.64 --Key Metric
Block changes: 1,042.08 20.77
Physical reads: 37.71 0.75 --Key Metric
Physical writes: 134.68 2.68 --Key Metric
User calls: 1,254.72 25.01
Parses: 4.92 0.10
Hard parses: 0.02 0.00
Sorts: 15.73 0.31
Logons: -0.01 0.00
Executes: 473.73 9.44 --Key Metric
Transactions: 50.17 --Key Metric
% Blocks changed per Read: 19.12 Recursive Call %: 4.71
Rollback per transaction %: 2.24 Rows per Sort: 20.91
No comments:
Post a Comment