Tuesday, May 17, 2016

How long does my auto optimizer stats collection job takes to run?

This query helps identify how long the automatic stats collection job takes to run.

The default maintenance window is from 10pm until 2am.

col client_name for a40
col job_name for a30
col job_status for a10
col job_duration for a50
set linesize 200
select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION 
from DBA_AUTOTASK_JOB_HISTORY 
where JOB_START_TIME >systimestamp -7 and client_name='auto optimizer stats collection';

CLIENT_NAME                              JOB_NAME                       JOB_STATUS JOB_START_TIME                      JOB_DURATION                                      
---------------------------------------- ------------------------------ ---------- ----------------------------------- --------------------------------------------------
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2522          STOPPED    5/12/2016 10:00:11.870810 PM -07:00 +00 03:59:51.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2502          STOPPED    5/11/2016 10:00:12.471104 PM -07:00 +00 03:59:50.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2482          STOPPED    5/10/2016 10:00:12.256956 PM -07:00 +00 03:59:49.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2462          STOPPED    5/9/2016 10:00:12.331670 PM -07:00  +00 04:00:03.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2442          STOPPED    5/8/2016 6:07:27.878272 PM -07:00   +00 07:52:40.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2423          SUCCEEDED  5/8/2016 7:10:14.887914 AM -07:00   +00 06:58:59.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2422          STOPPED    5/7/2016 10:08:38.445532 PM -07:00  +00 03:51:30.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2405          SUCCEEDED  5/7/2016 6:00:43.423975 AM -07:00   +00 12:44:10.000000                               
auto optimizer stats collection          ORA$AT_OS_OPT_SY_2402          STOPPED    5/6/2016 10:00:08.874159 PM -07:00  +00 03:59:59.000000

No comments :

Post a Comment