Step 1
Find the start and stop times of the maintenance window:
select window_name, cast(last_start_date as date) "Start Time", DURATION, cast(last_start_date+duration as date) "End Time"
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%');
Which results in:
| WINDOW_NAME | Start Time | DURATION | End Time |
| WEDNESDAY_WINDOW | 18.04.2018 23:59:00 | +00 04:00:00.000000 | 19.04.2018 03:59:00 |
So my Wednesday window starts at 23:59 and ends 4 hours later, at 03:59.
Step 2
Add the query above as a subquery in a query against DBA_OPTSTAT_OPERATIONS, to get all the IDs for the sub-steps that was executed during this window:
select ID
from DBA_OPTSTAT_OPERATIONS
where operation='gather_database_stats (auto)'
and CAST(start_time AS DATE) BETWEEN
( SELECT CAST (LAST_START_DATE AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
AND
(
SELECT CAST (LAST_START_DATE+DURATION AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
;
Step 3
Put everything together by querying DBA_OPTSTAT_OPERATION_TASKS, to see which tasks and which objects that were analyzed during the time window:
-- get the details for every task executed by "gather_database_stats (auto)"
SELECT opid,
target,
target_objn,
target_type,
target_size,
cast(start_time as date) "Start Time",
cast(end_time as date) "End Time",
status
FROM DBA_OPTSTAT_OPERATION_TASKS
WHERE OPID IN ( -- get the unique ID for the "gather_database_stats (auto)" jobs
SELECT ID FROM DBA_OPTSTAT_OPERATIONS
WHERE OPERATION='gather_database_stats (auto)'
AND CAST (START_TIME AS DATE) BETWEEN
-- get the start and stop times for the last window
-- for a particular weekday
(SELECT CAST(LAST_START_DATE AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
AND (
SELECT CAST(LAST_START_DATE+DURATION AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
)
order by CAST(start_time as DATE) asc;
Sample output (displaying the first and last 5 rows of the record set):
| OPID | TARGET | TARGET_OBJN | TARGET_TYPE | TARGET_SIZE | Start Time | End Time | STATUS |
48848 | SYS.ET$16E30DF80001 | 6177051 | TABLE | 0 | 18.04.2018 23:59:30 | 18.04.2018 23:59:30 | FAILED |
48848 | SYS.I_SEQ1 | 79 | INDEX | 1 | 18.04.2018 23:59:30 | 18.04.2018 23:59:31 | COMPLETED |
48848 | SYS.SEQ$ | 74 | TABLE | 7 | 18.04.2018 23:59:30 | 18.04.2018 23:59:31 | COMPLETED |
48848 | SYS.OPTSTAT_HIST_CONTROL$ | 506 | TABLE | 1 | 18.04.2018 23:59:31 | 18.04.2018 23:59:31 | COMPLETED |
48848 | SYS.REG$ | 5775 | TABLE | 1 | 18.04.2018 23:59:31 | 18.04.2018 23:59:31 | COMPLETED |
| ... |
48885 | SYS.WRH$_SERVICE_STAT.WRH$_SERVICE_STAT_2490506650_826 | 11986128 | TABLE PARTITION | 5 | 19.04.2018 02:00:48 | 19.04.2018 02:00:48 | COMPLETED |
48885 | SYS.WRH$_SEG_STAT_PK.WRH$_SEG_STAT_2490506650_826 | 11986126 | INDEX PARTITION | 1 | 19.04.2018 02:00:48 | 19.04.2018 02:00:48 | COMPLETED |
48885 | SYS.WRH$_SGASTAT_U.WRH$_SGASTAT_2490506650_826 | 11986114 | INDEX PARTITION | 1 | 19.04.2018 02:00:48 | 19.04.2018 02:00:48 | COMPLETED |
48885 | SYS.WRH$_SEG_STAT.WRH$_SEG_STAT_2490506650_826 | 11986124 | TABLE PARTITION | 5 | 19.04.2018 02:00:48 | 19.04.2018 02:00:48 | COMPLETED |
48885 | SYS.WRH$_SEG_STAT.WRH$_SEG_STAT_2490506650_826 | 11986124 | TABLE PARTITION | 5 | 19.04.2018 02:00:48 | 19.04.2018 02:00:48 | COMPLETED |
Check the overall status of your run:
SELECT target_type,status,count(*)
FROM DBA_OPTSTAT_OPERATION_TASKS
WHERE OPID IN ( -- get the unique ID for the "gather_database_stats (auto)" jobs
SELECT ID FROM DBA_OPTSTAT_OPERATIONS
WHERE OPERATION='gather_database_stats (auto)'
AND CAST(START_TIME AS DATE) BETWEEN
-- get the start and stop times for the last window
-- for a particular weekday
(SELECT CAST(LAST_START_DATE AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
AND (
SELECT CAST(LAST_START_DATE+DURATION AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
)
group by target_type,status
order by status;
Result:
| TARGET_TYPE | STATUS | COUNT(*) |
| INDEX | COMPLETED | 501 |
| INDEX PARTITION | COMPLETED | 177 |
| TABLE | COMPLETED | 394 |
| TABLE PARTITION | COMPLETED | 362 |
| TABLE | FAILED | 54 |
To further filter out those objecs that failed, use:
SELECT distinct target,target_objn,target_type
FROM DBA_OPTSTAT_OPERATION_TASKS
WHERE OPID IN ( -- get the unique ID for the "gather_database_stats (auto)" jobs
SELECT ID FROM DBA_OPTSTAT_OPERATIONS
WHERE OPERATION='gather_database_stats (auto)'
AND CAST(START_TIME AS DATE) BETWEEN
-- get the start and stop times for the last window
-- for a particular weekday
(SELECT CAST(LAST_START_DATE AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
AND (
SELECT CAST(LAST_START_DATE+DURATION AS DATE)
FROM DBA_SCHEDULER_WINDOWS
WHERE ENABLED='TRUE'
AND WINDOW_NAME LIKE UPPER('%&&day%')
)
)
AND status = 'FAILED'
order by TARGET;
Which in my case revealed that the following objects need attention:
| TARGET | TARGET_OBJN | TARGET_TYPE |
| SYS.ET$16E30DF80001 | 6177051 | TABLE |
| SYS.X$LOGMNR_CONTENTS | 4294951535 | TABLE |
| SYS.X$LOGMNR_REGION | 4294951631 | TABLE |
