Oracle automatic collection task statistics, SQL tuning

Time:2021-2-25

–View auto collection task status

SELECT CLIENT_NAME,STATUS FROM DBA_AUTOTASK_CLIENT;

SELECT CLIENT_NAME, STATUS
  FROM DBA_AUTOTASK_CLIENT
 WHERE CLIENT_NAME = 'auto optimizer stats collection';

–Turn off auto optimizer stats collection

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection',
                               OPERATION   => NULL,
                               WINDOW_NAME => NULL);
END;
/

–Shut down SQL optimizer SQL Tuning Advisor (STA)

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor',
                               OPERATION   => NULL,
                               WINDOW_NAME => NULL);
END;
/

–Turn off auto space Advisor

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor',
                               OPERATION   => NULL,
                               WINDOW_NAME => NULL);
END;
/

–Start the auto optimizer stats collection task

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto optimizer stats collection',
                              OPERATION   => NULL,
                              WINDOW_NAME => NULL);
END;
/

–Start SQL optimizer SQL Tuning Advisor (STA)

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'sql tuning advisor',
                              OPERATION   => NULL,
                              WINDOW_NAME => NULL);
END;
/

–Start auto space Advisor

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(CLIENT_NAME => 'auto space advisor',
                              OPERATION   => NULL,
                              WINDOW_NAME => NULL);
END;
/

–View the sub time windows contained in the time window group of the automatic collection task

 EXECUTE DBMS_AUTO_TASK_ADMIN.enABLE;

select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';

–View collection statistics details

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;

–Close Saturday

BEGIN
DBMS_SCHEDULER.DISABLE(
NAME=>'"SYS"."SATURDAY_WINDOW"',
FORCE=>TRUE);
END;

–Modify 4 a.m. collection

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
NAME=>'"SYS"."SATURDAY_WINDOW"',
ATTRIBUTE=>'REPEAT_INTERVAL',
VALUE=>'freq=daily;byday=SAT;byhour=8;byminute=0; bysecond=0');
END;
/

–Modify window start time

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');

–Adjust duration Monday

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;

Recommended Today

Third party calls wechat payment interface

Step one: preparation 1. Wechat payment interface can only be called if the developer qualification has been authenticated on wechat open platform, so the first thing is to authenticate. It’s very simple, but wechat will charge 300 yuan for audit 2. Set payment directory Login wechat payment merchant platform( pay.weixin.qq . com) — > Product […]