Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Disabling automatic statistics gathering in 10g and 11g

Hi,

Well, If your database environment has well established statistics gathering mechanism or your application has specific requirement of statistics gathering, you may need to disable the automatic statistics gathering, this is a bit different in 10g and 11g.

All of us aware starting 10g automatic statistics gather job will kicked off every night 10PM and runs for an 8 hours and then stopped.

Further, starting from 11g along with automatic statistics gathering few additional jobs also run as follows,

Oracle 11g includes three automated database maintenance tasks:

  • Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects (more info). The task name is 'auto optimizer stats collection'.
  • Automatic Segment Advisor - Identifies segments that could be reorganized to save space (more info). The task name is 'auto space advisor'.
  • Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL (more info). The task name is 'sql tuning advisor'.
  • But if you want to disable the same, in 10g and 11g here are the ways.

      10g:-
    SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
    

    or

    SQL> exec dbms_stats.set_param('AUTOSTATS_TARGET', 'ORACLE');

    Find more documentation here.
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1048566

      11g:-

    Note:- the dbms_stats.set_param has been deprecated and dbms_stats.set_***_prefs has been introduced in 11g.
    note if you keep AUTO instead of Oracle you are enabling the stats.

    SQL> exec dbms_stats.set_global_prefs('AUTOSTATS_TARGET','ORACLE'); 

    or

    Note:- this will disable all three maintainance tasks for all windows
    (since 11g the maintainance scheduled tasks has been differentiated as windows)

    SQL> EXEC DBMS_AUTO_TASK_ADMIN.disable;
    

    or

    Note:- (the window_name and client name can be found in DBA_AUTOTASK_WINDOW_CLIENTS, here we are disabling only monday window)

    SQL>  begin
        DBMS_AUTO_TASK_ADMIN.disable(
        client_name => 'auto optimizer stats collection',
        operation   => NULL,
        window_name => 'MONDAY_WINDOW');
    
    SQL>  DBMS_AUTO_TASK_ADMIN.disable(
        client_name => 'auto space advisor',
        operation   => NULL,
        window_name => 'MONDAY_WINDOW');
    
    SQL>  DBMS_AUTO_TASK_ADMIN.disable(
        client_name => 'sql tuning advisor',
        operation => NULL,
        window_name => 'MONDAY_WINDOW');
    END;
    /
    
    SYS on 07-AUG-12 at orcl >select * from DBA_AUTOTASK_WINDOW_CLIENTS where WINDOW_NAME = 'MONDAY_WINDOW';
    WINDOW_NAME                    WINDOW_NEXT_TIME                                                         WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
    ------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
    MONDAY_WINDOW                  08-AUG-12 10.00.00.000000 PM ASIA/CALCUTTA                               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
    1 rows selected.
    SYS on 07-AUG-12 at orcl >BEGIN
    2    DBMS_AUTO_TASK_ADMIN.disable(
    3      client_name => 'auto optimizer stats collection',
    4      operation   => NULL,
    5      window_name => 'MONDAY_WINDOW');
    6  END;
    7  /
    PL/SQL procedure successfully completed.
    SYS on 07-AUG-12 at orcl >select * from DBA_AUTOTASK_WINDOW_CLIENTS where WINDOW_NAME = 'MONDAY_WINDOW';
    WINDOW_NAME                    WINDOW_NEXT_TIME                                                         WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
    ------------------------------ --------------------------------------------------------------------------- ----- -------- -------- -------- -------- --------
    MONDAY_WINDOW                  08-AUG-12 10.00.00.000000 PM ASIA/CALCUTTA                               FALSE ENABLED  DISABLED ENABLED  ENABLED  DISABLED
    1 rows selected.
    SYS on 07-AUG-12 at orcl >
    

    Find More:- http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41448

    Hope this helps
    -Thanks
    Geek DBA

    Comments are closed.