태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'2010/08'에 해당되는 글 6건

  1. 2010.08.25 엑셈 퀴즈에 도전해보세요. (6)
  2. 2010.08.23 Deferred Segment Creation의 재미있는 두가지 버그
  3. 2010.08.17 Oracle 11g의 Batching NLJ와 정렬 문제 (5)
  4. 2010.08.11 Direct Path Insert와 Redo 데이터 (7)
  5. 2010.08.04 [티팩] Session Snapshot Report - Part 2
  6. 2010.08.02 인트라 블록 로우 체이닝(Intra-Block Row Chaining) (3)

엑셈 퀴즈에 도전해보세요.

오라클 2010.08.25 09:52
엑셈에서 매달 수행하는 이벤트인 엑셈 퀴즈의 8월달 문제입니다.

너무 쉬워서 경쟁이 매우 치열할 것 같습니다. :)

정답 외에 번쩍이는 아이디어가 있으시면 가산점 있으니 도전해보세요!

저작자 표시
신고

'오라클' 카테고리의 다른 글

V$ACTIVE_SESSION_HISTORY  (2) 2010.09.14
Remote SQL  (4) 2010.09.07
엑셈 퀴즈에 도전해보세요.  (6) 2010.08.25
Deferred Segment Creation의 재미있는 두가지 버그  (0) 2010.08.23
Oracle 11g의 Batching NLJ와 정렬 문제  (5) 2010.08.17
Trackback 0 : Comments 6
  1. 노을러브 2010.08.25 13:30 신고 Modify/Delete Reply

    퀴즈 정말 좋은것 같아요. 앞으로도 자주 내주시길...

  2. 라튜니 2010.08.26 08:43 신고 Modify/Delete Reply

    과제를 풀다가 스크립트 중 아래 내용은 오타인거 같아 이렇게 글을 남깁니다.

    select * from table(dbms_xplan.display(format=>'basic'));

    • 욱짜 2010.08.25 21:38 신고 Modify/Delete

      제가 깜박했네요.

      위의 문법은 11g에서만 사용됩니다. 10g에서라면 아래와 같이 하면 됩니다.

      select * from table(dbms_xplan.display)

      혹은

      select * from table(dbms_xplan.display(null,null,'basic'));

      11g에서는 SQL에서 PL/SQL 함수에서 Parameter Name을 지정할 수 있어 매우(정말 매우) 편리해졌습니다.

  3. 2010.09.03 17:31 신고 Modify/Delete Reply

    정답관련하여 Dummy 키를 추가한 일종의 복합 인덱스 라고 되어있는대 FBI로 보는게 옳지 않나요?

    • 욱짜 2010.09.06 19:13 신고 Modify/Delete

      맞습니다. 내부적으로는 Function Based Index로 분류해야 정확합니다.

Write a comment


Deferred Segment Creation의 재미있는 두가지 버그

오라클 2010.08.23 16:58
Oracle 11gR2에서는 Deferred Segment Creation이라는 기능이 추가되었습니다. 간단한게 말하면 CREATE TABLE 문을 선언해도 실제로 데이터가 추가될 때까지는 세그먼트를 생성하지 않는 것을 말합니다. Deferred Segment Creation과 INSERT ... SELECT에 관한 재미있는 버그 두 개가 있어 포스트를 통해 소개합니다.
  • 버그 9078678: 세그먼트가 없는 테이블에 대한 병렬 INSERT ... SELECT 문에 대해 예상 실행 계획(Explain Plan)을 보면 병렬이 아닌 직렬(Serial) 실행으로 보이지만, 실제 수행하면 병렬로 수행되는 버그입니다.
  • 버그 9329566: 세그먼트가 없는 테이블에 대해 INSERT ... SELECT 문을 수행하면, SELECT 문장을 두번 수행하는 버그입니다. 정확하게 말하면 SELECT 수행 후 한번 페치를 한 후, 다시 수행하는 버그입니다.
두 버그 모두 세그먼트가 없다는 특징으로 인해 발생하는 버그입니다. 간단한 테스트 케이스를 통해 살펴 보겠습니다. 우선 버그 9078678에 해당하는 현상입니다.

1. 오라클 버전은 11.2.0.1입니다.
TPACK@ukja1120> -- version
TPACK@ukja1120> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. 테이블 T1을 생성합니다. 테이블 T1은 세그먼트가 존재하지 않는 상태입니다. 테이블 T1에 대해 병렬 삽입(Parallel Insert) 문장을 수행하는 경우 실행 계획이 어떻게 되는지 보겠습니다.
TPACK@ukja1120> create table t1(c1 number);

Table created.

TPACK@ukja1120> alter session enable parallel dml;

Session altered.

TPACK@ukja1120> explain plan for
  2  insert /*+ parallel(t1 4) */ into t1
  3  select level from dual connect by level <= 10000;

Explained.

TPACK@ukja1120> select * from table(dbms_xplan.display);

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT               | T1   |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
힌트를 통해 병렬 실행을 지정했지만 직렬 실행(Serial Execution)이 선택되어 버렸습니다.

3. 예측 실행 계획이 아닌 실제로 SQL 문장을 실행한 후 병렬 실행 여부를 관찰해보겠습니다.

TPACK@ukja1120> insert /*+ parallel(t1 4) */ into t1
  2  select level from dual connect by level <= 10000;

10000 rows created.

TPACK@ukja1120> 
TPACK@ukja1120> commit;

Commit complete.

-- https://sites.google.com/site/ukja/sql-scripts-1/o-s/pqstat
TPACK@ukja1120> @pq_stat

DFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS 
---------- ---------- ---------- ---------- ---------- 
         1          0 Consumer   P000             2500 
         1          0 Consumer   P001             2500 
         1          0 Consumer   P002             2500 
         1          0 Consumer   P003             2500 
         1          0 Producer   QC              10000 
         1          1 Consumer   QC                  4 
         1          1 Producer   P000                1 
         1          1 Producer   P001                1 
         1          1 Producer   P002                1 
         1          1 Producer   P003                1 


10 rows selected.

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    0             0
DML Parallelized                        1             1
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                          4             0
Allocation Height                       4             0
Allocation Width                        1             0
Local Msgs Sent                        44            44
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      44            44
Distr Msgs Recv'd                       0             0

11 rows selected.
Explain Plan을 이용한 예측 실행 계획에서는 직렬 실행이라는 결과가 나왔지만, 실제 수행 결과는 병렬 실행이 되었다는 것을 알 수 있습니다. 더 웃긴 것은 이 상태에서(테이블 T1에 데이터가 추가된 상태에서) 예측 실행 계획을 다시 보면 병렬 실행을 하는 것으로 바뀐다는 것입니다.
TPACK@ukja1120> explain plan for
  2  insert /*+ append parallel(t1 4) */ into t1
  3  select level from dual connect by level <= 10000;

Explained.

------------------------------------------------------
| Id  | Operation                         | Name     |
------------------------------------------------------
|   0 | INSERT STATEMENT                  |          |
|   1 |  PX COORDINATOR                   |          |
|   2 |   PX SEND QC (RANDOM)             | :TQ10001 |
|   3 |    LOAD AS SELECT                 | T1       |
|   4 |     PX RECEIVE                    |          |
|   5 |      PX SEND ROUND-ROBIN          | :TQ10000 |
|*  6 |       CONNECT BY WITHOUT FILTERING|          |
|   7 |        FAST DUAL                  |          |
------------------------------------------------------
위 현상은 버그입니다. 세그먼트가 아직 생성되지 않은 테이블에 대해 병렬 실행 문장의 예측 실행 계획을 보면 직렬 실행하는 것으로 나타나지만, 실제로는 병렬 실행되는 버그입니다. 즉, 세그먼트가 없는 테이블에 대해 병렬 실행의 예측 실행 계획을 정확하게 보여주지 못하는 버그입니다.

버그 9329566에 해당하는 테스크 케이스도 보겠습니다.

1. 테이블 T1과 테이블 T2를 동일하게 생성합니다. 단, 테이블 T2에는 한건의 로우를 추가해서 세그먼트를 미리 만듭니다. 테이블 T1은 세그먼트가 아직 생성되지 않은 상태입니다. 10,000 블록의 크기를 가지는 테이블 T3도 만듭니다.

TPACK@ukja1120> create table t1(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));

Table created.

TPACK@ukja1120> create table t2(c1 varchar2(2000), c2 varchar2(2000), c3 varchar2(2000), c4 varchar2(1000));

Table created.

TPACK@ukja1120> 
TPACK@ukja1120> -- insert 1 row into table t2 to create the segment
TPACK@ukja1120> insert into t2 values('1','1','1','1');

1 row created.

TPACK@ukja1120> -- table size 10000 block
TPACK@ukja1120> create table t3
  2  as
  3  select rpad('x',2000) as c1, rpad('x',2000) as c2, rpad('x',2000) as c3, rpad('x',1000) as c4
  4  from dual
  5  connect by level <= 10000;

Table created.
2. 테이블 T3에서 최대값(MAX)을 읽어서 테이블 T1(세그먼트 아직 없음), 테이블 T2(세그먼트 있음)으로 삽입하는 쿼리의 Consistent Gets를 비교해보겠습니다. 테스트의 편의를 위해 티팩의 Session Snapshot Report를 사용합니다.
TPACK@ukja1120> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

TPACK@ukja1120> insert into t1
  2  select max(c1), max(c2), max(c3), max(c4) from t3;

1 row created.

TPACK@ukja1120> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

TPACK@ukja1120> insert into t2
  2  select max(c1), max(c2), max(c3), max(c4) from t3;

1 row created.

TPACK@ukja1120> 
TPACK@ukja1120> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.
아래 결과를 보면 테이블 T1(세그먼트 없음)에 대한 삽입이 테이블 T2(세그먼트 있음)에 대한 삽입 작업에 비해 2배 정도의 Consistent Gets를 보입니다.
TPACK@ukja1120> col item format a40
TPACK@ukja1120> col deltas format a20
TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)
  2  where type = 'STAT';

TPACK@ukja1120> select item, deltas from table(tpack.session_snapshot_report)
  2  where type = 'STAT';

ITEM                                     DELTAS
---------------------------------------- --------------------
...
physical read bytes                      164339712->82305024
consistent gets                          20451->10327
...
이것 또한 버그입니다.
  1. 테이블 T3에서 데이터를 읽는 후 그 데이터를 테이블 T1에 INSERT하려고 합니다.
  2. 그런데 테이블 T1은 아직 세그먼트가 없습니다. 따라서 우선 세그먼트를 먼저 만듭니다.
  3. 여기서 오라클은 1번 단계에서 얻은 값을 재활용하지 못하고 다시 테이블 T3에서 데이터를 읽습니다. 현재 쿼리는 테이블 T3에서 최대값(MAX)을 얻는 것이므로 한번 최대값을 얻을 때마다 테이블 크기인 10,000 블록을 읽어어 합니다. 이 때문에 테이블 T1에 대한 INSERT 작업시에는 20,000블록(2번 읽으므로), 테이블 T2에 대한 INSERT 작업시에는 10,000블록(1번 읽으므로)을 읽게 되는 것입니다.
  4. 이후로는 정상적으로 INSERT가 이루어집니다.
첫번째 버그는 MOS 문서 9078678에 해당하고, 두번째 버그는 MOS 문서 9329566에 해당합니다. 단, 9078678 문서는 정상적으로 검색이 불가능한 것 같습니다.

위 버그들은 세그먼트가 없는 테이블에 대해서만 발생하므로 치명적인 버그라고 보기는 힘들겠습니다. 오히려 개발자들이(설령 오라클 커널 개발자라고 하더라도) 얼마나 기본적인 함정에 쉽게 빠질 수 있는지 알 수 있는 좋은 예로 생각됩니다. 우리가 오라클에서 겪는 많은 문제들 중 상당수가 이런 로직 구멍(Logic Hole)에서 발생합니다. 성능 문제가 생겼을 때 이런 로직 구멍을 간파할 수 있는 테스팅 능력을 갖추는게 중요하겠죠.

PS) 오라클이 Deferred Segment Creation 기능을 디폴트로 활성화(TRUE) 상태로 한 것은 저로서는 의외입니다. 최소한 11gR2에서는 FALSE를 기본값으로 하고 12g(?)부터 TRUE로 하는 것이 옳았다고 생각되네요.

저작자 표시
신고
Trackback 0 : Comment 0

Write a comment


Oracle 11g의 Batching NLJ와 정렬 문제

오라클 2010.08.17 13:48
Oracle 11g에서 Nested Loops Join을 Batch로 처리하는 최적화(이것을 Batching NLJ라고 부르겠습니다)가 추가되었습니다. 아래 실행 계획을 보시면...
-- Oracle 10g
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  COUNT STOPKEY                 |       |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T2    |  <-- Here
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |      INDEX RANGE SCAN          | T1_N1 |
|*  6 |     INDEX RANGE SCAN           | T2_N1 |
------------------------------------------------

-- Oracle 11g
------------------------------------------------
| Id  | Operation                      | Name  |
------------------------------------------------
|   0 | SELECT STATEMENT               |       |
|*  1 |  COUNT STOPKEY                 |       |
|   2 |   NESTED LOOPS                 |       |
|   3 |    NESTED LOOPS                |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |
|*  5 |      INDEX RANGE SCAN          | T1_N1 |
|*  6 |     INDEX RANGE SCAN           | T2_N1 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2    |   <-- And here
------------------------------------------------
TABLE ACCESS BY INDEX ROWID (T2)의 위치가 10g에서 11g에서 전혀 다른 것을 알 수 있습니다. Oracle 11g에 추가된 Batching NLJ때문입니다. 앞으로 Nested Loops Join의 성능이 더 좋아질 것으로 기대할 수 있습니다.

그런데, 며칠 전 Oracle 11g에서 정렬이 깨어진다는 문제를 보고 받았습니다. 이 문제를 간단한 테스트 케이스로 만들어 보았습니다. 인덱스 T1_N1과 Nested Loops Join을 이용해서 ORDER BY 없이 Pagination Query를 구현하고 있는 것에 주의하시면 됩니다.
(이 테스트 케이스는 Windows 버전 오라클 11.1.0.6과 11.2.0.1에서 재현됩니다. 테이블스페이스는 USERS입니다. 다른 버전과 다른 OS, 다른 테이블스페이스에서는 재현되지 않을수도 있습니다)

create table t1
as
select 1 as c1, mod(level, 4) as c2, level as c3, level as c4, rpad('x',1000) as dummy
from dual
connect by level <= 1000;

create table t2
as
select 1001-level as c1, level as c2, rpad('x',1000) as dummy
from dual
connect by level <= 100;

create index t1_n1 on t1(c1, c2, c3);
create index t2_n1 on t2(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

explain plan for
select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
	rownum as rnum,
	t2.c1,
	t1.c4,
	t2.c2
from t1, t2
where
	t1.c3 = t2.c1
	and t1.c1 = 1
	and t1.c2 = 0
	and rownum <= 20
;

select * from table(dbms_xplan.display);

-- Read from the disk
alter system flush buffer_cache;

select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

-- Read from the buffer cache
select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

-- Disable exceptions for buffer cache misses
alter session set "_nlj_batching_misses_enabled" = 0;

-- Read from the disk
alter system flush buffer_cache;

select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;

-- Read from the buffer cache
select * from (
	select /*+ leading(t1 t2) use_nl(t2) index_asc(t1) index_asc(t2) */
		rownum as rnum,
		t2.c1,
		t1.c4,
		t2.c2
	from t1, t2
	where
		t1.c3 = t2.c1
		and t1.c1 = 1
		and t1.c2 = 0
		and rownum <= 20
) where rnum >= 15
;
테스트 시간이 아까우신 분들을 위해 결과를 보겠습니다. Batching NLJ를 사용할 경우 디스크에서 데이터를 읽는 경우와 버퍼 캐시에서 읽는 경우 정렬 결과가 다르다는 것에 주목하시기 바랍니다.
-- Case1 : batching NLJ enabled
-- when the query reads from the disk
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        980        980         21  <-- Why 980 here?
        18        968        968         33
        19        972        972         29
        20        976        976         25

-- when the query reads from the buffer cache
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

-- Case 2: batching NLJ disabled - 정확하게 말하면 Disk에서 읽을 때의 예외를 사용하지 않으면
-- when the query reads from the disk
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21

-- when the query reads from the buffer cache
      RNUM         C1         C4         C2
---------- ---------- ---------- ----------
        15        960        960         41
        16        964        964         37
        17        968        968         33
        18        972        972         29
        19        976        976         25
        20        980        980         21
위의 결과를 요약해보면, "Nested Loops Join의 새로운 최적화 코드는 외부 테이블(Outer Table, 선행 테이블)에서 데이터가 나오는 순서대로 조인 결과가 나온다는 것을 보장하지 않는다. 특히 디스크에서 데이터를 읽을 때는"라는 결론을 내릴 수 있겠습니다.

이러한 제약은(만일 위의 결론이 맞다면) ORDER BY의 부하없이 인덱스와 Nested Loops Join을 이용해서 Pagination Query를 구현하고자 하는 경우에 문제가 될 수 있겠습니다. 하지만 이것이 버그라고 하기는 힘들겠습니다. 오라클에서 정렬 결과를 보장하는 유일한 방법은 ORDER BY 구문을 사용하는 것입니다. 그 외에는 어떤 이유로도 오라클은 정렬 순서를 보장하지 않습니다.

위의 테스트 케이스에서 다음과 같은 세가지 방법 중 하나로 정렬 순서의 일관성을 맞출 수 있습니다.

  • _NLJ_BATCHING_MISSES_ENABLED 파라미터의 값으로 0으로 변경 (아마 가장 좋은 방법)
  • _NLJ_BATCHING_ENABLED 파라미터의 값을 0으로 변경
  • NO_NLJ_BATCHING(t2) 힌트를 적용
히든 파라미터의 변경은 반드시 오라클 지원의 검증 하에 수행해야 함을 다시 한번 주의시켜 드립니다. 또는, MOS의 공식 Workaround로 지정되어 있어야 합니다.

Batching NLJ에 대해서는 좀 더 연구와 테스트가 필요해보입니다. 특히 정확한 작동 방식과 함정에 대한 연구가 필요해보이네요.

저작자 표시
신고
Trackback 0 : Comments 5
  1. 아삽 2010.08.18 10:33 신고 Modify/Delete Reply

    좋은 정보 잘 배우고 갑니다. 감사합니다.^^

  2. 행인 2010.08.19 12:51 신고 Modify/Delete Reply

    항상 좋은 글 잘보고 있습니다.

    그런데 읽고 나서 생각해보니 이상한 점이 있었습니다.
    hash group by 가 10g에서 처음 나타났을 때에는 전세계가 시끄러웠습니다.
    Batching NLJ도 비슷한 현상을 유발하는데, 11g가 출시된지 2년이 넘은 현재까지 전세계적으로 문제삼는 경우는 거의 찾아보기가 힘들었습니다.

    왜 그랬을까요? 정말 이상했습니다.
    제가 생각해본 이유는 다음과 같습니다.

    1. 실제로 문제되는 경우가 매우 드믈다.
    group by는 매우 포괄적으로 모든 업무에서 사용됩니다.
    반면에 Batching NLJ 의 정렬순서가 문제가 되는 경우는 아래와 같은 상황일 때 뿐입니다.
    - NL join에서 inner table에 액세스하는 인덱스를 이용해 order by를 제거

    페이징처리를 위해 사용되는 경우가 대부분이겠지요.
    튜닝해보신분들은 아시겠지만 대부분의 페이징처리를 위한 SQL은 전체 검색결과를 얻고나서 필터링하는 방식으로 작성됩니다. 부분범위처리를 고려해서 정교하게 작성된 것은 넉넉히 잡아도 전체 페이징처리의 10% 이내일 것입니다.

    또한 인덱스를 이용해 부분범위처리로 튜닝된 경우라도 outer table의 인덱스에서 정렬순서가 결정되는 SQL이라면 문제가 되지않습니다.
    1 (Master정보) : M (Detail정보) 관계에서 M쪽의 테이블이 outer table이 된 경우겠죠. 제 경험으로는 이런 경우가 많습니다.
    왜냐하면 주요 검색조건이 가장 많이 포함되어 있기때문입니다.


    2. 전체 결과에 대한 영향범위가 좁다.
    hash group by가 적용되면 전체 겁색결과의 정렬순서가 영향을 받게됩니다.
    하지만 Batching NLJ의 경우에는 Fetch Size 범위내에서만 영향을 받는 것으로 보입니다.
    때문에 실제로 발생하는 경우에도 사용자들이 모르거나 무시하고 사용할 수도 있어 보입니다.

    물론 상기의 내용이 Batching NLJ 의 정렬순서 문제가 별거 아니라는 얘기는 아닙니다.
    단지 너무 겁먹고 히든 파라메더로 유용한 신기능을 막아버리는 사태가 사방에서 발생하지 않을까하는 노파심에서 몇자 적어봅니다.

    일반적인 가이드라인은 다음과 같이 정리하면 될 것 같습니다.

    1. 정렬이 필요한 SQL은 order by를 적용하는 것을 원칙으로 하며, Batching NLJ 을 사용한다.
    2. 부분범위처리를 위해 order by를 제거하는 튜닝은 오라클의 정렬 메카니즘을 정확히 이해하고 있는 전문가만 한다.
    사실 Batching NLJ 뿐만 아니라 Partition Table에서도 Local Index를 이용한 order by 대치 시에도
    2개 이상의 partition에서 데이터를 가져오게 되면 정렬문제가 발생합니다.

    * 이전 버전에서 upgrade하는 경우에는 부분범위로 튜닝된 SQL이 파악되지 않을 때는 결정하기가 쉽지 않겠네요. ㅎㅎ

    • 욱짜 2010.08.19 13:35 신고 Modify/Delete

      Batching NLJ에 의해서 정렬이 바뀌는 문제는 Disk I/O가 수반될 때만 생기는 것으로 생각됩니다. 위의 테스트 케이스를 보시면 알겠지만 Outer Table에서 인덱스를 이용해서 정렬을 유도하더라도 Dis k I/O가 발생하면 정렬 결과가 바뀌어 버립니다.

      이와 비슷한 버그로 MOS Note #6841549가 있는데, 이것도 참조하시면 도움 되겠습니다.

  3. hidden 2010.08.19 23:03 신고 Modify/Delete Reply

    히든을 Batching NLJ 를 막으려는 글은 매우 안좋아 보이는 군요..
    행인님의 글에 동감 합니다.

    • 욱짜 2010.08.20 12:49 신고 Modify/Delete

      오해의 소지가 있는거 같습니다.

      히든 파라미터의 변경은 오라클 서포트의 확인을 거친 후에 수행해야 하는 것은 주지의 사실이죠. 이 문제를 논의해오신 분도 오라클 서포트에 공식적으로 문의해둔 상태입니다.

      위의 사실을 포스트에 확실히 명기하지 않은 것은 확실히 문제의 소지가 있군요. 주의 사항을 확실히 명기할 필요가 있겠습니다.

Write a comment


Direct Path Insert와 Redo 데이터

오라클 2010.08.11 15:00
의외로 많은 분들이 아직도 Direct Path Insert(INSERT /*+ APPEND */ ... 포함)와 Redo 생성량간의 관계에 대해서 헷갈려하시는 것 같습니다.

Direct Path Insert에서 Redo 생성량에 영향을 주는 기본적인 요소는 다음과 같습니다.

  • 데이터베이스가 ARCHIVELOG 모드인가, NOARCHIVE LOG 모드인가?
  • 테이블이 LOGGING 모드인가, NOLOGGING 모드인가?
  • 테이블에 인덱스가 존재하는가?
위의 세가지 요소에 대해 Redo 생성량이 어떻게 되는지 아래와 같이 테스트해보겠습니다. (V$SESSTAT에서 redo size, redo entries를 수집하고 그 값을 비교하기 위해 티팩을 사용합니다)
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off

drop table t_log_no_idx purge;
drop table t_log_with_idx purge;
drop table t_nolog_no_idx purge;
drop table t_nolog_with_idx purge;

create table t_log_no_idx (c1 char(100)) logging;
create table t_log_with_idx (c1 char(100)) logging;
create table t_nolog_no_idx (c1 char(100)) nologging;
create table t_nolog_with_idx (c1 char(100)) nologging;

create index t_log_with_idx_n1 on t_log_with_idx(c1) ;
create index t_nolog_with_idx_n1 on t_nolog_with_idx(c1) ;

----------------------------------------------
-- case 1: noarchive log mode + no_index
select name, log_mode from v$database;   -- NOARCHIVELOG

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;

----------------------------------------------
-- case 2: noarchive log mode + with_index
select name, log_mode from v$database;

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

commit;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;


-----------------------------------------------
-- alter database to archive log mode
connect sys/password as sysdba
shutdown immediate
startup mount
alter database archivelog;
alter database open;

connect user/password

----------------------------------------------
-- case 3: archive log mode + no_index
select name, log_mode from v$database;   -- ARCHIVELOG

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_no_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;


----------------------------------------------
-- case 4: archive log mode + with_index
select name, log_mode from v$database;

truncate table t_log_no_idx;
truncate table t_log_with_idx;
truncate table t_nolog_no_idx;
truncate table t_nolog_with_idx;

exec tpack.begin_session_snapshot;

insert into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_log_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

insert /*+ append */ into t_nolog_with_idx select 'x' from dual connect by level <= 100000;

exec tpack.add_session_snapshot;

commit;

col type format a6 
col item format a30 
col deltas format a30
select type, item, deltas 
from table(tpack.session_snapshot_report)
where type = 'STAT' and item in ('redo size', 'redo entries')
;
Redo Size의 측정 결과를 정리해보면 아래 표와 같습니다.

.

NOARCHIVEARCHIVE

.

LoggingNologgingLoggingNologging

.

No IndexConventional13,327,16413,327,16413,353,08813,353,088

.

Direct 1,317,3721,351,51213,419,2281,360,888

.

With IndexConventional80,755,88480,755,88481,188,59281,188,592

.

Direct 55,740,47248,755,74067,802,16848,652,796

.

간단하게 정리해보면 다음과 같네요.

  1. NOARCHIVELOG 모드일 경우에는 테이블의 Logging 모드 여부에 상관없이 Direct Path Insert는 최소의 Redo를 생성한다.
  2. ARCHIVELOG 모드일 경우에는 테이블이 Nologging 모드여야만 Direct Path Insert가 최소의 Redo를 생성한다.
  3. 인덱스가 존재할 경우에는 Nologging모드에서 Direct Path Insert를 수행하더라도 적지 않은 Redo를 생성한다. 인덱스를 변경하는 과정에서의 Redo는 여전히 생성되기 때문이다.
세 번째 이유 때문에 보통 대량의 데이터를 Direct Path Insert로 추가할 경우에는 인덱스를 비활성화시킨 후 Insert가 끝나고 나면 리빌드하는 방법을 많이 사용합니다.

이 외에 Direct Path Insert에서 헷갈려하시는 내용들에는 다음과 같은 것들이 있습니다.

  • INSERT ... VALUES ... 구문도 Direct Path Insert가 되는가? 이 문제는 이 블로그 포스트에서 상세하 다룬바 있습니다. 요약하면, Oracle 10g까지는 불가능, Oracle 11gR1에서는 APPEND 힌트, Oracle 11gR2에서는 APPEND_VALUES 힌트를 사용하면 됩니다.
  • UPDATE나 DELETE 문장에서도 Direct Path Mode가 가능한가? 더 정확하게 말하면 최소한의 Redo를 생성하는 방식의 UPDATE나 DELETE 수행이 가능한가?입니다. 상식적으로 생각해보면 불가능하다는 판단을 내릴 수 있습니다. INSERT 문장이 Direct Path Mode가 가능한 것은 새로운 데이터의 추가이기 때문입니다. 기존 데이터를 변경하지 않기 때문에 테이블에 TM 락을 Exclusive하게 걸어버리고, High Water Mark 위에 데이터를 Append해버리면 됩니다. 중간에 에러가 생기면 추가된 공간을 무시해버리면 되기 때문에 Redo를 생성하지 않는 옵션이 가능합니다. 하지만 UPDATE나 DELETE는 기존 데이터를 변경하는 것이기 때문에 반드시 Redo가 생성됩니다.
제가 자주 접했던 질문에 대한 대답은 위의 내용 정도로 정리가 가능하겠습니다. 그 외에도 Redo 생성과 관련해서 이슈들이 많을텐데 기회가 닿은 대로 논의하면 좋겠네요.
저작자 표시
신고
tags : APPEND, Redo, 티팩
Trackback 0 : Comments 7
  1. 라튜니 2010.08.11 20:33 신고 Modify/Delete Reply

    항상 좋은 정보를 얻고 있습니다. 먼저 감사의 말씀을 드립니다.

    다름이 아니라 글을 보고 궁금한 점이 있어 염치불구 하고 이렇게 질문 드립니다.

    NOARCHIVE, ARCHIVE 모드 관계없이 인덱스가 있을 경우는 상당량의 REDO가 발생하는데요.

    인덱스가 있는 경우 해당 인덱스에도 NOLOGGING 모드로 설정해 버리면

    생성되는 REDO가 줄어드나요? 아님 인덱스 자체도 NOLOGGING 모드로 설정가능하지만,

    인덱스의 경우 KEY값의 순서에 해당하는 블록에 INSERT 되어야 하기 때문에

    NOLOGGING 모드 설정 자체가 큰 의미가 없는건지요? 궁금하네요~ 답변 부탁드립니다.


    여름철 건강 관리 잘 하시고 좋은 하루 되시길~

    • 욱짜 2010.08.12 09:11 신고 Modify/Delete

      말씀하신 것처럼 위의 예에서는 인덱스에 대한 Logging 모드는 의미가 없습니다. 스크립트에 오해의 소지가 있으므로 그 부분은 수정하도록 하겠습니다.

  2. 2dong0 2010.08.13 14:51 신고 Modify/Delete Reply

    언제나 좋은 정보 감사합니다.^^
    저도 욱짜님처럼 오라클을 완벽하게 이해하고 있으면 좋겠습니다^^ㅋㅋ

    • 욱짜 2010.08.13 15:21 신고 Modify/Delete

      완벽하게 이해하고 있다는 것은 오해의 소지가 있는데요?

      저는 성능 문제에 있어 가능한 많은 데이터를 분석하고 가능한 정확한 결론을 내리게끔 노력할 뿐입니다. 그리고 그런 방법을 공유하는 것 뿐이구요.

      완벽하게 이해했다면 이미 이 업계를 떠났을 겁니다. :)

  3. 2dong0 2010.08.15 20:41 신고 Modify/Delete Reply

    그저 제가 보기에 완변해보이실뿐입니다^^
    언제나 좋은 정보 공유해 주셔서 감사할 따름입니다
    업계를 떠나시는 그날까지 화이팅!!! 하세요^^

  4. 열심남 2010.08.16 13:24 신고 Modify/Delete Reply

    저도 담부터는 대량 작업이 잇으면 인덱스 disable하고 리빌드 하는 방식으로 가이드를 해야겠네요 ^^: 감사합니다.

    • 욱짜 2010.08.16 14:17 신고 Modify/Delete

      한가지 주의할점이 있습니다. 만일 새롭게 추가되는 데이터의 양에 비해 인덱스의 크기가 매우 크다고 하면(즉 기존의 데이터가 훨씬 많으면) 오히려 인덱스를 리빌드하는 것이 더 부담이 될 수도 있습니다. 데이터의 양에 따라 적절히 안배하면 되겠습니다.

Write a comment


[티팩] Session Snapshot Report - Part 2

오라클 2010.08.04 14:13
얼마전 오라클에서 가장 가벼운 작업은?이라는 퀴즈를 낸 적이 있었습니다. 그 때 답변으로 제시된 것은 이것입니다.
begin 
   null;
end;
/
의문을 가질 수 있습니다? 정말 제일 가벼운가? 얼마나 가벼운가? 특히 { select 1 from dual (fast dual일 경우) }와 비교하면 어떠한가?

이런 의문에 답을 할 수 있는 제일 좋은 도구가 티팩이 제공하는 Session Snapshot Report입니다. Session Snapshot Report는 V$SES[SYS]STAT, V$LATCH, V$SES[SYS]_TIME_MODEL, V$SESSION[SYSTEM]_EVENT 등 성능 분석에 필요한 핵심적인 뷰들에 대한 비교를 가능하게 해주기 때문입니다.

Session Snapshot Report를 이용해서 { select 1 from dual }{ begin null; end; }의 성능이 어떤 차이를 보이는지 분석해보겠습니다.

1. 두 경우를 루프를 돌면서 각각 100,000번 수행하고 그 차이를 Session Snapshot Report를 통해 분석해보겠습니다.

TPACK@ukja1021> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
TPACK@ukja1021> 
TPACK@ukja1021> declare
  2  	     v1      number;
  3  begin
  4  	     for idx in 1 .. 100000 loop
  5  		     select 1 into v1 from dual;
  6  	     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.70
TPACK@ukja1021> 
TPACK@ukja1021> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
TPACK@ukja1021> 
TPACK@ukja1021> declare
  2  	     v1      number;
  3  begin
  4  	     for idx in 1 .. 100000 loop
  5  		     begin null; end;
  6  	     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
TPACK@ukja1021> 
TPACK@ukja1021> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
TPACK@ukja1021> 
TPACK@ukja1021> col type format a10
TPACK@ukja1021> col item format a40
TPACK@ukja1021> col deltas format a20
TPACK@ukja1021> select type, item, deltas from table(tpack.session_snapshot_report);
Report는 아래와 같습니다(큰 차이를 보이는 부분만 편집). Call수, Execute Count, Time, Library Cache 래치와 Library Cache Pin의 획득 개수에서 { select 1 from dual }이 압도적으로 불리한 것을 알 수 있습니다. { begin null; end; }가 가장 가벼운 작업이라는 것이 증명된 것일까요?
TYPE       ITEM                                     DELTAS
---------- ---------------------------------------- --------------------
STAT       recursive calls                          100038->36
STAT       calls to get snapshot scn: kcmgss        100024->23
STAT       execute count                            100014->14

TIME       DB time                                  2792453->97955
TIME       sql execute elapsed time                 2791349->96441
TIME       DB CPU                                   2792453->92407

LATCH      library cache                            200066->56
LATCH      library cache pin                        200046->40
사실 위의 테스트는 함정을 가지고 있습니다. PL/SQL 블록내에서 수행되기 때문에 일반적인 쿼리의 수행 패턴이 아니라는 함정입니다. 일반적인 쿼리는 클라이언트에서 SQL*Net을 통해 쿼리를 수행 요청하고, 서버는 그 결과 중 일부를 클라이언트에게 보내주고, 클라이언트는 다시 다음 번 결과를 요청(페치)하는 일련의 과정을 거칩니다.

2. 이 함정을 피하기 위해 다음과 같이 개별 쿼리를 각각 10,000번씩 수행하도록 스크립트를 생성합니다.

spool select_dual.sql
select 
	'select 1 from dual; '
from dual 
connect by level <= 10000;
spool off


spool null.sql
select 
	'begin null; end; ' || chr(10) || '/'
from dual 
connect by level <= 10000;
spool off
3. 위에서 생성한 스크립트를 이용해 동일한 성능 비교를 수행합니다.
TPACK@ukja1021> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
TPACK@ukja1021> 
TPACK@ukja1021> @select_dual
...

TPACK@ukja1021> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
TPACK@ukja1021> 
TPACK@ukja1021> @null
...

TPACK@ukja1021> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
TPACK@ukja1021> 
TPACK@ukja1021> col type format a10
TPACK@ukja1021> col item format a40
TPACK@ukja1021> col deltas format a20
TPACK@ukja1021> select type, item, deltas from table(tpack.session_snapshot_report);
Report는 아래와 같습니다(큰 차이를 보이는 부분만 편집). 전혀 다른 모습을 보입니다.
  • User Calls 수에서는 { select 1 from dual }이 더 많습니다.
  • Network을 통한 데이터 전송도 { select 1 from dual }이 더 많습니다. 한건이라고는 사지만 페치가 이루어지기 때문입니다.
  • Execute Count는 동일합니다. 앞서의 비교에서는 { select 1 from dual }의 Execute Count가 훨씬 많았던 것과는 차이가 있습니다.
  • 수행 시간(DB time)의 차이도 크게 줄었습니다. 시간면에서의 차이는 거의 없다고 보입니다.
  • 재미있는 것은 { begin null; end; }가 Library Cache 래치를 두 배 정도 더 획득한다는 것입니다.
TYPE       ITEM                                     DELTAS
---------- ---------------------------------------- --------------------
STAT       bytes sent via SQL*Net to client         4040131->1310178
STAT       user calls                               30002->20002
STAT       SQL*Net roundtrips to/from client        20001->10001
STAT       execute count                            10013->10012
STAT       parse count (total)                      10001->10002
STAT       opened cursors cumulative                10001->10001
STAT       session cursor cache hits                10001->10000

TIME       DB time                                  1236939->1005865
TIME       DB CPU                                   1236939->1000893
TIME       sql execute elapsed time                 515669->437225

LATCH      session idle bit                         60023->40016
LATCH      library cache                            20309->40089
LATCH      library cache pin                        20168->40062
테스트 방식에 따라 전혀 의외의 결과가 나온다는 것을 알 수 있습니다. 실제로 쿼리가 수행되는 방식으로 시뮬레이션해보면 두 경우의 성능 차이가 생각보다 그렇게 크지 않다고 할 수 있습니다. 그 이유는 서버에서의 작업보다는 클라이언트와의 통신에서 발생하는 부하가 더 비중이 높기 때문이라고 볼 수 있습니다.

여기서 한가지 의문을 가질 수 있습니다.

{ begin null; end; } 가 오라클에서 가장 가벼운 작업이라는 것은 올바른 답인가? 여러분들의 판단은 어떤 것인가요?

PS) 이 포스트의 목적은 티팩의 Session Snapshot Report의 효용성을 설명하기 위한 것입니다. 무엇이 가장 가벼운 작업인가는 사실 중요한 것이 아니죠. 그것이 가장 가벼운 작업인지를 어떻게 증명하느냐가 핵심입니다.

이전 글 보기

  1. [티팩] 성능 문제를 트러블슈팅하는 두가지 틀(Frame)
  2. [티팩] oradebug
  3. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 1
  4. [티팩] [티팩] 지능적 대기 이벤트 분석 - Part 2 (핫 블록?)
  5. [티팩] 지능적인 대기 이벤트 분석 - Part3. (대기이벤트 프로파일링)
  6. [티팩] Session Snapshot Report - Part 1
저작자 표시
신고
tags : 티팩
Trackback 0 : Comment 0

Write a comment


인트라 블록 로우 체이닝(Intra-Block Row Chaining)

오라클 2010.08.02 16:13
로우 체이닝이라고 불리는 현상은 정확하게 구분하면 로우 체이닝(Row Chaining)로우 마이그레이션(Row Migration)으로 나뉩니다.

여기까지는 일반적으로 알려져 있습니다. 한가지 자주 언급되지 않는 현상이 인트라 블록 로우 체이닝(Intra-Block Row Chaining)입니다. 말 그대로 같은 블록 내에서 로우 체이닝이 발생한다는 것을 의미합니다.

블록 레벨에서(딕셔너리 레벨이 아닌) 하나의 로우가 저장할 수 있는 최대 컬럼 수는 255개입니다. 이 제약은 테이블이 255개 이상의 컬럼을 저장하지 못했던 먼 옛날의 버전의 오라클에서 생긴 제약입니다. 이 제약이 어떤 이유로 인해(아마도 구현상의 문제 때문에) 완전히 해소되지 못하고 인트라 블록 로우 체이닝을 일으키는 원인이 되는 것입니다.

간단한 테스트 케이스를 통해 상세한 논의를 해보겠습니다.

(단, 그 전에 한가지 전제가 필요하겠습니다. 오라클은 특정 로우를 읽으러 갔을 때 체이닝때문에 나머지 부분을 읽으러 가야할 경우 V$SES[SYS]STAT 뷰의 table fetch contined row 항목 값을 증가시킵니다. 따라서 이 항목값을 모니터링하면 로우 체이닝의 발생 여부를 간접적으로 모니터링할 수 있습니다.)

1. 오라클 버전은 11.1.0.6입니다.

SQL> select * from v$version where rownum = 1;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
2. 300개의 컬럼을 가지는 테이블 T_CHN2을 생성합니다. 편의를 위해서 모든 컬럼의 디폴트 값을 1로 지정합니다.
SQL> declare
  2  	v_sql		varchar2(20000);
  3  begin
  4  	v_sql := 'create table t_chn2 ('  || chr(10);
  5  
  6  	for idx in 1 .. 299 loop
  7  		v_sql := v_sql || 'c' || idx || ' number default 1, ' || chr(10);
  8  	end loop;
  9  
 10  	v_sql := v_sql || 'c300 number default 1)';
 11  
 12  	execute immediate v_sql;
 13  end;
 14  /

PL/SQL procedure successfully completed.
3. 총 1000건의 로우를 추가합니다. 컬럼 C1을 제외한 나머지 컬럼들은 디폴트 값이 1이 저장될 것입니다.
SQL> insert into t_chn2(c1)
  2  select
  3  	level
  4  from dual connect by level <= 1000;

1000 rows created.
4. 앞서 설명에 의하면 하나의 로우가 255개보다 많은 수의 컬럼으로 이루어진 경우에는 블록 내에서 체이닝이 발생하게 됩니다. 첫번째 블록에 대해 덤프를 수행해서 어떤 형태로 체이닝이 발생하는지 살펴 보겠습니다.
SQL> col fno new_value fno
SQL> col bno new_value bno
SQL> select
  2  	dbms_rowid.rowid_relative_fno(rowid) as fno,
  3  	dbms_rowid.rowid_block_number(rowid) as bno
  4  from t_chn2
  5  where c1 = 1;

       FNO        BNO
---------- ----------
         4       1216
아래 결과를 보면 재미있는 사실을 알 수 있습니다. 300개의 컬럼은 (나머지 255개의 컬럼 + 첫 45개의 컬럼)으로 체이닝된다는 것입니다. fg(flag byte)값이 L=Last Piece, H+F=Header+First Piece인 것으로부터 추측할 수 있습니다.
(블록 덤프을 간편하게 수행하기 위해 티팩을 사용합니다)
SQL> select * from table(tpack.block_dump(&fno, &bno, 0));
...
tab 0, row 0, @0x1c80
tl: 768 fb: -----L-- lb: 0x1  cc: 255  <-- 첫번째 로우의 나머지 255개 컬럼
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
...
col 254: [ 2]  c1 02
tab 0, row 1, @0x1bf0
tl: 144 fb: --H-F--- lb: 0x1  cc: 45   <-- 첫번째 로우의 첫 45개 컬럼
nrid:  0x010004c0.0
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
...
col 44: [ 2]  c1 02
tab 0, row 2, @0x18f0
tl: 768 fb: -----L-- lb: 0x1  cc: 255   <-- 두번째 로우의 나머지 255개 컬럼
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
...
col 254: [ 2]  c1 02
tab 0, row 3, @0x1860
tl: 144 fb: --H-F--- lb: 0x1  cc: 45   <-- 두번째 로우의 첫 45개 컬럼
nrid:  0x010004c0.2
col  0: [ 2]  c1 03
col  1: [ 2]  c1 02
...
5. (나머지 255개의 컬럼 + 첫 45개의 컬럼)로 체이닝이 이루어져 있으므로 첫 45개의 컬럼을 읽을 때까지는 체이닝에 의해 table fetch contined row 항목 값이 증가하는 현상이 발생하지 않은 것입니다.
(V$SESSTAT 뷰를 간편하게 조회하기 위해 역시 티팩을 사용합니다)
SQL> -- for table full scan(45th column)
SQL> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

SQL> 
SQL> select /*+ full(t_chn2) */ count(c45) from t_chn2;

COUNT(C45)
----------
      1000

SQL> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from table(tpack.session_snapshot_report)
  2  where type = 'STAT' and item like 'table fetch continued row'
  3  ;

no rows selected
반면에 46번째 컬럼을 액세스하게 되면 table fetch contined row 항목 값이 증가하게 됩니다. 불필요한 컬럼을 액세스하지 않아야 할 이유 중의 하나가 될 수 있겠습니다.
SQL> -- for table full scan (46th column)
SQL> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select /*+ full(t_chn2) */ count(c46) from t_chn2;

COUNT(C46)
----------
      1000

SQL> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from table(tpack.session_snapshot_report)
  2  where type = 'STAT' and item like 'table fetch continued row'
  3  ;

TYPE  ITEM                            START_VAL    END_VAL TOTAL_DELTA DELTAS
----- ------------------------------ ---------- ---------- ----------- -------
STAT  table fetch continued row            9214       9368         154 154
6. 테이블을 재구축(MOVE)하게 되면 어떻게 될까요? 로우 체이닝과 마찬가지로 인트라 블록 로우 체이닝또한 테이블을 재구축하는 것으로 해결되지 않을 것으로 추측할 수 있습니다. 단, 테이블 재구축에 의해 한가지 재미있는 현상이 발생합니다. 테이블을 재구축한 후 첫번째 블록을 덤프해보겠습니다.
SQL> -- after table move
SQL> alter table t_chn2 move;

Table altered.
재미있게도 300개의 컬럼이 (첫 255개의 컬럼 + 나머지 45개의 컬럼)으로 체이닝되어 있습니다. 테이블 재구축 과정에서 체이닝의 순서가 바뀌는 것을 알 수 있습니다.
SQL> -- get the 1st block(c1 = 1)
SQL> col fno new_value fno
SQL> col bno new_value bno
SQL> select
  2  	dbms_rowid.rowid_relative_fno(rowid) as fno,
  3  	dbms_rowid.rowid_block_number(rowid) as bno
  4  from t_chn2
  5  where c1 = 1;

       FNO        BNO
---------- ----------
         4       2120

SQL> select * from table(tpack.block_dump(&fno, &bno, 0));

tab 0, row 0, @0x1c7a
tl: 774 fb: --H-F--- lb: 0x0  cc: 255
nrid:  0x01000848.1
col  0: [ 2]  c1 25
col  1: [ 2]  c1 02
col  2: [ 2]  c1 02
...
col 254: [ 2]  c1 02
tab 0, row 1, @0x1bf0
tl: 138 fb: -----L-- lb: 0x0  cc: 45
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
...
그 덕분에 이번에는 Table Full Scan으로 255번째의 컬럼까지만 읽을 때는 table fetch contined row 항목 값이 증가하지 않습니다.
SQL> -- 255th column
SQL> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select /*+ full(t_chn2) */ count(c255) from t_chn2;

COUNT(C255)
-----------
       1000

SQL> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from table(tpack.session_snapshot_report)
  2  where type = 'STAT' and item like 'table fetch continued row'
  3  ;

no rows selected
반면에 256번째의 컬럼을 액세스하는 순간 table fetch contined row 항목 값이 증가하게 됩니다.
SQL> -- 256th column
SQL> exec tpack.begin_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select /*+ full(t_chn2) */ count(c256) from t_chn2;

COUNT(C256)
-----------
       1000

SQL> exec tpack.add_session_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from table(tpack.session_snapshot_report)
  2  where type = 'STAT' and item like 'table fetch continued row'
  3  ;

TYPE  ITEM                            START_VAL    END_VAL TOTAL_DELTA DELTAS
----- ------------------------------ ---------- ---------- ----------- -------
STAT  table fetch continued row           10578      10720         142 142
아무리 간단한 기능이라고 하더라도 실제의 동작 방식은 의외로 복잡하고 다양하다는 것을 알 수 있습니다.

로우 체이닝에 의한 부가적인 읽기 작업은 평상시에는 큰 영향이 없다가 특정 상황에서 갑자기 성능 저하를 일으키기도 합니다. 버그가 개입하는 경우도 종종 있구요. 위에서 보는 것과 같은 분석 기법을 몸에 익히고 있다면 그런 상황에서 좀 더 유연하고 과학적으로 대처할 수 있을 것 입니다.

PS) 나중에 기회 있을 때 로우 체이닝/로우 마이그레이션/인트라 블록 로우 체이닝까지 포함해서 좀 더 종합적인 포스트를 올리도록 하겠습니다.

저작자 표시
신고
Trackback 0 : Comments 3
  1. Ejql 2010.08.03 13:57 신고 Modify/Delete Reply

    인트라 블록이라.. 투덜이스머프가 생각나게하는 넘입니다.
    잘 보고 갑니다.

  2. 2dong0 2010.08.04 11:02 신고 Modify/Delete Reply

    재미있는 책을 읽는거 같아서 기분 좋습니다. ^^좋은 정보 감사합니다.

Write a comment