When passing method_opt as 'FOR ALL COLUMNS SIZE 1', it means no histogram is collected for columns on the table(s). exec dbms_stats.gather_schema_stats('SCOTT', method_opt=>'FOR ALL COLUMNS SIZE 1'); One can find the default value by executing as follows: select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;
Tag: gather
Tom Kyte’s utility to collect statistics
Tom Kyte's following routine is a utility that can be used to to collect statistics of a session at the start, middle and at the end. http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701
Global Statistics vs. Histograms with DBMS_STATS Package
The following article explains global statistics versus histogram with DBMS_STATS http://www.akadia.com/services/ora_gather_statistics.html
How to lock/unlock statistics on a table?
In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn't change. The following example … Continue reading How to lock/unlock statistics on a table?