More Tests on DBMS_STATS GATHER AUTO

After my posting of test results which show that options=>'GATHER AUTO' does override any specifications for ESTIMATE_PERCENT and METHOD_OPT, these test results below show that "GATHER AUTO" also overrides parameters set with SET_PARAM.
Once SET_PARAM is used, DBMS_STATS would use the specified values as the "new" defaults. However, with "GATHER AUTO" this does not happen. It merely ignores these values -- thus using a higher level of default values ('AUTO_SAMPLE_SIZE' and 'FOR ALL COLUMNS SIZE AUTO') :

col table_name format a25
col index_name format a29
col column_name format a25
col Sample_Perc format 999.99 hea 'S%age'

alter session set nls_date_format='DD-MON HH24:MI:SS';

select segment_type, segment_name, blocks from user_segments order by 1 desc,2;

SEGMENT_TYPE       SEGMENT_NAME                                                                          BLOCKS                     
------------------ --------------------------------------------------------------------------------- ----------                     
TABLE              SOURCE_TABLE                                                                            9216                     
TABLE              TEST_APPEND                                                                            60416                     
TABLE              TEST_BINDVAR_PEEKING                                                                    7680                     
INDEX              IND_TEST_APPEND                                                                        41344                     
INDEX              TEST_BINDVAR_PEEKING_COB_NDX                                                            3200                     
select num_rows from user_tables where table_name = 'TEST_APPEND';

  NUM_ROWS                                                                                                                          
----------                                                                                                                          
   4206140                                                                                                                          
select count(*) from test_append;

  COUNT(*)                                                                                                                          
----------                                                                                                                          
   3755880                                                                                                                          

select sysdate from dual;

SYSDATE                                                                                                                             
---------------                                                                                                                     
20-JUL 23:17:39                                                                                                                     

select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;

TABLE_NAME                    BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                                                   
------------------------- ---------- ---------- ----------- ------- ---------------                                                 
SOURCE_TABLE                    9172     652320       65232   10.00 20-JUL 23:09:41                                                 
TEST_APPEND                    60250    4206140      420614   10.00 20-JUL 23:09:53                                                 
TEST_BINDVAR_PEEKING            7600    1330390      133039   10.00 20-JUL 23:09:59                                                 
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;

TABLE_NAME                COLUMN_NAME               NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE    DENSITY HISTOGRAM                        
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------                  
SOURCE_TABLE              CREATED                           1330          0       65232  .00075188 NONE                             
SOURCE_TABLE              DATA_OBJECT_ID                    3924     599830        5249 .000254842 NONE                             
SOURCE_TABLE              GENERATED                            2          0       65232         .5 NONE                             
SOURCE_TABLE              LAST_DDL_TIME                     1374          0       65232 .000727802 NONE                             
SOURCE_TABLE              OBJECT_ID                        50422          0       65232 .000019833 NONE                             
SOURCE_TABLE              OBJECT_NAME                      28918          0       65232 .000034581 NONE                             
SOURCE_TABLE              OBJECT_TYPE                         38          0       65232 .026315789 NONE                             
SOURCE_TABLE              OWNER                               26          0       65232 .038461538 NONE                             
SOURCE_TABLE              SECONDARY                            2          0       65232         .5 NONE                             
SOURCE_TABLE              STATUS                               1          0       65232          1 NONE                             
SOURCE_TABLE              SUBOBJECT_NAME                      98     646920         540 .010204082 NONE                             
SOURCE_TABLE              TEMPORARY                            2          0       65232         .5 NONE                             
SOURCE_TABLE              TIMESTAMP                         1420          0       65232 .000704225 NONE                             
TEST_APPEND               CREATED                           1378          0      420614 .000725689 NONE                             
TEST_APPEND               DATA_OBJECT_ID                    4017    3864130       34201 .000248942 NONE                             
TEST_APPEND               GENERATED                            2          0      420614         .5 NONE                             
TEST_APPEND               LAST_DDL_TIME                     1425          0      420614 .000701754 NONE                             
TEST_APPEND               OBJECT_ID                        50442          0      420614 .000019825 NONE                             
TEST_APPEND               OBJECT_NAME                      30247          0      420614 .000033061 NONE                             
TEST_APPEND               OBJECT_TYPE                         41          0      420614 .024390244 NONE                             
TEST_APPEND               OWNER                               27          0      420614 .037037037 NONE                             
TEST_APPEND               SECONDARY                            2          0      420614         .5 NONE                             
TEST_APPEND               STATUS                               1          0      420614          1 NONE                             
TEST_APPEND               SUBOBJECT_NAME                     102    4171170        3497 .009803922 NONE                             
TEST_APPEND               TEMPORARY                            2          0      420614         .5 NONE                             
TEST_APPEND               TIMESTAMP                         1488          0      420614 .000672043 NONE                             
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                       10          0      133039         .1 NONE                             
TEST_BINDVAR_PEEKING      COUNTRY_OF_BIRTH                     1          0      133039          1 NONE                             
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                       10          0      133039         .1 NONE                             
TEST_BINDVAR_PEEKING      MONTH_NUMBER                        10          0      133039         .1 NONE                             
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                      10          0      133039         .1 NONE                             
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;

TABLE_NAME                INDEX_NAME                    LEAF_BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                    
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------                  
TEST_APPEND               IND_TEST_APPEND                     37570    3926410      392641   10.00 20-JUL 23:09:57                  
TEST_BINDVAR_PEEKING      TEST_BINDVAR_PEEKING_COB_NDX         2612    1337226      482304   36.07 20-JUL 23:10:00                  

select sysdate from dual;

SYSDATE                                                                                                                             
---------------                                                                                                                     
20-JUL 23:17:39                                                                                                                     

set feedback on
exec dbms_stats.set_param(pname=>'ESTIMATE_PERCENT',pval=>'25');

PL/SQL procedure successfully completed.

exec dbms_stats.set_param(pname=>'METHOD_OPT',pval=>'FOR ALL COLUMNS SIZE 50');

PL/SQL procedure successfully completed.

exec dbms_stats.set_param(pname=>'CASCADE',pval=>'FALSE');

PL/SQL procedure successfully completed.

set feedback off

REM  #######################Gathering Schema Stats #################
exec dbms_stats.gather_schema_stats(ownname=>'HEMANT',options=>'GATHER AUTO');

select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;

TABLE_NAME                    BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                                                   
------------------------- ---------- ---------- ----------- ------- ---------------                                                 
SOURCE_TABLE                    9172     461575      461575  100.00 20-JUL 23:17:53                                                 
TEST_APPEND                    60250    3755880     3755880  100.00 20-JUL 23:18:08                                                 
TEST_BINDVAR_PEEKING            7600    1330390      133039   10.00 20-JUL 23:09:59                                                 
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;

TABLE_NAME                COLUMN_NAME                 COUNT(*)                                                                      
------------------------- ------------------------- ----------                                                                      
SOURCE_TABLE              CREATED                          236                                                                      
SOURCE_TABLE              DATA_OBJECT_ID                   255                                                                      
SOURCE_TABLE              LAST_DDL_TIME                    237                                                                      
SOURCE_TABLE              OBJECT_NAME                      255                                                                      
SOURCE_TABLE              OBJECT_TYPE                       32                                                                      
SOURCE_TABLE              OWNER                             23                                                                      
SOURCE_TABLE              SUBOBJECT_NAME                   100                                                                      
SOURCE_TABLE              TIMESTAMP                        237                                                                      
TEST_APPEND               CREATED                          246                                                                      
TEST_APPEND               DATA_OBJECT_ID                   255                                                                      
TEST_APPEND               LAST_DDL_TIME                    242                                                                      
TEST_APPEND               OBJECT_NAME                      255                                                                      
TEST_APPEND               OBJECT_TYPE                       27                                                                      
TEST_APPEND               OWNER                             21                                                                      
TEST_APPEND               SUBOBJECT_NAME                   102                                                                      
TEST_APPEND               TIMESTAMP                        241                                                                      
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;

TABLE_NAME                COLUMN_NAME               NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE    DENSITY HISTOGRAM                        
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------                  
SOURCE_TABLE              CREATED                            874          0        4958 .002352941 HEIGHT BALANCED                  
SOURCE_TABLE              DATA_OBJECT_ID                    2938     422838       38737 .000352885 HEIGHT BALANCED                  
SOURCE_TABLE              GENERATED                            2          0        4958 1.0853E-06 FREQUENCY                        
SOURCE_TABLE              LAST_DDL_TIME                      886          0        4958 .002358491 HEIGHT BALANCED                  
SOURCE_TABLE              OBJECT_ID                        35508          0      461575 .000028163 NONE                             
SOURCE_TABLE              OBJECT_NAME                      26857          0      461575 .000044659 HEIGHT BALANCED                  
SOURCE_TABLE              OBJECT_TYPE                         32          0        4958 1.0853E-06 FREQUENCY                        
SOURCE_TABLE              OWNER                               23          0        4958 1.0853E-06 FREQUENCY                        
SOURCE_TABLE              SECONDARY                            1          0        4958 1.0853E-06 FREQUENCY                        
SOURCE_TABLE              STATUS                               1          0        4958 1.0853E-06 FREQUENCY                        
SOURCE_TABLE              SUBOBJECT_NAME                     100     457597        3978 .000125691 FREQUENCY                        
SOURCE_TABLE              TEMPORARY                            2          0        4958 1.0853E-06 FREQUENCY                        
SOURCE_TABLE              TIMESTAMP                          890          0        4958 .002386635 HEIGHT BALANCED                  
TEST_APPEND               CREATED                            942          0        6237 .002427184 HEIGHT BALANCED                  
TEST_APPEND               DATA_OBJECT_ID                    3592    3451090       23675 .000346741 HEIGHT BALANCED                  
TEST_APPEND               GENERATED                            2          0        6237 1.3366E-07 FREQUENCY                        
TEST_APPEND               LAST_DDL_TIME                      941          0        6237 .002347418 HEIGHT BALANCED                  
TEST_APPEND               OBJECT_ID                        44867          0      288110 .000022288 NONE                             
TEST_APPEND               OBJECT_NAME                      29033          0      288110 .000044779 HEIGHT BALANCED                  
TEST_APPEND               OBJECT_TYPE                         27          0        6237 1.3366E-07 FREQUENCY                        
TEST_APPEND               OWNER                               21          0        6237 1.3366E-07 FREQUENCY                        
TEST_APPEND               SECONDARY                            1          0        6237 1.3366E-07 FREQUENCY                        
TEST_APPEND               STATUS                               1          0        6237 1.3366E-07 FREQUENCY                        
TEST_APPEND               SUBOBJECT_NAME                     102    3725026       30854 .000016205 FREQUENCY                        
TEST_APPEND               TEMPORARY                            2          0        6237 1.3366E-07 FREQUENCY                        
TEST_APPEND               TIMESTAMP                          953          0        6237 .002380952 HEIGHT BALANCED                  
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                       10          0      133039         .1 NONE                             
TEST_BINDVAR_PEEKING      COUNTRY_OF_BIRTH                     1          0      133039          1 NONE                             
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                       10          0      133039         .1 NONE                             
TEST_BINDVAR_PEEKING      MONTH_NUMBER                        10          0      133039         .1 NONE                             
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                      10          0      133039         .1 NONE                             
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;

TABLE_NAME                INDEX_NAME                    LEAF_BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                    
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------                  
TEST_APPEND               IND_TEST_APPEND                     38328    3686411      392641   10.65 20-JUL 23:18:44                  
TEST_BINDVAR_PEEKING      TEST_BINDVAR_PEEKING_COB_NDX         2612    1337226      482304   36.07 20-JUL 23:10:00                  

select sysdate from dual;

SYSDATE                                                                                                                             
---------------                                                                                                                     
20-JUL 23:18:44                                                                                                                     

REM  #######################Gathering Schema Stats WITHOUT GATHER AUTO
exec dbms_stats.gather_schema_stats(ownname=>'HEMANT',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;

TABLE_NAME                    BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                                                   
------------------------- ---------- ---------- ----------- ------- ---------------                                                 
SOURCE_TABLE                    9172     458810       45881   10.00 20-JUL 23:18:46                                                 
TEST_APPEND                    60250    3760080      376008   10.00 20-JUL 23:18:58                                                 
TEST_BINDVAR_PEEKING            7600    1331200      133120   10.00 20-JUL 23:19:03                                                 
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;

TABLE_NAME                COLUMN_NAME               NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE    DENSITY HISTOGRAM                        
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------                  
SOURCE_TABLE              CREATED                           1274          0       45881 .000784929 NONE                             
SOURCE_TABLE              DATA_OBJECT_ID                    2921     419190        3962 .000342349 NONE                             
SOURCE_TABLE              GENERATED                            2          0       45881         .5 NONE                             
SOURCE_TABLE              LAST_DDL_TIME                     1303          0       45881  .00076746 NONE                             
SOURCE_TABLE              OBJECT_ID                        35520          0       45881 .000028153 NONE                             
SOURCE_TABLE              OBJECT_NAME                      25298          0       45881 .000039529 NONE                             
SOURCE_TABLE              OBJECT_TYPE                         39          0       45881 .025641026 NONE                             
SOURCE_TABLE              OWNER                               26          0       45881 .038461538 NONE                             
SOURCE_TABLE              SECONDARY                            2          0       45881         .5 NONE                             
SOURCE_TABLE              STATUS                               1          0       45881          1 NONE                             
SOURCE_TABLE              SUBOBJECT_NAME                      96     454710         410 .010416667 NONE                             
SOURCE_TABLE              TEMPORARY                            2          0       45881         .5 NONE                             
SOURCE_TABLE              TIMESTAMP                         1348          0       45881  .00074184 NONE                             
TEST_APPEND               CREATED                           1366          0      376008 .000732064 NONE                             
TEST_APPEND               DATA_OBJECT_ID                    3603    3456130       30395 .000277546 NONE                             
TEST_APPEND               GENERATED                            2          0      376008         .5 NONE                             
TEST_APPEND               LAST_DDL_TIME                     1404          0      376008 .000712251 NONE                             
TEST_APPEND               OBJECT_ID                        44979          0      376008 .000022233 NONE                             
TEST_APPEND               OBJECT_NAME                      29078          0      376008  .00003439 NONE                             
TEST_APPEND               OBJECT_TYPE                         41          0      376008 .024390244 NONE                             
TEST_APPEND               OWNER                               27          0      376008 .037037037 NONE                             
TEST_APPEND               SECONDARY                            2          0      376008         .5 NONE                             
TEST_APPEND               STATUS                               1          0      376008          1 NONE                             
TEST_APPEND               SUBOBJECT_NAME                     103    3730460        2962 .009708738 NONE                             
TEST_APPEND               TEMPORARY                            2          0      376008         .5 NONE                             
TEST_APPEND               TIMESTAMP                         1468          0      376008 .000681199 NONE                             
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                       10          0      133120         .1 NONE                             
TEST_BINDVAR_PEEKING      COUNTRY_OF_BIRTH                     1          0      133120          1 NONE                             
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                       10          0      133120         .1 NONE                             
TEST_BINDVAR_PEEKING      MONTH_NUMBER                        10          0      133120         .1 NONE                             
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                      10          0      133120         .1 NONE                             
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;

TABLE_NAME                INDEX_NAME                    LEAF_BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                    
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------                  
TEST_APPEND               IND_TEST_APPEND                     40670    3908800      390880   10.00 20-JUL 23:19:02                  
TEST_BINDVAR_PEEKING      TEST_BINDVAR_PEEKING_COB_NDX         2653    1358519      489984   36.07 20-JUL 23:19:05                  

select sysdate from dual;

SYSDATE                                                                                                                             
---------------                                                                                                                     
20-JUL 23:19:05                                                                                                                     
rem exec dbms_stats.restore_schema_stats(ownname=>'HEMANT',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');
exec dbms_stats.reset_param_defaults;

spool off

Testing the DBMS_STATS option GATHER AUTO

I have been aware of the issues with AUTO_SAMPLE_SIZE and the serious issues with 'FOR ALL COLUMNS SIZE AUTO' in 10gR2's DBMS_STATS.
I recently came across an application using options=>'GATHER AUTO' in a scripted call to DBMS_STATS (ie not relying on the GATHER_STATS_JOB).
Since I am not comfortable with this, I have looked up the documentation and logged an SR with Oracle Support asking for an explanation for the portion of the documentation that says "When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored". That line should mean that whatever I specify for ESTIMATE_PERCENT or METHOD_OPT or GRANULARITY or CASCADE would be ignored. Quit possibly, even if I have, in advance, used SET_PARAM to set some of the "default" values, the 'GATHER AUTO' would override them.

Oracle Support hasn't given me a satisfactory answer yet, just pointing me to Note#114671.1 but also telling me that "Gather Auto is just an option so it will not override all other parameter but every parameter has its default value".

I ran more than half a dozen different tests with different tables with different skews. I also included "transactions" that did large and small Insert/Update/Delete counts against the table between two calls to DBMS_STATS.

In all tests, the "GATHER AUTO" did override ESTIMATE_PERCENT and METHOD_OPT.

These tests were on 10.2.0.4. You can see that after every "GATHER AUTO" run, statistics on some tables have been updated with different sample sizes -- and the sample size may well vary between tables and indexes.

Particularly look at how the Sample Sizes vary for TEST_APPEND and TEST_BINDVAR_PEEKING (these tables are the same ones from all my previous test scenarios that I have published here).

Update 22-July : Oracle Support has come back to confirm that "GATHER AUTO" does override other parameters. Thus, it sets Oracle to use the defaults for the other parameters.

col table_name format a25
col index_name format a29
col column_name format a25
col Sample_Perc format 999.99 hea 'S%age'

alter session set nls_date_format='DD-MON HH24:MI:SS';

select segment_type, segment_name, blocks from user_segments order by 1 desc,2;

SEGMENT_TYPE       SEGMENT_NAME                                                                          BLOCKS                  
------------------ --------------------------------------------------------------------------------- ----------                  
TABLE              SOURCE_TABLE                                                                            9216                  
TABLE              TEST_APPEND                                                                            42112                  
TABLE              TEST_APPEND_2                                                                           4096                  
TABLE              TEST_BINDVAR_PEEKING                                                                    7680                  
INDEX              IND_TEST_APPEND                                                                        26880                  
INDEX              TEST_BINDVAR_PEEKING_COB_NDX                                                            3200                  
select num_rows from user_tables where table_name = 'TEST_APPEND';

  NUM_ROWS                                                                                                                       
----------                                                                                                                       
   2743908                                                                                                                       
select count(*) from test_append;

  COUNT(*)                                                                                                                       
----------                                                                                                                       
   2890416                                                                                                                       

select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;

TABLE_NAME                    BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                                                
------------------------- ---------- ---------- ----------- ------- ---------------                                              
SOURCE_TABLE                    4245     303666      303666  100.00 20-JUL 12:30:28                                              
TEST_APPEND                    42013    2743908      406067   14.80 20-JUL 12:33:10                                              
TEST_APPEND_2                   4029     288560      288560  100.00 20-JUL 12:32:59                                              
TEST_BINDVAR_PEEKING            7600    1337769        4890     .37 20-JUL 12:30:31                                              
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;

TABLE_NAME                COLUMN_NAME                 COUNT(*)                                                                   
------------------------- ------------------------- ----------                                                                   
SOURCE_TABLE              CREATED                          245                                                                   
SOURCE_TABLE              DATA_OBJECT_ID                   255                                                                   
SOURCE_TABLE              LAST_DDL_TIME                    243                                                                   
SOURCE_TABLE              OBJECT_NAME                      255                                                                   
SOURCE_TABLE              OBJECT_TYPE                       32                                                                   
SOURCE_TABLE              OWNER                             22                                                                   
SOURCE_TABLE              SUBOBJECT_NAME                   102                                                                   
SOURCE_TABLE              TIMESTAMP                        243                                                                   
TEST_APPEND               CREATED                          240                                                                   
TEST_APPEND               DATA_OBJECT_ID                   255                                                                   
TEST_APPEND               LAST_DDL_TIME                    241                                                                   
TEST_APPEND               OBJECT_NAME                      255                                                                   
TEST_APPEND               OBJECT_TYPE                       31                                                                   
TEST_APPEND               OWNER                             22                                                                   
TEST_APPEND               SUBOBJECT_NAME                   102                                                                   
TEST_APPEND               TIMESTAMP                        241                                                                   
TEST_APPEND_2             CREATED                          239                                                                   
TEST_APPEND_2             DATA_OBJECT_ID                   255                                                                   
TEST_APPEND_2             LAST_DDL_TIME                    247                                                                   
TEST_APPEND_2             OBJECT_NAME                      255                                                                   
TEST_APPEND_2             OBJECT_TYPE                       29                                                                   
TEST_APPEND_2             OWNER                             23                                                                   
TEST_APPEND_2             SUBOBJECT_NAME                    36                                                                   
TEST_APPEND_2             TIMESTAMP                        240                                                                   
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                     10                                                                   
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                     10                                                                   
TEST_BINDVAR_PEEKING      MONTH_NUMBER                      10                                                                   
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                    10                                                                   
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;

TABLE_NAME                COLUMN_NAME               NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE    DENSITY HISTOGRAM                     
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------               
SOURCE_TABLE              CREATED                            917          0        5625 .002444988 HEIGHT BALANCED               
SOURCE_TABLE              DATA_OBJECT_ID                    4028     278982       24684 .000270251 HEIGHT BALANCED               
SOURCE_TABLE              GENERATED                            2          0        5625 1.6669E-06 FREQUENCY                     
SOURCE_TABLE              LAST_DDL_TIME                      939          0        5625 .002380952 HEIGHT BALANCED               
SOURCE_TABLE              OBJECT_ID                        50611          0      303666 .000019759 NONE                          
SOURCE_TABLE              OBJECT_NAME                      30281          0      303666 .000038693 HEIGHT BALANCED               
SOURCE_TABLE              OBJECT_TYPE                         32          0        5625 1.6669E-06 FREQUENCY                     
SOURCE_TABLE              OWNER                               22          0        5625 1.6669E-06 FREQUENCY                     
SOURCE_TABLE              SECONDARY                            2          0        5625 1.6669E-06 FREQUENCY                     
SOURCE_TABLE              STATUS                               1          0        5625 1.6669E-06 FREQUENCY                     
SOURCE_TABLE              SUBOBJECT_NAME                     102     301200        2466 .000202758 FREQUENCY                     
SOURCE_TABLE              TEMPORARY                            2          0        5625 1.6669E-06 FREQUENCY                     
SOURCE_TABLE              TIMESTAMP                          944          0        5625 .002444988 HEIGHT BALANCED               
TEST_APPEND               CREATED                            898          0        5201 .002403846 HEIGHT BALANCED               
TEST_APPEND               DATA_OBJECT_ID                    4020    2522904       32706 .000312305 HEIGHT BALANCED               
TEST_APPEND               GENERATED                            2          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               LAST_DDL_TIME                      906          0        5201 .002320186 HEIGHT BALANCED               
TEST_APPEND               OBJECT_ID                        50577          0      406067 .000019772 NONE                          
TEST_APPEND               OBJECT_NAME                      30267          0      406067 .000041973 HEIGHT BALANCED               
TEST_APPEND               OBJECT_TYPE                         31          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               OWNER                               22          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               SECONDARY                            1          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               STATUS                               1          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               SUBOBJECT_NAME                     102    2721629        3297 .000022443 FREQUENCY                     
TEST_APPEND               TEMPORARY                            2          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               TIMESTAMP                          915          0        5201 .002358491 HEIGHT BALANCED               
TEST_APPEND_2             CREATED                            861          0        5582 .002331002 HEIGHT BALANCED               
TEST_APPEND_2             DATA_OBJECT_ID                     435     263432       25128 .002483204 HEIGHT BALANCED               
TEST_APPEND_2             GENERATED                            2          0        5582 1.7188E-06 FREQUENCY                     
TEST_APPEND_2             LAST_DDL_TIME                      845          0        5582 .002463054 HEIGHT BALANCED               
TEST_APPEND_2             OBJECT_ID                         4744          0        5582 .000210793 NONE                          
TEST_APPEND_2             OBJECT_NAME                       4650          0        5582 .000342583 HEIGHT BALANCED               
TEST_APPEND_2             OBJECT_TYPE                         29          0        5582 1.7188E-06 FREQUENCY                     
TEST_APPEND_2             OWNER                               23          0        5582 1.7188E-06 FREQUENCY                     
TEST_APPEND_2             SECONDARY                            2          0        5582 1.7188E-06 FREQUENCY                     
TEST_APPEND_2             STATUS                               1          0        5582 1.7188E-06 FREQUENCY                     
TEST_APPEND_2             SUBOBJECT_NAME                      36     285884        2676 .000186846 FREQUENCY                     
TEST_APPEND_2             TEMPORARY                            2          0        5582 1.7188E-06 FREQUENCY                     
TEST_APPEND_2             TIMESTAMP                          866          0        5582 .002347418 HEIGHT BALANCED               
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                       10          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      COUNTRY_OF_BIRTH                     1          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                       10          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      MONTH_NUMBER                        10          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                      10          0        4890 3.7376E-07 FREQUENCY                     
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;

TABLE_NAME                INDEX_NAME                    LEAF_BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                 
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------               
TEST_APPEND               IND_TEST_APPEND                     26414    2793966      910998   32.61 20-JUL 12:34:12               
TEST_BINDVAR_PEEKING      TEST_BINDVAR_PEEKING_COB_NDX         2600    1331200     1331200  100.00 20-JUL 12:30:33               

select sysdate from dual;

SYSDATE                                                                                                                          
---------------                                                                                                                  
20-JUL 14:16:07                                                                                                                  

REM  Table "TEST_APPEND_2" was dropped from another SQLPlus session
REM  #######################Gathering Schema Stats #################
exec dbms_stats.gather_schema_stats(ownname=>'HEMANT',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE,options=>'GATHER AUTO');


select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;

TABLE_NAME                    BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                                                
------------------------- ---------- ---------- ----------- ------- ---------------                                              
SOURCE_TABLE                    8477     607332      607332  100.00 20-JUL 14:16:17                                              
TEST_APPEND                    42013    2743908      406067   14.80 20-JUL 12:33:10                                              
TEST_BINDVAR_PEEKING            7600    1337769        4890     .37 20-JUL 12:30:31                                              
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;

TABLE_NAME                COLUMN_NAME                 COUNT(*)                                                                   
------------------------- ------------------------- ----------                                                                   
SOURCE_TABLE              CREATED                          241                                                                   
SOURCE_TABLE              DATA_OBJECT_ID                   255                                                                   
SOURCE_TABLE              LAST_DDL_TIME                    244                                                                   
SOURCE_TABLE              OBJECT_NAME                      255                                                                   
SOURCE_TABLE              OBJECT_TYPE                       28                                                                   
SOURCE_TABLE              OWNER                             20                                                                   
SOURCE_TABLE              SUBOBJECT_NAME                   102                                                                   
SOURCE_TABLE              TIMESTAMP                        244                                                                   
TEST_APPEND               CREATED                          240                                                                   
TEST_APPEND               DATA_OBJECT_ID                   255                                                                   
TEST_APPEND               LAST_DDL_TIME                    241                                                                   
TEST_APPEND               OBJECT_NAME                      255                                                                   
TEST_APPEND               OBJECT_TYPE                       31                                                                   
TEST_APPEND               OWNER                             22                                                                   
TEST_APPEND               SUBOBJECT_NAME                   102                                                                   
TEST_APPEND               TIMESTAMP                        241                                                                   
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                     10                                                                   
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                     10                                                                   
TEST_BINDVAR_PEEKING      MONTH_NUMBER                      10                                                                   
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                    10                                                                   
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;

TABLE_NAME                COLUMN_NAME               NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE    DENSITY HISTOGRAM                     
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------               
SOURCE_TABLE              CREATED                            909          0        5305 .002421308 HEIGHT BALANCED               
SOURCE_TABLE              DATA_OBJECT_ID                    4028     557964       49368 .000270251 HEIGHT BALANCED               
SOURCE_TABLE              GENERATED                            2          0        5305 8.0674E-07 FREQUENCY                     
SOURCE_TABLE              LAST_DDL_TIME                      915          0        5305 .002415459 HEIGHT BALANCED               
SOURCE_TABLE              OBJECT_ID                        50611          0      607332 .000019759 NONE                          
SOURCE_TABLE              OBJECT_NAME                      30281          0      607332 .000038693 HEIGHT BALANCED               
SOURCE_TABLE              OBJECT_TYPE                         28          0        5305 8.0674E-07 FREQUENCY                     
SOURCE_TABLE              OWNER                               20          0        5305 8.0674E-07 FREQUENCY                     
SOURCE_TABLE              SECONDARY                            2          0        5305 8.0674E-07 FREQUENCY                     
SOURCE_TABLE              STATUS                               1          0        5305 8.0674E-07 FREQUENCY                     
SOURCE_TABLE              SUBOBJECT_NAME                     102     602400        4932 .000101379 FREQUENCY                     
SOURCE_TABLE              TEMPORARY                            2          0        5305 8.0674E-07 FREQUENCY                     
SOURCE_TABLE              TIMESTAMP                          924          0        5305      .0025 HEIGHT BALANCED               
TEST_APPEND               CREATED                            898          0        5201 .002403846 HEIGHT BALANCED               
TEST_APPEND               DATA_OBJECT_ID                    4020    2522904       32706 .000312305 HEIGHT BALANCED               
TEST_APPEND               GENERATED                            2          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               LAST_DDL_TIME                      906          0        5201 .002320186 HEIGHT BALANCED               
TEST_APPEND               OBJECT_ID                        50577          0      406067 .000019772 NONE                          
TEST_APPEND               OBJECT_NAME                      30267          0      406067 .000041973 HEIGHT BALANCED               
TEST_APPEND               OBJECT_TYPE                         31          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               OWNER                               22          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               SECONDARY                            1          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               STATUS                               1          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               SUBOBJECT_NAME                     102    2721629        3297 .000022443 FREQUENCY                     
TEST_APPEND               TEMPORARY                            2          0        5201 1.8210E-07 FREQUENCY                     
TEST_APPEND               TIMESTAMP                          915          0        5201 .002358491 HEIGHT BALANCED               
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                       10          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      COUNTRY_OF_BIRTH                     1          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                       10          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      MONTH_NUMBER                        10          0        4890 3.7376E-07 FREQUENCY                     
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                      10          0        4890 3.7376E-07 FREQUENCY                     
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;

TABLE_NAME                INDEX_NAME                    LEAF_BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                 
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------               
TEST_APPEND               IND_TEST_APPEND                     26414    2793966      910998   32.61 20-JUL 12:34:12               
TEST_BINDVAR_PEEKING      TEST_BINDVAR_PEEKING_COB_NDX         2600    1331200     1331200  100.00 20-JUL 12:30:33               

select sysdate from dual;

SYSDATE                                                                                                                          
---------------                                                                                                                  
20-JUL 14:16:19                                                                                                                  

REM  #######################Gathering Schema Stats WITHOUT GATHER AUTO
exec dbms_stats.gather_schema_stats(ownname=>'HEMANT',estimate_percent=>10,method_opt=>'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

select table_name, blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_tables order by 1 ;

TABLE_NAME                    BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                                                
------------------------- ---------- ---------- ----------- ------- ---------------                                              
SOURCE_TABLE                    8477     610720       61072   10.00 20-JUL 14:16:21                                              
TEST_APPEND                    42013    2893200      289320   10.00 20-JUL 14:16:29                                              
TEST_BINDVAR_PEEKING            7600    1330440      133044   10.00 20-JUL 14:16:33                                              
select table_name, column_name, count(*) from user_tab_histograms group by table_name, column_name having count(*) > 2 order by 1,2;
select table_name, column_name, num_distinct, num_nulls, sample_size, density, histogram from user_tab_col_statistics order by 1,2;

TABLE_NAME                COLUMN_NAME               NUM_DISTINCT  NUM_NULLS SAMPLE_SIZE    DENSITY HISTOGRAM                     
------------------------- ------------------------- ------------ ---------- ----------- ---------- ---------------               
SOURCE_TABLE              CREATED                           1329          0       61072 .000752445 NONE                          
SOURCE_TABLE              DATA_OBJECT_ID                    3942     560580        5014 .000253678 NONE                          
SOURCE_TABLE              GENERATED                            2          0       61072         .5 NONE                          
SOURCE_TABLE              LAST_DDL_TIME                     1363          0       61072 .000733676 NONE                          
SOURCE_TABLE              OBJECT_ID                        50232          0       61072 .000019908 NONE                          
SOURCE_TABLE              OBJECT_NAME                      28710          0       61072 .000034831 NONE                          
SOURCE_TABLE              OBJECT_TYPE                         40          0       61072       .025 NONE                          
SOURCE_TABLE              OWNER                               26          0       61072 .038461538 NONE                          
SOURCE_TABLE              SECONDARY                            2          0       61072         .5 NONE                          
SOURCE_TABLE              STATUS                               1          0       61072          1 NONE                          
SOURCE_TABLE              SUBOBJECT_NAME                      96     605670         505 .010416667 NONE                          
SOURCE_TABLE              TEMPORARY                            2          0       61072         .5 NONE                          
SOURCE_TABLE              TIMESTAMP                         1412          0       61072 .000708215 NONE                          
TEST_APPEND               CREATED                           1371          0      289320 .000729395 NONE                          
TEST_APPEND               DATA_OBJECT_ID                    3827    2656590       23661 .000261301 NONE                          
TEST_APPEND               GENERATED                            2          0      289320         .5 NONE                          
TEST_APPEND               LAST_DDL_TIME                     1417          0      289320 .000705716 NONE                          
TEST_APPEND               OBJECT_ID                        47989          0      289320 .000020838 NONE                          
TEST_APPEND               OBJECT_NAME                      29714          0      289320 .000033654 NONE                          
TEST_APPEND               OBJECT_TYPE                         41          0      289320 .024390244 NONE                          
TEST_APPEND               OWNER                               27          0      289320 .037037037 NONE                          
TEST_APPEND               SECONDARY                            2          0      289320         .5 NONE                          
TEST_APPEND               STATUS                               1          0      289320          1 NONE                          
TEST_APPEND               SUBOBJECT_NAME                     102    2870020        2318 .009803922 NONE                          
TEST_APPEND               TEMPORARY                            2          0      289320         .5 NONE                          
TEST_APPEND               TIMESTAMP                         1474          0      289320 .000678426 NONE                          
TEST_BINDVAR_PEEKING      CITY_OF_BIRTH                       10          0      133044         .1 NONE                          
TEST_BINDVAR_PEEKING      COUNTRY_OF_BIRTH                     1          0      133044          1 NONE                          
TEST_BINDVAR_PEEKING      DATE_OF_BIRTH                       10          0      133044         .1 NONE                          
TEST_BINDVAR_PEEKING      MONTH_NUMBER                        10          0      133044         .1 NONE                          
TEST_BINDVAR_PEEKING      MONTH_OF_BIRTH                      10          0      133044         .1 NONE                          
select table_name, index_name, leaf_blocks, num_rows, sample_size, sample_size*100/num_rows Sample_Perc, last_analyzed from user_indexes order by 1,2;

TABLE_NAME                INDEX_NAME                    LEAF_BLOCKS   NUM_ROWS SAMPLE_SIZE   S%age LAST_ANALYZED                 
------------------------- ----------------------------- ----------- ---------- ----------- ------- ---------------               
TEST_APPEND               IND_TEST_APPEND                     25250    2776200      277620   10.00 20-JUL 14:16:32               
TEST_BINDVAR_PEEKING      TEST_BINDVAR_PEEKING_COB_NDX         2600    1331200     1331200  100.00 20-JUL 14:16:37               

select sysdate from dual;

SYSDATE                                                                                                                          
---------------                                                                                                                  
20-JUL 14:16:38                                                                                                                  
rem exec dbms_stats.restore_schema_stats(ownname=>'HEMANT',AS_OF_TIMESTAMP=>'20-JUL-2008 11:15:00AM');

spool off

Cardinality Estimate : Dependent Columns --- Reposted

I have reposted the article Cardinality Estimate - Dependent Columns as the previous posting had one "less than" sign in the text which resulted in blogger rewriting the rest of the text.

"Table Elimination" (aka "Join Elimination")

The Optimizer Group's blog posting on the Table Elimination transformation is certainly worth reading. This is a useful feature (although it can leave some DBA's scratching their heads wondering why a t able doesn't appear in the Execution Plan when it "*should*").

Bind Variable Peeking

Here are two simple test cases which demonstrate how Bind Variable Peeking can cause the wrong execution plan to be used.
In this table TEST_BINDVAR_PEEKING, I have only 3 values for COUNTRY_OF_BIRTH but 'IN' accounts for 10 of every 12 rows. Therefore, with a Histogram on COUNTRY_OF_BIRTH, a query for 'IN' should use a FullTableScan while a query for 'FR' should use an IndexRangeScan.

In the first set of tests, I run a query with a bind variable. At the first, execution, the bind variable is set to 'FR' so Oracle, "peeking" into the value, selects an IndexRangeScan. However, at the next execution, the value is actually 'IN' but Oracle sticks to the old execution plan (ie, it doesn't "peek" at the value at the second execution of the same SQL). Therefore, it uses a a poor execution plan for 'IN', resulting in higher Buffer Gets.

I can force it to "re-peek" at the Bind Variable value if I execute, typically, an ANALYZE or DBMS_STATS on the table. However, what I really need is *any* DDL that will invalidate the existant SQL statement. Since my statistics are actually correct, I do not need to waste time or resources on an ANALYZE or DBMS_STATS again, but just force an Invalidation by using the "COMMENT ON TABLE" statement which is a DDL !
A simple (and elegant, you say ?) solution, without the cost of ANALYZE / DBMS_STATS !
{Of course, if my Statisticswere really incorrect, I would have to re-gather Statistics}.

In the second set of tests, I extend the query to two tables. Again, I get a poor execution plan on the larger table. However, now I can execute DDL on even the other (smaller) table to force an Invalidation and a "re-peek" ! This is a strategy I could use when I know other SQLs current in the Shared Pool are correctly executing against TEST_BINDVAR_PEEKING and I do not want to invalidate all those other SQLs. I can choose to invalidate all SQLs using COUNTRY_MASTER and yet achieve my objective of forcing a re-peek on the Bind Variable against TEST_BINDVAR_PEEKING !

drop table country_master ;
create table country_master as select distinct country_of_birth from test_cardinality;
create index country_master_ndx on country_master(country_of_birth) ;

drop table test_bindvar_peeking ;
create table test_bindvar_peeking as select * from test_cardinality where 1=2;
alter table test_bindvar_peeking nologging;

REM  Can you guess why I have an ORDER BY COUNTRY_OF_BIRTH for the first two inserts ?
rem   Hint : It helped me "tweak" the data !
rem   In what way ?  Post your answers as comments to this blog posting
insert /*+ APPEND */ into test_bindvar_peeking select * from test_cardinality order by country_of_birth;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_cardinality order by country_of_birth;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;
insert /*+ APPEND */ into test_bindvar_peeking select * from test_bindvar_peeking ;
commit;


create index test_bindvar_peeking_cob_ndx on test_bindvar_peeking (country_of_birth);

exec dbms_stats.gather_table_stats(user,tabname=>'COUNTRY_MASTER',method_opt=>'FOR ALL COLUMNS SIZE 250',estimate_percent=>100,cascade=>TRUE);
exec dbms_stats.gather_table_stats(user,tabname=>'TEST_BINDVAR_PEEKING',method_opt=>'FOR ALL COLUMNS SIZE 250',estimate_percent=>100,cascade=>TRUE);

REM  Verify the number of records
select country_of_birth from country_master order by 1;

COU                                                                                                                                 
---                                                                                                                                 
FR                                                                                                                                  
IN                                                                                                                                  
UK                                                                                                                                  
select country_of_birth, count(*) from test_bindvar_peeking group by country_of_birth order by 2;

COU   COUNT(*)                                                                                                                      
--- ----------                                                                                                                      
FR       65536                                                                                                                      
UK       65536                                                                                                                      
IN      655360                                                                                                                      


REM Verify the presence of a Frequency Histogram on COUNTRY_OF_BIRTH
select endpoint_value, endpoint_number
  2  from user_tab_histograms
  3  where table_name = 'TEST_BINDVAR_PEEKING' and column_name = 'COUNTRY_OF_BIRTH'
  4  order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER                                                                                                      
-------------- ---------------                                                                                                      
    3.6512E+35           65536                                                                                                      
    3.8062E+35          720896                                                                                                      
    4.4287E+35          786432                                                                                                      

REM Verify the execution plans and Buffer Gets for  'FR' and 'IN'
set autotrace on
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth='FR' group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
PARIS                   65536                                                                                                       

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3593298901                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------------------------                       
| Id  | Operation                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |                       
-------------------------------------------------------------------------------------------------------------                       
|   0 | SELECT STATEMENT             |                              |    12 |   132 |   570   (2)| 00:00:07 |                       
|   1 |  HASH GROUP BY               |                              |    12 |   132 |   570   (2)| 00:00:07 |                       
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_BINDVAR_PEEKING         | 65536 |   704K|   566   (1)| 00:00:07 |                       
|*  3 |    INDEX RANGE SCAN          | TEST_BINDVAR_PEEKING_COB_NDX | 65536 |       |   131   (1)| 00:00:02 |                       
-------------------------------------------------------------------------------------------------------------                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   3 - access("COUNTRY_OF_BIRTH"='FR')                                                                                              


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        936  consistent gets                                                                                                        
          0  physical reads                                                                                                         
        432  redo size                                                                                                              
        594  bytes sent via SQL*Net to client                                                                                       
        492  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth='IN' group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
KOLHAPUR                65536                                                                                                       
SOLAPUR                 65536                                                                                                       
DELHI                   65536                                                                                                       
CALCUTTA                65536                                                                                                       
NAGPUR                  65536                                                                                                       
JABALPUR                65536                                                                                                       
BADLAPUR                65536                                                                                                       
MADRAS                  65536                                                                                                       
KANPUR                  65536                                                                                                       
BOMBAY                  65536                                                                                                       

Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2629260985                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------------------------                                         
| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |                                         
-------------------------------------------------------------------------------------------                                         
|   0 | SELECT STATEMENT   |                      |    12 |   132 |   877   (7)| 00:00:11 |                                         
|   1 |  HASH GROUP BY     |                      |    12 |   132 |   877   (7)| 00:00:11 |                                         
|*  2 |   TABLE ACCESS FULL| TEST_BINDVAR_PEEKING |   655K|  7040K|   838   (2)| 00:00:11 |                                         
-------------------------------------------------------------------------------------------                                         
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - filter("COUNTRY_OF_BIRTH"='IN')                                                                                              


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
       3694  consistent gets                                                                                                        
          0  physical reads                                                                                                         
        504  redo size                                                                                                              
        758  bytes sent via SQL*Net to client                                                                                       
        492  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
         10  rows processed                                                                                                         

set autotrace off

REM  REM  REM ##############################################################################################

REM Define the Bind Variables
variable bv varchar2(5) ;
variable bv2 varchar2(5) ;

REM set the value to 'FR'
exec :bv := 'FR';

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
PARIS                   65536                                                                                                       

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
  2  where sql_id in ('1grp430nhcxp7') order by sql_id, child_number;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE                                                                                          
------------- ------------ ---------------                                                                                          
SQL_TEXT                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS                                                                                                
------------- ---------- -----------                                                                                                
1grp430nhcxp7            0      3593298901                                                                                          
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth                        
            0          1         936                                                                                                
                                                                                                                                    


REM now, change the value to 'IN'.  We know, from Histograms and the autotrace, that a different execution plan should be expected
exec :bv := 'IN';

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
KOLHAPUR                65536                                                                                                       
SOLAPUR                 65536                                                                                                       
NAGPUR                  65536                                                                                                       
DELHI                   65536                                                                                                       
CALCUTTA                65536                                                                                                       
JABALPUR                65536                                                                                                       
BADLAPUR                65536                                                                                                       
MADRAS                  65536                                                                                                       
KANPUR                  65536                                                                                                       
BOMBAY                  65536                                                                                                       

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
  2  where sql_id in ('1grp430nhcxp7') order by sql_id, child_number;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE                                                                                          
------------- ------------ ---------------                                                                                          
SQL_TEXT                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS                                                                                                
------------- ---------- -----------                                                                                                
1grp430nhcxp7            0      3593298901                                                                                          
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth                        
            0          2        5786                                                                                                
                                                                                                                                    

REM  SURPRISED ??  Oracle used the *same* execution plan for 'IN' as it used for 'FR'
rem   The 'PLAN_HASH_VALUE' didn't change  ; Invalidations didn't increase
rem   but Executions did increase and incremental Buffer_Gets is very high
rem  Oracle had done Bind Variable Peeking the first time to read the 'FR' but did not do so again to read the 'IN' !


REM  How do we force Bind Variable Peeking ?   A Gather_Stats or Analyze OR  *any* DDL will do the job !
REM A "COMMENT ON TABLE" is a DDL command that will Invalidate the SQL and cause it to be re-parsed at the next execution
comment on table test_bindvar_peeking is '';

exec :bv := 'IN';

select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
KOLHAPUR                65536                                                                                                       
SOLAPUR                 65536                                                                                                       
DELHI                   65536                                                                                                       
CALCUTTA                65536                                                                                                       
NAGPUR                  65536                                                                                                       
JABALPUR                65536                                                                                                       
BADLAPUR                65536                                                                                                       
MADRAS                  65536                                                                                                       
KANPUR                  65536                                                                                                       
BOMBAY                  65536                                                                                                       

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
  2  where sql_id in ('1grp430nhcxp7') order by sql_id, child_number;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE                                                                                          
------------- ------------ ---------------                                                                                          
SQL_TEXT                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS                                                                                                
------------- ---------- -----------                                                                                                
1grp430nhcxp7            0      2629260985                                                                                          
select city_of_birth, count(*) from test_bindvar_peeking where country_of_birth = :bv group by city_of_birth                        
            1          1        3694                                                                                                
                                                                                                                                    
REM  See that the PLAN_HASH_VALUE has changed, Invalidations has been incremented !  Different Execution Plan used this time
REM   What caused it ?  The "COMMENT" was DDL which Invalidated the query


REM REM ####################################################################################################
REM  What if there are two tables in the query ?

exec :bv2  := 'FR';
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp
  2  where cm.country_of_birth = tbp.country_of_birth and tbp.country_of_birth = :bv2 group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
PARIS                   65536                                                                                                       

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
  2  where sql_id in ('732a37npkf6zd') order by sql_id, child_number;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE                                                                                          
------------- ------------ ---------------                                                                                          
SQL_TEXT                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS                                                                                                
------------- ---------- -----------                                                                                                
732a37npkf6zd            0      1488351023                                                                                          
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp where cm.country_of_birth = tbp.country_of_birth and
 tbp.country_of_birth = :bv2 group by city_of_birth                                                                                 
            0          1         936                                                                                                
                                                                                                                                    


exec :bv2  := 'IN';
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp
  2  where cm.country_of_birth = tbp.country_of_birth and tbp.country_of_birth = :bv2 group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
KOLHAPUR                65536                                                                                                       
SOLAPUR                 65536                                                                                                       
NAGPUR                  65536                                                                                                       
DELHI                   65536                                                                                                       
CALCUTTA                65536                                                                                                       
JABALPUR                65536                                                                                                       
BADLAPUR                65536                                                                                                       
MADRAS                  65536                                                                                                       
KANPUR                  65536                                                                                                       
BOMBAY                  65536                                                                                                       

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
  2  where sql_id in ('732a37npkf6zd') order by sql_id, child_number;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE                                                                                          
------------- ------------ ---------------                                                                                          
SQL_TEXT                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS                                                                                                
------------- ---------- -----------                                                                                                
732a37npkf6zd            0      1488351023                                                                                          
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp where cm.country_of_birth = tbp.country_of_birth and
 tbp.country_of_birth = :bv2 group by city_of_birth                                                                                 
            0          2        5787                                                                                                
                                                                                                                                    
REM We see the same execution plan as for 'FR'


REM This comment on the second table in the query should help
comment on table country_master is '';

exec :bv2  := 'IN';
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp
  2  where cm.country_of_birth = tbp.country_of_birth and tbp.country_of_birth = :bv2 group by city_of_birth;

CITY_OF_BIRTH        COUNT(*)                                                                                                       
------------------ ----------                                                                                                       
KOLHAPUR                65536                                                                                                       
SOLAPUR                 65536                                                                                                       
DELHI                   65536                                                                                                       
CALCUTTA                65536                                                                                                       
NAGPUR                  65536                                                                                                       
JABALPUR                65536                                                                                                       
BADLAPUR                65536                                                                                                       
MADRAS                  65536                                                                                                       
KANPUR                  65536                                                                                                       
BOMBAY                  65536                                                                                                       

select sql_id, child_number, plan_hash_value, sql_text,invalidations,executions,buffer_gets from v$sql
  2  where sql_id in ('732a37npkf6zd') order by sql_id, child_number;

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE                                                                                          
------------- ------------ ---------------                                                                                          
SQL_TEXT                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------
INVALIDATIONS EXECUTIONS BUFFER_GETS                                                                                                
------------- ---------- -----------                                                                                                
732a37npkf6zd            0       598869587                                                                                          
select city_of_birth, count(*) from country_master cm, test_bindvar_peeking tbp where cm.country_of_birth = tbp.country_of_birth and
 tbp.country_of_birth = :bv2 group by city_of_birth                                                                                 
            1          1        3695                                                                                                
                                                                                                                                    
REM  Yes, the execution plan has changed, even if my DDL was on the smaller (smallest) table in the query