Database Reference
In-Depth Information
STATS_UPDATE_TIME
--------------------------------
26-MAR-14 22.03.03.104730 +01:00
27-MAR-14 22.01.14.193033 +01:00
13-APR-14 14.14.57.461660 +02:00
Whenever it may be necessary, statistics can be restored from the history. For that purpose, the
dbms_stats
package provides the following procedures:
•
restore_database_stats
restores object statistics for the whole database.
•
restore_dictionary_stats
restores object statistics for the data dictionary.
•
restore_fixed_objects_stats
restores object statistics for fixed tables and their indexes.
•
restore_schema_stats
restores object statistics for a single schema.
restore_table_stats
restores object statistics for a single table.
In addition to the parameters specifying the target (for example, the schema and table names for the
restore_table_stats
procedure), all these procedures provide the following parameters:
•
•
as_of_timestamp
specifies to restore the statistics that were in use at a specific time.
•
force
specifies whether locked statistics should be overwritten. Note that locks on statistics
are part of the history. This means the information about whether statistics are locked or not is
also restored. The default value is
FALSE
.
no_invalidate
specifies whether cursors depending on the overwritten statistics are
invalidated. This parameter accepts the values
TRUE
,
FALSE
, and
dbms_stats.auto_invalidate
.
The default value is
dbms_stats.auto_invalidate
.
The following call restores the object statistics of the
SH
schema to the values that were in use one day ago.
Because the
force
parameter is set to
TRUE
, the restore is done even if statistics are currently locked:
•
dbms_stats.restore_schema_stats(ownname => 'SH',
as_of_timestamp => systimestamp - INTERVAL '1' DAY,
force => TRUE)
Locking Object Statistics
In some situations, you want to make sure that object statistics for part of the database aren't available or can't be
changed, either because you want to use dynamic sampling (see Chapter 9), because you have to use object statistics
that aren't up-to-date (for example, because the content of some tables changes very frequently and you want to
carefully gather stats only when the table contains a representative set of rows), or because gathering statistics isn't
possible (for example, because of bugs).
It's possible to explicitly lock object statistics by executing one of the following procedures in the
dbms_stats
package. Note that these locks have nothing to do with regular database locks. They are, in fact, simple flags set at the
table level in the data dictionary:
•
lock_schema_stats
locks object statistics for all tables belonging to a schema:
dbms_stats.lock_schema_stats(ownname => user)
•
lock_table_stats
locks object statistics for a single table:
