Re: Oracle Brain Teaser

> I am seeing some SQL
> which generates over 30,000 LIOs per execution. Now with a buffer
> cache of only 200 buffers, I would expect the hit ratio for this
> statement to be very poor.
>
> However, the disk reads for this particular statement is less than
> 1000. This doesnt make sense to me.

You have 30K LIO's. LIO's do not necessarily mean that a physical IO has to take place. All of the LIOs can take place right in the buffer cache without incurring any physical IO. You can have 30,000 LIOs hit 100 blocks of the buffer cache which may or may not be a good thing. Cary Millsap has a good paper on his web site (http://www.hotsos.com) which discusses this, titled "Why You Should Focus on LIOs Instead of PIOs".

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"