Scheduler
dbms_job 패키지의 실행 주기 설정을 수작업으로 관리하는 것이 번거로우십니까? 이제 10g데이타베이스가 제공하는 Scheduler를 이용해 보십시오.
여러분들 중 일부는 백그라운드 데이타베이스 작업 스케줄을 설정하기 위해 dbms_job 패키지를 사용하고 있을 것입니다. 하지만 필자가 알기로는 대부분의 DBA들이 dbms_job 패키지를 사용하지 않고 있습니다.
이 패키지는 PL/SQL 코드 세그먼트만을 처리할 수 있으며, 데이타베이스 외부의 운영체제 파일 또는 실행 파일 이미지를 처리할 수 없다는 기능적 한계를 갖고 있습니다. 이 때문에 DBA들은 Unix의 cron, Windows의 AT 명령 등을 사용하여 운영 체제 레벨에서 스케줄링을 설정하는 방법을 선택합니다. 또는 그래픽 사용자 인터페이스를 제공하는 써드 파티 툴을 사용하기도 합니다.
그 러나 dbms_job은 이들과 다른 장점들이 있습니다. 이 중 하나는 데이터베이스가 실행되고 있는 경우에만 활성화된다는 것입니다. 만약 데이터베이스가 다운되어 있다면 해당 Job들은 실행되지 않습니다. 데이터베이스 외부에 존재하는 툴들은 수작업을 통해 데이터베이스가 실행되고 있는지 조사되어야 하며, 복잡한 작업일 수 있습니다. Dbms_job의 다른 장점으론 데이터베이스 내부에 존재한다는 것입니다. 그러므로 SQL*Plus와 같은 유틸리티를 이용해 쉽게 접근 가능하다는 것입니다.
Oracle Database 10g Scheduler는 모든 종류의 작업을 지원하는 내장형 작업 스케줄러 유틸리티를 제공합니다. 10g Scheduler의 가장 큰 장점은, 데이타베이스에 포함된 형태로 제공되므로 추가적인 비용이 들지 않는다는 것입니다. 이번 연재에서는 Scheduler의 기능에 대해 자세히 살펴보기로 합니다.
Creating Jobs Without Programs
개념의 이해를 돕기 위해 예를 통해 설명하겠습니다. 아카이브 로그 파일을 다른 파일시스템으로 이동하기 위해 아래와 같은 이름의 셸 스크립트를 생성했다고 가정해 봅시다:
/home/arup/dbtools/move_arcs.sh
별도의 프로그램을 생성하지 않고도 작업 유형으로 “Executable”을 지정하고 스케줄링을 설정할 수 있습니다.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_2',
schedule_name => 'EVERY_30_MINS',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/
또 Schedule Name을 설정하지 않고 작업을 생성하는 것도 가능합니다.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE_3',
job_type => 'EXECUTABLE',
job_action => '/home/arup/dbtools/move_arcs.sh',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
enabled => true,
comments => 'Move Archived Logs to a Different Directory'
);
end;
/
앞의 두 가지 예를 통해 dbms_job과 비교했을 때의 Scheduler의 장점을 확인할 수 있습니다. Scheduler를 이용하면 PL/SQL 프로그램뿐 아니라 OS 유틸리티와 프로그램을 실행하는 것이 가능합니다. 이러한 기능을 활용하여 범용적인 데이타베이스 작업 관리 환경을 구현할 수 있습니다. 또 Scheduler는 자연 언어(natural language)를 사용하여 실행 주기를 정의할 수 있다는 매우 중요한 이점을 제공합니다. 스케줄을 매 30분 단위로 실행하고자 하는 경우, (PL/SQL 문법 대신) 자연 언어 형식의 표현을 사용하여 REPEAT_INTERVAL 매개변수를 아래와 같이 정의할 수 있습니다:
'FREQ=MINUTELY; INTERVAL=30'
좀 더 복잡한 예를 들어 설명해 보겠습니다. 운영 중인 애플리케이션이 오전 7시에서 오후 3시까지의 시간대에 집중적으로 사용된다고 가정해 봅시다. Statspack을 실행하면 월요일~금요일 오전 7시 ~ 오후 3시의 시스템 통계를 수집할 수 있습니다. DBMS_JOB.SUBMIT을 사용하여 작업을 생성하는 경우라면, NEXT_DATE 매개변수는 아래와 같이 정의됩니다:
DECODE
(
SIGN
(
15 - TO_CHAR(SYSDATE,'HH24')
),
1,
TRUNC(SYSDATE)+15/24,
TRUNC
(
SYSDATE +
DECODE
(
TO_CHAR(SYSDATE,'D'), 6, 3, 1
)
)
+7/24
)
위의 코드가 이해하기 쉽습니까? 전혀 그렇지 않습니다.
이번에는 DBMS_SCHEDULER를 이용하는 방법을 살펴 보겠습니다. REPEAT_INTERVAL 매개변수는 아래와 같이 간단하게 정의됩니다:
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15'
이 매개변수를 이용해 실행 주기를 다양한 형태로 설정할 수 있습니다. 몇 가지 예가 아래와 같습니다:
● 매월 마지막 일요일:
FREQ=MONTHLY; BYDAY=-1SUN
● 매월 세 번째 금요일:
FREQ=MONTHLY; BYDAY=3FRI
● 매월 뒤에서 두 번째 금요일
FREQ=MONTHLY; BYDAY=-2FRI
마이너스(-) 기호는 숫자가 뒤에서부터 계산된다는 의미입니다.
설정된 실행 주기가 올바른지 확인하려면 어떻게 해야 할까요? 캘린더 문자열을 이용해서 날짜를 미리 확인할 수 있다면 편리하지 않을까요? EVALUATE_CALENDAR_STRING 프로시저를 이용하여 실행 예정 시각을 미리 검토할 수 있습니다. 위의 예 ? 월~금7:00 AM ~ 3:00 PM Statspack 실행 ? 에서 설정된 실행 주기를 확인하는 방법이 아래와 같습니다:
set serveroutput on size 999999
declare
L_start_date TIMESTAMP;
l_next_date TIMESTAMP;
l_return_date TIMESTAMP;
begin
l_start_date := trunc(SYSTIMESTAMP);
l_return_date := l_start_date;
for ctr in 1..10 loop
dbms_scheduler.evaluate_calendar_string(
'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15',
l_start_date, l_return_date, l_next_date
);
dbms_output.put_line('Next Run on: ' ||
to_char(l_next_date,'mm/dd/yyyy hh24:mi:ss')
);
l_return_date := l_next_date;
end loop;
end;
/
실행 결과는 다음과 같습니다:
Next Run on: 03/22/2004 07:00:00
Next Run on: 03/22/2004 15:00:00
Next Run on: 03/23/2004 07:00:00
Next Run on: 03/23/2004 15:00:00
Next Run on: 03/24/2004 07:00:00
Next Run on: 03/24/2004 15:00:00
Next Run on: 03/25/2004 07:00:00
Next Run on: 03/25/2004 15:00:00
Next Run on: 03/26/2004 07:00:00
Next Run on: 03/26/2004 15:00:00
위 결과로 미루어 설정된 내용에 문제가 없음을 확인할 수 있습니다.
작업과 프로그램의 연계
위의 사례는 특정 프로그램과 연결되지 않은 작업을 생성하는 방법을 설명하고 있습니다. 이번에는 특정 OS 유틸리티를 사용하는 프로그램을 생성하고, 실행 주기를 정의한 스케줄을 설정한 뒤, 이 두 가지를 조합하여 새로운 작업을 생성하는 방법을 설명합니다.
먼저 작업 내에서 프로그램을 사용한다는 사실을 데이타베이스가 인지하도록 해야 합니다. 이 프로그램을 생성하려면, CREATE JOB 권한을 갖고 있어야 합니다.
begin
dbms_scheduler.create_program
(
program_name => 'MOVE_ARCS',
program_type => 'EXECUTABLE',
program_action => '/home/arup/dbtools/move_arcs.sh',
enabled => TRUE,
comments => 'Moving Archived Logs to Staging Directory'
);
end;
/
이제 named program unit을 생성하여, 그 유형을 “executable”로 지정하고 실행될 program unit의 이름을 정의하였습니다.
다음으로, 매 30분 간격으로 실행되는 EVERY_30_MIN이라는 이름의 스케줄을 생성합니다:
begin
dbms_scheduler.create_schedule
(
schedule_name => 'EVERY_30_MINS',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
comments => 'Every 30-mins'
);
end;
/
프로그램과 스케줄을 생성한 뒤, 이 두 가지를 연관시킴으로써 새로운 작업을 생성합니다.
begin
dbms_scheduler.create_job
(
job_name => 'ARC_MOVE',
program_name => 'MOVE_ARCS',
schedule_name => 'EVERY_30_MINS',
comments => 'Move Archived Logs to a Different Directory',
enabled => TRUE
);
end;
/
이제 매 30분마다 move_arcs.sh 셸 스크립트를 실행하는 작업이 생성되었습니다. 이 스케줄은 데이타베이스 내부의 Scheduler 기능을 통해 관리되며, 따라서 cron 또는 AT 유틸리티를 사용할 필요가 없습니다.
Classes, Plans, and Windows
작업 스케줄링 시스템에 효과적으로 활용하려면 작업의 우선순위 지정이 가능해야 합니다. 예를 들어 OLTP 워크로드가 실행 중인 시간대에 통계 수집 작업이 갑자기 실행되어 성능을 저하시킬 수 있습니다. 통계 수집 작업이 OLTP 성능에 영향을 미치지 않도록 하기 위해, Scheduler가 제공하는 job classes, resource plans, and Scheduler Windows기능을 활용할 수 있습니다.
Job class는 할당된 자원을 공유하는 “resource consumer group”으로 매핑됩니다. Job class를 생성하기 위해, 먼저 OLTP_GROUP이라는 이름의 resource consumer group을 정의해 보겠습니다.
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group (
consumer_group => 'oltp_group',
comment => 'OLTP Activity Group'
);
dbms_resource_manager.submit_pending_area();
end;
/
다음에는 resource plan을 생성합니다.
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan
('OLTP_PLAN', 'OLTP Database Activity Plan');
dbms_resource_manager.create_plan_directive(
plan => 'OLTP_PLAN',
group_or_subplan => 'OLTP_GROUP',
comment => 'This is the OLTP Plan',
cpu_p1 => 80, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => 4,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'OTHER_GROUPS',
switch_time => 10,
switch_estimate => true,
max_est_exec_time => 10,
undo_pool => 500,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
);
dbms_resource_manager.create_plan_directive(
plan => 'OLTP_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => NULL,
cpu_p1 => 20, cpu_p2 => NULL, cpu_p3 => NULL, cpu_p4 => NULL,
cpu_p5 => NULL, cpu_p6 => NULL, cpu_p7 => NULL, cpu_p8 => NULL,
parallel_degree_limit_p1 => 0,
active_sess_pool_p1 => 0,
queueing_p1 => 0,
switch_group => NULL,
switch_time => NULL,
switch_estimate => false,
max_est_exec_time => 0,
undo_pool => 10,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_time_in_call => NULL
);
dbms_resource_manager.submit_pending_area();
end;
/
마지막으로 앞에서 생성된 resource consumer group을 이용해 job class를 생성합니다.
begin
dbms_scheduler.create_job_class(
job_class_name => 'OLTP_JOBS',
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
log_history => 45,
resource_consumer_group => 'OLTP_GROUP',
comments => 'OLTP Related Jobs'
);
end;
/
이 프로시저에서 사용된 매개변수들을 설명해 보겠습니다. LOGGING_LEVEL 매개변수는 해당 job class를 위해 얼마나 많은 로그 데이타를 기록할 것인지 정의하는데 사용됩니다. LOGGING_FULL은 job class에 포함된 작업의 모든 활동(생성, 삭제, 실행, 변경 등)을 로그에 기록함을 의미합니다. 로그는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있으며, LOG_HISTORY 매개변수에 저장된 대로 45일간 보관됩니다 (디폴트 값은 30일입니다). 이 class와 연관된 resource_consumer_group 도 정의되어 있습니다. DBA_SCHEDULER_JOB_CLASSES 뷰를 통해 정의된 job class들을 확인할 수 있습니다.
작업을 생성하는 과정에서, 필요한 경우 해당 작업을 job class에 할당할 수 있습니다. 예를
들어 collect_opt_stats() 저장 프로시저를 실행하여 옵티마이저 통계를 수집하는 COLLECT_STATS 작업을 생성하면서, 아래와 같이 job class를 할당할 수 있습니다.
begin
dbms_scheduler.create_job
(
job_name => 'COLLECT_STATS',
job_type => 'STORED_PROCEDURE',
job_action => 'collect_opt_stats',
job_class => 'OLTP_JOBS',
repeat_interval => 'FREQ=WEEKLY; INTERVAL=1',
enabled => true,
comments => 'Collect Optimizer Stats'
);
end;
/
위 명령을 실행하면 생성된 작업이 OLTP_JOBS 클래스에 할당됩니다. OLTP_JOBS 클래스에 적용되는 OLTP_GROUP resource plan을 통해 프로세스에 할당되는 CPU 자원, 다른 그룹으로 전환되기 전에 최대 실행 가능한 횟수, 전환되는 그룹 등을 설정할 수 있습니다. 동일한 job class에 할당된 작업은 동일한 resource plan의 적용을 받습니다. 이 기능을 활용하면 서로 다른 유형의 작업이 같은 리소스를 두고 경합을 벌이는 상황을 방지할 수 있습니다.
Scheduler Window는 특정 resource plan이 사용되는 시간대를 의미합니다. 예를 들어, 실시간 의사결정 작업에 관련한 업데이트 배치 작업이 주간에는 높은 우선순위를 갖는 반면 야간에는 낮은 우선순위를 갖는다고 가정해 봅시다. 이 경우 시간대별로 다른 resource plan을 정의하고, 정의된 내용을 Scheduler Window를 이용해 적용할 수 있습니다.
모니터링
실행 중인 작업의 상태는 DBA_SCHEDULER_JOB_LOG 뷰를 통해 확인할 수 있습니다. 이 뷰의 STATUS 컬럼은 작업의 현재 상태를 표시하는데 사용됩니다. 만일 STATUS 컬럼이 FAILED로 표시된다면, DBA_SCHEDULER_JOB_RUNS_DETAILS 뷰를 통해 그 원인을 확인할 수 있습니다.
관리
지금까지 여러 가지 유형의 오브젝트(program, schedule, job, job class 등)를 생성하는 방법에 대해 설명했습니다. 이렇게 생성된 오브젝트를 변경할 필요가 있다면, DBMS_SCHEDULER 패키지가 제공하는 API를 사용하면 됩니다.
Enterprise Manager 10g 홈 페이지에서 Administration 링크를 클릭하면, 그림 1과 같이 Administration 화면이 표시됩니다. Scheduler와 관련된 작업은 우측 하단의 “Scheduler” 항목에 표시됩니다 (그림의 붉은색 원 참조).
그림 1: Administration 페이지
이 페이지에서 제공되는 하이퍼링크를 활용하면 작업의 생성, 삭제, 관리와 같은 Scheduler 관련 작업을 쉽게 수행할 수 있습니다. 몇 가지 예를 들어 설명해 보겠습니다. 이미 작업을 생성해 둔 상태이므로, Job 탭을 클릭하면 그림 2와 같은 화면이 표시될 것입니다.
그림 2: 작업 스케줄의 확인
COLLECT_STATS 작업을 클릭하여 그 속성을 변경해 보도록 합시다. “Name” 필드를 클릭하면 그림 3과 같은 화면이 표시됩니다.
그림 3: 작업 매개변수
지금까지 확인한 것처럼, EM을 이용하면 작업, 스케줄, 옵션 등에 관련한 매개변수를 수정할 수 있습니다. 변경 작업을 완료한 뒤 “Apply” 버튼을 누르면 변경사항은 영구적으로 적용됩니다. “Apply” 버튼을 누르기 전에 “Show SQL” 버튼을 눌러 실행되는 SQL 구문을 확인하는 것도 가능합니다. 또 SQL 구문을 스크립트에 저장하여 나중에 실행하거나, 템플릿 용도로 활용할 수도 있습니다.
col name format a50;
select job,substr(replace(sys_connect_by_path(ename, ',' ),' ',''),2) name
from(
select job,ename,
row_number() over(partition by job order by job,ename desc) r1,
(LEAD(ename) over(partition by job order by job,ename desc)) r2
from scott.emp
)
where r1 = 1
start with r2 is null
connect by prior ename = r2
order siblings by r2;