Statistics preference management

Statistics preferences specify the default values for statistics collection strategies, such as the values of granularity and method_opt.

Available statistics preferences

In Oracle mode, the following virtual tables are provided for you to query the current settings of statistics preferences:

• SYS.ALL_VIRTUAL_OPTSTAT_GLOBAL_PREFS_REAL_AGENT for querying global preference settings

• SYS.ALL_VIRTUAL_OPTSTAT_USER_PREFS_REAL_AGENT for querying table-level preference settings

The following table lists the available preferences that you can set.

Set and retrieve preferences

Set and retrieve global preferences

You can set and retrieve global preferences 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;

The parameters are described as follows:

• pname: the name of a preference.

• pvalue: the value of the preference.

Set and retrieve table-level preferences

You can set and retrieve table-level preferences 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 global APPROXIMATE_NDV preference to FALSE.

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

• Reset all global preferences to their default values.

  CALL reset_global_pref_defaults();
  

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

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

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

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