태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

[퀴즈] Partitioned Table과 Insert 성능 - 해답편 + 티팩

오라클 2010.06.28 15:58
이전 포스트 [퀴즈] Partitioned Table과 Insert 성능에서 제가 생각한 답변이 나왔습니다. 이렇게 적어주셨습니다.
테이블이 파티셔닝에 되어 있어,
insert 할 때 insert 대상 블럭(버퍼)가 계속 바뀌게 되고,
그로 인해 logical reads가 증가하기 때문인 것 같습니다.

음... 마치 미리 답을 알고 있었다는 듯한 간단 명료한 답변입니다. ^^

이 퀴즈에서 예를 들었던 해시 파티션의 경우 매번 Insert가 이루어질 때마다 매번 다른 블록으로 들어갈 활륙이 높습니다(하지만 항상 그런 것은 아닙니다. 해시 파티션의 경우 해시값에 따라 들어갈 파티션을 결정하게 되는데 같은 해시값이 중복해서 나올 수 있기 때문입니다). 같은 원리로 레인지 파티션이나 리스트 파티션에서도 이런 문제가 발생할 수 있습니다.

가령 100개의 로우가 한번에 하나의 블록안에 들어가게 되면 Batch Processing이 이루어집니다. (또는 Array Insert라고도 합니다) Array Insert 효과에 의해서 Logical Reads도 줄어들고 Redo 데이터도 줄어듭니다.

퀴즈에서와 같이 아래와 같이 테이블을 만듭니다.

create table t1(c1 number);
create table t2(c1 number) partition by hash(c1) partitions 4;
Case 1. 가장 성능이 좋은 경우입니다. 가능한 많은 수의 로우가 한번에 하나의 블록안에 들어가므로 Array Insert의 효과가 극대화됩니다.
-- 가장 성능이 좋음. 
insert into t1 select level from dual connect by level <= 500000;
Case 2. 해시 파티션인 경우 각 로우가 매번 다른 블록으로 들어갈 확률이 높습니다. 따라서 Array Insert의 효과가 크게 감소합니다.
-- 성능이 안좋아짐
insert into t2 select level from dual connect by level <= 500000;
Case 3. 가장 성능이 안좋은 경우는 다음과 같이 건건이 Insert하는 경우입니다. Array Insert가 아예 발생하지 않기 때문입니다.
-- 성능이 가장 안좋음
begin
	for idx in 1 .. 500000 loop
		insert into t1 values(idx);
	end loop;
	commit;
end;
/
Case 4. 해시 파티션에 데이터가 추가되는 원리를 이해한다면, 아래와 같은 방법을 사용하면 Case 1과 같이 가장 성능이 좋게 만들 수 있습니다. ORA_HASH 함수를 이용해서 가능한 같은 파티션으로 정렬된 형태로 Insert하는 트릭입니다. 파티션키에 정렬된 형태로 Insert를 하는 것이 핵심입니다. 레인지 파티션이나 리스트 파티션에서도 동일한 방법을 사용할 수 있습니다.
-- 가장 성능이 좋음
insert into t2 select level from dual connect by level <= 500000 order by ora_hash(level, 3);

하지만 중요한 것은 답을 맞추고 안맞추고 아니라, 데이터를 통해서 객관적으로 검증할 수 있는가입니다. 이전에 이런 퀴즈가 있었죠.

1. 오라클에서 특정 세션(혹은 시스템)의 현재 상태를 바로 알 수 있는 가장 좋은 방법은 무엇일까요?

2. 오라클에서 특정 세션(혹은 시스템)을 하는 일을 추적할 수 있는 가장 좋은 방법은 무엇일까요?

첫번째 질문에 대한 제 대답은 "V$SESSTAT 뷰에 대해 Snapshot을 만들고, 각 Snapshot간의 Delta값을 비교한다"입니다. 즉 세션의 성능 문제를 분석하는데 있어서 가장 중요한 Snapshot 데이터는 V$SESSTAT 뷰이며, 이 데이터를 비교라는 방법을 이용해서 분석하는 것이 가장 기본적인 방법입니다. 이것이 티팩의 핵심적인 아이디어라는 것도 이미 말씀드렸습니다.

거창하게 말씀드렸는데, 더 간결 솔직하게 말하면 V$SESSTAT 뷰가 제공하는 데이터를 분석해보면 어느 정도 의미있는 분석을 할 수 있다. 요런 이야기입니다.

티팩의 Session Snapshot Report를 이용해서 위의 내용을 분석해보겠습니다.

create table t1(c1 number);
create table t2(c1 number) partition by hash(c1) partitions 4;

-- Session Snapshot 시작
exec tpack.begin_session_snapshot;

-- Case 1
insert into t1 select level from dual connect by level <= 500000;

exec tpack.add_session_snapshot;

-- Case 2
insert into t2 select level from dual connect by level <= 500000;

exec tpack.add_session_snapshot;

-- Case 3
begin
	for idx in 1 .. 500000 loop
		insert into t1 values(idx);
	end loop;
	commit;
end;
/
	
exec tpack.add_session_snapshot;

-- Case 4
insert into t2 select level from dual connect by level <= 500000 order by ora_hash(level, 3);

exec tpack.add_session_snapshot;

-- Session Snapshot Report 보기
set pages 10000
set lines 200
col item format a30
col deltas format a50
select * from table(tpack.session_snapshot_report);
결과는 다음과 같습니다.(내용이 길어셔 편집했음을 알려드립니다) DELTAS 값을 잘 분석해보시기 바랍니다.
TYPE       ITEM                            START_VAL    END_VAL TOTAL_DELTA DELTAS
---------- ------------------------------ ---------- ---------- ----------- --------------------------------------------------
STAT       redo size                      1343966008 1567241868   223275860 8486656->87223020->119117508->8448676
STAT       undo change vector size         423505332  485865780    62360448 1695312->26462580->32510584->1691972
STAT       session logical reads            14843144   16914592     2071448 15863->1521337->518902->15346
STAT       db block changes                 10655679   12453768     1798089 12623->762448->1010680->12338
STAT       db block gets                    11368998   13058307     1689309 13946->1144031->517777->13555
STAT       db block gets from cache         11368998   13058307     1689309 13946->1144031->517777->13555
STAT       redo entries                      5338577    6240096      901519 7792->381384->504754->7589
STAT       calls to kcmgrs                   5831580    6730131      898551 6973->380346->504279->6953
STAT       HSC Heap Segment Block Changes    4933463    5814688      881225 2827->375425->500125->2848
...
STAT       Heap Segment Array Inserts        2682231    3063258      381027 2785->375297->113->2832
...
STAT       DB time                             26491      31554        5063 162->1280->3406->215
...
위의 결과에서 HSC Heap Segment Block ChangesHeap Segment Array Inserts 두 개의 지표(불행히도 이 두 개의 유용한 지표는 11g에서 추가된 것입니다)를 잘 해석하시면 Array Insert가 미친 영향을 완벽하게 해석할 수 있습니다. 단, 티팩 자체가 발생시키는 Array Insert가 100여회 정도된다는 것을 고려해서 해석해야 합니다.

티팩이 해주는게 고작 이것이냐고 비난하지 마시기 바랍니다. 티팩이 하고자 하는 것은 성능 트러블슈팅을 위해 필요한 기본적인 데이터를 자동으로 수집하고 적절히 리포트해주는 것일 뿐, 결국 최종 해석은 사람의 몫입니다.

중요한 것은 데이터에 기반한 과학적인 분석을 하느냐 아니면 이거 아니면 저거 다 찔러보는 방식의 분석을 하느냐일 것입니다.

저작자 표시
신고
tags :
Trackback 0 : Comments 4
  1. onsider 2010.06.29 10:24 신고 Modify/Delete Reply

    Blog 내용을 테스트 하던 중 ... 테스트하는 DB User 와 tpack db user와 다를 경우 Error가 발생하네요 ..(권한 문제 때문에 당연한것 이겠지만..)

    그래서 모든 유저에서 사용할수 있도록 스크립트가 추가되면 괜찮을것 같습니다.. (Install시 선택할수 있도록)

    CREATE PUBLIC SYNONYM TPACK FOR TPACK.TPACK;
    CREATE PUBLIC SYNONYM TPACK_SERVER FOR TPACK.TPACK_SERVER;
    GRANT EXECUTE ON TPACK TO PUBLIC;
    GRANT EXECUTE ON TPACK_SERVER TO PUBLIC;

    • 욱짜 2010.06.29 10:40 신고 Modify/Delete

      좋은 지적입니다.

      원래 이 부분을 인스톨 과정에 넣으려고 했는데, 권한 문제가 걱정되어 제거했습니다.

      인스톨 과정에서 선택이 가능하도록 하는 옵션 고민하겠습니다.

  2. salvationism 2010.07.01 00:26 신고 Modify/Delete Reply

    결과값이 여러가지 스냅중 하나 인가요?
    아니면 여러 스냅의 Total 값인가요? 다른 글 안보고 이글을 바로보니 이해가 잘 안되네요.. ^^;;
    Delta 값을 분석하는 내용이 추가되면 좋을거 같습니다.
    욱짜님의 시각도 한번 보고 싶구요 ^^

    • 욱짜 2010.07.01 09:33 신고 Modify/Delete

      제 시각이라는게 별도로 없구요. 데이터를 유심히 볼 뿐입니다. ^^

      Session Snapshot Report에서 각 항목의 의미는 이렇습니다.

      - TYPE: STAT, WAIT, TIME, LATCH, ...
      - ITEM: TYPE에 따른 항목(가령 STAT일 경우 session logical reads 등)
      - START_VAL: 최초 Snapshot의 값
      - END_VAL: 마지막 Snapshot의 값
      - TOTAL_DELTA: END_VAL - START_VAL
      - DELTAS: 각 Snapshot간의 Delta값의 흐름

      STAT.Heap Segment Array Inserts 항목을 보면 DELTAS가

      2785->375297->113->2832

      입니다.

      즉 첫번째 실행(2번째 Snapshot과 1번째 Snapshot의 Delta)시에는 2785회, 두번째 실행(3번째 Snapshot과 2번째 Snapshot의 Delta)시에는 375,297회... 이런 식입니다.

      첫번째 실행의 경우 500,000건의 로우를 Insert하는데 Array Processing이 총 2,500(티팩 라이브러리에서 자체적으로 발생하는 100여회의 값을 빼면)회 정도 발생했으니 한번의 Array Insert에서 대략 200건의 로우를 Insert한다는 것을 의미합니다.

      두번째 실행의 경우 500,000건의 로우를 Insert하는데 Array Processing이 총 375,000회 정도 발생했다고 보고되는 것은 한번의 Array Insert에서 고작 1,2건 정도씩 밖에 Insert가 이루어지지 않았다는 것을 의미합니다.

      반대로 세번째 실행의 경우는 INSERT ... VALUES 구문이므로 아예 Array Insert가 발생하지 않았다는 것을 의미하구요.

      네번째 실행의 경우에는 첫번째 실행과 거의 동일한 정도의 Array Insert가 이루어졌다는 것을 의미합니다.

Write a comment

티스토리 툴바