Oracle Logical IO / Consistent Reads inconsistent?

Question

I use Logical IO to tune my Oracle queries and generally ignore wall-clock times as we have a large Oracle DB in a cluster with a total of about 80Gb SGA and an EMC cluster with tiered storage (Gigs of cache, SSD, Fiber disk and finally SATA)

This measure works for me, but I have noticed that sometimes the first Consistent Read value differs from subsequent ones (say 4000 IOs then 200 IOs where from them on the 200 IO remains consistent).

Whats up with that? Why is the the first request higher? I should be dealing with Logical IO and this should not be affected by whether the blocks we are looking for are in a buffer somewhere or whether we go directly to disk.

:)

To preempt your next question - I am looking at LAST_CR_BUFFER_GETS from Autotrace in SQL Developer.

Answer 1

My guess would be that the first query doing the extra work of delayed block cleanout because some other session at some earlier point in time modified (or inserted) the data without being able to do a commit cleanout. That causes Oracle to do a bit of extra work the next time the dirty block is read to update its status. Here is a good example of the impact of delayed block cleanouts on consistent gets. I'm not 100% certain that delayed block cleanouts are reflected in LAST_CR_BUFFER_GETS from DBMS_XPLAN but I would certainly expect that they are.