SQL Cache - Disk reads larger than buffer gets??

[peter_strauss]

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 · Accepted solution]

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

[peter_strauss · Accepted solution]

Hello Martin,

I'm happy to hear that you like my questions. I only have another 4 million or so to go and then I will know everything.

The execution plan follows. I'm not sure what it's doing. Is there an aggregate function in there somewhere?

Kind regards,

Peter

SQL Statement

-

---

SELECT

o.obj#, u.name, o.name, t.spare1, DECODE(bitand(t.flags, 268435456), 68435456, t.initrans, t.pctfree$)

FROM

sys.obj$ o, sys.user$ u, sys.tab$ t

WHERE

(bitand(t.trigflag, 1048576) = 1048576) and o.obj#=t.obj# and o.owner# = u.user#

Execution Plan

Explain from v$sql_plan: Address: C0000000CC674F30 Hash_value: 2372567631 Child_number: 0

-

---

SELECT STATEMENT ( Estimated Costs = 166 , Estimated #Rows = 0 )

--- 6 HASH JOIN

( Estim. Costs = 166 , Estim. #Rows = 8 )

Estim. CPU-Costs = 5.271.745 Estim. IO-Costs = 160

Memory Used KB: 188.416

-- 4 NESTED LOOPS

( Estim. Costs = 164 , Estim. #Rows = 8 )

Estim. CPU-Costs = 4.842.218 Estim. IO-Costs = 158

1 TABLE ACCESS FULL TAB$

( Estim. Costs = 156 , Estim. #Rows = 8 )

Estim. CPU-Costs = 4.768.366 Estim. IO-Costs = 150

--- 3 TABLE ACCESS BY INDEX ROWID OBJ$

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 9.231 Estim. IO-Costs = 1

2 INDEX UNIQUE SCAN I_OBJ1

Search Columns: 1

Estim. CPU-Costs = 1.900 Estim. IO-Costs = 0

-

---

5 TABLE ACCESS FULL USER$

( Estim. Costs = 2 , Estim. #Rows = 26 )

Estim. CPU-Costs = 18.663 Estim. IO-Costs = 2

[peter_strauss · Accepted solution]

Actuall, I guess in this case one of the fields referenced is a nocache lob.

I'll hunt around in the DBA views to see if I can verify this.

Thanks again.

Peter

[Former Member]

Hello Peter,

also the hash join in the execution plan can be the reason for the direct path accesses.

Regards

Martin

[peter_strauss]

Sorry, I should have got that from your first reply.

Regards,

Peter