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: Analyze vs. dbms_stats with Partitioning

Re: Analyze vs. dbms_stats with Partitioning

From: Dimitre Radoulov <cichomitiko_at_gmail.com>
Date: Wed, 1 Jun 2005 21:55:54 +0200
Message-ID: <016101c566e3$f14de330$2a00a8c0@IBME1D11967173>


As Tom Kytes says:


Tom,

   Could you please tell me if there are any other important differences, advantages with DBMS_STATS over ANALYZE other than the points listed below.

  1. DBMS_STATS can be done in parallel
  2. Monitoring can be done and stale statistics can be collected for changed rows using DBMS_STATS.

Thanks,
Suresh

and we said...

you can import/export/set statistics directly with dbms_stats

it is easier to automate with dbms_stats (it is procedural, analyze is just a
command)

dbms_stats is the stated, preferred method of collecting statisttics.

dbms_stats can analyze external tables, analyze cannot.

DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other statistics. For example, the table statistics gathered by DBMS_STATS include the number of rows, number of blocks currently containing data, and average row length but not the number of chained rows, average free
space, or number of unused data blocks.

dbms_stats (in 9i) can gather system stats (new)

ANALYZE calculates global statistics for partitioned tables and indexes instead
of gathering them directly. This can lead to inaccuracies for some statistics,
such as the number of distinct values. DBMS_Stats won't do that.

Most importantly, in the future, ANALYZE will not collect statistics needed by
the cost-based optimizer.


HTH Dimitre

> Mike,
>
> One big advantage is that dbms_stats can gather stats in parallel.
> Analyze cannot.
>
> But a bigger reason is that analyze goes away in 10g ( if I remember
> correctly).
>
> Tom
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hand, Michael T
> Sent: Wednesday, June 01, 2005 2:59 PM
> To: oracle-l_at_freelists.org
> Subject: RE: Analyze vs. dbms_stats with Partitioning
>
> Ok, first misconception reached. I thought analyze didn't handle
> partitioned segments but a quick RTFM and trace shows normal range
> partition pruning with Analyze generated stats. So is there an
> advantage of dbms_stats here? Or is the advantage the addition features
> dbms_stats brings withit?
>
> Mike
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hand, Michael T
> Sent: Wednesday, June 01, 2005 1:29 PM
> To: oracle-l_at_freelists.org
> Subject: Analyze vs. dbms_stats with Partitioning
>
> I am in the initial stages of experimenting with Partitioning, and
> therefore also moving from analyze to dbms_stats. Does one have to
> delete the analyze-created stats before generating the dbms_stats-based
> ones, or is that a Oracle-legend I heard/read in the distant past? Is
> there anyway to determine which means was used to generate the stats
> after the fact?
>
> This is for V9.2.0.5 on Tru64.
>
> Thanks,
> Mike H
>
> P.S. Am reading several articles by ANanda and JLewis on the subject.
>
> --=3D20
> This transmission is intended only for use by the addressee(s) named
> herein=3D
> and may contain information that is proprietary, confidential and/or
> legal=3D
> ly privileged. If you are not the intended recipient, you are hereby
> notifi=3D
> ed that any disclosure, copying, distribution, or use of the information
> co=3D
> ntained herein (including any reliance thereon) is STRICTLY PROHIBITED.
> If =3D
> you received this transmission in error, please immediately contact the
> sen=3D
> der and destroy the material in its entirety, whether in electronic or
> hard=3D
> copy format. Thank you.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 01 2005 - 16:01:13 CDT

Original text of this message

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