RE: More on dbms_stats

Just be aware of the side-effect that the compute statistics clause will wipe out any histogram on the column(s) on which index is being created/rebuilt (atleast as of 9i).

Regards,
Charu

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling Sent: 10 August 2005 19:11
To: Thomas.Mercadante_at_labor.state.ny.us
Cc: ldutra_at_toyota.com.br; Deepak Sharma; oracle-l_at_freelists.org Subject: Re: Too many rows reported for index in tkprof

Actually don't use either - use create/rebuild index ... compute statistics;

You get the statistics "for free" as part of the index creation or rebuild. In oracle 10 that's even the default.

Mercadante, Thomas F (LABOR) wrote:
> Probably should have used "analyze" :O
>
>
>>A trivial, but important question is whether you
>>analyzed the index after creating it?
>
>
> Sure, with DBMS_STATS.
>

--
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l




-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala
Sent: 11 August 2005 00:19
To: mgogala_at_allegientsystems.com
Cc: breitliw_at_centrexcc.com; oracle-l_at_freelists.org
Subject: Re: More on dbms_stats



On 08/10/2005 05:48:23 PM, Mladen Gogala wrote:


> Wolfgang Breitling wrote:

>

> > It potentially, and likely, creates an awful lot of histograms exactly

> > BECAUSE the method_opt setting of 'for all columns size 1' is ignored

> > and replaced with 'for all columns size auto'.

> >

> I prefer skewed columns, that is the columns from SYS.COL_USAGE$.



As was gently and privately pointed out, I badly mixed things up yet again.
The table SYS.COL_USAGE$ is used by 'FOR ALL COLUMNS SIZE AUTO' method while
FOR ALL COLUMNS SIZE SKEWONLY computes histograms for all columns. I tried
accusing the manuals, but they did a good job explaining that:

- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have
histograms.
- AUTO : Oracle determines the columns to collect histograms based on data
distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on
the data distribution of the columns.

I must be growing old.
--
Mladen Gogala
http://www.mgogala.com


--
http://www.freelists.org/webpage/oracle-l


*********************************************************
Disclaimer:

The contents of this E-mail (including the contents of the enclosure(s) or attachment(s) if any) are privileged and confidential material of MBT and should not be disclosed to, used by or copied in any manner by anyone other than the intended addressee(s).   In case you are not the desired addressee, you should delete this message and/or re-direct it to the sender.  The views expressed in this E-mail message (including the enclosure(s) or attachment(s) if any) are those of the individual sender, except where the sender expressly, and with authority, states them to be the views of MBT.

This e-mail message including attachment/(s), if any, is believed to be free of any virus.  However, it is the responsibility of the recipient to ensure that it is virus free and MBT is not responsible for any loss or damage arising in any way from its use

*********************************************************
--
http://www.freelists.org/webpage/oracle-l