oracle查看自动任务,Oracle 自动收集任务-统计信息、SQL Tuning

-- 查看自动收集任务状态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 A30

COL ACTUAL_START_DATE FOR A40

COL RUN_DURATION FOR A30

SET LINES 180 PAGES 100

-- 10GSELECT 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;

-- 关闭周六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=6;byminute=0;bysecond=0');

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0');

-- 调整持续时间 周一BEGIN

DBMS_SCHEDULER.DISABLE(NAME => 'TUESDAY_WINDOW');

DBMS_SCHEDULER.SET_ATTRIBUTE(NAME => 'TUESDAY_WINDOW',

ATTRIBUTE => 'DURATION',

VALUE => NUMTODSINTERVAL(2, 'hour'));

DBMS_SCHEDULER.ENABLE(NAME => 'TUESDAY_WINDOW');

END;