Interpreting Statspack Part-II

Load Profile: -

This section gives you per second and per transaction statistics.

Details are: -

a) An increase in redo size, block changes and %blocks changed per read indicate increase DML i.e. insert, update or delete activity.

b) Parses: This figure indicates the application is not reusing the SQL and needs investigation and the program is parsing the SQL repeatedly instead of reusing it.

c)  The hard parse figures will show an increased adhoc queries or that certain portion does not use bind variables. 

Instance Efficiency Percentages:

Details are: -

a) Buffer No wait %: This value should greater than 99%

b) Buffer Hit %: This value should be ideally above 95%. Unselective indexes will deceptively give you higher misleading figures and evidently you will see waits indicating these problems.

c) Library hit %: This value should be more that 95% .Lesser figure indicates that bind variables are not used or that the allocated memory is insufficient.

d) In Memory sort %: This should be above 97% in OLTP systems. You definitely do not want disk sorts leading to a poor response time. Try and get a figure as close to 100 by increasing the pga_aggregate_target / sort_area_size. Proper Indexing also helps. Consider reverse key indexes for columns generated by sequences.

e) Soft parse%. This figure should be above 95%. A lower figure indicates that SQL is not being reused and needs investigation. It is also seen in the section Load Profile.

f) Latch Hit %: This figure should be above 99%. Any figure less than this indicates a bad situation. Top waits will also indicate the issue in details.

Any drastic change in these ratios from day to day cycles indicate as change that needs to be investigated in details to avoid performance issues.

Enjoy:-)