02 December, 2014

StatsPack and AWR Reports -- Bits and Pieces -- 4

This is the fourth post in a series.

Post 1 is here.
Post 2 is here.
Post 3 is here.

Buffer Cache Hit Ratios

Many novice DBAs may use Hit Ratios as indicators of performance.  However, these can be misleading or incomplete.

Here are two examples :

Extract A: 9i StatsPack

Instance Efficiency Percentages (Target 100%)
Buffer  Hit   %:   99.06

It would seem that with only 0.94% of reads being physical reads, the database is performing optimally.  So, the DBA doesn't need to look any further.  

Or so it seems.

If he spends some time reading the report, he also then comes across this :

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                           837,955       4,107    67.36
CPU time                                                        1,018    16.70
db file scattered read                             43,281         549     9.00

                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read           837,955          0      4,107      5    403.3
db file scattered read             43,281          0        549     13     20.8

Physical I/O is a significant proportion (76%) of total database time.  88% of the physical I/O is single-block  reads ("db file sequential read").  This is where the DBA must identify that tuning *is* required.

Considering the single block access pattern it is likely that a significant proportion are index blocks as well.  Increasing the buffer cache might help cache the index blocks.

Extract B : 10.2 AWR

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %:99.98Redo NoWait %:100.00
Buffer Hit %:96.43In-memory Sort %:99.99
Library Hit %:97.16Soft Parse %:98.16
Execute to Parse %:25.09Latch Hit %:99.85
Parse CPU to Parse Elapsd %:89.96% Non-Parse CPU:96.00

The Buffer Hit Ratio is very good.  Does that mean that I/O is not an issue ?

Look again at the same report 

Top 5 Timed Events
EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time147,59342.3
db file sequential read31,776,67887,659325.1User I/O
db file scattered read19,568,22079,142422.7User I/O
RMAN backup & recovery I/O1,579,31437,6502410.8System I/O
read by other session3,076,11014,21654.1User I/O

User I/O is actually significant.  The SQLs with the highest logical I/O need to be reviewed for tuning.


No comments: