oracle统计信息时间预估,查看统计信息分析每次经历的时间

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