■Keyword:

优化器 统计信息 optimizer stats  statistics auto tasks

自动统计信息收集

■正文：

以下为自动统计收集(Automatic Statistics Collection)的停止（无效）和启动（有效）的方法。

11g以后的版本：

1.确认自动统计收集(Automatic Statistics Collection)的状态

    例：

    COL CLIENT_NAME FORMAT a31

    SELECT CLIENT_NAME, STATUS

    FROM   DBA_AUTOTASK_CLIENT

    WHERE  CLIENT_NAME = 'auto optimizer stats collection';

2.自动统计收集(Automatic Statistics Collection)的停止（无效）方法

    例：

    BEGIN

      DBMS_AUTO_TASK_ADMIN.DISABLE (  

        client_name  => 'auto optimizer stats collection'

    ,   operation    => NULL

    ,   window_name  => NULL

    );

    END;

    /

3.自动统计收集(Automatic Statistics Collection)的启动（有效）方法

    例：

    BEGIN

      DBMS_AUTO_TASK_ADMIN.ENABLE (  

        client_name  => 'auto optimizer stats collection'

    ,   operation    => NULL

    ,   window_name  => NULL

    );

    END;

    /

4.自动统计收集(Automatic Statistics Collection)的执行历史。

    例：

    set linesize 300

    set pagesize 100

    col CLIENT_NAME for a35;

    col window_name for a20;

    col window_start_time for a35;

    col WINDOW_DURATION for a35;

    select * from dba_autotask_client_history

    where CLIENT_NAME = 'auto optimizer stats collection';

10g的版本：

1.确认自动统计收集(Automatic Statistics JOB)的状态

   例：

    SELECT job_name, state

     FROM DBA_SCHEDULER_JOBS

     WHERE job_name='GATHER_STATS_JOB';

2.自动统计收集的停止（无效）方法

   例：

    exec dbms_scheduler.disable('GATHER_STATS_JOB');

3.自动统计收集的启动（有效）方法

   例：

    exec dbms_scheduler.enable('GATHER_STATS_JOB');

4.自动统计收集的执行历史。

   例：

    select * from DBA_SCHEDULER_JOB_LOG

    where JOB_NAME = 'GATHER_STATS_JOB';

    select * from DBA_SCHEDULER_JOB_RUN_DETAILS

    where JOB_NAME = 'GATHER_STATS_JOB';

■执行例

    -bash-4.1$ sqlplus / as sysdba

    Connected to:

    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    SQL> COL CLIENT_NAME FORMAT a31

    SELECT CLIENT_NAME, STATUS

    FROM   DBA_AUTOTASK_CLIENT

    WHERE  CLIENT_NAME = 'auto optimizer stats collection';SQL>   2    3

    CLIENT_NAME                     STATUS

    ------------------------------- --------

    auto optimizer stats collection ENABLED

    SQL> BEGIN

      DBMS_AUTO_TASK_ADMIN.DISABLE (

        client_name  => 'auto optimizer stats collection'

    ,   operation    => NULL

    ,   window_name  => NULL

    );

    END;

    /  2    3    4    5    6    7    8

    PL/SQL procedure successfully completed.

    SQL> SELECT CLIENT_NAME, STATUS

    FROM   DBA_AUTOTASK_CLIENT

    WHERE  CLIENT_NAME = 'auto optimizer stats collection';  2    3

    CLIENT_NAME                     STATUS

    ------------------------------- --------

    auto optimizer stats collection DISABLED

    SQL> BEGIN

      DBMS_AUTO_TASK_ADMIN.DISABLE (

        client_name  => 'auto optimizer stats collection'

    ,   operation    => NULL

    ,   window_name  => NULL

    );

    END;

    /  2    3    4    5    6    7    8

    PL/SQL procedure successfully completed.

    SQL> BEGIN

      DBMS_AUTO_TASK_ADMIN.ENABLE (

        client_name  => 'auto optimizer stats collection'

    ,   operation    => NULL

    ,   window_name  => NULL

    );

    END;

    /  2    3    4    5    6    7    8

    PL/SQL procedure successfully completed.

    SQL> SELECT CLIENT_NAME, STATUS

    FROM   DBA_AUTOTASK_CLIENT

    WHERE  CLIENT_NAME = 'auto optimizer stats collection';

      2    3

    CLIENT_NAME                     STATUS

    ------------------------------- --------

    auto optimizer stats collection ENABLED

    SQL>

    SQL> set linesize 300

    set pagesize 100

    col CLIENT_NAME for a35;

    col window_name for a20;

    col window_start_time for a35;

    col WINDOW_DURATION for a35;SQL> SQL> SQL> SQL> SQL>

    SQL> select * from dba_autotask_client_history

    where CLIENT_NAME = 'auto optimizer stats collection';   2

    CLIENT_NAME                         WINDOW_NAME          WINDOW_START_TIME                   WINDOW_DURATION         JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME

    ----------------------------------- -------------------- ----------------------------------- ----------------------------------- ------------ ------------ -------------- --------------------------------

    auto optimizer stats collection     FRIDAY_WINDOW        17-AUG-18 10.00.00.456844 PM +00:00 +000000000 03:59:59.793112       17           17          0 18-AUG-18 02.00.00.249956 AM +00:00

    auto optimizer stats collection     FRIDAY_WINDOW        24-AUG-18 10.00.00.363430 PM +00:00 +000000000 03:59:59.894939       17           17          0 25-AUG-18 02.00.00.258369 AM +00:00

    auto optimizer stats collection     MONDAY_WINDOW        13-AUG-18 10.00.00.180899 PM +00:00 +000000000 04:00:00.002172       17           17          0 14-AUG-18 02.00.00.183071 AM +00:00

    auto optimizer stats collection     MONDAY_WINDOW        20-AUG-18 10.00.00.391454 PM +00:00 +000000000 03:59:59.754087       17           17          0 21-AUG-18 02.00.00.145541 AM +00:00

    auto optimizer stats collection     SATURDAY_WINDOW      18-AUG-18 06.00.00.266367 AM +00:00 +000000000 20:00:00.047784      113          113          0 19-AUG-18 02.00.00.314151 AM +00:00

    auto optimizer stats collection     SATURDAY_WINDOW      25-AUG-18 06.00.00.066274 AM +00:00 +000000000 20:00:00.240956      113          113          0 26-AUG-18 02.00.00.307230 AM +00:00

    auto optimizer stats collection     SUNDAY_WINDOW        19-AUG-18 06.00.00.064142 AM +00:00 +000000000 20:00:00.480723      113          113          0 20-AUG-18 02.00.00.544865 AM +00:00

    auto optimizer stats collection     SUNDAY_WINDOW        26-AUG-18 06.00.00.095080 AM +00:00 +000000000 20:00:00.038830      113          113          0 27-AUG-18 02.00.00.133910 AM +00:00

    auto optimizer stats collection     THURSDAY_WINDOW      16-AUG-18 10.00.01.356192 PM +00:00 +000000000 03:59:58.902128       18           18          0 17-AUG-18 02.00.00.258320 AM +00:00

    auto optimizer stats collection     THURSDAY_WINDOW      23-AUG-18 10.00.00.298115 PM +00:00 +000000000 04:00:00.073176       18           18          0 24-AUG-18 02.00.00.371291 AM +00:00

    auto optimizer stats collection     TUESDAY_WINDOW       14-AUG-18 10.00.00.200087 PM +00:00 +000000000 03:59:59.917263       18           18          0 15-AUG-18 02.00.00.117350 AM +00:00

    auto optimizer stats collection     TUESDAY_WINDOW       21-AUG-18 10.00.00.370821 PM +00:00 +000000000 03:59:59.777210       18           18          0 22-AUG-18 02.00.00.148031 AM +00:00

    auto optimizer stats collection     TUESDAY_WINDOW       11-SEP-18 10.00.00.279092 PM +00:00 +000000000 03:59:59.866087       17           17          0 12-SEP-18 02.00.00.145179 AM +00:00

    auto optimizer stats collection     WEDNESDAY_WINDOW     15-AUG-18 10.00.00.223601 PM +00:00 +000000000 03:59:59.983360       17           17          0 16-AUG-18 02.00.00.206961 AM +00:00

    auto optimizer stats collection     WEDNESDAY_WINDOW     22-AUG-18 10.00.00.379332 PM +00:00 +000000000 03:59:59.815978       17           17          0 23-AUG-18 02.00.00.195310 AM +00:00

    auto optimizer stats collection     WEDNESDAY_WINDOW     12-SEP-18 10.00.00.431903 PM +00:00 +000000000 03:59:59.669209       18           18          0 13-SEP-18 02.00.00.101112 AM +00:00

    16 rows selected.

    SQL>

关于统计信息收集可参考过去文章：

自动统计信息收集（Automatic Optimizer Statistics Collection）

■参考

    Home/Database/Oracle/Oracle Database/Release 12.2

    SQL Tuning Guide

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/gathering-optimizer-statistics.html#GUID-E4EFD512-EAF9-4AF3-943F-FDEC7E47B23C

    >13.1.1 About Automatic Optimizer Statistics Collection