Collection strategy prefs management

Statistics collection preference settings (prefs) specify the default values for statistics collection strategies, such as the values of granularity and method_opt.

Collection strategy preferences

In Oracle mode, the following virtual tables are provided for you to query the current prefs:

• SYS.ALL_VIRTUAL_OPTSTAT_GLOBAL_PREFS_REAL_AGENT for querying prefs at the GLOBAL level.

• SYS.ALL_VIRTUAL_OPTSTAT_USER_PREFS_REAL_AGENT for querying prefs at the table level.

The following table lists the configurable preferences.

Set and retrieve prefs

Set and retrieve global prefs

You can set and retrieve global prefs by using the following stored procedures:

• reset_global_pref_defaults

• reset_param_defaults

• set_global_prefs

• set_param

• get_param

The definitions are as follows:

PROCEDURE reset_global_pref_defaults;

PROCEDURE reset_param_defaults;

PROCEDURE set_global_prefs(
  pname         VARCHAR2,
  pvalue        VARCHAR2
);

PROCEDURE set_param(
  pname          VARCHAR2,
  pval           VARCHAR2
);

FUNCTION get_param (
  pname           VARCHAR2
)RETURN VARCHAR2;

Parameters:

• pname: the name of a preference.

• pvalue: the value of the preference.

Set and retrieve table-level prefs

You can set and retrieve table-level prefs by using the following stored procedures:

• set_schema_prefs

• set_table_prefs

• delete_schema_prefs

• delete_table_prefs

• get_prefs

The definitions are as follows:

PROCEDURE set_schema_prefs(
  ownname        VARCHAR2,
  pname          VARCHAR2,
  pvalue         VARCHAR2
);

PROCEDURE set_table_prefs(
  ownname        VARCHAR2,
  tabname        VARCHAR2,
  pname          VARCHAR2,
  pvalue         VARCHAR2
);

PROCEDURE delete_schema_prefs(
  ownname        VARCHAR2,
  pname          VARCHAR2
);

PROCEDURE delete_table_prefs (
  ownname        VARCHAR2,
  tabname        VARCHAR2,
  pname          VARCHAR2
);

FUNCTION get_prefs (
  pname           VARCHAR2,
  ownname         VARCHAR2 DEFAULT NULL,
  tabname         VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;

The following table describes the parameters.

Examples

• Set the default value of the global-level APPROXIMATE_NDV parameter to FALSE.

  CALL dbms_stats.set_global_prefs('APPROXIMATE_NDV', 'FALSE');
  

• Restore all global preferences to default values.

  CALL reset_global_pref_defaults();
  

• Set the default value of DEGREE for the statistics collection strategy of tbl1 to 128 for user test.

  CALL dbms_stats.set_table_prefs('test', 'tbl1', 'DEGREE', '128');
  

• Delete the default value of DEGREE for the statistics collection strategy of tbl1 for user test.

  CALL dbms_stats.delete_table_prefs('test', 'tbl1', 'DEGREE');