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 Values
There 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 handling
dbms_stats.lock_table_stats('APPOWNER','TABLE1');

-- Gather stats for the schema with all default values
dbms_stats.gather_schema_stats('APPOWNER');

-- Gather stats for individual tables that need non-default parameter settings
dbms_stats.unlock_table_stats('APPOWNER','TABLE1');
dbms_stats.gather_table_stats('APPOWNER','TABLE1',estimate_percent=>30,method_opt=>'FOR ALL COLUMNS SIZE 1');

end;
/

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 Levels
As I mentioned above, preferences can be set at various levels: global, database, schema or table.

  • global - highest level; base default settings
  • database - sets preference for all tables regardless of schema, excluding Oracle-owned tables
  • schema - sets preference for all tables in a specified schema
  • table - sets preference for a specified 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 Names
Preferences can be set for the following:

  • AUTOSTATS_TARGET (only available with set_global_prefs)
  • CASCADE
  • DEGREE
  • ESTIMATE_PERCENT
  • METHOD_OPT
  • NO_INVALIDATE
  • GRANULARITY
  • PUBLISH
  • INCREMENTAL
  • STALE_PERCENT

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.

Examples
begin

-- Reset all preferences
dbms_stats.reset_global_pref_defaults ;

-- Set global level preferences
dbms_stats.set_global_prefs (pname=>'STALE_PERCENT',pvalue=>'5');
dbms_stats.set_global_prefs (pname=>'NO_INVALIDATE',pvalue=>FALSE);

-- Set schema level preferences
dbms_stats.set_schema_prefs (ownname=>'APPOWNER',pname=>'PUBLISH',pvalue=>'FALSE');

-- Set table level preferences
dbms_stats.set_table_prefs (ownname=>'APPOWNER',tabname=>'TABLE1',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 1');

end;
/

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.

-- get_stat_prefs.sql

set lines 1000
set pages 1000
set 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) pval
from table(sys.odcivarchar2list('ESTIMATE_PERCENT','STALE_PERCENT','INCREMENTAL','PUBLISH','NO_INVALIDATE',
'METHOD_OPT','DEGREE','GRANULARITY','CASCADE','AUTOSTATS_TARGET','CONCURRENT'))
order by pname ;

prompt

accept p_schema char default 'ALL' prompt 'Enter the schema or leave blank for all: '

prompt
prompt Table Preferences (only if different from global prefs)

declare

v_schema varchar2(30) := '&p_schema' ;
v_pval varchar2(4000) ;

cursor param_cur is
select column_value pname, dbms_stats.get_prefs(pname=>column_value) pval
from 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 is
select 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_tables
where (owner = v_schema or v_schema = 'ALL')
order by owner, table_name ;


begin

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;

end;
/

Summary
Managing 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 Ahead
In Part 5, I'll review each of the parameters and the values to which they can be set.