TIMESTAMP 인덱스 - Right-Growing 경합 #2

지난 글에서 살펴본 테스트 시나리오를 조금 보완했다.

테스트를 위해 아래와 같이 테이블과 프로시저를 생성하자. t2 테이블에 몇가지 항목를 추가했고, total_waits으로 지표를 변경했다. t3 테이블에 세그먼트 통계를 기록한다.

-- 1-1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;

CREATE TABLE t1 (c1 TIMESTAMP, c2 NUMBER);

CREATE TABLE t2 (
    index_type          NUMBER
  , num_rows            NUMBER
  , buffer_busy_waits   NUMBER
  , index_contention    NUMBER
  , allocate_itl_entry  NUMBER
  , row_lock_contention NUMBER
  , end_time            DATE);

CREATE TABLE t3 AS SELECT 0 AS index_type, a.* FROM v$segment_statistics a WHERE 0 = 1;

-- 1-2
CREATE OR REPLACE PROCEDURE p1 (i_ln IN NUMBER)
IS
    l_ln NUMBER := i_ln;
    l_ts DATE   := SYSDATE + INTERVAL '10' SECOND;
BEGIN
    WHILE l_ts > SYSDATE
    LOOP
        INSERT INTO t1 VALUES (SYSTIMESTAMP + NUMTODSINTERVAL (i_ln * 0.1, 'SECOND'), MOD (l_ln, 10));
        COMMIT;
        DBMS_LOCK.SLEEP (0.01);
    END LOOP;
END;
/

아래의 PL/SQL 블록을 수행하자.

-- 2
DECLARE
    PROCEDURE p2
    IS
    BEGIN
        EXECUTE IMMEDIATE 'TRUNCATE TABLE t1';
        EXECUTE IMMEDIATE 'DROP INDEX t1_x1';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    PROCEDURE p3 (i_index_type IN NUMBER)
    IS
    BEGIN
        INSERT
          INTO t2
        SELECT i_index_type, a.num_rows, b.buffer_busy_waits, b.index_contention, b.allocate_itl_entry, b.row_lock_contention, SYSDATE
          FROM (SELECT COUNT (*) AS num_rows FROM t1) a
             , (SELECT MAX (DECODE (event, 'buffer busy waits'            , total_waits)) AS buffer_busy_waits
                     , MAX (DECODE (event, 'enq: TX - index contention'   , total_waits)) AS index_contention
                     , MAX (DECODE (event, 'enq: TX - allocate ITL entry' , total_waits)) AS allocate_itl_entry
                     , MAX (DECODE (event, 'enq: TX - row lock contention', total_waits)) AS row_lock_contention
                  FROM v$system_event
                 WHERE event IN ('buffer busy waits'
                               , 'enq: TX - index contention'
                               , 'enq: TX - allocate ITL entry'
                               , 'enq: TX - row lock contention')) b;

        INSERT
          INTO t3
        SELECT i_index_type, a.*
          FROM v$segment_statistics a
         WHERE a.owner = 'TUNA'
           AND a.object_name = 'T1_X1';

        COMMIT;
    END;

    PROCEDURE p4
    IS
        l_job NUMBER;
    BEGIN
        FOR i IN 1 .. 10 LOOP
            DBMS_JOB.SUBMIT (l_job, 'p1 (' || i || ');');
        END LOOP;
        COMMIT;
        DBMS_LOCK.SLEEP (15);
    END;
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE t1';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE t2';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE t3';

    -- 0
    p1 (1);
    p3 (0);

    -- 1
    p2;
    EXECUTE IMMEDIATE 'CREATE INDEX t1_x1 ON t1 (c1)';
    p1 (1);
    p3 (1);

    -- 2
    p2;
    EXECUTE IMMEDIATE 'CREATE INDEX t1_x1 ON t1 (c1)';
    p4;
    p3 (2);

    -- 3
    p2;
    EXECUTE IMMEDIATE 'CREATE INDEX t1_x1 ON t1 (TRUNC (c1))';
    p4;
    p3 (3);

    -- 4
    p2;
    EXECUTE IMMEDIATE 'CREATE INDEX t1_x1 ON t1 (TRUNC (c1), c2)';
    p4;
    p3 (4);

    -- 5
    p2;
    EXECUTE IMMEDIATE q'[CREATE INDEX t1_x1 ON t1 (TRUNC (c1), TO_CHAR (c1, 'FF1'))]';
    p4;
    p3 (5);
END;
/

결과는 지난 테스트와 대동소이하다. enq: TX - index contention 대기도 성능에 영향을 미치는 것을 확인할 수 있다.

-- 3-1
WITH w1 AS (
SELECT a.index_type
     , a.num_rows
     , ROUND ((a.buffer_busy_waits   - LAG (a.buffer_busy_waits)   OVER (ORDER BY a.end_time)), 2) AS buffer_busy_waits
     , ROUND ((a.index_contention    - LAG (a.index_contention)    OVER (ORDER BY a.end_time)), 2) AS index_contention
     , ROUND ((a.allocate_itl_entry  - LAG (a.allocate_itl_entry)  OVER (ORDER BY a.end_time)), 2) AS allocate_itl_entry
     , ROUND ((a.row_lock_contention - LAG (a.row_lock_contention) OVER (ORDER BY a.end_time)), 2) AS row_lock_contention
     , ROUND ((a.end_time - LAG (a.end_time) OVER (ORDER BY a.end_time)) * 24 * 60 * 60) AS epalsed
  FROM t2 a)
SELECT   a.index_type
       , a.num_rows
       , ROUND (a.num_rows / 10, 2) AS num_rows_per_sec
       , a.buffer_busy_waits
       , a.index_contention
       , a.allocate_itl_entry
       , a.row_lock_contention
       , a.epalsed
    FROM w1 a
       , w1 b
   WHERE a.index_type > 0
     AND b.index_type = 2
ORDER BY 1;

INDEX_TYPE NUM_ROWS NUM_ROWS_PER_SEC BUFFER_BUSY_WAITS INDEX_CONTENTION ALLOCATE_ITL_ENTRY ROW_LOCK_CONTENTION EPALSED
---------- -------- ---------------- ----------------- ---------------- ------------------ ------------------- -------
         1      621            62.10                 0                0                  0                   0      10
         2    6,311           631.10             3,800              207                  0                   0      15
         3    6,276           627.60             1,522              118                  1                   0      15
         4    6,248           624.80             1,277               39                  0                   0      15
         5    6,253           625.30               947               15                  0                   0      15

5 행이 선택되었습니다.

아래는 세그먼트 통계를 조회한 결과다. row lock waits 열에 값이 존재하는데 위에서 살펴본 enq: TX - index contention과 값이 유사하다. 인덱스에 대한 enq: TX - row lock contention 대기는 주로 UNIQUE 인덱스에 동일한 값이 입력된 경우 발생한다. 세그먼트 통계의 row lock waits 지표는 enq: TX 계열의 몇가지 지표를 집계한 것으로 보인다. 해석에 주의할 필요가 있다. 2번보다 5번의 space 지표가 작은 원인도 추후 확인이 필요하다. 

-- 3-2
SELECT   index_type
       , MAX (DECODE (statistic_name, 'buffer busy waits', value)) AS buffer_busy_waits
       , MAX (DECODE (statistic_name, 'ITL waits'        , value)) AS itl_waits
       , MAX (DECODE (statistic_name, 'row lock waits'   , value)) AS row_lock_waits
       , MAX (DECODE (statistic_name, 'space used'       , value)) AS space_used
       , MAX (DECODE (statistic_name, 'space allocated'  , value)) AS space_allocated
    FROM t3
GROUP BY index_type
ORDER BY 1;

INDEX_TYPE BUFFER_BUSY_WAITS  ITL_WAITS ROW_LOCK_WAITS SPACE_USED SPACE_ALLOCATED
---------- ----------------- ---------- -------------- ---------- ---------------
         1                 0          0              0     36,510          65,536
         2             3,236          0            206    179,282         327,680
         3               679          1            116    407,002         524,288
         4               434          0             39    292,978         458,752
         5               590          0             15    162,212         262,144

5 행이 선택되었습니다.