Hit ratio and db sequential read in statspack

[chikkodi]

A statspack report on one of our dbs shows a hit ratio of > 90%
Buffer Nowait %: 99.93 Redo NoWait %: 100.00
Buffer Hit %: 93.43 In-memory Sort %: 99.99
Library Hit %: 99.87 Soft Parse %: 96.55
Execute to Parse %: 99.43 Latch Hit %: 95.93
Parse CPU to Parse Elapsd %: 61.31 % Non-Parse CPU: 99.08

However, there are a lot of db file scattered read and db file sequential read events

db file scattered read 801,322 0 4,891 6 258.5
db file sequential read 790,680 0 3,961 5 255.1

The data tablespace where application data resides also has a lot of reads/sec.

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
APP_DATA
1,582,830 328 5.6 3.2 754 0 51,181 4.2

How is this possible? Doesnt buffer cache hit ratio directly correspond to db file read?

[slimdave]

BCHR tells you how many logical reads are completed without a physical read ... it doesn't tell you anything about the number of physical reads themselves.

[chikkodi]

Can you explain that in more detail?
The fact that we are having high tablespace disk reads would indicate I need to increase the buffer cache, but BCHR is > 90%, which means things are fine. Thats contradicting right? - This is from my understanding, which may be wrong ofcourse!

[davey23uk]

BCHR is > 90%, which means things are fine

no it doesnt

it could mean things are very bad and you are doing loads of PIO's which in turn makes your BCHR really high because a lot of stuff is naturally becoming an LIO beacuse of the PIO you just did

[slimdave]

it could mean things are very bad and you are doing loads of PIO's which in turn makes your BCHR really high because a lot of stuff is naturally becoming an LIO beacuse of the PIO you just did

Other way round I think, Davey.

Inefficient SQL -> High LIO's -> Possibly increased PIO's -> High BCHR ... but still poor performance.