📖 16장. Resource Manager

💡 오라클 리소스 매니저라는 기능이 있다. 이것은 특정 세션이 database의 자원을 무한히 사용하지 못하도록 제한을 거는 기능이다.

✔️ 자원(Resource) 사용 제한을 거는 오라클 기능 2가지

• 프로파일 (Profile)
  • 자원관리
  • 패스워드
• 리소스 관리 (Resource Manager)
  • 프로파일 보다 더 많은 자원 사용에 대한 제한을 걸 수 있다.
    • 어떠한 제한을 할 수 있는가?
    1. 악성 SQL이 실행 안되도록 제한
    2. lock waiting 현상에 대한 제한
    3. 병렬처리에 대한 제한 (ex: select /*_ parallel 4 */ ...) ➡️ 본인은 빠르지만 다른사람들은 느려진다! (병렬도 : cpu_count * 2)
    4. Undo 사용에 대한 제한 - 과도한 DML 작업을 하지 못하게!
       (ex: 누군가가 sales 테이블 같은 큰 테이블을 update 했다고 가정하면 undo tablespace 풀난다.)

       update sales -- 2억건 이상 있는 테이블이다.
         set amount_sold=9000; 
       -- 위처럼 해서 안된다고 하면 where절 써서 하나씩 하시오 라고 말해준다.  

    5. Active session (waiting 하는 세션) 들에 제한 - 지금 수행하려는 SQL은 악성 SQL입니다. 다시 짜세요 라는 메세지를 출력하게 하면서 아예 악성SQL은 돌지 못하게 한다.
    6. Idle 시간에 대한 제한 (db접속해놓고 아무것도 안하는 세션들을 끊어버림)
    7. execution 시간에 대한 제한

✅ 4번까지는 확실하게 해놓아야 일이 힘들지 않다..!
ex ) 현대카드 dba의 사용 예

$ sh daytime.sh

제한할 병렬도는 ? 4
제한할 undo 사용량은? 50m
제한할 execution time(초)?
제한할 idle time(초)? 600
제한할 blocking idle time(초)? 10

락 waiting이 10초 이상 되면 자동으로 lock holder를 kill 시킴.

✏️ resource manager 구현 방법

(p16-4)

1. resource plan : 리소스 할당에 대한 청사진(큰 계획)
2. resource consumer group : 리소스의 요구사항이 유사한 그룹

dba 그룹, 개발자 그룹, 외부 엔지니어 그룹, 운영자 그룹

3. resource plan directive : 리소스를 분배하는 방법을 지정

• cpu
• locl waiting time
• undo
• degree (병렬도)
• active session의 갯수
• idle time

✔️ resourec manager 구현

✔️ SYSTEM user로 접속하여 consumer group ONLINE_USERS 를 생성한다

SQL> conn system/oracle
SYSTEM> exec dbms_resource_manager.create_pending_area();
--  리소스 메니저를 구현하기 위한  영역을 설정하겠다. 

✔️ 컨슈머 그룹 생성 (ONLINE_USERS) / SYS_GROUP 과 OTHER_GROUP는 항상 디폴트로 있다.

SYSTEM> exec dbms_resource_manager.create_consumer_group('ONLINE_USERS','Users for Online');

-- SYS_GROUP 과 OTHER_GROUP 은 이미 만들어져 있기 때문에 ONLINE_USERS 만 생성한다.
    ↑               ↑
 dba권한 유저들    리소스 사용에 대한 제한이 높은 유저들

💡 db 에 만들어져있는 Consumer 그룹확인하는 명령어

 select consumer_group 
    from dba_rsrc_consumer_groups
    where consumer_group like '%ONLINE%';

CONSUMER_GROUP
------------------------------
ONLINE_USERS

✔️ resource plan DAYTIME 을 다음과 같이 생성한 후 view를 통해 확인한다.

A. SYS_GROUP    : CPU_P1=100

B. ONLINE_USERS : CPU_P2=70, MAX_IDEL_BLOCKER_TIME=30
-- MAX_IDEL_BLOCKER_TIME  :  lock  wating time 을 30초로 제한하겠다.

C. OTHER_GROUPS : CPU_P2=30, PARALLEL_DEGREE_P1=4 
-- PARALLEL_DEGREE_P1 : 병렬도를 4로 제한하겠다   

select  /*+  parallel(emp, 8)  */   count(*)
  from  emp;  

✔️ 리소스 플랜 생성 (daytime)

SQL> 
begin
 dbms_resource_manager.create_plan('DAYTIME','Plan for day');
end;
/

PL/SQL procedure successfully completed.

✔️ 위에서 생성한 daytime plan 이 생성되었는지 확인하시오 !

select plan
  from dba_rsrc_plans; 

✔️ 리소스 디렉티브 생성 (디렉티브 : 분배방법)
1. 기존에 이미 있는 SYS_GROUP은 CPU를 1순위로 100% 사용하도록 지시어 생성

SQL> 
begin
 dbms_resource_manager.create_plan_directive('DAYTIME', 'SYS_GROUP','1st Group Set', cpu_p1=>100);
end;
/

PL/SQL procedure successfully completed.

2. online_users 그룹은 lock blocking time을 30초로 지정, cpu 사용 우선순위 2번째 이고 70%만 사용

begin
 dbms_resource_manager.create_plan_directive('DAYTIME', 'ONLINE_USERS','2nd Group set', 
cpu_p2=>70,max_idle_blocker_time=>30);
end;
/

3. other_groups 는 병렬도를 4로 지정, cpu 사용율 우선순위가 2번째이고 30%만 사용하도록 설정

begin
 dbms_resource_manager.create_plan_directive('DAYTIME', 'OTHER_GROUPS','3rd Group', 
cpu_p2=>30,parallel_degree_limit_p1=>4);
end;
/

➡️ 2, 3번 cpu_p2 (cpu 사용)이 두개가 합쳐서 100% 이 되어야 검증에서 에러나지않는다.

✔️ 검증 작업

SYSTEM> 
exec dbms_resource_manager.validate_pending_area();
PL/SQL procedure successfully completed.

✔️ 구현

SYSTEM> 
exec dbms_resource_manager.submit_pending_area();
PL/SQL procedure successfully completed.
SYSTEM> alter system set resource_manager_plan=daytime;

💡내가 어떻게 설정했는지 조회하는 방법

SQL> select plan, group_or_subplan, cpu_p1, cpu_p2,  
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;

✔️ SCOTT user를 consumer group ONLINE_USERS로 지정하고 default consumer group으로 지정한다.

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'ONLINE_USERS', true);
PL/SQL procedure successfully completed.

-- 지금 당장 consumer group이 ONLINE_USERS가 되는 것.
SQL> exec dbms_resource_manager.set_initial_consumer_group('SCOTT', 'ONLINE_USERS');
PL/SQL procedure successfully completed.   

online_users 그룹은 lock blocking time을 30초로 지정, cpu 사용 우선순위 2번째 이고 70%만 사용하는걸로 위에서 설정했다.

문제 scott 터미널 창 2개를 열고 다음과 같이 update를 수행해서 lock이 30초 후에 풀리는지 확인하기

SYS> alter system set resource_manager_plan=daytime;

          #scott 터미널 창1                       #scott 터미널 창2
           
           update emp
            set sal = 0
            where ename ='ALLEN';                update emp
                                                  set sal = 6000
                                                  where ename ='ALLEN';

lock을 먼저 잡을 세션을 죽였다.

✏️ resourec manager 설정 변경

문제 위의 max_idle_bloker_time을 60초로 변경하기

SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_max_idle_blocker_time=>60);
end;
/

SQL> select plan, group_or_subplan, cpu_p1, cpu_p2,  
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;

SYSTEM> exec dbms_resource_manager.submit_pending_area();

문제 max_idle_time을 수정해서 scott유저로 접속해서 10초동안 아무런 작업을 하지 않으면 자동으로 접속이 끊기도록 설정

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_max_idle_time=>10);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;

➡️ 바뀐것을 확인

➡️ scott에 접속해서 10초동안 가만히 있으니 끊겨있다.

문제 그럼 다시 idle_time의 영향을 받고 계속 접속할 수 있도록 설정하기

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> 
begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_max_idle_time=>-1);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;

문제 scott 유저가 undo 사용량을 제한하게끔 설정
new_undo_pool =>10

SYS> grant select on sales to scott;
SCOTT> create table sales500
       as
        select * from sh.sales;
SCOTT> delete from sales500;        

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> 
begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_undo_pool=>10);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select undo_pool, plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;

점심시간 문제 다시 undo를 제한없이 쓸 수 있도록 설정!

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> 
begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_undo_pool=>-1);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select undo_pool, plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;

✏️ Resource Manager 로 cpu 사용 제한하기

select * from v$rsrc_consumer_group;
OTHER_GROUPS -- sys, system user (cpu_p1, 100%)
SYS_GROUP    -- scott (cpu_p2, 70%)
ONLINE_USERS -- hr, sh user (cpu_p2, 30%)

select name, consumed_cpu_time, cpu_wait_time
  from v$rsrc_consumer_group;

SYSTEM @ orcl3 > save cpu.sql
Created file cpu.sql

❓ consumed_cpu_time : 소비한 cpu 시간
❓ cpu_wait_time : cpu를 사용하지 못하고 대기한 시간

문제 scott 유저로 접속하여 악성SQL을 수행해서 CPU 사용율을 높인 후 cpu.sql을 수행하고 online_users 그룹의 cpu 사용율이 올라가는지 확인하기

SCOTT @ orcl3 > select count(*)from sh.sales, sh.sales;

➡️ scott 혼자 다 쓰는것이다. 만약 sys, system 유저가 사용중이었다면 scott은 남아있는 것 가지고 쓰는것임

문제 system 유저에서 아래의 악성 SQL을 수행한후 scott 유저에서 똑같이 아래의 악성SQL을 수행하고 다른 터미널 창을 열어서 @cpu를 수행한다. 누가 더 cpu를 많이 쓰는지 확인해보자!

SCOTT @ orcl3 > select count(*)from sh.sales, sh.sales;
SYS @ orcl3 > select count(*)from sh.sales, sh.sales;

➡️ 생각과 다르게 scott이 더 cpu를 많이 사용한다.

✏️ 리소스 매니저를 이용해서 병렬도 제한하기

❓ 병렬로 작업 한다는 것은?

select /* parallel(emp 4) */ ename, sal
 from emp;

• 병렬 작업이 지원되려면 오라클을 반드시 enterprise edition으로 설치해야한다.

8코어 ~ 24코어, 오라클 1 코억씩 5억씩 받ㄴ는다.
24코어면 120억을 줘야 오라클 쓸 수 있거, 매년 구입가격의 3%를 유지보수로 내야한다.

1. 병렬 쿼리를 실행하고 병렬 프로세서들이 잘 떠있는지 확인하시오.

SCOTT> select /*+  parallel(s1,2) parallel(s2,2) */  count(*)
        from sh.sales  s1, sh.sales  s2;

2. sys user에서 다음과 같이 병렬 프로세서들이 떠있는지 확인 !

SYS> col program for a25
SYS> col process for a10

SYS> select process, program
      from v$session
      where program like '%(P0%';

3. scott user가 병렬도를 마음껏 병렬도를 줄 수 있도록 설정하기!

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> 
begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_parallel_degree_limit_p1=>-1);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select undo_pool, plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2;
    

SCOTT>
select /*+  parallel(s1,6) parallel(s2,6) */  count(*)
        from sh.sales s1, sh.sales s2;  

-- SYSTEM user에서 확인
SYSTEM> select process, program
      from v$session
      where program like '%(P0%';

✏️ 리소스 매니저를 이용해서 예상되는 SQL 실행시간이 지정된 시간보다 더 오래 걸릴 것 같으면 끊는 실습

1. SYSTEM 유저에서 아래와 같이 설정

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> 
begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_max_est_exec_time=>120);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select undo_pool, plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2; 

➡️ 낮시간에는 서버에 부하를 주는 무거운 SQL을 수행되지 못하게 막는것!

문제 위 예상 실행시간을 5분으로 변경하기

SYS @ orcl3 > conn system/oracle;
SYSTEM> exec dbms_resource_manager.create_pending_area();

SYSTEM> 
begin
         dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_max_est_exec_time=>600);
end;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

SQL> select undo_pool, plan, group_or_subplan, cpu_p1, cpu_p2, max_idle_time, 
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='DAYTIME'
     order by 1,2; 

문제 다시 모든 리소스 매니저로 제한된 기능을 풀어줍니다.
: 밤에는 제한된 기능을 풀어줄 수 있도록 아래 명령으로 풀어준다. 쉘이나 dbms 스케줄을 이용하여 걸어준다.

SYSTEM> alter system set resource_manager_plan='';

다시 아침에 업무시간이 되기 전에 resource_manager_plan을 daytime으로 지정한다. 밤에는 배치 작업이 수행되면 하루 결산을 하거나 하는 아주 무거운 SQL 들이 많이 자동으로 돕니다. (아침에 걸고, 밤에는 풀고 !)

crotab, dbms_job, dbms_scheduler ?

➡️ 위 기능을 자동으로 하는 방법
1. 리눅스의 crotab을 이용
2. 오라클의 dbms_job 또는 dbms_scheduler를 이용

문제 resource manager에 대한 사용을 편하게 할 수 있는 프로시저를 생성하기 (익명 PL/SQL 작성)

SYSTEM> @resource.sql

제한할 병렬도는?                new_paralle_degree_limit_p1
제한할 undo 사용량은?           new_undo_pool
제한할 execution time(초)?     new_max_est_exec_time
제한할 idle time(초)?          new_max_idle_time
제한할 blocking idle time(초)? new_max_idle_blocker_time

accept  p_degree  prompt '제한할 병렬도는 ?  ' 
accept  p_undo    prompt '제한할 undo 사용량은? '
accept  p_exec    prompt '제한할 execution time(초) ? '
accept  p_idle    prompt '제한할 idle time(초) ? '
accept  p_block   prompt '제한할 blocking idle time(초) ? '

exec dbms_resource_manager.create_pending_area();

begin
          dbms_resource_manager.update_plan_directive(
          plan=>'DAYTIME',
          group_or_subplan=>'ONLINE_USERS',
          new_parallel_degree_limit_p1 => &p_degree,
          new_undo_pool => &p_undo, 
          new_max_est_exec_time => &p_exec,
          new_max_idle_time => &p_idle,
               new_max_idle_blocker_time  =>  &p_block 
);
end;
/

exec dbms_resource_manager.submit_pending_area();

alter system  set  resource_manager_plan= 'daytime';

alter system set resource_manager_plan= ''; 로 끄기

문제 nighttime이라는 resouce_manager_plan을 생성하기 그리고 hr 계정에서 이 플랜을 지정하세요

제한할 병렬도는? 4                  new_paralle_degree_limit_p1
제한할 undo 사용량은? 500바이트      new_undo_pool
제한할 execution time(초)? 300     new_max_est_exec_time
제한할 idle time(초)? 10           new_max_idle_time
제한할 blocking idle time(초)? 5   new_max_idle_blocker_time

✔️ SYSTEM user로 접속하여 consumer group ONLINE_USERS2 를 생성한다

SQL> conn system/oracle
SYSTEM> exec dbms_resource_manager.create_pending_area();
--  리소스 메니저를 구현하기 위한  영역을 설정하겠다. 

✔️ 컨슈머 그룹 생성 (ONLINE_USERS2) / SYS_GROUP 과 OTHER_GROUP는 항상 디폴트로 있다.

SYSTEM> exec dbms_resource_manager.create_consumer_group('ONLINE_USERS2','Users for Online2');

-- SYS_GROUP 과 OTHER_GROUP 은 이미 만들어져 있기 때문에 ONLINE_USERS 만 생성한다.
    ↑               ↑
 dba권한 유저들    리소스 사용에 대한 제한이 높은 유저들

💡 db 에 만들어져있는 Consumer 그룹확인하는 명령어

 select consumer_group 
    from dba_rsrc_consumer_groups
    where consumer_group like '%ONLINE%';

CONSUMER_GROUP
------------------------------
ONLINE_USERS2

✔️ 리소스 플랜 생성 (nighttime)

SQL> 
begin
 dbms_resource_manager.create_plan('NIGHTTIME','Plan for night');
end;
/

PL/SQL procedure successfully completed.

✔️ 위에서 생성한 nighttime plan 이 생성되었는지 확인하시오 !

select plan
  from dba_rsrc_plans; 

✔️ 리소스 디렉티브 생성 (디렉티브 : 분배방법)
1. 기존에 이미 있는 SYS_GROUP은 CPU를 1순위로 100% 사용하도록 지시어 생성

SQL> 
begin
 dbms_resource_manager.create_plan_directive('NIGHTTIME', 'SYS_GROUP','1st Group Set', cpu_p1=>100);
end;
/

PL/SQL procedure successfully completed.

제한할 병렬도는? 4                  new_paralle_degree_limit_p1
제한할 undo 사용량은? 500바이트      new_undo_pool
제한할 execution time(초)? 300     new_max_est_exec_time
제한할 idle time(초)? 10           new_max_idle_time
제한할 blocking idle time(초)? 5   new_max_idle_blocker_time

2. online_users2 그룹은 lock blocking time을 30초로 지정, cpu 사용 우선순위 2번째 이고 70%만 사용

begin
 dbms_resource_manager.create_plan_directive('NIGHTTIME', 'ONLINE_USERS2','2nd Group set', 
  cpu_p2=>70,
  parallel_degree_limit_p1=>4,
  undo_pool=>500,
  max_est_exec_time=>300,
  max_idle_time=>10,
  max_idle_blocker_time=>5);
end;
/

3. other_groups 는 병렬도를 4로 지정, cpu 사용율 우선순위가 2번째이고 30%만 사용하도록 설정

begin
 dbms_resource_manager.create_plan_directive('NIGHTTIME', 'OTHER_GROUPS','3rd Group', 
cpu_p2=>30,parallel_degree_limit_p1=>4);
end;
/

➡️ 2, 3번 cpu_p2 (cpu 사용)이 두개가 합쳐서 100% 이 되어야 검증에서 에러나지않는다.

✔️ 검증 작업

SYSTEM> 
exec dbms_resource_manager.validate_pending_area();
PL/SQL procedure successfully completed.

✔️ 구현

SYSTEM> 
exec dbms_resource_manager.submit_pending_area();
alter system set resource_manager_plan=nighttime;
PL/SQL procedure successfully completed.

💡내가 어떻게 설정했는지 조회하는 방법

SQL> select plan, group_or_subplan, cpu_p1, cpu_p2,  
            parallel_degree_limit_p1 dop, max_idle_blocker_time block_t
     from dba_rsrc_plan_directives
     where plan='NIGHTTIME'
     order by 1,2;

✔️ HR user를 consumer group ONLINE_USERS2로 지정하고 default consumer group으로 지정한다.

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group('HR', 'ONLINE_USERS2', true);
PL/SQL procedure successfully completed.

-- 지금 당장 consumer group이 ONLINE_USERS가 되는 것.
SQL> exec dbms_resource_manager.set_initial_consumer_group('HR', 'ONLINE_USERS2');
PL/SQL procedure successfully completed.   

📖 17장. Scheduler(스케줄러)

💡 os의 crontab과 같은 기능을 오라클에서 구현한것. 특정 시간에 특정 작업이 자동으로 수행되도록 설정하는 기능!

✔️ 스케줄러의 종류 2가지?

• time base 스케줄러 : 특정 시간에 특정 작업이 수행되도록
• event base 스케줄러 : 특정 이벤트가 발생하면 특정 작업이 수행되도록

➡️ dba 작업이 스케줄러 사용에 유용한 경우
1. 매일밤 또는 매월 말일에 월말 작업(통계 정보 수집)을 자동화
2. database 백업을 자동화
3. 테이블 통계정보 수집 자동화
4. invalid된 인덱스나 프로시저를 다시 valid시키는 작업이 특정시간에 자동으로 수행되게 할 때

➡️ crontab과 비교해서 더 좋은 기능
1. 작업 수행과정이 잘 진행되고 있는지 모니터링 가능
2. 작업 수행 이력을 확인할 수 있다.
3. 스케줄을 설정하는게 훨씬 더 자세히 설정할 수 있다.
(예: 월, 수, 금 밤 10시에 특정 작업이 수행되게 해라! / 매월 말일 밤 10시에 작업이 수행되게 하라!)

✍🏻 스케줄러 구현 단계 4가지

1. 프로그램 생성 (dbms_scheduler.create_program)
2. 스케줄 생성 (dbms_scheduler.create_scheduler)
3. 작업 생성 (dbms_scheduler.create_job)
4. 작업이 잘 수행되는지 모니터링 (dbms_scheduler_job_run_details)

✏️ Time base 스케줄러 작업 실습

0. scott 에게 dba 권한을 부여하고,scott유저에서 emp01 테이블을 생성

SYS> grant dba to scott;
SCOTT> create table emp01
         ( hiredate date );         

1. scott 으로 접속해서 스케줄을 생성한다.

SCOTT> 
begin
       dbms_scheduler.create_schedule(
       schedule_name=>'scott_insert_sch', 
       repeat_interval=>'freq=secondly; interval=5');
end;
/

➡️ 이름은 scott_insert_sch이고, repeat_interval=>'freq=secondly; interval=5는 5초마다 한번씩 작동되는 스케줄 이라는 것이다!

2. 프로그램 생성

SCOTT> 
begin
       dbms_scheduler.create_program(
       program_name=>'scott_insert_prog',
       program_type=>'PLSQL_BLOCK',
       program_action=>'begin insert into emp01(hiredate) values (sysdate); end;');
end;
/

➡️ program_action begin, end사이에 있는것을 수행할것이다.그것이 emp01 테이블에 오늘 날짜를 입력하는 프로그램 생성

3. 작업(job) 생성

SCOTT> 
begin
       dbms_scheduler.create_job(
       job_name=>'scott_insert',
       program_name=>'scott_insert_prog',
       schedule_name=>'scott_insert_sch' );
end;
/

4. 작업, 프로그램 enable 시키기

SCOTT> exec dbms_scheduler.enable('scott_insert');
SCOTT> exec dbms_scheduler.enable('scott_insert_prog');

5. 결과 확인

SCOTT> select * from emp01;

6. 스케줄이 잘 도는지 확인하시오 !

select * from  dba_scheduler_job_run_details 
 WHERE owner='SCOTT';

schedule, job, program을 중지/Drop하기

문제 scott_insert job을 중지시키기

SCOTT> exec dbms_scheduler.disable('scott_insert');

문제 scott_insert job을 drop 시키시오!

SCOTT> exec dbms_scheduler.drop_job('scott_insert');

문제 scott_insert_sch 스케줄을 drop하기!

SCOTT> exec dbms_scheduler.drop_schedule('scott_insert_sch');

문제 scott_insert_prog 프로그램을 drop하기!

SCOTT> exec dbms_scheduler.drop_program('scott_insert_prog');

문제 아래의 로그스위치를 일으키는 pl/sql이 5초에 한번씩 수행되게 스케줄, 프로그램, 작업(job)을 만드시오!

begin 
 execute immediate 'alter system switch logfile';
end; --얘를 5초에 한번씩 수행되도록!!!

'begin 
 execute immediate ''alter system switch logfile'';
end;' 

1. scott 으로 접속해서 스케줄을 생성한다.

SCOTT> 
begin
       dbms_scheduler.create_schedule(
       schedule_name=>'scott_logs_sch', 
       repeat_interval=>'freq=secondly; interval=5');
end;
/

➡️ 이름은 scott_logs_sch이고, repeat_interval=>'freq=secondly; interval=5는 5초마다 한번씩 작동되는 스케줄 이라는 것이다!

2. 프로그램 생성

SCOTT> 
begin
       dbms_scheduler.create_program(
       program_name=>'scott_logs_prog',
       program_type=>'PLSQL_BLOCK',
       program_action=>'begin 
 execute immediate ''alter system switch logfile'';
end;');
end;
/

➡️ program_action begin, end사이에 있는것을 수행할것인데 그것은 로그스위치가 일어나는 것 !

3. 작업(job) 생성

SCOTT> 
begin
       dbms_scheduler.create_job(
       job_name=>'scott_logs',
       program_name=>'scott_logs_prog',
       schedule_name=>'scott_logs_sch' );
end;
/

4. 작업, 프로그램 enable 시키기

SCOTT> exec dbms_scheduler.enable('scott_logs');
SCOTT> exec dbms_scheduler.enable('scott_logs_prog');

5. 결과 확인 - alert log file 확인

$ tail -f alert*

6. 스케줄이 잘 도는지 확인하시오 !

select * from  dba_scheduler_job_run_details 
 WHERE owner='SCOTT';

7. 다시 disable시키기

SCOTT> exec dbms_scheduler.disable('scott_logs');

오늘의 마지막 문제 dept table에 데이터를 입력하는 아래의 프로시저를 수행하시오!

create or replace procedure dept_insert
is
   
    v_deptno dept.deptno%type;
    
begin  

  select max(deptno) + 10 into v_deptno
    from dept;
    
  insert into dept(deptno, dname, loc)
    values(v_deptno, 'aaa','bbb');

end;
/
------------------------------------------------
exec dept_insert

1. scott 으로 접속해서 스케줄을 생성한다.

SCOTT> 
begin
       dbms_scheduler.create_schedule(
       schedule_name=>'scott_dept_insert_sch', 
       repeat_interval=>'freq=secondly; interval=5');
end;
/

2. 프로그램 생성

SCOTT> 
begin
       dbms_scheduler.create_program(
       program_name=>'scott_dept_insert_prog',
       program_type=>'STORED_PROCEDURE',
       program_action=>'dept_insert');
end;
/

SCOTT @ orcl3 > begin
       dbms_scheduler.create_program(
       program_name=>'scott_dept_insert_prog',
       program_type=>'PLSQL_BLOCK',
       program_action=>'begin dept_insert; end;');
end;
/ 

➡️ program_action begin, end사이에 있는것을 수행!

3. 작업(job) 생성

SCOTT> 
begin
       dbms_scheduler.create_job(
       job_name=>'scott_insert_dept',
       program_name=>'scott_dept_insert_prog',
       schedule_name=>'scott_dept_insert_sch' );
end;
/

4. 작업, 프로그램 enable 시키기

SCOTT> exec dbms_scheduler.enable('scott_insert_dept');
SCOTT> exec dbms_scheduler.enable('scott_dept_insert_prog');

5. 결과 확인

SCOTT> select * from dept;

6. 스케줄이 잘 도는지 확인하시오 !

select * from  dba_scheduler_job_run_details 
 WHERE owner='SCOTT';

7. 다시 disable시키기

SCOTT> exec dbms_scheduler.disable('scott_insert_dept');

exec dbms_scheduler.drop_program('scott_dept_insert_prog');
exec dbms_scheduler.drop_schedule('scott_dept_insert_sch');
exec dbms_scheduler.drop_job('scott_insert_dept');