Notes in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
"Paul Drake" <drak0nian_at_yahoo.com> wrote in message
news:1ac7c7b3.0403140852.4a4fd0c1_at_posting.google.com...
>
> 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
