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
No comments:
Post a Comment