in Oracle

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 is an example :

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.
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.

.
.
.