select id,operation,to_char(start_time,'dd.mm.yyyy hh24:mi:ss') "starttime", to_char(end_Time,'dd.mm.yyyy hh24:mi:ss') "endtime", to_dsinterval(to_char(end_time-start_Time,'dd.mm.yyyy hh24:mi:ss')) "elapsed", status,job_name from DBA_OPTSTAT_OPERATIONS where operation = 'gather_database_stats (auto)' order by start_time desc fetch first 5 rows only;
Gave the following result:
ID | OPERATION | starttime | endtime | elapsed | STATUS | JOB_NAME |
---|---|---|---|---|---|---|
58778 | gather_database_stats (auto) | 07.02.2017 22:00:02 | 08.02.2017 02:00:15 | +00 04:00:13.178197 | TIMED OUT | ORA$AT_OS_OPT_SY_1965 |
58585 | gather_database_stats (auto) | 06.02.2017 22:00:06 | 07.02.2017 02:00:24 | +00 04:00:18.089479 | TIMED OUT | ORA$AT_OS_OPT_SY_1963 |
58273 | gather_database_stats (auto) | 05.02.2017 06:00:04 | 06.02.2017 02:00:26 | +00 20:00:21.968028 | TIMED OUT | ORA$AT_OS_OPT_SY_1950 |
58088 | gather_database_stats (auto) | 04.02.2017 06:00:02 | 05.02.2017 02:00:32 | +00 20:00:29.696085 | TIMED OUT | ORA$AT_OS_OPT_SY_1945 |
58038 | gather_database_stats (auto) | 03.02.2017 22:00:02 | 04.02.2017 02:00:17 | +00 04:00:15.314408 | TIMED OUT | ORA$AT_OS_OPT_SY_1943 |
The output indicates that the Automatic maintenance job never get to finish before the maintenance Windows Closes.
You can examine another view, dba_opstat_operation_tasks, to see the details on what the job didn't have time to do before the window closed:
select opid, target,target_type, to_char(start_Time,'dd.mm.yyyy hh24:mi:ss') "starttime", to_char(end_time,'dd.mm.yyyy hh24:mi:ss') "endtime" ,status from dba_optstat_operation_tasks where opid=58778 order by start_Time desc ;
Sample output:
OPID | TARGET | TARGET_TYPE | starttime | endtime | STATUS |
---|---|---|---|---|---|
58778 | SCOTT.TABLE1.SYS_SUBP25275 | TABLE | 08.02.2017 02:00:11 | 08.02.2017 02:00:11 | TIMED OUT |
58778 | SCOTT.TABLE1.SYS_SUBP25267 | TABLE | 08.02.2017 02:00:11 | 08.02.2017 02:00:11 | TIMED OUT |
Notice how the starttime and endtime is identical; this is because the window Closes, and the job aborts. Oracle then records this timestamp as the start time and end time for the Objects it never had time to analyze.
No comments:
Post a Comment