Object Statistics - Troubleshooting Oracle Performance - page 239

• The user who submits the gathering must have either the dba role or the following privileges:

CREATE JOB , MANAGE SCHEDULER , and MANAGE ANY QUEUE .

• no_invalidate specifies whether cursors depending on the processed objects are invalidated
and, therefore, whether their future usage is prevented. This parameter accepts the values TRUE ,
FALSE , and dbms_stats.auto_invalidate . When the parameter is set to TRUE , the cursors
depending on the changed object statistics aren't invalidated and, therefore, remain available
for future executions. On the other hand, if it's set to FALSE , all the cursors are immediately
invalidated. With the value dbms_stats.auto_invalidate , which is a constant evaluating to
NULL , the cursors are invalidated over a period of time. This last possibility is good for avoiding
reparsing spikes. The default value is dbms_stats.auto_invalidate (this default value can be
changed—see the section “Configuring the dbms_stats package” later in this chapter).

When dbms_stats.auto_invalidate is used, the dbms_stats package marks all cursors that depend on the
changed statistics for delayed invalidation. The package sets a timestamp at the cursor level specifying when the
cursor should no longer be used. Note that the timestamp is different for every cursor and is based on a random
value of up to five hours from the moment the cursor is marked. The real invalidation is performed by the server
processes that try to reuse a cursor that's marked for delayed invalidation. As a result, if a cursor marked for
delayed invalidation is never reparsed, it will never be invalidated. The only exceptions are cursors related to
parallel SQL statements. Such cursors are immediately invalidated by the dbms_stats package.

Backup Table
The backup table parameters listed in Table 8-2 are supported by all procedures used for gathering object statistics.
They instruct the dbms_stats package to back up current statistics in a backup table before overwriting them with the
new ones in the data dictionary. The parameters are as follows:

stattab specifies a backup table outside the data dictionary where the statistics are stored.
If NULL (the default value) is specified, no backup table is used.

•

statid is an optional ID used to recognize multiple sets of object statistics stored in the
backup table specified with the stattab parameter. Only valid Oracle identifiers 4 are
supported. If NULL (the default value) is specified, no ID is associated to the object statistics.

•

statown specifies the owner of the table specified with the stattab parameter. The default
value is NULL , and therefore the current user is used.
To create the backup table, the dbms_stats package provides the create_stat_table procedure. As shown in
the following example, its 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 in which
tablespace the table is created. If the tblspace parameter isn't specified, by default, the table ends up in the default
tablespace of the user:

•

dbms_stats.create_stat_table(ownname => user,
stattab => 'MYSTATS',
tblspace => 'USERS')