Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    Automatic Optimizer Statistics Collection (Oracle Database 10g to 11g Change)

    Share

    Mathew
    Guest

    Automatic Optimizer Statistics Collection (Oracle Database 10g to 11g Change)

    Post  Mathew on Sun Nov 21, 2010 7:36 am

    In Oracle Database 11g Automatic optimizer statistics collection runs as part of the automated maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined maintenance windows. The AutoTask statistics collection replaces the Oracle Database 10g GATHER_STATS_JOB. Automatic optimizer statistics collection is enabled by default. If for some reason automatic optimizer statistics collection needs to be disabled, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package.

    BEGIN
    DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
    END;


    To re-enable automatic optimizer statistics collection, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package

    BEGIN
    DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
    END;

    You can query the dba_autotask_client and dba_autotask_job_history to find out the details of Automatic Optimizer Statistics Collection.

      Current date/time is Fri Jul 21, 2017 4:35 am