오라클 19c autotask, 자동통계수집 잡 확인 및 비활성화

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 19.27.0.0

 

방법 : 오라클 19c autotask, 자동통계수집 잡 확인 및 비활성화

본문에서는 19c에서 자동통계정보 수집 잡을 비활성화하고 비활성화시 나오는 개념인 window_clients, autotask를 설명함
자동통계정보 수집 잡 뿐만 아니라 autotask에 등록된 3가지 모두 제어하는 방법도 설명함

 

 

테스트
dba_autotask_operation 뷰 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14

set lines 200 pages 1000 col client_name for a40 col operation_name for a30 col stats for a10 col attributes for a60 select client_name, operation_name, status, attributes from dba_autotask_operation; --where client_name = 'auto optimizer stats collection';   CLIENT_NAME                              OPERATION_NAME                 STATUS   ATTRIBUTES ---------------------------------------- ------------------------------ -------- ------------------------------------------------------------ auto optimizer stats collection          auto optimizer stats job       ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL auto space advisor                       auto space advisor job         ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL sql tuning advisor                       automatic sql tuning task      ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

여기서 auto optimizer stats collection이 자동 통계 수집 job임

 

 

비활성화 방법
각 client_name을 아래 프로시저에 넣어주고 실행하면됨

 

 

자동통계잡 비활성화

1 2 3 4 5 6 7 8 9 10 11

SQL> BEGIN   DBMS_AUTO_TASK_ADMIN.DISABLE(     client_name => 'auto optimizer stats collection',     operation   => null,     window_name => null   ); END; /   PL/SQL procedure successfully completed.

 

 

dba_autotask_operation 뷰 재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> set lines 200 pages 1000 col client_name for a40 col operation_name for a30 col stats for a10 col attributes for a60 select client_name, operation_name, status, attributes from dba_autotask_operation; --where client_name = 'auto optimizer stats collection';   CLIENT_NAME                              OPERATION_NAME                 STATUS   ATTRIBUTES ---------------------------------------- ------------------------------ -------- ------------------------------------------------------------ auto optimizer stats collection          auto optimizer stats job       DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL auto space advisor                       auto space advisor job         ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL sql tuning advisor                       automatic sql tuning task      ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

자동 통계 수집 job이 DISABLED로 변경됨
컬럼 설명 :
CLIENT_NAME : 작업의 이름 (예: 'auto optimizer stats collection')
OPERATION_NAME : 실제 수행하는 작업 이름 (gather stats 등)
STATUS : ENABLED / DISABLED 상태 확인 가능
ATTRIBUTES : 작업 속성에 대한 부가 정보

 

 

윈도우별 자동 통계 수집 활성 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL> set lines 200 pages 1000 col window_name for a30 select window_name, window_active, autotask_status,  optimizer_stats, segment_advisor, sql_tune_advisor, to_char(window_next_time, 'yyyy/mm/dd hh24:mi:ss') as next_run from dba_autotask_window_clients order by window_name;   WINDOW_NAME                    WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE NEXT_RUN ------------------------------ ----- -------- -------- -------- -------- ------------------- FRIDAY_WINDOW                  FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/06/27 22:00:00 MONDAY_WINDOW                  FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/06/30 22:00:00 SATURDAY_WINDOW                FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/06/28 06:00:00 SUNDAY_WINDOW                  FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/06/29 06:00:00 THURSDAY_WINDOW                FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/06/26 22:00:00 TUESDAY_WINDOW                 FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/07/01 22:00:00 WEDNESDAY_WINDOW               FALSE ENABLED  DISABLED ENABLED  ENABLED  2025/06/25 22:00:00   7 rows selected.

optimizer_stats 부분이 disabled로 되어있음
윈도우란? : "언제, 얼마나 오래 자동 작업을 실행할 수 있는지"를 지정해놓은 예약된 시간 구간(오라클이 자동 작업을 실행하기 위해 열어두는 시간 예약표)
컬럼 설명 :
WINDOW_NAME : 유지관리 윈도우 이름
WINDOW_ACTIVE : 지금 윈도우가 실행 중인지 (TRUE / FALSE)
AUTOTASK_STATUS : 이 윈도우에서 유지관리 작업 전체 상태
OPTIMIZER_STATS : 이 윈도우에서 자동 통계 수집이 활성화됐는지 여부
NEXT_RUN : 다음 실행 예정 시간

 

 

해당 윈도우들이 몇시에 실행되는지 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL>  set lines 200 pages 1000 col window_name for a30 col repeat_interval for a70 col duration for a20 select window_name, repeat_interval, duration, enabled from dba_scheduler_windows order by window_name;   WINDOW_NAME                    REPEAT_INTERVAL                                                        DURATION             ENABL ------------------------------ ---------------------------------------------------------------------- -------------------- ----- FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00        TRUE MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00        TRUE SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00        TRUE SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00        TRUE THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00        TRUE TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00        TRUE WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00        TRUE WEEKEND_WINDOW                 freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00        FALSE WEEKNIGHT_WINDOW               freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00        FALSE   9 rows selected.

컬럼 설명 :
WINDOW_NAME : 윈도우 이름
REPEAT_INTERVAL : 이 윈도우가 언제 반복되는지 나타냄(FREQ, BYDAY, BYHOUR 등의 문법은 Oracle Scheduler의 Repeat 표현 방식)
DURATION : 이 윈도우가 열려 있는 시간 +000 04:00:00이면 4시간 동안 유지됨을 의미함
ENABLED : 이 윈도우가 현재 활성화되어 사용되는지 여부, TRUE이면 스케줄대로 실행됨, FALSE이면 비활성화 상태
MONDAY_WINDOW 윈도우를 예를들어 설명하면 매주 월요일 밤 10시 정각에 시작(freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0)하고 4시간동안 유지됨(+000 04:00:00), 현재 동작하는 상태임(TRUE)

 

 

auto task 전체 비활성화

1 2 3

SQL> exec dbms_auto_task_admin.disable;   PL/SQL procedure successfully completed.

 

 

재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> set lines 200 pages 1000 col client_name for a40 col operation_name for a30 col stats for a10 col attributes for a60 select client_name, operation_name, status, attributes from dba_autotask_operation;   CLIENT_NAME                              OPERATION_NAME                 STATUS   ATTRIBUTES ---------------------------------------- ------------------------------ -------- ------------------------------------------------------------ auto optimizer stats collection          auto optimizer stats job       DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL auto space advisor                       auto space advisor job         ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL sql tuning advisor                       automatic sql tuning task      ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

전체 비활성화를 했지만 자동통계잡만 disabled이고 나머지는 여전히 enabled로 보임

 

 

일단 자동통계잡 다시 활성화

1 2 3 4 5 6 7 8 9 10 11

SQL> BEGIN   DBMS_AUTO_TASK_ADMIN.ENABLE(     client_name => 'auto optimizer stats collection',     operation   => null,     window_name => null   ); END; /   PL/SQL procedure successfully completed.

 

 

재확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL> set lines 200 pages 1000 col client_name for a40 col operation_name for a30 col stats for a10 col attributes for a60 select client_name, operation_name, status, attributes from dba_autotask_operation;   CLIENT_NAME                              OPERATION_NAME                 STATUS   ATTRIBUTES ---------------------------------------- ------------------------------ -------- ------------------------------------------------------------ auto optimizer stats collection          auto optimizer stats job       ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL auto space advisor                       auto space advisor job         ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL sql tuning advisor                       automatic sql tuning task      ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

자동통계 잡이 다시 활성화됨

 

 

위에서 dbms_auto_task_admin.disable 명령을 통해 전체 task를 비활성화하려 했음
하지만 실제로 dba_autotask_operation에서 봤을때 status가 그대로 enabled였음
이 명령어는 전체 task를 변경하는 명령이기때문에 dba_autotask_operation와 같은 개별적인 클라이언트를 보여주는 뷰에서는 표시되지 않음
dba_autotask_window_clients 뷰에서 확인해야함

 

 

윈도우별 자동 통계 수집 활성 상태 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SQL> set lines 200 pages 1000 col window_name for a30 select window_name, window_active, autotask_status,  optimizer_stats, segment_advisor, sql_tune_advisor, to_char(window_next_time, 'yyyy/mm/dd hh24:mi:ss') as next_run from dba_autotask_window_clients order by window_name;   WINDOW_NAME                    WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE NEXT_RUN ------------------------------ ----- -------- -------- -------- -------- ------------------- FRIDAY_WINDOW                  FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/06/27 22:00:00 MONDAY_WINDOW                  FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/06/30 22:00:00 SATURDAY_WINDOW                FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/06/28 06:00:00 SUNDAY_WINDOW                  FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/06/29 06:00:00 THURSDAY_WINDOW                FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/06/26 22:00:00 TUESDAY_WINDOW                 FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/07/01 22:00:00 WEDNESDAY_WINDOW               FALSE DISABLED ENABLED  ENABLED  ENABLED  2025/06/25 22:00:00   7 rows selected.

기존과는 다르게 autotask_status 컬럼이 disabled로 변경됨

 

 

위 내용을 쉽게 설명하자면
오라클의 autotask는 집 전기 배전함 구조와 유사하게 작동한다고 보면됨
집에는 메인 스위치가 있고 각 방에는 개별 전등 스위치가 있음

 

 

dbms_auto_task_admin.disable 단일 명령은 메인 스위치(aka 두꺼비집)를 내리는 것과 같음
이걸 수행하면 개별 작업이 enabled 상태라고 해도 실제로는 실행되지 않음
전기 공급이 차단된 상태에서 방 스위치를 켜봤자 불은 들어오지 않음

 

 

반대로 dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection') 같이 client_name을 명시해주는 명령은 개별 방의 스위치를 끄는 것과 같음
이 경우는 메인 스위치가 올라가 있는 상태에서만 의미가 있음
즉 전기 공급이 되고 있어야 개별 스위치의 on off 여부가 실제 작동에 영향을 줌

 

 

dba_autotask_operation 뷰는 각 방의 스위치 위치를 보여주는 뷰임
여기서 status가 enabled로 보이더라도 메인 전원이 꺼져 있으면 실제로는 동작하지 않음

 

 

dba_autotask_window_clients 뷰는 실제 전기가 흐르고 불이 켜질 수 있는지 여부를 보여주는 뷰임
여기에는 개별 스위치뿐 아니라 메인 스위치 상태인 autotask_status도 함께 표시됨
이 값이 disabled이면 전체 유지관리 기능이 꺼진 상태이고 아무 작업도 실행되지 않음(optimizer_stats, segment_advisor, sql_tune_advisor 모두 미실행)

 

 

그렇기 때문에 실제 동작 여부는 dba_autotask_operation이 아니라 dba_autotask_window_clients에서 확인해야 더 정확함
여기서 optimizer_stats나 autotask_status가 disabled로 되어 있으면 자동 통계 수집은 실행되지 않음

 

 

정리하면 메인 스위치를 내리면 집 전체에 전기가 끊기는 것처럼 dbms_auto_task_admin.disable은 자동 유지관리 전체를 멈추게 함
각 방의 스위치를 조작하려면 client_name을 명시해서 disable하거나 enable해야 하고
실제 어떤 작업이 실행될 수 있는지는 window_clients 뷰를 확인해야 알 수 있음

 

 

참고. 관련 뷰

dba_auto_stat_executions
dba_autotask_client
dba_autotask_client_history
dba_autotask_client_job
dba_autotask_job_history
dba_autotask_operation
dba_autotask_schedule
dba_autotask_schedule_control
dba_autotask_status
dba_autotask_task
dba_autotask_window_clients
dba_autotask_window_history
dba_scheduler_windows

 

 

결론 :
dba_autotask_operation에 나오는 개별 autotask를 중지시키고 싶은 경우 dbms_auto_task_admin.disable(client_name => ~)를 수행하면됨
이 경우 dba_autotask_window_clients의 개별 client_name 부분이 disabled로 변경됨
dba_autotask_window_clients에 나오는 전체 autotask를 중지시키고 싶은 경우 dbms_auto_task_admin.disable를 수행하면됨
이 경우 dba_autotask_window_clients의 autotask_status가 disabled로 변경됨

 

 

참조 : 

DBMS_AUTO_TASK_ADMIN.DISABLE still shows the client_name enabled in DBA_AUTOTASK_CLIENT view (Doc ID 1565768.1)
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_TASK_ADMIN.html#GUID-1820ACEA-FAB9-47E9-A1B2-0B41FF9DCC5F
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_AUTO_TASK_ADMIN.html#GUID-8CD58D39-436B-4642-BAEB-00C651DE8A8F