2 Replies Latest reply: Jul 5, 2013 10:52 AM by MSandico RSS

    How do i know auto optimizer is running and when?

    MSandico

      Hi All,

       

      Oracle 11G on Windows here. I am wondering how i can tell the Oracle auto optimizer task is running and when the jobs have been scheduled and run.

       

      From other forums, i run this: select client_name,status, attributes, window_group,service_name from dba_autotask_client;

       

      [code]

      REM INSERTING into EXPORT_TABLE

      SET DEFINE OFF;

      Insert into EXPORT_TABLE (CLIENT_NAME,STATUS,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME) values ('auto optimizer stats collection','ENABLED','ON BY DEFAULT, VOLATILE, SAFE TO KILL','ORA$AT_WGRP_OS',null);

      Insert into EXPORT_TABLE (CLIENT_NAME,STATUS,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME) values ('auto space advisor','ENABLED','ON BY DEFAULT, VOLATILE, SAFE TO KILL','ORA$AT_WGRP_SA',null);

      Insert into EXPORT_TABLE (CLIENT_NAME,STATUS,ATTRIBUTES,WINDOW_GROUP,SERVICE_NAME) values ('sql tuning advisor','DISABLED','ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL','ORA$AT_WGRP_SQ',null);

      [/code]

       

      When i run:

       

      SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS where job_name like 'GATHER%';

      it returns 0 rows.

       

      I've read that with the new auto task feature in Oracle 11G, Oracle automatically creates these gather stats jobs then deleted them after they run. BUT they should still be in the DBA_SCHEDULER_JOB_RUN_DETAILS view with name like GATHER_STATS_23423423.

       

      Is this true? How do i know Oracle is automatically gathering stats and performing this essesntial maintenance task?

       

      Thanks in advance!