In a previous post, I showed how to check if an autostats job had succeeded.
Recently I had to unlock table stats on a very large partitioned table, and then lock down older partition stats explicitly, to allow Oracle to gather statistics for the most recent partitions automatically with the auto stats job.
I wanted to know if Oracle was able to analyze the partitions within the designated 4 hours of weekday window.
When you read the code snippet below, please keep in mind that I have defined my weekday windows to start at 23:59 every evening.
I found the following query to be useful:
select distinct
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 IN (
SELECT ID
FROM DBA_OPTSTAT_OPERATIONS
WHERE OPERATION LIKE 'gather_database_stats (auto)'
AND TO_CHAR(START_TIME,'dd.mm.yyyy hh24:mi') = '09.04.2018 23:59'
)
and target like '%SALES%';
The result:| OPID | TARGET | TARGET_TYPE | starttime | endtime | STATUS |
|---|---|---|---|---|---|
47815 | SH.SALES | TABLE | 09.04.2018 23:59:39 | 10.04.2018 03:25:17 | COMPLETED |
47815 | SH.SALES.P_SALES_DATA_2016 | TABLE PARTITION | 10.04.2018 03:25:41 | 10.04.2018 03:59:00 | TIMED OUT |
47815 | SH.SALES.P_SALES_DATA_MAX | TABLE PARTITION | 10.04.2018 03:25:33 | 10.04.2018 03:25:33 | COMPLETED |
All in all, the autostat job started on April 9th, 2018 at 23:59, had the following outcome:
select status,count(*)
from dba_optstat_operation_tasks
where opid IN (
SELECT ID
FROM DBA_OPTSTAT_OPERATIONS
WHERE OPERATION LIKE 'gather_database_stats (auto)'
AND TO_CHAR(START_TIME,'dd.mm.yyyy hh24:mi') = '09.04.2018 23:59'
)
group by status;
| STATUS | COUNT(*) |
|---|---|
| FAILED | 1 |
| TIMED OUT | 12 |
| COMPLETED | 339 |
From the output you can now proceed to find out why the analysis failed or timed out. The query below will list these objects for you:
select distinct 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 IN (
SELECT ID
FROM DBA_OPTSTAT_OPERATIONS
WHERE OPERATION LIKE 'gather_database_stats (auto)'
AND TO_CHAR(START_TIME,'dd.mm.yyyy hh24:mi') = '09.04.2018 23:59'
)
and STATUS IN ('TIMED OUT','FAILED');
