Default parameter settings when gathering statistics on 9i and 10g

This document outlines how to determine the default parameter settings when gathering statistics on Table on 9i and 10g.

On 9i, Gather procedures have a number of hard coded default values
On 10g, All procedures that gather optimizer statistics no longer have hardcoded default values.

The defaults can be viewed using :

select dbms_stats.get_param(‘cascade’) from dual;
select dbms_stats.get_param(‘degree’) from dual;
select dbms_stats.get_param(‘estimate_percent’) from dual;
select dbms_stats.get_param(‘method_opt’) from dual;
select dbms_stats.get_param(‘no_invalidate’) from dual;
select dbms_stats.get_param(‘granularity’) from dual;

Parameters can be set using DBMS_STATS.SET_PARAM.

EXAMPLES

Get Parameter Example

SQL> select dbms_stats.get_param(‘method_opt’) from dual;

DBMS_STATS.GET_PARAM(‘METHOD_OPT’)
———————————-
FOR ALL COLUMNS SIZE AUTO

Set Parameter Example

SQL> exec dbms_stats.set_param(‘METHOD_OPT’, ‘FOR ALL COLUMNS SIZE 1’)
PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param(‘method_opt’) from dual;

DBMS_STATS.GET_PARAM(‘METHOD_OPT’)
———————————-
FOR ALL COLUMNS SIZE 1

The default values on 9i are hard coded and are as follows:

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL, –> ALL partitions
estimate_percent NUMBER DEFAULT NULL, –> 100% sample
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT ‘FOR ALL COLUMNS SIZE 1’,
degree NUMBER DEFAULT NULL, –> parallel degree 1
granularity VARCHAR2 DEFAULT ‘DEFAULT’, –> level (PARTITION + GLOBAL)
cascade BOOLEAN DEFAULT FALSE, –> does not cascade to indexes by default
no_invalidate BOOLEAN DEFAULT FALSE);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: