RE: Any changes in 10g CBO behavior with temporaty tables?

Alex,

Keep in mind that the "optimizer_dynamic_sampling" parameter defaults to 1 in 9i and 2 in 10g (R1 and R2). This parameter was introduced with the objective of overcoming the absence of stats in GTTs. From a cut-n-paste of a relevant portion of Ch 14 from my book:

At level 1, the optimizer samples all tables that do not have statistics if the following criteria are met:

• There is at least one table with no statistics in the query.
• This table is joined to another table or appears in a subquery or non-mergeable view.
• This table has no indexes.
• This unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table.

An undocumented parameter _OPTIMIZER_DYN_SMP_BLKS controls the number of blocks used for sampling and is set at 32 by default. At level 2, all tables and objects that are not analyzed will be sampled regardless of the exclusions
at level 1, and the number of blocks sampled doubles to 64.

The level of 2 in 10g would effectively double the number of blocks sampled and overrides the exclusion clauses at level 1 and this might have contributed to yout differences. If you had set some default stats for your GTTs, this does not hold of course!

As well, not all 9i databases set/use System Stats while 10g uses a default value that is installed during Db creation time. (I have not really tested what happens to Systems stats if you clone a Db that was created on a less powerful system).

Regards,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)  

Co-Author: Oracle Database 10g Insider Solutions http://www.amazon.com/exec/obidos/tg/detail/-/0672327910/  

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alex Gorbachev Sent: Tuesday, January 17, 2006 3:07 PM
To: ORACLE-L
Subject: Any changes in 10g CBO behavior with temporaty tables?

Hi all,

We have the cases when 10g generates execution plans differently than 9i. Stats on tables are the same. Is anyone aware of any changes/improvements in temporary tables handling by CBO in 10.1.0.4 compare to 9.2.0.5?

TIA,
Alex

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l