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
March 2004 Hotsos Symposium - The Burden of Proof
Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message
news:404723b6$0$19705$afc38c87_at_news.optusnet.com.au...
> > When I switched to cost based, Oracle assumed that the
> > generated temporary table had the standard 8,168 rows in
> > it and changed the latter part of the execution path to sort/merge
> > rather than nested loop full tablescan.
>
> Is this 9ir2? The 8168 rows, I mean?
>
9.2.0.4
But I ran the same thing on 10.1.0.1, and got
23,400+ for the same query, even when I
thought I had dynamic sampling enabled :(
>
> > When I enabled dynamic sampling at level 2 for the session,
> > Oracle then got a better idea of cardinality, and changed the
> > plan again.
>
>
> With something like this (all temp tables, all joined),
> where does the dynamic sampling kick-in? I mean, how does it
> decide at which point to do a sample and of which?
> Is there a rule somewhere?
>
The rule for session level, or query level hinting is that
"level 2" means all tables with no statistics where a
couple of other conditions are met (details in the
Perf Tuning Guide) but one of the comments there
is about 'would otherwise have to do an expensive
full tablescan' - and how will Oracle know whether
an in-memory temp table will be expensive if it
doesn't sample it anyway ? I'm still working on
dynamic_sampling and 'local' temporary tables
independently - give me more time and I'll get
around to what happens when they meet.
Received on Thu Mar 04 2004 - 07:29:46 CST
