RE: inefficient sql

  • From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
  • To: "ric.van.dyke@xxxxxxxxxx" <ric.van.dyke@xxxxxxxxxx>, "IIotzov@xxxxxxxxxxxxxxx" <IIotzov@xxxxxxxxxxxxxxx>, "Chris.Stephens@xxxxxxx" <Chris.Stephens@xxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 30 May 2012 19:35:42 +0200

I have not tested this (yet), but I don't think the logical reads are increased 
when you do (serial) direct reads. So you might want to summate the direct 
reads with the logical reads when you are using these kind of ratio's.

 
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Ric Van Dyke
Sent: woensdag 30 mei 2012 16:23
To: IIotzov@xxxxxxxxxxxxxxx; Chris.Stephens@xxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: inefficient sql

>> This ratio is, however, practically meaningless for SQLs that do
aggregates, such as COUNT, SUM, AVG, etc.  Separating aggregate from
regular SQLs would be the biggest challenge for an automated report.


Yes,  you apply the ratio to the step just before the aggregation or
grouping is done.   You can get this from stat lines or a 10046 trace or
data from various v$ views where you can see the rows returned per RSO.
Then just apply the ratio to that line before the aggregation takes
place, this gives you a better idea of how that SQL is doing from a LIO
perspective.  Doing this in an automated report would be tricky to say
the least.  

In the end you can have a report that will tell you about SQL with a
high LIO/ROWs ratio, but you will have to be smart enough to figure out
if that SQL really needs to be worked on or not.  If it really were that
simple to have a report that does this, I'm pretty sure Oracle would
already have it. 

-------------------------
Ric Van Dyke
Education Director 
Hotsos Ltd

--
http://www.freelists.org/webpage/oracle-l


Other related posts: