Re: Should you still tune queries by LIOs?

The notion of 'is it worth to spend the time to do this' depends on how essential the process is to your business. If it's some nightly batch nobody really cares about certainly not. If it's a query which is executed zillion times in the day, any, even modest, improvement is good for the taking.

In your case, 20,000 LIOs is not, in itself, enormous. It's a matter of scale. A 10-fold improvement is more visible on a query which runs for hours than on a query which only takes 0.5 seconds. However, if this later query isexecuted very often, the end-user will notice no improvement, but the system will - the benefit will only appear at peak-time. Just the difference between driving a car the maximum speed of which is just above the speed limit and one which can go much faster. You won't notice much of a differencein town (normally :-)), but it may make a difference when overtaking a lorry (truck) in a steep slope.

I tend to think that when trying to improve performance somewhere, you have two things to deliver. Some spectacular visible gain for the show. But you must also try to improve the overall behaviour of the system - if, once again, some queries are executed at a very high rate.

Regards,

Stephane Faroult

RoughSea Ltd
http://www.roughsea.com

On Tue, 07 Sep 2004 13:24 , ryan_gaffuri_at_comcast.net sent:

I believe its Mogens chapter in the Tales of the Oak Table book where he saidhe found with 10g that LIOs and CPU usage do not necessarily correspend. He argues that tuning queries should be explicitly based on elapsed time. My understanding of LIOs is that every LIO is a buffer cache latch get, so even if you do not use up more CPU you are incurring serialization and under concurrency can cause performance problems. I have seen queries go from 20,000 LIOs down to 300 with a very small performance improvement. Is it worth it to spend the time to do this?
BTW, its a very good book. The chapter by Dave Ensor on the history of Oracleis one of the best chapters you can find anywhere. I hope he writes more now that he is retired.
--

To unsubscribe -
oracle-l-request_at_freelists.org[1]','','','')">oracle-l-request_at_freelists.org[2] To search the archives - http://www.freelists.org/archives/oracle-l/[3]

• Links --- 1 javascript:parent.opencompose('<a href= 2 javascript:parent.opencompose('oracle-l-request_at_freelists.org','','','') 3 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle-l%2F
  --
  To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/