analyze vs dbms_stats

From: David <thump_at_cosmiccooler.org>
Date: Fri, 25 Jun 2004 09:37:03 -0700 (PDT)
Message-ID: <3815.64.37.153.21.1088181423.squirrel@www.cosmiccooler.org>

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_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:33:53 CDT