Last time, in Part 3, I reviewed collecting statistics on data dictionary and fixed objects. In today's post, I want to continue our discussion on gathering stats from Part 2 and review setting collection parameters in more detail.
Setting Parameter ValuesThere are several ways to set parameters to non-default values. One of the best ways (and recommended by Oracle) to set and manage parameter values is to use the dbms_stats.set_*_prefs procedures (where * is either global, database, schema or table). In days past, if a DBA created a manual collection script, he/she might do something like this:
begin-- Lock objects that need special handlingdbms_stats.lock_table_stats('APPOWNER','TABLE1');
-- Gather stats for the schema with all default valuesdbms_stats.gather_schema_stats('APPOWNER');
-- Gather stats for individual tables that need non-default parameter settingsdbms_stats.unlock_table_stats('APPOWNER','TABLE1');dbms_stats.gather_table_stats('APPOWNER','TABLE1',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE 1');
This method would allow a full schema collection to occur with default settings in use while skipping over any objects that were locked. Then, after the main collection completed, the specific tables with different needs would be unlocked and the stats gathered on those one-by-one specifying the parameter settings for each.
Of course, this is just one example of how it might have been done. I've seen manual collection scripts that list out every table separately using different parameter values. But, there are possible pitfalls to using such a scheme. What happens if tables are added or dropped? What happens if you need to change something or want to "reset" all your parameters back to defaults? With detailed scripts like these, you always have to make sure you have checked and re-checked everything to know that 1) everything is accounted for and 2) everything has correct individual settings. This is not only tedious, but error-prone.
Instead of going to that level of trouble, another much easier alternative is to set preferences for specific objects that have different collection requirements. Then, when a schema-level collection is done, the individual preferences are utilized appropriately allowing the use of a single collection command while still allowing a few objects to utilize non-default values at the same time.
Preference LevelsAs I mentioned above, preferences can be set at various levels: global, database, schema or table.
You can start with a clean slate initially and use dbms_stats.reset_global_pref_defaults. This procedure will reset any preference previously changed by any other set_*_prefs call and return the value to its default.
Preference NamesPreferences can be set for the following:
When used in a prefs call, the preference name is identified with the PNAME parameter. The value to which the parameter is set is identified with the PVALUE parameter. In Part 5, I'll go over each of these preferences and describe them in detail.
-- Reset all preferencesdbms_stats.reset_global_pref_defaults ;
-- Set global level preferencesdbms_stats.set_global_prefs (pname=>'STALE_PERCENT',pvalue=>'5');dbms_stats.set_global_prefs (pname=>'NO_INVALIDATE',pvalue=>FALSE);
-- Set schema level preferencesdbms_stats.set_schema_prefs (ownname=>'APPOWNER',pname=>'PUBLISH',pvalue=>'FALSE');
-- Set table level preferencesdbms_stats.set_table_prefs (ownname=>'APPOWNER',tabname=>'TABLE1',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 1');
These are just a few examples of setting preferences, but the really nice thing is that you only have to set them once and, until you wish to change them, they will remain in force without the need to reset of do anything additional. You can keep a script like the example one shown above where you keep all your preferences, or you can run the following script (written for SQL*Plus but can easily be executed in Toad or SQL*Developer with a few minor changes) any time you wish to see which preferences are currently set.
set lines 1000set pages 1000set serveroutput on
col pname format a20 heading 'Parameter Name'col pval format a50 heading 'Parameter Value'
prompt Global Preferences
select column_value pname, dbms_stats.get_prefs(pname=>column_value) pvalfrom table(sys.odcivarchar2list('ESTIMATE_PERCENT','STALE_PERCENT','INCREMENTAL','PUBLISH','NO_INVALIDATE', 'METHOD_OPT','DEGREE','GRANULARITY','CASCADE','AUTOSTATS_TARGET','CONCURRENT')) order by pname ;
accept p_schema char default 'ALL' prompt 'Enter the schema or leave blank for all: '
promptprompt Table Preferences (only if different from global prefs)
v_schema varchar2(30) := '&p_schema' ;v_pval varchar2(4000) ;
cursor param_cur isselect column_value pname, dbms_stats.get_prefs(pname=>column_value) pvalfrom table(sys.odcivarchar2list('ESTIMATE_PERCENT','STALE_PERCENT','INCREMENTAL','PUBLISH','NO_INVALIDATE', 'METHOD_OPT','DEGREE','GRANULARITY','CASCADE','AUTOSTATS_TARGET','CONCURRENT')) order by pname ;
cursor schema_cur isselect username from dba_users where username not in ('SYS','SYSTEM','DBSNMP','XDB')and (username = v_schema or v_schema = 'ALL')and account_status = 'OPEN' order by username;
cursor table_cur is select table_name from dba_tableswhere (owner = v_schema or v_schema = 'ALL')order by owner, table_name ;
for param_rec in param_cur loop
for schema_rec in schema_cur loop for table_rec in table_cur loop select dbms_stats.get_prefs(pname=>param_rec.pname, ownname=>schema_rec.username, tabname=>table_rec.table_name) into v_pval from dual ; if v_pval is null or v_pval = param_rec.pval then null; else dbms_output.put_line (param_rec.pname || ',' || v_pval || ',' || schema_rec.username || ',' || table_rec.table_name) ; end if; end loop; end loop; end loop;
SummaryManaging statistics collection parameters is easy to do using the dbms_stats.set_*_prefs procedures. Default preferences can be used as a general rule and only specific changes can be made for objects that are better suited to non-default values. By using preference settings, once you set a preference, it will be stored and utilized for all future collections without the need to include the specific parameter values in the gather_*_stats call. This greatly simplifies collections and helps eliminate the need for detailed manual collection scripts where objects are dealt with one-by-one.
A Look AheadIn Part 5, I'll review each of the parameters and the values to which they can be set.