RAC 에서 특정 Instance만 성능이 느린 이유

출 처: http://scidb.tistory.com/

지난번에 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문까지 성능이 느려질 수 있으므로 주의해야 한다.