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
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.
|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|
|Event||Waits||Time(s)||Avg Wait(ms)||% Total Call Time||Wait Class|
|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|