[Oracle] Resource Manager를 이용한 자원 제한 1

💡 resource manager를 사용하는 이유

예를들어 쿠팡은 낮시간에는 주문을 받는 db 유져들의 리소스 사용이 더 중요합니다.
데이터 분석을 하는 batch 작업이나 dss 작업은 주문을 받는 oltp 유져보다 사용이 덜 중요합니다.
--> 따라서 낮에는 oltp 유져가 더 많은 자원을 사용하게 하고, 밤에는 batch나 dss 작업이 더 많은 자원을 사용하게 하는 것을 시스템으로 강제화 할 수 있습니다.

---

💡 resource manager로 할 수 있는 제한

1. 악성 SQL이 실행되지 않도록 제한
2. Lock waiting 현상에 대한 제한
3. 병렬 처리에 대한 제한 (병렬도를 크게 못주게 설정)
4. undo 사용에 대한 제한 (과도한 DML 작업을 못하게)
5. Active session (waiting 하는 세션)들에 대한 제한
6. idle 시간에 대한 제한(db 접속해놓고 아무것도 안하면 세션을 끊어버림)
7. execution time(실행시간)에 대한 제한

---

💡 resource manager를 구현하기 위해 알아야 할 3가지 용어

• 용어 정리 3가지

1. resource plan
   : 리소스 할당에 대한 청사진(큰 계획)
   ex) 낮(day), 밤(night)
    
2. resource consumer group
   : 리소스의 요구사항이 유사한 그룹
   ex. oltp 그룹, batch 그룹
    
3. resource plan directive
   : 리소스를 분배하는 방법을 지정
   ex)
   - cpu
   - lock waiting time
   - undo
   - degree(병렬도)
   - active session의 갯수
   - execution time
   - idle time

---

[실습1] resource manager 전체 실습

■  resource  manager  구현

or 쪽 db 에서 수행하기

a) SYSTEM user로 접속하여 consumer group ONLINE_USERS 를 생성한다

SQL> conn system/oracle_4U

SQL> exec dbms_resource_manager.create_pending_area();

   -->  리소스 메니저를 구현하기 위한  영역을 설정하겠다. 

-- 컨슈머 그룹 생성

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

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

    A. SYS_GROUP    : CPU_P1=100  <-- cpu 사용을 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; 

-- 리소스 디렉티브 생성 (디렉티브 : 분배방법)

-- 기존에 이미 있는 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.

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

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



-- 검증 작업

SQL> exec dbms_resource_manager.validate_pending_area();


PL/SQL procedure successfully completed.


-- 구현

SQL> exec dbms_resource_manager.submit_pending_area();


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='DAYTIME'
        order by 1,2;


c) 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.

 

SQL>

SQL> exec dbms_resource_manager.set_initial_consumer_group('SCOTT', 'ONLINE_USERS');


PL/SQL procedure successfully completed.

 
SQL>  select username, initial_rsrc_consumer_group
           from dba_users
           where username='SCOTT';

---

[실습2] 낮 플랜으로 리소스 메니져를 사용하겠다고 하고 작동되는지 확인하시오

alter system set resource_manager_plan=daytime;

scott 터미널창을 2개를 열고 다음과 같이 update를 수행해서 
lock이 30초 후에 풀리는지 테스트 하시오 !

    scott   터미널창1                                   scott 터미널창2

  update  emp
    set  sal = 0
    where  ename='ALLEN';                        update  emp
                                                  set  sal = 6000
                                                  where  ename='ALLEN';

💡 30초 후에 Lock holder 세션이 자동으로 kill 됨

---

[실습3] 위의 설정을 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;
/

SYSTEM> exec dbms_resource_manager.submit_pending_area();

💡 dba가 오랫동안 락을 잡고있는 세션이 있으면 항상 찾아서 kill 시켜야하는데,
자동으로 오랫동안 락을 홀딩하고 있는 세션을 찾아 kill 시켜버림

---

[문제] max_idle_time을 수정해서 scott 유져로 접속해 10초 동안 아무 작업을 하고 있지 않으면 자동으로 접속이 끊기게 하시오

아래와 같이 system에서 설정해놓고 scott으로 접속해서 10초 동안 아무 작업도 하지 않으면 어떻게 되는지 테스트하시오

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();

결과:

SYSTEM @ ORA19 > connect scott/tiger
연결되었습니다.
SCOTT @ ORA19 >
SCOTT @ ORA19 > /
begin
*
1행에 오류:
ORA-03135: 접속이 끊겼습니다. 프로세스
ID: 30442
세션 ID: 148 일련 번호: 64274