Re: inefficient sql

  • From: Martin Berger <martin.a.berger@xxxxxxxxx>
  • To: Chris.Stephens@xxxxxxx
  • Date: Fri, 1 Jun 2012 19:19:00 +0200

Chris,

I once started (in an pure OLTP system) to report all Statements which
used TEMP to our developers.
After about the 2nd report they freaked and I was 'kindly asked' not
to do these reports anymore.

Thinking about LIOs / statement is so much away from that ... you are
a lucky man ;-)

Martin

On Tue, May 29, 2012 at 10:14 PM, Stephens, Chris
<Chris.Stephens@xxxxxxx> wrote:
> I'm interested in creating a daily report to run in our development 
> environments to spot inefficient SQL early in the process.
> I've already got one that lists top ten highest elapsed time and top ten most 
> frequently executed.  They have helped tremendously in focusing on the right 
> SQL.  However, there is often SQL here that makes its way into integration 
> and production that could be improved upon. (Yes I know where SQL falls in 
> the optimization hierarchy and am well aware that business tasks are what are 
> important but these reports have proved their value over and over.)
>
> I'm pretty confident that a ratio of LIO's to rows returned by each row 
> operation in an SQL execution plan is a good indicator of SQL efficiency.  I 
> think I've heard this in a few different presentations.  I don't, however, 
> recall what that ratio should be or if I'm misremembering completely.
>
> What do you all consider good indicators of inefficient SQL and how to you 
> identify those statements?
>
> Thanks!
> Chris
--
http://www.freelists.org/webpage/oracle-l


Other related posts: