RE: inefficient sql

From: D'Hooge Freek <Freek.DHooge_at_uptime.be>
Date: Wed, 30 May 2012 19:35:42 +0200
Message-ID: <4814386347E41145AAE79139EAA398981CC0DC6C50_at_ws03-exch07.iconos.be>



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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke Sent: woensdag 30 mei 2012 16:23
To: IIotzov_at_newsamerica.com; Chris.Stephens_at_adm.com; oracle-l_at_freelists.org 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
Received on Wed May 30 2012 - 12:35:42 CDT

Original text of this message