-- 查看主动收集工作状态
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 collection
BEGIN
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 advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor',
OPERATION => NULL,
WINDOW_NAME => NULL);
END;
/
-- 启动主动收集工作 auto optimizer stats collection
BEGIN
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 advisor
BEGIN
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 A30
COL 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;
-- 11G
SELECT 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;
-- 敞开周六
BEGIN
DBMS_SCHEDULER.DISABLE(
NAME=>'"SYS"."SATURDAY_WINDOW"',
FORCE=>TRUE);
END;
-- 批改早上 4 点收集
BEGIN
DBMS_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)
BEGIN
DBMS_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;
/