Pending Statistics - 11GR2 - Bug !?!?!

Its now possible in Oracle 11g to gather optimizer statistics but not have them published immediately. Instead the statistics gathered for these objects are displayed in USER_*_PENDING_STATS. These statistics will be used by Optimizer if  initialization parameter OPTIMIZER_USE_PENDING_STATS is set to TRUE and running the necessary queries. Once you are happy with the pending statistics you can publish them.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y
  4  )
  5  as
  6  select rownum, mod(rownum,5)
  7  from dual
  8  connect by level <= 10000;

Table created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     cascade => true,
  7     method_opt=>'for all columns size 1');
  8  end;
  9  /
 
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:35:30 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 2
X                 2

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

Now to setup this Pending Stats we need to call this SET_TABLE_PREFS method defined in DBMS_STATS Api.

rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3     ownname => user,
  4     tabname => 'T',
  5     pname => 'PUBLISH',
  6     pvalue => 'FALSE');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> 100,
  6     cascade => true,
  7     method_opt=>'for all columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T';
TABLE_NAME                     LAST_ANALYZED
------------------------------ -----------------------
T                              14-apr-2011 12:36:38 am

Elapsed: 00:00:00.03

rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5
X               255

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

As you see this Pending statistics is available in USER_*_PENDING_STATS and doesn't get published.

rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:35:30 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 2
X                 2

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

But it looks like a BUG in 11GR2 (11.2.0.1.0) When Index comes in place along with Tables. Here is the Testcase to demonstrate that !

rajesh@ORA11GR2> select * from v$version;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y
  4  )
  5  as
  6  select rownum, mod(rownum,5)
  7  from dual
  8  connect by level <= 10000;

Table created.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y);

Index created.

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     cascade => true,
  7     method_opt=>'for all indexed columns size 1');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:41:14 am

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 2

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3     ownname => user,
  4     tabname => 'T',
  5     pname => 'PUBLISH',
  6     pvalue => 'FALSE');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> 100,
  6     cascade => true,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -----------------------
T                              14-apr-2011 12:41:42 am

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:41:14 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

This looks fine now. Since Pending statistics doesn't distrubed the Existing statistics. Now see what happens to Histograms.

rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

Latest Histograms Buckets has got updated in USER_TAB_HISTGRM_PENDING_STATS which is fine, but going back to Orginal Histogram data dictionary it got reflected. ( Even if PUBLISH = FALSE)  but it **SHOULD NOT**.

rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

This looks like a BUG in 11GR2 and Tom kyte confirms the same in asktom.