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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
