Re: Need opinions on "Compute Statistics"

Sybrand Bakker wrote:
>
> On 4 Jun 2003 08:11:31 -0700, aoshell2_at_yahoo.com wrote:
>
> Answers embedded
>
> >I would like to get some opinions on the usage of the ANALYZE
> >STATISTICS command. I am using Oracle 8.1.6 and am doing some data
> >loads for large tables.
> >
> >Procedure is designed as follows:
> >- truncate table, drop indexes, insert data, recreate indexes, ANALYZE
> >STATISTICS
> >
> >1. If I use a statement like "ANALYZE TABLE A_PRODUCT_DETAILS COMPUTE
> >STATISTICS" is this computing the statistics on the indexes also?
> NO. You need the cascade option
>
> If
> >not, do I really need to since I am recreating them just prior to
> >issuing the statement and have done a TRUNCATE which is supposed to
> >RESET indexes?
>
> Not ANALYZing the indexes would make the ANALYZE TABLE pretty useless.
>
> The same applies to statistics without histograms.
>
> >
> >2. What is the school of though on ESTIMATING statistics and what
> >percent would be recommended for the fastest execution while retaining
> >some level of accurracy. Largest table is about 2 million rows and is
> >taking about 23 minutes.
>
> run the ANALYZE during off-peak hours. I must laugh about making a
> problem of 23 minutes analyze. Please keep in mind MANY statististical
> attributes are INACCURATE using estimate.
>
> >
> >3. Should i be using the DBMS_STATS package instead and if so why?
>
> ANALYZE has been desupported by Oracle. DBMS_STATS generates more
> accurate statistics, can be run in parallel and you can play around
> with multiple variants.
> >
> >Thanks for all input - I can read the Oracle manuals but it's nice to
> >get some opinions from those in the field.
> >
>
> I think 80 percent of my answers above come directly from the manual.
> Please keep in mind Usenet is a volunteer business.
>
> >Cheers!
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

"The same applies to statistics without histograms."

An excessive number of histograms merely serves to pollute the dictionary cache and add to parse times...

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"