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.

  • You've probably already checked this, but just to be clear - are the plans the same each time? The same query can have a different plan after the first execution because of things like cardinality feedback.
    – Jon Heller
    Nov 21 '13 at 3:18
  • Are you aware that autotrace lies? kerryosborne.oracle-guy.com/2010/02/autotrace-lies . DBMS_XPLAN is your friend.
    – Philᵀᴹ
    Nov 21 '13 at 15:39

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.