2012-04-25

Disabling histogram creation


If your environment only has SQL statements with bind variables then it is better to drop the existing
histograms and disable histograms from being created in the future. Disabling histogram creation
ensures the execution plan will not change depending on the bind variable value and will furthermore
reduce the time it takes to gather statistics. Without the histograms on the column the Optimizer will
assume a uniform distribution of rows across the distinct values in a column and will use NDV to
determine the cardinality estimate when it peeks at the initial bind values in the SQL statements

ex:  exec dbms_stats.delete_schema_stats('SH');


Next, change the default value for the METHOD_OPT parameter to prevent histograms from being
created in the future by using the DBMS_STATS.SET_PARAM procedure. This ensures that both the
DBMS_STATS.GATHER_*_STATS procedures and the automatic statistics gathering job will not collect
histograms in the future.
            exec dbms_stats.set_param( pname => 'METHOD_OPT', pval => 'FOR ALL COLUMN SIZE 1') ;


Note in Oracle database 11g you can drop unwanted histograms without dropping all column statistics 
by using DBMS_STATS.DELETE_COLUMN_STATS and setting the col_stat_type to histogram. 


Niciun comentariu:

Trimiteți un comentariu