Re: inefficient sql

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_at_adm.com> 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