RE: analyze vs dbms_stats

  • From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 25 Jun 2004 11:49:03 -0500

Not that it directly answers your question, but I see that the method =
differs: you have ANALYZE collecting histograms with 254 buckets on =
indexed columns (and I believe implicitly on every other column with 1 =
bucket), while DBMS_STATS is generating 254-bucket histos on all =
columns.

There are other differences internal to ANALYZE and DBMS_STATs, but this =
one jumped out at me.

My $.02,
Rich

Rich Jesse                           System/Database Administrator
rjesse@xxxxxxxxxxxx                  Quad/Tech Inc, Sussex, WI USA



-----Original Message-----
Sent: Friday, June 25, 2004 11:37 AM
Subject: analyze vs dbms_stats


I'm trying to get our shop to convert from analyze to dbms_stats.
I'm running into some "strange" results though and wanted to see if I'm
missing something or you have some advice.

analyze command:
ESTIMATE STATISTICS SAMPLE 30
 PERCENT
 FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254

dbms_stats code I'm running:
exec DBMS_STATS.GATHER_TABLE_STATS ( -
   ownname    =3D>    'STATION_TEST', -
   tabname    =3D>    'MEMBERS', -
   partname   =3D>    NULL, -
   estimate_percent =3D> 30, -
   block_sample  =3D>   FALSE, -
   method_opt =3D>    'FOR ALL COLUMNS SIZE 254', -
   degree      =3D>     0, -
   granularity =3D>    'DEFAULT', -
   cascade     =3D>   TRUE, -
   stattab     =3D>  NULL, -
   statid      =3D> NULL, -
   statown     =3D>    NULL, -
   no_invalidate =3D>   FALSE);
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: