Re: Any changes in 10g CBO behavior with temporaty tables?
- From: Alex Gorbachev <gorbyx@xxxxxxxxx>
- To: John Kanagaraj <john.kanagaraj@xxxxxxx>
- Date: Wed, 18 Jan 2006 09:31:24 +0100
Thanks for update John. Yep we thought about dynamic sampling but all
temporary tables had the same stats (non empty) and it looks like
automatic stats gathering doesn't affect temp tables (stats can be
locked anyway).
In fact, stats were set long time ago to some representtive values but
at some relatively recent point were mistakenly recollected (which was
wrong, of course) but didn't cause bad execution palns in 9i. That
what led me to the idea that 9i didn't realy used temp tables stats
correctly. Will try to test if I get some time.
Alex
2006/1/18, John Kanagaraj <john.kanagaraj@xxxxxxx>:
> 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
>
>
>
--
Best regards,
Alex Gorbachev
--
//www.freelists.org/webpage/oracle-l
Other related posts:
