I am using version 11.2.0.4 of oracle. I want to know how we should set the global level/schema level/table level preferences?
As our database is currently running with old method of stats gathering method in which each of the parameter of stats gathering has been hard coded in the dbms_stats block itself and is being called by the respective application through the procedure or scripts etc..and we are not having proper control on the type of parameter getting used for new objects(like some application uses method_opt as "For all columns size 1" and some use "for all column size auto"). So we want to switch to the PREFERENCE method in which all the application just needs to call the generic block without any specific parameters in it and the values will be retrieved from the table/schema/global level preferences and we will having better control through this setup, rather these parameters scattered through out the script and code etc.
I have few doubts, since we know there are certain parameters whose default values maynot suit certain scenario. lets say default value of parameter METHOD_OPT is actually "for all columns size auto" but considering some of the issue associated with Histogram we do not want to collect histogram on all the columns, so we want to set the value to "for all column size 1". so my question is, as we have global level preference having all the default values, so should we just change the global prefs like below rather setting that METHOD_OPT parameters for all the tables in table level preference and we will just set the table level preferences in case of exceptional scenarios.. say any specific column needs histogram then we will only set that at table preference level for that table only? Or is there any issue with this approach and we should not play/change the global level/schema level defaults rather we should set the specific values for each table at table level preference only? in this case we need to ensure when ever new table is added it should have proper table level pref set or else it will pick the default global/schema level pref and may cause issue. So want to know, what is the appropriate method of setting this considering future oracle release implementation etc, so that we will not fall in any wrong trap.
exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE 1');
OR
exec DBMS_STATS.SET_TABLE_PREFS (USER,'TAB1,'METHOD_OPT','FOR ALL COLUMNS SIZE 1');
exec DBMS_STATS.SET_TABLE_PREFS (USER,'TAB2,'METHOD_OPT','FOR ALL COLUMNS SIZE 1');
exec DBMS_STATS.SET_TABLE_PREFS (USER,'TAB3,'METHOD_OPT','FOR ALL COLUMNS SIZE 1');
exec DBMS_STATS.SET_TABLE_PREFS (USER,'TAB4,'METHOD_OPT','FOR ALL COLUMNS SIZE 1');
Edited:
Additionally, should we rely on auto stats job to gather stats on regular basis based on set preferences? or we should have it our own which will still use the set preferences?