SQL Cache - Disk reads larger than buffer gets??
On an Oracle 10g test system, from ST04 -> Detailed analysis -> SQL request
I'm getting what seems to be a strange result for one statement:
Disk reads: 799,713
Buffer gets: 553.377
I thought that for each disk read, the block was read into the data buffer and then read from there.
This means that 'Disk reads' greater than 'Buffer gets' should be impossible.
Is what I'm seeing in my SQL cursor cache a bug or can someone suggest a scenario where disk reads could be greater than buffer gets?
Martin Frauendorfer replied
again a nice question from your side
What you have forgotten in your considerations are the so called "direct path reads" that bypass the buffer pool. These direct path reads happen if sort, hash or bitmap operations are performed in PSAPTEMP, if NOCACHE LOBs are read or if parallel query is used. In most cases sorts are responsible for a higher number of disk reads compared to buffer gets. Can you check if the SQL statement in question contains a sort or aggregate related component (like ORDER BY, GROUP BY, DISTINCT, ...)?