Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL Cache - Disk reads larger than buffer gets??

Hello,

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?

Kind regards,

Peter

Former Member replied

Hello Peter,

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, ...)?

Regards

Martin

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question