20 July, 2008

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



15 July, 2008

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.

06 July, 2008

"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*").

02 July, 2008

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