You can prevent it by not gathering histograms using sampling.
your method_opt
"method_opt=>'FOR ALL COLUMNS SIZE 1 FOR ALL INDEXED COLUMNS'"
contains two directives with the second 'FOR ALL INDEXED COLUMNS
[size 75]' overwriting the first 'FOR ALL COLUMNS SIZE 1'
i.e. you are collecting column statistics with histograms on all
indexed columns and ONLY on indexed columns. The use of a temp table
when collecting histograms on (multiple) columns using sampling - 30
percent in your case - is deliberate. It ensures that all histograms
get gathered from the same sample thus preserving any correlations
between columns.
In order to avoid the creation of the temporary tables:
(a) gather base column statistics - with optional sampling - for all
columns using method_opt=>'FOR ALL COLUMNS SIZE 1'
(b) gather histograms for select columns WITHOUT SAMPLING:
method_opt=>'FOR COLUMNS SIZE 254 cola,colb,colc...'
gathering histograms with sampling is contradictory. You want the
histogram because the data distribution is skewed, but by sampling
you are liable to miss some subtleties of the distribution skew.
'for all indexed columns' is a stupid option. As if histograms were
ever only useful on indexed columns. Indiscriminantly gathering
histograms, i.e. on all or an all indexed columns, can be (and has
been) dangerous to sql performance.
At 10:25 PM 11/5/2006, cosmin ioan wrote:
>hi all,
>I'm doing a bunch of serial jobs of the form:
>
>exec dbms_stats.gather_table_stats( ownname=> 'xxx',
>tabname=>'xxxx',estimate_percent=> 30,method_opt=>'FOR ALL COLUMNS
>SIZE 1 FOR ALL INDEXED COLUMNS', cascade=> true);
>
>for many tables in a schema.
>
>I have noticed that, for some tables only, even small ones, Oracle
>decides it's easier to create a global temporary table in the
>SYS.ora_temp_xyz rather than simply doing the "select..."
>
>this to me, is messing up too much the SYS data dictionary--
>creating tons of objects of this nature...
>
>Why does this happen and how can I prevent it?
>thx,
>Cosmin
>
>
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
