Statistics preference management

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

Tables for storing statistics preferences

Currently, the OceanBase Database optimizer uses two internal tables to store global-level and table-level statistics preferences, respectively.

• The internal table __all_optstat_global_prefs stores global-level statistics preferences.

• The internal table __all_optstat_user_prefs stores table-level statistics preferences.

The following table describes the fields in the __all_optstat_global_prefs internal table.

Note

When a tenant is created, the related preferences are inserted into __all_optstat_global_prefs. You cannot add data to or delete data from this table, but you can modify the data in this table.

The following table describes the fields in the __all_optstat_user_prefs internal table.

Note

Data in the __all_optstat_user_prefs internal table is explicitly inserted when the user creates the table. When the OceanBase Database optimizer obtains a preference, it queries the __all_optstat_user_prefs internal table first. If the preference cannot be found in __all_optstat_user_prefs, the optimizer queries the __all_optstat_global_prefs internal table.

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

• SYS.ALL_VIRTUAL_OPTSTAT_GLOBAL_PREFS_REAL_AGENT for querying global-level preferences

• SYS.ALL_VIRTUAL_OPTSTAT_USER_PREFS_REAL_AGENT for querying table-level preferences

Configurable statistics preferences

The following table lists the configurable statistics preferences.

Configure and retrieve statistics preferences

Configure and retrieve global-level statistics preferences

You can configure and retrieve global-level statistics 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;

Parameters:

• pname: the name of the preference.

• pvalue: the value of the preference.

Configure and retrieve table-level statistics preferences

You can configure and retrieve table-level statistics 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 default value of the global-level APPROXIMATE_NDV parameter to FALSE.

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

• Reset all global-level 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');