행위

오라클 자동 통계정보 스케줄

DB CAFE

(오라클 통계정보에서 넘어옴)

1 Oracle 통계정보 자동 갱신

android 통계가 없거나 오래된 테이블/인덱스에 대해서 자동으로 옵티마이저 통계 수집.

  1. 10g 자동통계 job인 GATHER_STATS_JOB 보다 향상 된 기능 제공
  2. 11g Automated Maintenance Tasks Infrastructure(이하 AutoTask)로 통합


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

1.1 AUTO TASK 실행 가능 환경 확인

1.1.1 JOB QUEUE PROCESS 개수 확인

1.2 자동 수행 뷰 목록

android

  1. JOB_QUEUE_PROCESSES는 DBMS_JOB 작업 및 Oracle Scheduler (DBMS_SCHEDULER) 작업 실행을 위해 생성 할 수있는 인스턴스 당 최대 작업 슬레이브 수를 지정.
  2. DBMS_JOB 및 Oracle Scheduler는 동일한 작업 조정자와 작업 슬레이브를 공유하며 둘 다 JOB_QUEUE_PROCESSES 매개 변수로 제어.
  3. JOB_QUEUE_PROCESSES의 값이 0으로 설정되면 DBMS_JOB 작업 및 Oracle Scheduler 작업이 인스턴스에서 실행되지 않습니다.
    JOB_QUEUE_PROCESSES가 1-1000 범위의 값으로 설정되면 DBMS_JOB 작업 및 Oracle Scheduler 작업 실행 됨.
  4. Oracle Scheduler 작업에 대해 생성 된 실제 작업 슬레이브 수는 사용 가능한 리소스, Resource Manager 설정 및 현재 실행중인 작업을 비롯한 여러 요인에 따라 스케줄러에 의해 자동 조정.
    그러나 인스턴스에서 DBMS_JOB 작업 및 Oracle Scheduler 작업을 실행하는 총 작업 슬레이브 수는 해당 인스턴스의 JOB_QUEUE_PROCESSES 값을 초과 할 수 없습니다.
  5. Oracle Scheduler 작업을 실행하는 작업 슬레이브의 수는 MAX_JOB_SLAVE_PROCESSES Scheduler 속성 값으로 추가로 제한 됨.
  6. Advanced replication은 데이터 새로 고침을 위해 Oracle Scheduler를 사용.
  7. Oracle Streams Advanced Queuing은 메시지 전파를 위해 Oracle Scheduler를 사용.
  8. Materialized view는 자동 새로 고침을 위해 Oracle Scheduler 사용.
  9. JOB_QUEUE_PROCESS를 0으로 설정하면 이러한 기능은 물론 Oracle Scheduler 또는 DBMS_JOB을 사용하는 다른 모든 기능이 비활성화됩니다.


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

1.2.1 통계 레벨 확인 확인

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

1.2.2 JOB QUEUE PROCESS 개수 변경

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

1.2.3 통계 수집 레벨 변경

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

1.3 자동 수행 뷰 목록

android

  1. DBA_AUTOTASK_CLIENT 과거 7일간과 과거 30일간에 집계된 AutoTask 의 집계 데이터를 볼 수 있습니다.
  2. DBA_AUTOTASK_CLIENT_HISTORY 메인테넌스 윈도우별로 AutoTask의 실행횟수 이력을 제공합니다.
  3. DBA_AUTOTASK_CLIENT_JOB 현재 실행중인 AutoTask 를 표시합니다.
  4. DBA_AUTOTASK_JOB_HISTORY AutoTask Job 이력을 제공합니다.
  5. DBA_AUTOTASK_OPERATION AutoTask 에 관련된 속성을 표시합니다.
  6. DBA_AUTOTASK_SCHEDULE 향후 32일간의 메인테넌스 윈도우 스케줄을 표시합니다.
  7. DBA_AUTOTASK_TASK AutoTaks의 각종 통계 데이터를 표시합니다.
  8. DBA_AUTOTASK_WINDOW_CLIENTS 메인테넌스 윈도우별 상태를 제공합니다.
  9. DBA_AUTOTASK_WINDOW_HISTORY 메인테넌스 윈도의 실행 이력을 제공합니다.


1.4 자동 수행 스케줄 목록 조회

  1. 요일별 자동 수행 목록
  • 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

1.5 자동 수행 목록

  • 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 , 이것으로 최근 수행여부를 확인.

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

  • DBA_AUTOTASK_CLIENT_HISTORY
SELECT * 
  FROM DBA_AUTOTASK_CLIENT_HISTORY
 ORDER BY WINDOW_START_TIME DESC
;

auto optimizer stats collection

1.7 자동 수행 시작/정지

--disable Autotask  
exec dbms_auto_task_admin.disable;

--ensable Autotask  
exec dbms_auto_task_admin.enable;

1.8 통계정보만 수집 정지

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

android

  • OPERATION 과 WINDOW_NAME 둘 다 NULL이면,해당Client는 Disable/Enable 된다.
  • OPERATION이 NULL 이 아니면,WINDOW_NAME은 무시되고 해당 OPERATION은 Disable/Enable 된다.
  • OPERATION이 NULL 이고 WINDOW_NAME이 NULL이 아니면, 해당 Client는 지정한 Window에 한하여 Disable/Enable 된다.


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

/*
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;

2 자동 통계정보 수집

  1. oracle 10g 이상인 경우 parameter 등을 변경하면 통계정보가 자동으로 scheduler에 의해 자동 수집
  2. oracle의 job object중에 gather_stats_job에 의해 통계정보들이 수집.
  3. gather_stats_job은 기존 DATA의 10% 이상의 변경이 있거나, 오랫동안 통계치가 변경되지 않거나, 통계치가 없거나 한 Object 에 대한 Gathering 을 수행.

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

android

  1. AWR(Automactic Workload Repository) 분석, Self-Tuing 및 일반적인 Tuning 을 목적으로 하는 자료로 특정 시간 동안 DB에서 발생한 여러 가지 상황 정보를 의미 합니다.
  2. Wait Events, Latches, Enqueues, Cpu Consumption, SGA Compoenent, PGA 등에 대한 자료 입니다.
  3. AWR의 정보는 default로 7일 보관 됩니다.
  4. CBO(Cost-Based Optimizer) DB 의 object 즉, application 및 oracle sys 및 system user의 table, index 에 대한 통계정보 수집을 합니다.
  5. 이 수집된 정보는 Optimizer 가 수행계획 수립 시에 의해 사용됩니다.
  6. Optimizer historical 통계정보는 default로31일간 보관됩니다.


  • 자동 통계정보 수집 옵션
statistics_level = {all | typical | basic} 

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

2.2 자동 통계정보 수집 변경

- 통계정보 parameter 확인

$ sqlplus / as sysdba
SQL > show parameter statistics_level
  1. database level 적용
SQL > alter system set statistics_level = 'ALL';
SQL > alter system set statistics_level = 'BASIC';
  1. session level 적용
SQL > alter session set statistics_level = 'ALL';
SQL > alter session set statistics_level = 'BASIC';

2.3 자동 통계정보 수집 확인

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

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

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

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;

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

select program_action 
  from dba_scheduler_programs 
 where program_name ='GATHER_STATS_PROG'

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

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

select * 
  from dba_scheduler_wingroup_members 
 where window_group_name ='MAINTENANCE_WINDOW_GROUP';

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

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

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

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


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

  • 윈도우가 매우 짧은 기간 동안 열려 있으면 기본 유지 관리 작업이 실패합니다.
  • 경우에 따라 작업을 완료까지 시간이 적절하지 않을 수 있습니다.
  • MMON이 작업 실행 시도를 기록하거나 DBA_AUTOTASK_TASK에 작업을 표시하지 못할 수 있음.
-- 1,스케줄 윈도우 확인 
select window_name,repeat_interval,duration from dba_scheduler_windows; 

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

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

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

2.10 자동 통계정보 수집 중지

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

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

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

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

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

2.13 자동 통계정보 수집 재설정

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

2.14 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

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

  • 작업이 작동을 멈춘 경우 마지막으로 성공한 (또는 실패한) 작업이 여전히 멈춰 있고 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

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

  • 최후의 방법이니 운영중인 시스템에서는 절대 수행 불가
-- 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');