analyze vs dbms_stats
- From: "David" <thump@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 25 Jun 2004 09:37:03 -0700 (PDT)
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 => 'STATION_TEST', -
tabname => 'MEMBERS', -
partname => NULL, -
estimate_percent => 30, -
block_sample => FALSE, -
method_opt => 'FOR ALL COLUMNS SIZE 254', -
degree => 0, -
granularity => 'DEFAULT', -
cascade => TRUE, -
stattab => NULL, -
statid => NULL, -
statown => NULL, -
no_invalidate => FALSE);
sample of stats info from analyze:
Table STATION_TEST.MEMBERS
- Number of rows : 14284780
- Number of blocks : 1623961
- Average row length : 192
Column RESTRICTION_CODE
- Number of distinct values : 0
- Number of nulls : 14284780
Column DAY_PHONE
- Number of distinct values : 51
- Number of nulls : 14273930
Column EVE_PHONE
- Number of distinct values : 44
- Number of nulls : 14284620
Column ISP_ID
- Number of distinct values : 10
- Number of nulls : 14284770
sample of dbms stats generated statistics:
Table STATION_TEST.MEMBERS
- Number of rows : 14283597
- Number of blocks : 1623961
- Average row length : 193
Column RESTRICTION_CODE
- Number of distinct values : 0
- Number of nulls : 14283597
Column DAY_PHONE
- Number of distinct values : 95
- Number of nulls : 14273027
Column EVE_PHONE
- Number of distinct values : 0
- Number of nulls : 14283467
Column ISP_ID
- Number of distinct values : 0
- Number of nulls : 14283577
Column USER_ID (NOT NULL)
- Number of distinct values : 14283597
- Number of nulls : 0
As it turns out analyze in this test is far more accurate. For instance
eve_phone actually has 66 distinct values.
Any ideas on the disparity? I am going to try compute, but apples to
apples reflect a big diff...
Thanks
- David
----------------------------------------------------------------
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:
