Oracle 통계정보 자동 갱신

AutoTask 의 3개의 Task 작업
1. - 옵티마이저 통계 수집 (Automatic Optimizer Statistics Collection)
2. - 세그먼트 어드바이져 (Automatic Segment Advisor)
3. - SQL 튜닝 어드바이져 (Automatic SQL Tuning Advisor)

AUTO TASK 실행 가능 환경 확인

JOB QUEUE PROCESS 개수 확인

자동 수행 뷰 목록

select * from gv$parameter
 where name like '%job_queue_processes%'
;

통계 레벨 확인 확인

select * from gv$parameter
 where name like '%statistics_level%'
;

JOB QUEUE PROCESS 개수 변경

-- 만약 0 이면 적당한 값으로 변경 
alter system set job_queue_processes=100 scope=both;

통계 수집 레벨 변경

-- 기본은  'typecal' 로 등록된 스케줄러에 의해 동작.
-- all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집.
-- 구지 상세하게 통계정보를 수집하고 싶다면 all (비추천), 통계 정보 수집시 라이브러리캐시 정보 갱신 , 라이브러리 캐시 락 발생 가능성 존재 
alter system set statistics_level = all scope=both;

자동 수행 뷰 목록

틀:고지 상자

자동 수행 스케줄 목록 조회

요일별 자동 수행 목록

DBA_AUTOTASK_WINDOW_CLIENTS

SELECT WINDOW_NAME
     , AUTOTASK_STATUS -- 자동수행 태스크 상태 
     , OPTIMIZER_STATS -- 통계 수집 작업  
     , SEGMENT_ADVISOR -- 디스크 상태 수집 작업
     , SQL_TUNE_ADVISOR -- 튜닝 상태 수집 작업
     , HEALTH_MONITOR  -- 모니터링 
  FROM DBA_AUTOTASK_WINDOW_CLIENTS;

-- AUTOTASK OPERATION
SELECT * 
  FROM DBA_AUTOTASK_OPERATION;

auto optimizer stats collection

자동 수행 목록

DBA_AUTOTASK_CLIENT

SELECT CLIENT_NAME
     , STATUS
     , MEAN_JOB_DURATION
     , TOTAL_CPU_LAST_7_DAYS
  FROM DBA_AUTOTASK_CLIENT;

MEAN_JOB_DURATION은 평균 job 수행시간. not null이라면 한 번이라도 수행된 적이 있다는 것을 의미.
TOTAL_CPU_LAST_7_DAYS은 최근 7일동안의 Cumulative CPU time , 이것으로 최근 수행여부를 확인.

자동 수행 목록 히스토리 조회

DBA_AUTOTASK_CLIENT_HISTORY

SELECT * 
  FROM DBA_AUTOTASK_CLIENT_HISTORY
 ORDER BY WINDOW_START_TIME DESC
;

auto optimizer stats collection

자동 수행 시작/정지

--disable Autotask  
exec dbms_auto_task_admin.disable;

--ensable Autotask  
exec dbms_auto_task_admin.enable;

통계정보만 수집 정지

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
END;

틀:고지 상자

통계/디스크/튜닝 정보 수집 활성화

/*
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE (  
    client_name  => 'auto optimizer stats collection'
,   operation    => NULL
,   window_name  => NULL 
);
*/

BEGIN
    -- 1.통계정보 수집 활성화
    dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
    -- 2.디스크 정보 수집 활성화
    dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
    -- 3.튜닝 정보 수집 활성화  
    dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

자동 통계정보 수집

틀:고지 상자

자동 통계정보 수집 기능(Automatic Statistics) 대상

틀:고지 상자

자동 통계정보 수집 옵션

statistics_level = {all | typical | basic} 

typical => 전반적인 DB 성능과 관련 통계정보와 DB 자가 관리 기능관련 정보를 수집(기본값)
all => typical + 일정시간 OS 통계정보와 실행 계획 통계를 수집.
basic => 수집을 하지 않음.

자동 통계정보 수집 변경

- 통계정보 parameter 확인

$ sqlplus / as sysdba
SQL > show parameter statistics_level

database level 적용

SQL > alter system set statistics_level = 'ALL';
SQL > alter system set statistics_level = 'BASIC';

session level 적용

SQL > alter session set statistics_level = 'ALL';
SQL > alter session set statistics_level = 'BASIC';

자동 통계정보 수집 확인

select job_name, program_name, schedule_name, job_class
  from dba_scheduler_jobs
 where job_name ='GATHER_ STATS_JOB'

참고) 자동통계정보 수집에서 window의 의미는 수행될 작업에 할당될 시스템 자원에 대한 정보

자동 통계정보 수집 잡 실행 로그 확인

SELECT *
  FROM (  SELECT LOG_DATE
               , JOB_NAME
               , STATUS
               , ACTUAL_START_DATE
               , RUN_DURATION
            FROM DBA_SCHEDULER_JOB_RUN_DETAILS
           WHERE JOB_NAME = 'GATHER_STATS_JOB'
        ORDER BY LOG_ID DESC)
 WHERE ROWNUM <= 10;

자동 통계정보 수집 시 실행되는 program 확인

select program_action 
  from dba_scheduler_programs 
 where program_name ='GATHER_STATS_PROG'

참고) dba_scheduler_programs 는 DB내의 모든 scheduler program을 확인하는 system view

자동 통계정보 수집 시 스케줄 확인

 select * 
  from dba_scheduler_wingroup_members 
 where window_group_name ='MAINTENANCE_WINDOW_GROUP';

참고) dba_scheduler_wingroup_members 는 각각의 time windows 어떻게 정의되어 있는지 확인하는 system view 입니다.

자동 통계정보 수집 스케줄 확인

select window_name, repeat_interval, duration
  from dba_scheduler_windows;
--  where window_name in ('WEEKNIGHT_WINDOW','WEEKEND_WINDOW');

- 매주 월,화,수,목,금요일은 밤 10시에 8시간 동안 수행. - 토요일 0시에 수행되어 이틀 동안 수행.

자동 통계정보 수집 스케줄 시간 변경

틀:고지 상자

-- 1,스케줄 윈도우 확인 
select window_name,repeat_interval,duration from dba_scheduler_windows; 

-- 2. 윈도우 작동 시간 증가 
exec dbms_scheduler.set_attribute('MONDAY_WINDOW','DURATION','+000 04:00:00');

자동 통계정보 수집 스케줄 활성화

-- 토요일 활성화 
exec dbms_scheduler.enable('SATURDAY_WINDOW');

자동 통계정보 수집 중지

SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';
SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';

요일별 통계수집 스케줄러 정지

exec dbms_scheduler.disable('WEEKNIGHT_WINDOW');
exec dbms_scheduler.disable('WEEKEND_WINDOW');

자동 통계정보 수집중인 작업 수동 종료

-- 작동중인 토요일 스케줄러 종료
execute dbms_scheduler.close_window ('SATURDAY_WINDOW');

자동 통계정보 수집 재설정

SQL> exec dbms_scheduler.enable('GATHER_STATS_JOB');
SQL> select job_name, state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';

DBA_AUTOTASK_WINDOW_CLIENTS 테이블 WINDOW_NEXT_TIME 컬럼이 과거날짜인경우

DBA_AUTOTASK_WINDOW_CLIENTS.WINDOW_NEXT_TIME이 과거 날짜인 경우 윈도우를 DROP후 재생성 필요.

-- 윈도우 drop할경우 발생된느 에러는 무시해도 됨.
@?/RDBMS/ADMIN/CATNOMWN.SQL 
or
-- 수동 스케줄 삭제 
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('MONDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('TUESDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('WEDNESDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('THURSDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('FRIDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('SATURDAY_WINDOW');
EXECUTE DBMS_SCHEDULER.DROP_WINDOW('SUNDAY_WINDOW');

-- 그리고 스케줄 재생성 
@?/RDBMS/ADMIN/CATMWIN.SQL

스케줄잡이 실패하거나 계속작동중인경우 강제 종료

작업이 작동을 멈춘 경우 마지막으로 성공한 (또는 실패한) 작업이 여전히 멈춰 있고 DBA_AUTOTASK_TASK가 작업이 일관되게 존재하는 것으로 나타나면이 작업 삭제

exec dbms_scheduler.drop_job (job_name => '<job_name>',force =>true);
eg:
exec dbms_scheduler.drop_job (job_name => 'ora$at_os_opt_sy_2611',force =>true);

Note: <JOB_NAME> is from DBA_SCHEDULER_JOB_RUN_DETAILS.JOB_NAME

스케줄러 미작동시 최후의 방법

최후의 방법이니 운영중인 시스템에서는 절대 수행 불가

-- 1. 모든 스케줄러 작업이 완료 될 때까지 확인 / 대기 
select owner,job_name from dba_scheduler_running_jobs;

-- 2. 스케줄러 중지 
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
alter system set job_queue_processes=0;
exec dbms_ijob.set_enabled(FALSE);

-- 3. Flush shared pool once
alter system flush shared_pool;
alter system flush shared_pool;

-- 4. 스케줄러 활성화 
exec dbms_ijob.set_enabled(TRUE);
alter system set job_queue_processes=1000; 
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');