RE: Should you still tune queries by LIOs?

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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Stephane Faroult
Sent: Tuesday, September 07, 2004 9:03 AM To: oracle-l_at_freelists.org; ryan_gaffuri_at_comcast.net 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_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?=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_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=3D 2 javascript:parent.opencompose('oracle-l-request_at_freelists.org','','','') 3 modules/refer.pl?redirect=3Dhttp%3A%2F%2Fwww.freelists.org%2Farchives%2Fo= racle
  -l%2F
  --
  To unsubscribe - = mailto:oracle-l-request_at_freelists.org&subject=3Dunsubscribe=20 To search the archives - http://www.freelists.org/archives/oracle-l/

--

To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe To search the archives - http://www.freelists.org/archives/oracle-l/ Received on Tue Sep 07 2004 - 10:43:48 CDT