I went through some articles in the web as well as in the forum regarding stats gathering which I have posted here.
http://structureddata.org/2008/03/26/choosing-an-optimal-stats-gathering-strategy/
In the above post author mentions that
"It may be best to change the default value of the METHOD_OPT via DBMS_STATS.SET_PARAM to 'FOR ALL COLUMNS SIZE REPEAT' and gather stats with your own job. Why REPEAT and not SIZE 1? You may find that a histogram is needed somewhere and using SIZE 1 will remove it the next time stats are gathered. Of course, the other option is to specify the value for METHOD_OPT in your gather stats script"
Following one is post from Oracle forums.
603975
In the above post Mr Lewis mentions about adding
method_opt => 'for all columns size 1' to the DBMS job
And in the same forum post Mr Richard Foote has mentioned that
"Not only does it change from 'FOR ALL COLUMNS SIZE 1' (no histograms) to 'FOR ALL COLUMNS SIZE AUTO' (histograms for those tables that Oracle deems necessary based on data distribution and whether sql statements reference the columns), but it also generates a job by default to collect these statistics for you.
It all sounds like the ideal scenario, just let Oracle worry about it for you, except for the slight disadvantage that Oracle is not particularly "good" at determining which columns really need histograms and will likely generate many many many histograms unnecessarily while managing to still miss out on generating histograms on some of those columns that do need them."
http://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/
Our environment Windows 2003 server Oracle 10.2.0.3 64bit oracle
We use the following script for our analyze job.
BEGIN DBMS_STATS.GATHER_TABLE_STATS
(ownname => ''username'', '
'tabname => TABLE_NAME
'method_opt => ''FOR ALL COLUMNS SIZE AUTO''
'granularity => ''ALL'', '
'cascade => TRUE, '
'degree => DBMS_STATS.DEFAULT_DEGREE);
END;
This anayze job runs a long time (8hrs) and we are also facing performance issues in production environment.
Here are my questions
What is the option I should use for method_opt parameter?
I am sure there are no hard and fast rules for this and each environment is different.
But reading all the above post kind of made me confused and want to be sure we are using the correct options.
I would appreciate any suggestions, insight or further readings regarding the same.
Appreciate your time
Thanks
Niki