Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> re: dbms_stats messing up sys schema

re: dbms_stats messing up sys schema

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 09 Nov 2006 10:02:47 -0700
Message-Id: <20061109170230.03F684A5AF1@turing.freelists.org>


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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 09 2006 - 11:02:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US