Starting with Oracle 12c release 2, a new
preference parameter got introduced for stats gathering purpose.
PREFERENCE_OVERRIDES_PARAMETER – can be
either True or False (false by default), that control whether to override the
input value of the parameter with the preference value of that parameter for
stats gathering operation.
For example, the following stats
gathering command we see that only 20% of the rows were requested and build
stats based on that sample.
demo@ORA12C> exec
dbms_stats.gather_table_stats(user,'big_table',estimate_percent=>20);
PL/SQL procedure successfully completed.
demo@ORA12C> select num_rows,blocks,sample_size
2 from user_tables
3 where table_name ='BIG_TABLE';
NUM_ROWS BLOCKS SAMPLE_SIZE
---------- ---------- -----------
1001980 18856
200396
demo@ORA12C> select
column_name,num_distinct,num_nulls,histogram
2 from user_tab_col_statistics
3 where table_name ='BIG_TABLE'
4 order by column_name ;
COLUMN_NAME
NUM_DISTINCT NUM_NULLS HISTOGRAM
-------------------- ------------ ---------- ---------------
APPLICATION
1 940105 NONE
CREATED 1371 0 NONE
CREATED_APPID
0 1001980 NONE
CREATED_VSNID
0 1001980 NONE
DATA_OBJECT_ID
594 992460 NONE
DEFAULT_COLLATION
1 992470 NONE
DUPLICATED
1 940105 NONE
EDITIONABLE
2 980230 NONE
EDITION_NAME
0 1001980 NONE
GENERATED
2 0 NONE
ID
1001980 0 NONE
LAST_DDL_TIME
1387 0 NONE
MODIFIED_APPID
0 1001980 NONE
MODIFIED_VSNID
0 1001980 NONE
NAMESPACE
13 0 NONE
OBJECT_ID
61664 0 NONE
OBJECT_NAME
51007 0 NONE
OBJECT_TYPE
33 0 NONE
ORACLE_MAINTAINED
2 940105 NONE
OWNER
22 0 FREQUENCY
SECONDARY
2 0 NONE
SHARDED
1 940105 NONE
SHARING
3 940105 NONE
STATUS
2 0 NONE
SUBOBJECT_NAME
29 999795 NONE
TEMPORARY
2 0 NONE
TIMESTAMP
1499 0 NONE
27 rows selected.
demo@ORA12C>
However the best practice would be go
with auto_sample_size(the default value – that gets 100% sample) rather than
these explicit values. In order to improve the accuracy of the statistics we
should use the recommended best value of auto_sample_size (or let it default)
for estimate_percent parameter.
demo@ORA12C> exec
dbms_stats.gather_table_stats(user,'big_table');
PL/SQL procedure successfully completed.
demo@ORA12C> select num_rows,blocks,sample_size
2 from user_tables
3 where table_name ='BIG_TABLE';
NUM_ROWS BLOCKS SAMPLE_SIZE
---------- ---------- -----------
1000000 18856
1000000
demo@ORA12C> select
column_name,num_distinct,num_nulls,histogram
2 from user_tab_col_statistics
3 where table_name ='BIG_TABLE'
4 order by column_name;
COLUMN_NAME
NUM_DISTINCT NUM_NULLS HISTOGRAM
-------------------- ------------ ---------- ---------------
APPLICATION
1 938318 NONE
CREATED
1374 0 NONE
CREATED_APPID
0 1000000 NONE
CREATED_VSNID
0 1000000 NONE
DATA_OBJECT_ID
590 990461 NONE
DEFAULT_COLLATION
1 990552 NONE
DUPLICATED
1 938318 NONE
EDITIONABLE
2 978462 NONE
EDITION_NAME
0 1000000 NONE
GENERATED
2 0 NONE
ID
1000000 0 NONE
LAST_DDL_TIME
1389 0 NONE
MODIFIED_APPID
0 1000000 NONE
MODIFIED_VSNID
0 1000000 NONE
NAMESPACE
13 0 NONE
OBJECT_ID
62292 0 NONE
OBJECT_NAME
51836 0 NONE
OBJECT_TYPE
33 0 NONE
ORACLE_MAINTAINED
2 938318 NONE
OWNER
22 0 FREQUENCY
SECONDARY
2 0 NONE
SHARDED
1 938318 NONE
SHARING 3 938318 NONE
STATUS
2 0 NONE
SUBOBJECT_NAME
30 997860 NONE
TEMPORARY
2 0 NONE
TIMESTAMP
1503 0 NONE
27 rows selected.
demo@ORA12C>
so rather than having to find and edit
all the existing statistics gathering script that explicitly set
estimate_percent to its non-default value, we can set this preference parameter
“PREFERENCE_OVERRIDES_PARAMETER” to True, that will automatically override all
the non-default parameter values used in the DBMS_STATS command with the
corresponding preference values.
demo@ORA12C> select
dbms_stats.get_prefs(pname=>'ESTIMATE_PERCENT',ownname=>user,tabname=>'BIG_TABLE')
from dual;
DBMS_STATS.GET_PREFS(PNAME=>'ESTIMATE_PERCENT',OWNNAME=>USER,TABNAME=>'BIG_TABLE')
------------------------------------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
demo@ORA12C> begin
2 dbms_stats.set_table_prefs(
3 ownname=>user,
4 tabname=>'BIG_TABLE',
5
pname=>'PREFERENCE_OVERRIDES_PARAMETER',
6 pvalue=>'TRUE') ;
7 end;
8 /
PL/SQL procedure successfully completed.
demo@ORA12C> exec
dbms_stats.gather_table_stats(user,'big_table',estimate_percent=>20);
PL/SQL procedure successfully completed.
demo@ORA12C> select num_rows,blocks,sample_size
2 from user_tables
3 where table_name ='BIG_TABLE';
NUM_ROWS BLOCKS SAMPLE_SIZE
---------- ---------- -----------
1000000 18856
1000000
demo@ORA12C> select
column_name,num_distinct,num_nulls,histogram
2 from user_tab_col_statistics
3 where table_name ='BIG_TABLE'
4 order by column_name;
COLUMN_NAME
NUM_DISTINCT NUM_NULLS HISTOGRAM
-------------------- ------------ ---------- ---------------
APPLICATION
1 938318 NONE
CREATED
1374 0 NONE
CREATED_APPID
0 1000000 NONE
CREATED_VSNID
0 1000000 NONE
DATA_OBJECT_ID
590 990461 NONE
DEFAULT_COLLATION
1 990552 NONE
DUPLICATED
1 938318 NONE
EDITIONABLE
2 978462 NONE
EDITION_NAME
0 1000000 NONE
GENERATED
2 0 NONE
ID
1000000 0 NONE
LAST_DDL_TIME
1389 0 NONE
MODIFIED_APPID
0 1000000 NONE
MODIFIED_VSNID
0 1000000 NONE
NAMESPACE
13 0 NONE
OBJECT_ID
62292 0 NONE
OBJECT_NAME
51836 0 NONE
OBJECT_TYPE
33 0 NONE
ORACLE_MAINTAINED
2 938318 NONE
OWNER
22 0 FREQUENCY
SECONDARY
2 0 NONE
SHARDED
1 938318 NONE
SHARING
3 938318 NONE
STATUS
2 0 NONE
SUBOBJECT_NAME
30 997860 NONE
TEMPORARY
2 0 NONE
TIMESTAMP
1503 0 NONE
27 rows selected.
demo@ORA12C>
In our case, the estimate percent of 20% is
actually be overridden and dbms_stats.auto_sample_size is getting used.
