Tuning Statspack Oracle 9i/10g

“Perfection is achieved, not when there is nothing left to add, but when there is nothing left to take away.”

Statspack Tuning Oracle9i & 10g

Overview

• Statspack, Tools & Scripts that you can still use

– Top Waits

– Load Profile

– Latch Waits

– Top SQL

– Instance Activity

– File I/O

• The Future OEM & ADDM

• Helpful V$/X$

Tuning in General

• Both an Art and a Science

• Exceptions often rule the day…Not a “one size fits all”

• Hardware & Architecture must be right for your application or difficult to succeed.

• Statspack & Enterprise Manager (also 3rd party products) are best for simple tuning and ongoing maintenance.

• V$/X$ are best for drilling deep into problems

• Enterprise Manager 10g will radically change things

Statspack – Check Regularly

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 Allocation

10. Undo

Statspack – Miscellaneous notes

• SQL> @spcreate (system/manager as SYSDBA) <1m

• SQL> alter user PERFSTAT account lock; (also unlock)

• SQL> EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER – (i_snap_level=>5, i_buffer_gets_th=>100000, - i_modify_parameter=>'true');

• SQL> execute STATSPACK.SNAP; (do this for start/end) PL/SQL procedure successfully completed.

(spauto.sql – Setup a snap on the hour every hour)

• SQL> @ORACLE_HOME/rdbms/admin/spreport (also sprepsql)

Statspack – Header Information

DB Name DB Id Instance Inst Num Release Cluster Host

------------ ----------- ------------ -------- ----------- -------- ----

ORA92 968233682 P10 1 9.2.0.4.0 NO RJN1

Snap Id Snap Time Sessions Curs/Sess Comment

--------- ------------------ -------- --------- ------------

Begin Snap: 458 28-Nov-03 00:15:00 814 179.1

End Snap: 505 28-Nov-03 23:45:00 816 211.4

Elapsed: 1,410.00 (mins)

Cache Sizes (end)

~~~~~~~~~~~~~~~~~

Buffer Cache: 32,773M Std Block Size: 8K

Shared Pool Size: 2,048M Log Buffer: 1,024K

Statspack – Header Information (Take care)

• Ensure that you’re running for the right instance.

• Check the start/end times

• Check the cache sizes

– Could have been changed during the run

– Last page will tell you more on starting/ending values of initialization parameters

Statspack – Load Profile

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

--------------- ---------------

Redo size: 1,409,245.79 36,596.21

Logical reads: 157,472.47 4,089.35

Block changes: 4,061.85 105.48

Physical reads: 5,965.05 154.90

Physical writes: 587.76 15.26

User calls: 5,922.08 153.79

Parses: 92.11 2.39

Hard parses: 0.17 0.00

Sorts: 93.88 2.44

Logons: 0.25 0.01

Executes: 5,686.76 147.68

Transactions: 38.51

% Blocks changed per Read: 2.58 Recursive Call %: 2.38

Rollback per transaction %: 1.22 Rows per Sort: 114.10

Statspack – Instance Efficiency

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 99.08 Redo NoWait %: 99.86

Buffer Hit %: 96.39 In-memory Sort %: 99.95

Library Hit %: 100.00 Soft Parse %: 99.82

Execute to Parse %: 98.38 Latch Hit %: 99.64

Parse CPU to Parse Elapsd %: 85.11 % Non-Parse CPU: 99.86

Statspack - Instance Efficiency

Things to look for…

• Buffer NoWait % of less than 99 percent. This is ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then could be a (hot) block(s) being contended for that should be found in the Buffer Wait Section.

• Buffer Hit % of less than 95 percent. This is the ratio of hits on a request for a specific buffer and the buffer was in memory instead of needing to do a physical I/O.

• When this varies greatly one day to the next, further investigation should be done as to the cause.

• If you have unselective indexes that are frequently accessed, it will drive your hit ratio higher, which can be misleading indication of good performance.

• When you effectively tune your SQL and have effective indexes on your entire system, this issue is not encountered as frequently and the hit ratio is a better performance indicator.

• Library Hit % of less than 95 percent. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small).

• A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case a smaller shared pool may only be a band-aid that will potentially fix a library latch problem which may result).

• You must fix the problem (use bind variables or CURSOR_SHARING) and then appropriately size the shared pool. I’ll discuss this further when we get to latch issues.

• In-Memory Sort % of less than 95 percent in OLTP. In an OLTP system, you really don’t want to do disk sorts. Setting the PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE) initialization parameter effectively will eliminate this problem.

Latch Hit % of less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue. More in the Latch Wait section.