Right-hand 인덱스에서 발생하는 gc buffer busy acquire 및 gc buffer busy release 대기이벤트 감소 방안

RAC 환경에서 Right-hand 인덱스는 인서트가 집중되는 시점에 대량의 gc buffer busy acquire 및 gc buffer busy release 대기이벤트가 발생할 수 있는 문제점을 내포하고 있습니다. 이는 Right-hand 인덱스의 특성 상 마지막 리프 블록에 입력이 집중되기 때문입니다.

일반적으로, Right-hand 인덱스의 문제를 해결하기 위해 제시되는 방법은 Reverse Index 및 Global HASH Index 파티션입니다. 그리고 일반적으로 제시되는 방법은 아니지만 (모델링 및 AP의 변경이 필요하므로 특수한 경우에만 적용 가능) LIST 파티션을 이용한 입력 노드의 완벽한 분리입니다.

이번 시간에는 부하 테스트를 통해 이들 간의 성능을 비교하고 고려사항을 정리함으로써 gc buffer busy 관련 대기이벤트에 대한 적절한 해결 방안을 선택하는데 도움이 되고자 합니다.

1. 테스트 케이스 설명 및 테스트 환경 설정


테스트 환경: Oracle 12.1.0.2 64bit 3-노드

  • Case#1: 튜닝 적용 전
  • Case#2: Reverse Index 적용
  • Case#3: Global Hash Index Partition 적용 (해시 파티션 개수 8개)
  • Case#4: LIST 파티션을 이용한 입력 노드의 분리
-----------------------------------------------------------
--Case#1 환경 설정
-----------------------------------------------------------
drop table t1 purge;
create table t1
 (c1 number,
  dummy char(100));

create unique index t1_uk on t1(c1); 

drop sequence t1_seq;
create sequence t1_seq cache 10000 order;

-----------------------------------------------------------
--Case#2 환경 설정
-----------------------------------------------------------
drop table t1 purge;
create table t1
 (c1 number,
  dummy char(100));

create unique index t1_uk on t1(c1) reverse; 

drop sequence t1_seq;
create sequence t1_seq cache 10000 order;

-----------------------------------------------------------
--Case#3 환경 설정
-----------------------------------------------------------
drop table t1 purge;
create table t1
 (c1 number,
  dummy char(100));

create unique index t1_uk on t1(c1) global partition by hash(c1) partitions 8;

drop sequence t1_seq;
create sequence t1_seq cache 10000 order;

-----------------------------------------------------------
--Case#4 환경 설정
-----------------------------------------------------------
drop table t1 purge;

create table t1
 (
  inst_id number,
  c1 number,
  dummy char(100))
 partition by list(inst_id)
 (
  partition t1_inst1 values(1),
  partition t1_inst2 values(2),
  partition t1_inst3 values(3)
 );

create unique index t1_uk on t1(inst_id, c1) local;

drop sequence t1_seq;
create sequence t1_seq cache 10000 order;

-----------------------------------------------------------
--프로시저 (Case#1~3에서 사용)
-----------------------------------------------------------
create or replace procedure insert_t1 (LoopCnt number)
 is
 begin
     for i in 1..LoopCnt loop
      insert into t1 values(t1_seq.nextval, 'dummy');
     end loop;
 end;
/

-----------------------------------------------------------
--프로시저 (Case#4에서 사용)
-----------------------------------------------------------
create or replace procedure insert_t1 (LoopCnt number)
 is
 v_inst_id number;
 begin
     select userenv('INSTANCE') into v_inst_id from dual;
     for i in 1..LoopCnt loop
       insert into t1 values(v_inst_id, t1_seq.nextval, 'dummy') ;
     end loop;
 end;
/

상기 프로시저를 각 노드마다 10개의 세션 (총 30개 세션)으로 동시에 수행한 후에 결과를 분석합니다.

2. 결과 분석


3개의 권고안 모두 gc buffer busy 관련 대기이벤트에 대한 튜닝이 가능한 것으로 확인됩니다. (그림-1~2 및 결과-1 참조) 테스트 환경에 따라서 수치 상의 큰 차이가 있을 수 있으나, gc buffer busy acquire/release와 같이 경합과 관련된 대기이벤트에 대한 튜닝 방안은 경합 위치를 최대한 분산시키는 것이라는 것을 알 수 있습니다.

그림-1. 테스트 케이스 별 CPU 및 대기 클래스 별 대기 시간 비교

%ec%84%b1%eb%8a%a53-1

그림-2. 테스트 케이스 별 CPU 및 대기 클래스 별 대기 시간 비교 (Other 클래스 제외)

%ec%84%b1%eb%8a%a53-2

테스트를 위해 order 속성의 시퀀스를 이용함에 따라 ‘enq: SV – contention’ 대기이벤트의 대기시간이 전체 수행 시간의 80%를 차지합니다. 따라서 gc 관련 대기이벤트의 감소 효과를 보다 쉽게 확인하기 위해서 “그림-2″에서는 ‘enq: SV – contention’ 대기이벤트가 속한 Other 클래스를 제외한 그래프를 비교합니다.

결과-1. 테스트 케이스별 CPU 사용 시간 및 대기이벤트 대기 시간 비교

-- 아래의 결과는 테스트 수행 전/후에 각각 V$SYSTEM_EVENT 수치를 저장한 후 Delta 값을 비교한 것입니다.
FLAG       CLASS           NAME                                      1          2          3          4
---------- --------------- -------------------------------- ---------- ---------- ---------- ----------
[EVENT]    Other           enq: SV -  contention                 16869      14916      19951      13076
[EVENT]    Cluster         gc buffer busy acquire                 1825         57         99          3
[EVENT]    Cluster         gc current block busy                   926         79        596          0
[EVENT]    Cluster         gc buffer busy release                  432         19         40
[EVENT]    Concurrency     buffer busy waits                       284         11         23          4
[EVENT]    Concurrency     enq: TX - index contention              216         26         20          1
[EVENT]    Concurrency     cursor: pin S wait on X                 131        153        204         73
[EVENT]    Cluster         gc cr grant 2-way                        70         22         70         18
[EVENT]    Cluster         gc cr block 2-way                        58         11         27          0
[EVENT]    Concurrency     row cache lock                           57         14         41         10
[EVENT]    Concurrency     library cache load lock                  38         23         25         27
[EVENT]    Cluster         gc current split                         34          1          2
[EVENT]    Cluster         gc current grant busy                    30          8         16          0
[EVENT]    Concurrency     library cache lock                       22         20         27         29
[EVENT]    Cluster         gc cr multi block request                13          3          7          2
[EVENT]    Concurrency     library cache pin                        11          1          3          0
[EVENT]    Cluster         gc current block lost                    11          4         13          0
[EVENT]    Cluster         gc current block 2-way                    9         60        122          0
[EVENT]    Concurrency     latch: cache buffers chains               8          0          0          0
[EVENT]    Cluster         gc current grant 2-way                    4          1         13          0
[EVENT]    Cluster         gc current block 3-way                    4         31         30          0

3. 결과 요약


테스트 케이스 별 개선 효과 및 적용 시 고려 사항을 정리하면 다음과 같습니다.

구분 수행 결과 고려 사항
Case #2

(Reverse Index)

우수 Reverse 인덱스의 구조상 Index range scan을 수행할 수 없음.
Case #3

(Global Hash Index Partition)

비교적 우수 Index range scan 수행 시, 파티션 개수만큼의 partition iteration 수행 필요.
Case #4

(LIST 파티션을 이용한 입력 노드의 분리)

가장 우수 모델링 및 AP 수정 필요.

Index range scan 수행 시, 노드 개수만큼의 partition iteration 수행 필요.

4. 글을 마치며


좋은 엔지니어가 되기 위해서는 정확한 도구로 정확히 문제를 파악하고, 해당 환경에 가장 적절한 튜닝 방법을 제시하는 능력이 필요합니다. 그러기 위해서는 다각적인 사고를 할 수 있는 사고의 유연성을 기르는 것도 필요할 것 같습니다.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s