Re: 9i Slow SQL Performance - EXPERTS apply within

"Johne_uk" <edgarj_at_tiscali.co.uk> wrote in message news:1144250472.665731.226580_at_j33g2000cwa.googlegroups.com...
>I did the level 12 trace and got the following results. Time seems to
> be in the fetch statement. I guess Optimising the query is the only way
> ahead then.
>
>

You said in your original post that:

    4. Ran Explain Plans on both 9i instances (execution plans were     identical as were stats).

But the plan you showed as the original fast plan started with

Rows Row Source Operation

-------  ---------------------------------------------------
     21  FILTER
     22   NESTED LOOPS
     22    NESTED LOOPS
      2     TABLE ACCESS BY INDEX ROWID MA_COMPANY
      2      INDEX UNIQUE SCAN (object id 108099)

and in the more recent slow case started with

Rows Row Source Operation

-------  ---------------------------------------------------

    30 FILTER
114690 SORT GROUP BY
443468 FILTER

443468     NESTED LOOPS
   116      NESTED LOOPS

Without analyzing too closely, this looks like a case of complex view merging taking place. For example an aggregate (group by) view being unwrapped into the main query, and the "group by" being done after all joins are completed.

9i does this more or less by default, 8i hardly ever. The behaviour is affected by parameter

    _complex_view_merging
which defaults to false in 8i and true in 9i. Possibly your 'fast' 9i has had this parameter set back to false (or even had its setting of optimizer_features_enable set back to 8.x.x)

For test purposes try putting no_merge(view_name) hints into your query - where 'view_name' is the name a likely aggregate view in the FROM clause.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html