【常用命令】自动统计收集的停止（无效）和启动（有效）

■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