Tom:
This Pending statistics stuff work's properly in Index absence. However with Index in place Statistics still remains good, Only Histograms becomes problem
Table Without index - Things goes fine ( Statistics + Histograms )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>
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>
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>
rajesh@ORA11GR2>
Table With Index ( Statistics goes fine but Histograms Fails )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> 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>
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>
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>
rajesh@ORA11GR2>