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

TIMESTAMP 열이 선두인 인덱스는 선두 값이 항상 증가하기 때문에 Right-growing 현상이 발생한다. 이로 인해 블록 경합이 발생하여 쿼리의 성능이 저하될 수 있다. 경합을 해소할 수 있는 몇가지 방안을 테스트해보자.

테스트를 위해 아래와 같이 테이블을 생성하자. t1은 데이터를 입력할 테이블, t2는 테스트 결과를 저장할 테이블이다.

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

CREATE TABLE t1 (c1 TIMESTAMP, c2 NUMBER);
CREATE TABLE t2 (index_type NUMBER, num_rows NUMBER, time_waited_micro NUMBER, end_time DATE);

아래는 t1 테이블에 데이터를 입력할 프로시저다. 10초간 수행되며, 1/100초마다 INSERT 문을 수행한다.

-- 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 블록이다. 아래와 같이 t1_x1 인덱스를 변경하며 테스트를 수행한다. 2~5번은 p4 프로시저로 DBMS_JOB 패키지를 통해 10개의 세션에서 동시에 데이터를 입력한다.

1. c1 : 경합 발생
2. c1 : 경합 발생
3. TRUNC (c1) : 시분초를 절삭함으로써 ROWID로 경합 해소 (일자로만 조회)
4. TRUNC (c1), c2 : 변별력이 좋은 열(c2)을 추가하여 경합 해소 (일반적인 방식)
5. TRUNC (c1), TO_CHAR (c1, 'FF1') : 변별력이 좋은 열이 없는 경우 소수점 첫째 자리를 추가하여 경합 해소

-- 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
               ( index_type,   num_rows,   time_waited_micro, end_time)
        SELECT i_index_type, a.num_rows, b.time_waited_micro, SYSDATE
          FROM (SELECT COUNT (*) AS num_rows FROM t1) a
             , (SELECT time_waited_micro FROM v$system_event WHERE event = 'buffer busy waits') b;

        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';

    -- 0
    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;
/

테스트 결과는 아래와 같다. 의도한 결과가 도출되지 않았다. 2번에서 1.17초의 대기 시간이 발생했지만 입력 건수가 증가하는 현상(625.10 > 59.20 * 10)을 확인할 수 있다. 초기 블록 할당에 의한 현상으로 보인다. 2~5번은 대기 시간은 감소했지만, 입력 건수도 함께 감소한 현상을 확인할 수 있다. 함수 수행(3~5)과 추가 데이터의 저장(4~5)으로 인한 것으로 보인다. 추후 시나리오를 보완하여 실환경에서 다시 테스트할 필요가 있겠다.

-- 3
WITH w1 AS (
SELECT a.*
     , ROUND ((a.time_waited_micro - LAG (a.time_waited_micro) OVER (ORDER BY a.end_time)) / 1e6, 2) AS time_waited
     , 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
       , ROUND (a.num_rows / b.num_rows, 2) AS num_rows_ratio
       , a.time_waited
       , ROUND (a.time_waited / b.time_waited, 2) AS time_waited_ratio
       , 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 NUM_ROWS_RATIO TIME_WAITED TIME_WAITED_RATIO EPALSED
---------- -------- ---------------- -------------- ----------- ----------------- -------
         1      592            59.20           0.09        0.00              0.00      10
         2     6251           625.10           1.00        1.17              1.00      15
         3     6203           620.30           0.99        0.84              0.72      15
         4     6192           619.20           0.99        0.49              0.42      15
         5     6170           617.00           0.99        0.45              0.38      15

5 행이 선택되었습니다.