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

                                                                   Avg

                                                     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.98	Redo NoWait %:	100.00
Buffer Hit %:	96.43	In-memory Sort %:	99.99
Library Hit %:	97.16	Soft Parse %:	98.16
Execute to Parse %:	25.09	Latch 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

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		147,593		42.3
db file sequential read	31,776,678	87,659	3	25.1	User I/O
db file scattered read	19,568,220	79,142	4	22.7	User I/O
RMAN backup & recovery I/O	1,579,314	37,650	24	10.8	System I/O
read by other session	3,076,110	14,216	5	4.1	User I/O

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

.
.
.