檢視統計資訊分析每次經歷的時間

wei-xh發表於2011-03-11

As of Oracle Database 10g, the procedures in the package dbms_stats working at database, data
dictionary, or schema level, log information about their execution in the data dictionary. This
logging information is externalized through the data dictionary view dba_optstat_operations.
Basically, you are able to know which operations were performed, when they were started, and
how long they took. The following example, which is an excerpt taken from a production database,
shows that the procedure gather_database_stats is started every day, except on Sunday,
and takes between 10 and 17 minutes to run:

SELECT operation,target, start_time,
   (end_time-start_time) DAY(1) TO SECOND(0) AS duration
   FROM dba_optstat_operations
    ORDER BY DURATION desc;

OPERATION                      TARGET     START_TIME                               DURATION
------------------------------ ---------- ---------------------------------------- ------------
gather_schema_stats            APOLLO     07-MAR-11 10.19.55.408841 PM +08:00      +0 03:19:11
gather_schema_stats            VODKA      08-MAR-11 01.39.06.322315 AM +08:00      +0 00:54:06
gather_schema_stats            BPM        07-MAR-11 09.54.06.545743 PM +08:00      +0 00:25:49
gather_schema_stats            CAESAR     08-MAR-11 02.33.12.589664 AM +08:00      +0 00:13:16
export_schema_stats            APOLLO     19-FEB-11 11.00.45.480386 PM +08:00      +0 00:00:19
export_schema_stats            APOLLO     26-FEB-11 11.02.02.211903 PM +08:00      +0 00:00:13
export_schema_stats            APOLLO     05-MAR-11 11.03.56.529214 PM +08:00      +0 00:00:12
export_schema_stats            APOLLO     07-MAR-11 09.53.50.080197 PM +08:00      +0 00:00:11
export_schema_stats            APOLLO     12-FEB-11 10.58.29.181090 PM +08:00      +0 00:00:10
export_schema_stats            VODKA      07-MAR-11 09.54.02.209570 PM +08:00      +0 00:00:02
export_schema_stats            BPM        07-MAR-11 09.54.01.232017 PM +08:00      +0 00:00:01
export_schema_stats            CAESAR     07-MAR-11 09.54.04.475133 PM +08:00      +0 00:00:01

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-689179/,如需轉載,請註明出處,否則將追究法律責任。

相關文章