RE: analyze vs dbms

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Fri, 25 Jun 2004 11:49:03 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93B47@qtiexch2.qgraph.com>

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_at_qtiworld.com                  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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Fri Jun 25 2004 - 11:46:58 CDT

RE: analyze vs dbms_stats