PRIVATE STATISTICS

posted Jan 31, 2012, 12:18 PM by Sachchida Ojha
In Oracle 11g, the stats gathering task has been divided into 2 parts. 1. Collection 2. Publishing (when they are made effective). By default, the stats are published as soon as they are collected, but you can change that.

begin

dbma_stats.set_table_prefs('DBAREF','EMP','PUBLISH','FALSE');

end;

SQL> select dbms_stats.get_prefs('PUBLISH','DBAREF','EMP') prefs from dual;

PREFS
----------
FALSE

After that delete all rows from from emp table and then gather the stats as usual.  After the stats gathering, if you check the table for the stats, you will see,

SQL> select last_analyzed,num_rows from user_tables where table_name='EMP';

LASt_ANAL              NUM_ROWS
----------------               ---------------------
31-JAN-12                10

Note that we still see 10 rows in num_rows column. This is because stats have not been published yet. they are in pending state which you can see  user_tab_pending_stats view.

Now you can publish the pending stats using

begin
dbms_stats.publish_pending_stats('DBAREF','EMP');
end;

Now if you see the num_rows in user_tables
SQL> select last_analyzed,num_rows from user_tables where table_name='EMP';

LASt_ANAL              NUM_ROWS
----------------               ---------------------
31-JAN-12                0



Comments