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
5 comments:
Thanks for the valuable info. I'll check it out later myself.
Hemant,
Curious to know if you'd any performance issues after introducing GATHER AUTO? I'm also thinking to include this in our stats job.. I'm interested to know if you'd any good/bad experiences with this parameter..
Thanks
Vijai
Vijai,
No, I haven't used GATHER AUTO when I have had control over the GATHER_%_STATS scripts. This was a packaged application from a vendor that used GATHER AUTO.
Hemant
Hemanth can you pls tell me what the issue with auto_sample_size and FOR ALL COLUMNS SIZE AUTO in 10204?
Regards,
Satheesh Shanmugam
Satheesh Babu,
You'd have to test and identify any issues. AUTO is AUTO, but some algorithms might have been improved.
For example, Greg Rahn has documented AUTO_SAMPLE_SIZE improvements in 11g (see http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_stats/) and possibly some of the improvements have been backported to 10.2.0.4
Hemant
Post a Comment