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 |
DBA_OPTSTAT_OPERATION_TASKS does not exists on 11g. Does exists a 11g version of such query?
ReplyDelete