System Statistics - Troubleshooting Oracle Performance - page 195

To execute the alter_stats_history_retention and purge_stats procedures, you need to have the analyze
any and analyze any dictionary system privileges.
Working with a Backup Table
Most dbms_stats procedures used for managing system statistics are able to work with either the data dictionary or
a backup table. But there's one procedure limited to working only with the data dictionary: the restore_system_stats
procedure.
While all operations are performed against the data dictionary by default, procedures supporting a backup
table provide three parameters for you to use if you want to work with a backup table instead. The three parameters
are as follows:
stattab specifies the name of a table outside the data dictionary where the statistics are
stored. The default value is NULL .
statown specifies the owner of the table specified with the stattab parameter. The default
value is NULL , and therefore the current user is used.
statid is an optional identifier used to recognize multiple sets of statistics stored in the
backup table, specified with the stattab and statown parameters. Only valid Oracle
identifiers 2 are supported.
For example, the following call gathers noworkload statistics and stores them in the backup table named
mystats , which is owned by the system user:
dbms_stats.gather_system_stats(gathering_mode => 'noworkload',
statown => 'system',
stattab => 'mystats')
To create a backup table, invoke the create_stat_table procedure in the dbms_stats package. Creation is a
matter of specifying the owner (with the ownname parameter) and the name (with the stattab parameter) of the
backup table. In addition, the optional tblspace parameter specifies the tablespace in which the table is created. If the
tblspace parameter isn't specified, the table ends up in the default tablespace of the user. Following is an example:
dbms_stats.create_stat_table(ownname => user,
stattab => 'mystats',
tblspace => 'users')
The dbms_stats package provides the drop_stat_table procedure to drop a backup table. You can also drop
a backup table with a regular DROP TABLE statement. For example:
dbms_stats.drop_stat_table(ownname => user,
stattab => 'mystats')
Logging of Management Operations
All dbms_stats procedures used for managing system statistics, except for restore_system_stats , log some information
about their activities into the data dictionary. This information is externalized through the dba_optstat_operations view
and, from version 12.1 onward, also in the dba_optstat_operation_tasks view. Note that in a multitenant environment,