-- 查看主动收集工作状态SELECT CLIENT_NAME,STATUS FROM DBA_AUTOTASK_CLIENT;SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'auto optimizer stats collection';-- 敞开信息主动收集auto optimizer stats collectionBEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => NULL);END;/-- 敞开SQL优化器SQL Tuning Advisor(STA)BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor', OPERATION => NULL, WINDOW_NAME => NULL);END;/-- 敞开主动空间诊断auto space advisorBEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor', OPERATION => NULL, WINDOW_NAME => NULL);END;/-- 启动主动收集工作auto optimizer stats collectionBEGIN DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => NULL);END;/-- 启动SQL优化器SQL Tuning Advisor(STA)BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'sql tuning advisor', OPERATION => NULL, WINDOW_NAME => NULL);END;/-- 启动主动空间诊断auto space advisorBEGIN DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto space advisor', OPERATION => NULL, WINDOW_NAME => NULL);END;/-- 查看主动收集工作所属工夫窗口组蕴含的子工夫窗口 EXECUTE DBMS_AUTO_TASK_ADMIN.enABLE;select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';-- 查看收集统计信息明细SET LINES 350;COL WINDOW_NAME FOR A20;COL DURATION FOR A15;COL REPEAT_INTERVAL FOR A70;COL NEXT_START_DATE FOR A25;COL LAST_START_DATE FOR A25;SELECT W.WINDOW_NAME, W.REPEAT_INTERVAL, W.DURATION, W.NEXT_START_DATE, W.LAST_START_DATE, W.ENABLED FROM DBA_AUTOTASK_WINDOW_CLIENTS C, DBA_SCHEDULER_WINDOWS W WHERE C.WINDOW_NAME = W.WINDOW_NAME AND C.OPTIMIZER_STATS = 'ENABLED';COL JOB_NAME FOR A30COL ACTUAL_START_DATE FOR A40 COL RUN_DURATION FOR A30 SET LINES 180 PAGES 100 -- 10G SELECT OWNER, JOB_NAME, STATUS, ACTUAL_START_DATE, RUN_DURATION FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'GATHER_STATS_JOB' ORDER BY 4; -- 11GSELECT OWNER, JOB_NAME, STATUS, ACTUAL_START_DATE, RUN_DURATION FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME LIKE 'ORA$AT_OS_OPT_S%' ORDER BY 4;-- 敞开周六BEGINDBMS_SCHEDULER.DISABLE(NAME=>'"SYS"."SATURDAY_WINDOW"',FORCE=>TRUE);END;-- 批改早上4点收集BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(NAME=>'"SYS"."SATURDAY_WINDOW"',ATTRIBUTE=>'REPEAT_INTERVAL',VALUE=>'freq=daily;byday=SAT;byhour=8;byminute=0; bysecond=0');END;/-- 批改窗口启动工夫EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0');-- 调整持续时间(2h)BEGINDBMS_SCHEDULER.DISABLE(NAME => 'SATURDAY_WINDOW');DBMS_SCHEDULER.SET_ATTRIBUTE(NAME => 'SATURDAY_WINDOW',ATTRIBUTE => 'DURATION',VALUE => NUMTODSINTERVAL(8, 'hour'));DBMS_SCHEDULER.ENABLE(NAME => 'SATURDAY_WINDOW');END;/-- 查看启用工作明细COL WINDOW_NAME FOR A20;COL DURATION FOR A15;COL REPEAT_INTERVAL FOR A70;SELECT W.WINDOW_NAME, W.REPEAT_INTERVAL, W.DURATION, W.ENABLED FROM DBA_AUTOTASK_WINDOW_CLIENTS C, DBA_SCHEDULER_WINDOWS W WHERE C.WINDOW_NAME = W.WINDOW_NAME;-- 查看执行记录COL WINDOW_NAME FOR a18;COL DURATION FOR a15;COL JOB_DURATION FOR a15;COL REPEAT_INTERVAL FOR A60;SELECT CH.WINDOW_NAME, W.REPEAT_INTERVAL, W.DURATION JOB_DURATION, TO_CHAR(CH.WINDOW_START_TIME, 'yyyy-mm-dd hh24:mi:ss') EXEC_START_TIME, TO_CHAR(CH.WINDOW_END_TIME, 'yyyy-mm-dd hh24:mi:ss') EXEC_END_TIME, CH.JOBS_CREATED, CH.JOBS_COMPLETED FROM DBA_AUTOTASK_CLIENT_HISTORY CH, DBA_SCHEDULER_WINDOWS W WHERE CH.CLIENT_NAME LIKE '%stats%' AND CH.WINDOW_NAME = W.WINDOW_NAME AND W.ENABLED = 'TRUE' AND WINDOW_START_TIME >= SYSDATE - 7 ORDER BY WINDOW_START_TIME DESC;-- 查看正在执行的主动收集工作SELECT CLIENT_NAME, JOB_NAME, JOB_SCHEDULER_STATUS FROM DBA_AUTOTASK_CLIENT_JOB WHERE CLIENT_NAME = 'auto optimizer stats collection';-- 终止正在运行的主动收集工作exec DBMS_SCHEDULER.DISABLE(NAME => 'FRIDAY_WINDOW');exec dbms_scheduler.stop_job(ORA$AT_OS_OPT_SY_161);exec dbms_scheduler.stop_job(ORA$AT_OS_OPT_SY_161,force);begin DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL );end;/