Re: Should you still tune queries by LIOs?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, ryan_gaffuri@xxxxxxxxxxx
- Date: Tue, 7 Sep 2004 16:03:10 +0200
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@xxxxxxxxxxx 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@xxxxxxxxxxxxx[1]','','','')">oracle-l-request@xxxxxxxxxxxxx[2]
To search the archives - //www.freelists.org/archives/oracle-l/[3]
--- Links ---
1 javascript:parent.opencompose('<a href=
2 javascript:parent.opencompose('oracle-l-request@xxxxxxxxxxxxx','','','')
3
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle-l%2F
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
