RE: dbms_utility and dbms_stat difference

From: Madhavan Amruthur <mad5698_at_fastmail.fm>
Date: Fri, 23 Aug 2002 11:45:30 -0800
Message-ID: <F001.004BE98E.20020823114530@fatcity.com>

Hi Chuan,

> Just wonder whether "analyze table <name> estimate statistics"
> generates the stats at partition level. But for sure, it does not
> generate the stats at table level. Please correct me if wrong.

Yes it does here is a sample.... (sorry for the long post)

SQL> analyze table atest delete statistics   2 /

Table analyzed.

SQL> select
table_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS')   2 from user_tables
  3 where table_name = 'ATEST'
  4 /

TABLE_NAME                       NUM_ROWS TO_CHAR(LAST_ANALY
------------------------------ ---------- ------------------

ATEST SQL> select
table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS')   2 from user_tab_partitions
  3 where table_name = 'ATEST'
  4 /

TABLE_NAME                     PARTITION_NAME                  

NUM_ROWS

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

---

TO_CHAR(LAST_ANALY

---

ATEST                          P1


ATEST                          P2

SQL> analyze table atest estimate statistics sample 10 percent   2 /

Table analyzed.

SQL> select
table_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS')   2 from user_tables
  3 where table_name = 'ATEST'
  4 /

TABLE_NAME                       NUM_ROWS TO_CHAR(LAST_ANALY
------------------------------ ---------- ------------------
ATEST                                 199 23-AUG-02-11-33-21

SQL> select
table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS')   2 from user_tab_partitions
  3 where table_name = 'ATEST'
  4 /

TABLE_NAME                     PARTITION_NAME                  

NUM_ROWS

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

---

TO_CHAR(LAST_ANALY

---

ATEST                          P1                                    

99
23-AUG-02-11-33-21

ATEST                          P2                                   

100
23-AUG-02-11-33-21 SQL> analyze table atest partition (p1) estimate statistics sample 10 percent
  2 /

Table analyzed.

SQL> exec dbms_lock.sleep(10)

PL/SQL procedure successfully completed.

SQL> / Table analyzed.

SQL> select
table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS')   2 from user_tab_partitions
  3 where table_name = 'ATEST'
  4 /

TABLE_NAME                     PARTITION_NAME                  

NUM_ROWS

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

---

TO_CHAR(LAST_ANALY

---

ATEST                          P1                                    

99
23-AUG-02-11-33-31

ATEST                          P2                                   

100
23-AUG-02-11-33-21
> As Connor said, dbms_stats runs a lot heavier than analyze, I think,
> it's because it gathers the stats at table and partition level and
> related indexes.

analyze also generates statistics for at table, partition and indexes. If you specify analyze table <name> compute statistics (generates for all associated objects) If you use for table , for all indexes, then it does only table level and index level.  

> In brief, I prefer to choose "analyze .. estimate statistics" for
> non-partition table and dbms_stats for partitioned table.

dbms_stats in my opinion executes much better and the parallelism is a big help.

Hope this helps.
Regards,
Madhavan
http://www.dpapps.com

-- 
Madhavan Amruthur
DecisionPoint Applications

-- 
http://fastmail.fm -- Does exactly what it says on the tin
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Madhavan Amruthur
  INET: mad5698_at_fastmail.fm

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 23 2002 - 14:45:30 CDT