Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: 8i to 9i migration, performance issues

Re: 8i to 9i migration, performance issues

From: Jonathan Lewis <>
Date: Sun, 14 Mar 2004 21:57:57 +0000 (UTC)
Message-ID: <c32kh5$dm$>

Notes in-line.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar

"Paul Drake" <> wrote in message

> one of the things that was not covered in the above doc (thanks for
> the link) was that of dynamic sampling. If you are using global
> temporary tables (GTTs) in 9.2 and have optimizer_dynamic_sampling = 2
> (or above) oracle will gather stats on the GTTs when determining the
> execution plan.
> This was covered in detail at the Hotsos Symposium last week, but you
> are best off to test for yourself, with a 10053 trace.
> I would imagine that the predicate sampling available with
> optimizer_dynamic_sampling = 4 would also not be available if
> optimizer_features_enable is set to 8.1.7.
Dynamic_sampling will, indeed, be disabled if this parameter is set to 8.1.7
> this subject is incredibly interesting to me, as I still have a
> database where this is the setting, as some queries were not executing
> in a reasonable amount of time (after migrating to 9.2) but as
> ofe=8.1.7 "fixed" the problem,
One of the disabled features is subquery unnesting, and one of the bugs in the 9.2 optimizer is the fact that the costing for unnested subqueries is extremely high (fixed in 10.1), so unnesting happens pretty much everywhere it can. This means you can end up with hideously inefficient unnests, where a filtered subquery would be much better. Alternative workarounds: a) set _unnest_subquery = true (check name of parameter) b) Find the worst queries, check that they are unnesting, then put the /*+ no_unnest */ hint just after the select in the subquery that you do not want unnested. we're kinda stuck there (until each
> query is identified, tuned and rolled back into the app). We might
> just be leaving it set as such until the migration to 10.1 is planned,
> and catch it in testing there.
> Pd
Received on Sun Mar 14 2004 - 15:57:57 CST

Original text of this message