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: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Fri, 10 Nov 2006 09:55:49 +0100
Message-ID: <486b2b610611100055u4afb1241k6ecd451b06e9bb15@mail.gmail.com>


Wolfgang,

could you provide some more details why gathering histograms on all columns can result in bad performance ? Isn't it generally good practice to provide the optimizer with as much information as possible ?

Stefan

On 11/9/06, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 10 2006 - 02:55:49 CST

Original text of this message

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