Database Reference
In-Depth Information
■
The export and import procedures provided by the
dbms_stats
package process locks set with the
techniques described in the “Locking Object Statistics” section earlier in this chapter. The only exception, which applies
only to releases prior to version 11.2.0.2, is related to tables without object statistics. For such tables, locks disappear
when their object statistics are exported and imported.
Caution
Logging of Management Operations
Many procedures in the
dbms_stats
package log information about their execution in the data dictionary. This
logging information is externalized through the
dba_optstat_operations
and, in a 12.1 multitenant environment,
cdb_optstat_operations
views. Basically, you can know which operations were performed, when they were started,
and how long they took. As of version 12.1, information about the status,
6
the session, and (optionally) the job
associated with the operation is also available. The following example, an excerpt taken from a production database,
shows that the
gather_database_stats
procedure was started every day and took between 9 and 18 minutes to run
(note that April 5-6, 2014 is a weekend):
SQL> SELECT operation, start_time,
2 (end_time-start_time) DAY(1) TO SECOND(0) AS duration
3 FROM dba_optstat_operations
4 ORDER BY start_time DESC;
OPERATION START_TIME DURATION
--------------------------- ----------------------------------- -----------
gather_database_stats(auto) 09-APR-14 10.00.08.877925 PM +02:00 +0 00:10:28
gather_database_stats(auto) 08-APR-14 10.00.02.899209 PM +02:00 +0 00:09:30
gather_database_stats(auto) 07-APR-14 10.00.04.119250 PM +02:00 +0 00:12:45
gather_database_stats(auto) 06-APR-14 10.05.00.173419 PM +02:00 +0 00:00:55
gather_database_stats(auto) 06-APR-14 06.04.46.957190 PM +02:00 +0 00:00:50
gather_database_stats(auto) 06-APR-14 02.04.32.438573 PM +02:00 +0 00:00:53
gather_database_stats(auto) 06-APR-14 10.04.16.208319 AM +02:00 +0 00:01:27
gather_database_stats(auto) 06-APR-14 06.00.09.299059 AM +02:00 +0 00:04:55
gather_database_stats(auto) 05-APR-14 10.03.28.888807 PM +02:00 +0 00:00:58
gather_database_stats(auto) 05-APR-14 06.03.14.637546 PM +02:00 +0 00:00:43
gather_database_stats(auto) 05-APR-14 02.02.59.997594 PM +02:00 +0 00:01:06
gather_database_stats(auto) 05-APR-14 10.02.46.052860 AM +02:00 +0 00:01:13
gather_database_stats(auto) 05-APR-14 06.00.03.801439 AM +02:00 +0 00:06:05
gather_database_stats(auto) 04-APR-14 10.00.03.068541 PM +02:00 +0 00:17:32
gather_database_stats(auto) 03-APR-14 10.00.02.781440 PM +02:00 +0 00:06:59
gather_database_stats(auto) 02-APR-14 10.00.02.702294 PM +02:00 +0 00:12:45
gather_database_stats(auto) 01-APR-14 10.00.03.254860 PM +02:00 +0 00:12:48
