Re: Lies, damn lies and statistics

Karen Morton wrote:
>
> Stephanie,

Cough, cough.

> I've ran into the exact same thing that statistics don't always tell the
> real story and that the bottom-line has to be elapsed time.
>
> I am curious however as to how you wrote the best performing (fastest)
> query. I have a client who uses the exact same structure and methodology
> you mention (innate tree structure, views with outer joins, etc) and they
> have some pretty long and nasty queries (I see your 176 step execution plan
> and raise you to 298!). I've had pretty decent success re-writing these
> monsters but would be interested in hearing your different approaches to
> re-writing such queries to see if I can pick up a few ideas I hadn't thought
> of. Any generic ideas or methods you use might prove helpful to many.
>
> Thanks,
> Karen
>
> Karen Morton
> Morton Consulting
> karen_at_morton-consulting.com
>

Karen,

  The only effective method I know is to take an axe and cut everything which is not strictly necessary. More often than not, when you replace a view by its query, you realize that you only need some of the columns of the view, and that some (possibly not fantastically efficient) joins in the view can be happily disposed of. You also find useless joins outside of views.
In the query mentioned above, the hard-core was a

   start with a_id in

         (select a.a_id
          from a, b, c, d
          where b.a_id = a.a_id
            and <condition on b>
            and c.b_id = b.b_id
            and <condition on c>
            and d.c_id = c.c_id
            and <condition on d>
          union
          select a.a_id
          from a, b, e, f
          where b.a_id = a.a_id
            and <condition on b>
            and e.b_id = b.b_id
            and <condition on e>
            and f.e_id = e.e_id
            and <condition on f>
          union
          ...)

Why joining on a, since the primary key of a (which is what we want) can be found as foreign key in b? Why repeat the condition on b in the second part of the union ? And so on.
Once everything was reduced to something I could grasp and constituent pieces, it was far easier to twitch the code here and there and get decent results.
'Far easier' is of course a way of speaking. I would be glad if there were recipes. I usually start with something twice as bad as the original query, which a bit of additional tuning takes to about ten times slower than what you want to improve. At which point, about 2 hours after having started, I am beginning to get a better feeling of the data, take another cup of coffee (and possibly another one), scrap everything I have done so far and get some results. Another example today (3rd of my 4 customers this week) where, starting with 170,000 or about LIOs I have managed after much effort to peak at 500,000 before reaching the finish line at 2,000. Phew, reputation saved, but it was hot.

HTH, Stephane Faroult
Oriole Software

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).