Wednesday, February 8, 2017

How to check if the "gather_database_stats" automatic maintenance jobs has succseeded

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