Thursday, April 19, 2018

How do I find which objects that were analyzed during an autostats job?


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

1 comment:

  1. DBA_OPTSTAT_OPERATION_TASKS does not exists on 11g. Does exists a 11g version of such query?

    ReplyDelete