Science of Database

지난번에 Range 파티션에서 maxvalue의 진정한 의미 라는 글에서 Multi-Column으로 Range 파티션을 구성할 때 주의사항에 대하여 알아 보았다. 이 글을 쉽게 이해하려면 위의 글을 먼저 보기 바란다. 테스트용 스크립트도 위의 글에서 사용한 것을 그대로 사용한다.

 

RAC로 4 Node로 구성되어있는 환경에서 동일한 SQL이 모든 Instance에서 골고루 수행될 때 1번 Instance 만 유독 느리다면 무엇을 의심해야 할까? 네트워크 등의 문제일 수 있지만 가장 먼저 조사해야 할 것은 gc_current_grant_busy 이벤트가 발생하느냐 이다.

테스트 환경을 만들어 보자.

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

PARTITION pt_2_3 VALUES LESS THAN ('2', 5),

  ...중간생략

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

 

Table created.

 

---> 여기서 이전 글에서 사용했던 Insert 문과 dbms_stats.gather_table_stats 수행

 

 

상황 : 아래의 SQL 2개가 모든 Instance에서 동시에 여러 번 수행된다.

 

SELECT COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';           --> 3번 파티션

 

UPDATE T

   SET pad = LPAD('A', 4000, 'B')

 WHERE DAY_NUM = '4'          --> 4번 파티션

   AND INST_ID = :V_INST_ID;      --> 현재 수행되고 있는 Instance 번호 대입

 

이 상황에서 1번 Instance의 Update문만 유독 느리게 수행된다. 아래는 개발자와 필자의 대화내용이다.

 

개발자 : 왜 Update문의 Bind 변수에 1번만 넣으면 느린가요?

필자    : 1번 Instance에서 Update 하려면  다른 Instance에서 Exclusive Mode의 Lock 권한을 받아야 하기 때문으로 추측됩니다.

개발자 : 권한이라뇨?

필자    : SELECT 시에 DAY_NUM 4번에 해당하는 파티션을 5번 이상 Access 했기 때문에 권한이 다른 INSTANCE로 넘어간 것 같습니다. 이 현상을 FDC(Fairness Down Convert) 라고 합니다. FDC가 발생한 후에 DAY_NUM 4번에 해당하는 첫번째 파티션(pt_4_1)의 해당 블록에 UPDATE문을 수행하려면 권한을 받는 작업(gc_current_grant_busy 이벤트)이 필요합니다.

개발자 : 그럴 리가요? Update 문은 DAY_NUM = '4' 조건이고 Select 문은 DAY_NUM = '3' 조건이므로 서로 다른 파티션 입니다. 따라서 SELECT 문과 UPDATE문이 동일 파티션을 Access 할 이유가 없습니다.

필자   : 그 SELECT 문이 실제로는 DAY_NUM = '4' 의 첫번째 파티션을 항상 Access 합니다. MAXVALUE를 지정하지 않았으므로 그런 것 입니다.

개발자 :  그렇군요. 어쩐지 trace에 gc_current_grant_busy가 많이 보였습니다.

 

아래는 개발자가 제시한 Trace 내용 중 Wait Event 부분을 발췌한 것이다.

 

core1_ora_13638.trc

-----------------------------

WAIT #11: nam='gc current grant busy' ela= 947 p1=28 p2=1672046 p3=33619969 obj#=12043270 tim=12372374088207

WAIT #11: nam='gc current grant busy' ela= 992 p1=29 p2=2310876 p3=33619969 obj#=12070599 tim=12372374089432

...중간생략

WAIT #11: nam='gc current grant busy' ela= 767 p1=28 p2=1673090 p3=33619969 obj#=12043272 tim=12372374096882

 

Fairness Down Convert란 무엇인가?
Exclusive mode의 lock이 Shared lock 모드로 Down Convert 된다는 뜻이다. 다른  Instance의 요청에 의해서 Exclusive mode의 lock 상태에서 블럭을 다른 INSTANCE로 전송하는 작업은 무거운 연산이므로 특정 횟수 이상 블럭을 요청할 경우 Shared lock 모드로 전환하겠다는 뜻이다.  FDC 발생 이후로는 블럭을 요청한 INSTANCE로는 블럭 전송이 불필요 하다. 따라서 성능이 향상된다.  하지만 반대로 원래의 Instance에서 그 블럭을 Update 하려면 권한을 받아야만 하므로 성능이 느려지는 것이다.

FDC를 Control 할 수 있는 파라미터는 _FAIRNESS_THRESHOLD 이다. 이 파라미터는 Default로 4 이다. 즉 특정 블록을 다른 Instance에서 5번 이상 Access 하는 경우 FDC가 발생하여 요청한 Instance로 권한이 넘어간다.

 

결론:

FDC 기능은 성능을 향상 시키기 위한 용도로 만들어 졌다. 하지만 위의 경우와 같이 오히려 느려지는 경우도 있다. Trade Off 특징이 잘 나타난다. 파티션의 특징을 잘 모르고 사용하였기 때문인데 해당 Select 문 뿐만 아니라 DML문까지 성능이 느려질 수 있으므로 주의해야 한다.

얼마전에 필자는 다음과 같은 질문을 받았다.
"PL/SQL 의 기능중에 커서(Select 문)을 인자로 받아서 복잡한 계산을 수행후 결과를 집합으로 RETURN 하는 기능이 있습니까?"
이런 경우 필자는 예외없이 Pipelined Table Function 을 권장한다.(단 버젼이 8i 이상이라면)
Pipelined Table Function 를 사용하여야 하는 이유는 4가지 이다.

1.PL/SQL 의 유일한 단점은 부분범위처리가 안된다는 것이다.
  즉 모든처리가 끝나야만 결과가 화면에 Return 된다는 것이다.
  Pipelined Table Function 을 사용하면 이런단점을 극복할수 있다.
  당연히 조회화면등에서 성능이 개선된다.
  이개념을 이용하려면 Pipe Row 기능을 이해해야한다.
  Pipe Row 기능은 9i 이상에서만 사용가능하며 8i 라면 Table Function 만 사용이 가능하므로
  부분범위 처리가 불가능하다.

2.SQL 이 길어서 A4 용지 기준으로 1 ~ 2 페이지가 넘어가는 경우가 있다.
  이런경우 모니터링을 해보면 엄청난양의 SQL 이 네트웍을 타고 DBMS 에 전달 된다.
  이런 SQL 들이 여러명이 사용하고 자주 사용된다면 네트웍의 부하가 상당하므로
  Pipelined Table Function 을 사용하면 SQL 이 1~2줄로 줄어들므로 네트웍 튜닝이 가능해진다.
  이부분의 모니터링은 AutoTrace 의  "bytes received via SQL*Net from client" 부분을
 살펴보면 된다.
 아래그림의 선택된 부분이 문제의 네트웍 전송량이다.
 아래를 결과를 보면 DB 서버로 부터 결과를 전송받은 양보다 Client 에서 SQL 문을
 DB 서버로 전송한 데이터양이 더크다.

이런일이 많을경우 전체적인 시스템이 느려지게 되는데 왜느린지 알수가 없는경우가 많다.
왜냐하면 시스템 Wait Event 모니터링을 해도 이런종류의 Event 는 대부분의 DBA 들이 Idle Event 로 생각하기 때문이다.
현재 시중에 있는 일반적인 Wait Event 책이 사람들을 그렇게 생각하도록 만든다.
이런경우는 Idle Event 로 생각하면 안된다.
대부분의 그런종류의 책들은 Event 들의 원인 + 조치방법으로 되어 있다.
하지만 이런경우 해법을 찾을수 있는 책은 거의없다.
Rechmond See 의 "Oracle Wait Interface" 라는 책을 보면 SQL 에 문제가 있거나 네트웍 성능이 문제라고 되어 있지만 그렇지 않은 경우가 대부분이다.
왜냐하면 SQL 이 길다고 그 SQL 이 잘못된것은 아니며, 대부분 네트웍을 점검해봐도 정상이기 때문이다.  
유일하게 욱짜님의 책에 "실행횟수가 많은경우 DBMS CALL 을 줄이고 PL/SQL 로 처리하라" 고 되어있다.
하지만 SQL이 조회화면의 SELECT 문일 경우라면?
이경우는 DBMS CALL 을 줄일수도 없고 DML(INSERT/UPDATE/DELETE) 처럼 PL/SQL로 바꿔서 Array Processing 으로 처리할수도 없는 노릇이다.
이때의 Solution 은 단한가지이다.
SQL 이 Select 이면 아래 예제에서 사용될 Table 함수나 Ref 커서를 사용한 Procedure 를 이용하면 된다.
위의 기능들은 대부분의 사람들이 알고 있지만 위의 기능을 SQL*Net message from client Event  의 해법으로 생각하는 사람들이 거의없는 이유는 무었일까?

3.SQL 을 인자로 던질수 있으며 결과가 Multi Column + Multi Row 로 Return 될수 있다는 점이다.

4.모듈로써 공유가 가능하다는점
  이것이 안된다면 복잡한 계산을 해야하는 모든곳에서 기능을 구현하여야만 한다.

필자는 1,2번이 맘에 들지만 개발자들은 3,4 번을 가장 맘에 들어한다.(아마도 입장 차이인가 보다.^^)
아래의 Script 를 보자.
Script 상의 오른쪽의 주석을 참조하기 바란다.(Oracle 의 HR 스키마에서 테스트 하면됨)

1.먼저 패키지 Header 를 만든다.

CREATE OR REPLACE PACKAGE refcur_pkg IS
 
    TYPE refcur_t IS REF CURSOR            -- cursor type 을 선언한다.
    RETURN employees%ROWTYPE; 
   
    TYPE outrec_typ IS RECORD (            -- structure type을 선언한다.
       var_num employees.employee_id%type,
       var_char1 VARCHAR2(30),
       var_char2 VARCHAR2(30)   );
                             
    TYPE outrecset IS TABLE OF outrec_typ; -- 위에서 선언한 structure 를 배열로 type 으로 선언한다
     
    FUNCTION f_trans(p refcur_t)         -- 커서를 인자로 받아서 Structure 배열을 Return 하는 함수를 선언한다.
    RETURN outrecset PIPELINED;    -- 위에서 선언한 Structure 배열을 사용함.
                                                     -- 반드시 PIPELINED를 명시해야함.
   
END refcur_pkg;
/

 
2.패키지 Body 를 만든다.

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

    FUNCTION f_trans(p refcur_t)
    RETURN outrecset PIPELINED IS -- Structure 배열을 Return 하는 함수임.  
 
        out_rec outrec_typ;               -- PACKAGE Header 에서 선언한 structute type 을 변수로 선언한다.
        in_rec p%ROWTYPE;            -- p cursor 네의 의 모든컬럼을 변수로 선언한다.

        BEGIN

          LOOP

              FETCH p INTO in_rec;
              EXIT WHEN p%NOTFOUND;
              -- first row
              out_rec.var_num := in_rec.employee_id;
              out_rec.var_char1 := in_rec.first_name;
              out_rec.var_char2 := in_rec.last_name;
              PIPE ROW(out_rec);     --> employee_id, first_name, last_name 으로 1 row 를 즉시 return 한다.
              -- second row
              out_rec.var_char1 := in_rec.email;
              out_rec.var_char2 := in_rec.phone_number;
              PIPE ROW(out_rec);     --> employee_id, email, phone_number 으로 1 row 를 즉시 return 한다.

          END LOOP;

          CLOSE p;

        RETURN; -- return 하는 변수를 지정하지 않는다.(LOOP 내에서 모두 Return 되었기 때문이다.)

        END;
END refcur_pkg;
/

위 함수의 Logic 을  설명하면 함수는 사원 성명에 대하여 1줄 return 하고
사원의 번화번호및 email 에 대하여 또 한줄 return 한다.
위 함수의 특징은 Pipe Row 에 있다.
Pipe Row 를 명시하면 Loop 내에서 결과를 즉시  Return 한다.
즉 모든 Loop 가 끝나길 기다릴 필요가 없는것이다.
물론 전체를 처리해야만 하는경우는 Pipe Row 를 명시하지 않으면 되고 Bulk Collect 기능을 권장한다.
이때는 함수 선언시 PIPELINED 를 명시하면 안되며 RETURN 시의 변수도 지정해야한다.
Pipe Row 와 PIPELINED 는 항상 Pair 로 움직여야 한다.

3.만들어진 Pipelined Table Function 를 사용한다.

SELECT *
FROM  TABLE(refcur_pkg.f_trans(CURSOR(SELECT *
                                                               FROM employees
                                                             WHERE department_id = 60) ) );

4.결과

결론 : Pipelined Table Function 함수는 부분범위 처리가 가능하며 결과를 Row Set 으로 Return 할수있다.
         이기능은 SQL*Net message from client Event  과다현상의 훌륭한 해결책이다.
         이기능을 잘 사용하면 다양한 분야에 활용할수 있다.

Reference : 10g PL/SQL User's Guide and Reference 의 Tuning PL/SQL Applications for Performance 부분.

편집후기 :
Table 함수와 테이블간의 조인이 가능한지 질문이 들어왔다.
당연히 된다.
한가지 주의할점은 조인절이 따로 필요없고 Table 함수의 인자로 컬럼의 Value 가 필요하다는 것이다.
아래에 예제를 참조하라.
아래 예제에서 CAST 함수를 쓴이유는 버젼이 8i 이기 때문이다. (9i 이상은 필요없음)

select X.SUBCON_CD,
           X.SUBCON_NM,
           X.SUBCON_CONTI_CLS,
           X.SUBCON_DESC,
           Y.COM_CLS4_NM,
           Y.COM_CLS4_ALIAS_CD,
           Y.COM_CLS2,
           Y.COM_CLS4_DESC
   From TOLC_S_SUBCONTINENT X,
           TABLE( CAST( COMM.get_com_info(X.SUBCON_CONTI_CLS) AS COMLIST_T) ) Y