RE: Should you still tune queries by LIOs?
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 7 Sep 2004 10:44:49 -0500
BUT...
1. A lot more than just CPU gets consumed in proportion to your LIO =
count.
Latch acquisitions occur in rough proportion to LIO count. Latch
acquisitions are serial.
2. It's not just a matter of whether a program has an important business
function. If a program does 20,000 LIOs when it could have done the job =
with
20, it can cause exponential response time degradation for everyone if
hundreds of users run that program simultaneously.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 9/20 Hartford, 10/18 New Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stephane Faroult
Sent: Tuesday, September 07, 2004 9:03 AM
To: oracle-l@xxxxxxxxxxxxx; ryan_gaffuri@xxxxxxxxxxx
Subject: Re: Should you still tune queries by LIOs?
=20
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.=20
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,=20
Stephane Faroult=20
RoughSea Ltd=20
http://www.roughsea.com=20
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?=20
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.=20
--
To unsubscribe -
oracle-l-request@xxxxxxxxxxxxx[1]','','','')">oracle-l-request@freelists.=
org
[2]
To search the archives - //www.freelists.org/archives/oracle-l/[3]
--- Links ---
1 javascript:parent.opencompose('<a href=3D
2
javascript:parent.opencompose('oracle-l-request@xxxxxxxxxxxxx','','','')
3
modules/refer.pl?redirect=3Dhttp%3A%2F%2Fwww.freelists.org%2Farchives%2Fo=
racle
-l%2F
--
To unsubscribe - =
mailto:oracle-l-request@xxxxxxxxxxxxx&subject=3Dunsubscribe=20
To search the archives - //www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
