Shulou(Shulou.com)06/01 Report--

In oracle9i, the default statistics collection does not collect histogram information, that is, the default MOTHOD_OPT mode is FOR ALL COLUMNS SIZE 1

Starting with 10g, the default METHOD_OPT in the dbms_stats package has been adjusted, and the default METHOD_ opt value is FOR ALL COLUMNS SIZE AUTO.

SQL > select * from vendorversionbot BannerMurray Oracle Database for Linux: Version select dbms_stats.get_param ('method_opt') from dual; DBMS_STATS.GET_PARAM (' METHOD_OPT')-FOR ALL COLUMNS SIZE AUTO

This shows that, starting from 10g, there is oracle in the histogram part of statistical information collection. Since judging from the actual use, the intelligent judgment of oracle is not 100% correct.

Oracle often collects a lot of histogram information that is not necessary, and some histogram information will have an unnecessary impact on the query.

Since we simply delete the histogram, the automatic statistics of oracle will be collected again, so we need to take some necessary measures to avoid this problem.

10g: solution delete table statistics collect target statistics manually, do not collect histogram lock table statistics create JOB manual collection statistics 11g

In 11g, oracle has added new features to the dbms_stats package, which can be modified by using the dbms_stats.set_table_prefs package.

Delete histogram information:

Dbms_stats.delete_column_stats procedure and setting the col_stat_type parameter to HISTOGRAM.

BEGIN dbms_stats.delete_column_stats (ownname= > 'SH', tabname= >' SALES', colname= > 'PROD_ID', col_stat_type= > HISTOGRAM'); END; Use the new dbms_stats.set_table_pref procedure to set a specific value for the method_opt parameter for the table effected by this problem. The following value for the method_opt parameter tells Oracle to continue to collect histograms as usual on all of the columns in the SALES table except for the PROD_ID column, which should never have a histogram created on it. BEGIN dbms_stats.set_table_prefs ('SH',' SALES','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID'); END;/

The auto stats gathering job or your own statistics gathering commands will now use the table preference you set when it gathers statistics on this table and will no longer create a histogram on the ID column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.