Friday, October 10, 2008

How to know whether I use analyze or DBMS_STATS

Automatic statistics gathering should be sufficient for most of the cases. And automatic statistics gathering is done by default during an overnight batch window. In order to know more about automatic statistics gathering just have a look at Analyze and collecting Statistics on Tables Schemas Database Index and About STATISTICS_LEVEL parameter in Oracle

In most cases automatic statistics gather is sufficient but still you may need to gather manually statistics if you do huge insert or update or delete on the table. You might know we can gather statistics by two ways.

1)Analyze command.
2)DBMS_STATS package.

By querying from the dba_tables view you can say by which method you gathered statistics. Let's see it with an example.
I am creating two tables named with_analyze and with_dbms_stats. On table with_analyze use analyze to estimate statistics and on table with_dbms_stats use DBMS_STATS package to gather statistics.

SQL> Create table with_analyze(col1 number);

Table created.

SQL> Create table with_dbms_stats(col1 number);

Table created.

SQL> analyze table with_analyze compute statistics;

Table analyzed.

SQL> exec dbms_stats.gather_table_stats('SYS','WITH_DBMS_STATS');

PL/SQL procedure successfully completed.

Now let's have a look at GLOBAL_STATS column of user_tables(contain only currently user owned table) or dba_tables(contain all table) table or all_tables(contain all tables to which current user has permission).

If you gather statistics on a table with analyze command then GLOBAL_STATS field will be FALSE for that table.

If you gather statistics on a table with DBMS_STATS package then GLOBAL_STATS field will be TRUE for that table.
SQL> select table_name,last_analyzed, GLOBAL_STATS from dba_tables where 
table_name in ('WITH_ANALYZE','WITH_DBMS_STATS');


TABLE_NAME LAST_ANAL GLO
------------------------------ --------- ---
WITH_ANALYZE 10-OCT-08 NO
WITH_DBMS_STATS 10-OCT-08 YES

Related Documents
Analyze and collecting Statistics on Tables Schemas Database Index
About STATISTICS_LEVEL parameter in Oracle

No comments:

Post a Comment