查看统计信息分析每次经历的时间

最新推荐文章于 2023-12-26 09:30:00 发布

转载最新推荐文章于 2023-12-26 09:30:00 发布 · 168 阅读

本文介绍 Oracle Database 10g 中 dbms_stats 包的使用情况，该包会在数据字典中记录其执行信息，通过 dba_optstat_operations 视图可以查询到这些操作的时间及持续时长。

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-689179/