출처 : http://ukja.tistory.com/320
갑자기 퀴즈가 하고 싶어졌습니다. 지난 번 퀴즈가 너무 쉬웠다는 비난이 쏟아져서... @_@
1. 오라클 버전은 11.1.0.6입니다.
TPACK@ukja1106> select * from v$version where rownum = 1; BANNER ----------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production2. 파티션이 아닌 테이블 T1과 해시 파티션인 테이블 T2가 있습니다.
TPACK@ukja1106> create table t1(c1 number); Table created. Elapsed: 00:00:00.03 TPACK@ukja1106> create table t2(c1 number) 2 partition by hash(c1) partitions 4; Table created. Elapsed: 00:00:00.013. 파티션이 아닌 테이블 T1에 대해 40만건을 Insert하는데는 1초가 조금 안걸립니다.
TPACK@ukja1106> insert into t1 select level from dual connect by level <= 400000; 400000 rows created. Elapsed: 00:00:00.564. 해시 파티션인 테이블 T2에 대해 40만건을 Insert하는데는 무려! 6.3초가 걸립니다.
TPACK@ukja1106> insert into t2 select level from dual connect by level <= 400000; 400000 rows created. Elapsed: 00:00:06.325. 자, 여기서 문제 나갑니다. 왜 이렇게 큰 성능 차이가 발생할까요?
======================================================================
출처 : http://ukja.tistory.com/321
티팩 (TPACK)
홈페이지 : https://sites.google.com/site/otpack/
설치 : https://sites.google.com/site/otpack/guide/install_guide
이전 포스트 [퀴즈] 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: 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값의 흐름
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 Changes와 Heap Segment Array Inserts 두 개의 지표(불행히도 이 두 개의 유용한 지표는 11g에서 추가된 것입니다)를 잘 해석하시면 Array Insert가 미친 영향을 완벽하게 해석할 수 있습니다. 단, 티팩 자체가 발생시키는 Array Insert가 100여회 정도된다는 것을 고려해서 해석해야 합니다.
티팩이 해주는게 고작 이것이냐고 비난하지 마시기 바랍니다. 티팩이 하고자 하는 것은 성능 트러블슈팅을 위해 필요한 기본적인 데이터를 자동으로 수집하고 적절히 리포트해주는 것일 뿐, 결국 최종 해석은 사람의 몫입니다.
중요한 것은 데이터에 기반한 과학적인 분석을 하느냐 아니면 이거 아니면 저거 다 찔러보는 방식의 분석을 하느냐일 것입니다.
- TPACK 권한 부여
CREATE PUBLIC SYNONYM TPACK FOR TPACK.TPACK; |