RE: Any changes in 10g CBO behavior with temporaty tables?
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: <gorbyx@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Jan 2006 16:03:08 -0800
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
