인덱스와 DML 문

인덱스는 DML 문의 성능에 영향을 미친다. 관련 내용을 살펴보자.

 

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (dt DATE, cd VARCHAR2(6), vl VARCHAR2(10));

INSERT
  INTO t1
SELECT a.dt, b.cd, LPAD ('X', 10, 'X') AS vl
  FROM (SELECT DATE '2050-01-01' + ROWNUM - 1 AS dt FROM XMLTABLE ('1 to 10')) a
     , (SELECT LPAD (ROWNUM, 6, '0') AS cd FROM XMLTABLE ('1 to 2000000')) b;

COMMIT;

CREATE INDEX t1_x1 on t1 (dt, cd) NOLOGGING;
CREATE INDEX t1_x2 on t1 (cd) NOLOGGING;

 

t1 테이블에 일별 2백만 건의 데이터가 입력된다고 가정하자. 아래 쿼리는 데이터 입력에 39.98초가 소요되었다.

-- 2
INSERT /*+ MONITOR */
  INTO t1
SELECT DATE '2050-01-01' + 10 AS dt, LPAD (ROWNUM, 6, '0') AS cd, LPAD ('X', 10, 'X') AS vl
  FROM XMLTABLE ('1 to 2000000');

2000000 행이 생성되었습니다.

경   과: 00:00:39.98

 

SQL Monitor 레포트에서 27초의 IO Waits이 발생한 것을 확인할 수 있다.

-- 3
Global Information
------------------------------
 SQL ID              :  0rjyu8u98c81d
 SQL Execution ID    :  16777216
 Duration            :  41s

Global Stats
=========================================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read  | Read  | Write | Write |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs  | Bytes | Reqs  | Bytes |
=========================================================================
|      41 |      14 |       27 |   626K | 78256 | 612MB |    17 |   9MB |
=========================================================================

 

ASH를 조회해보면 t1_x2 인덱스에서 db file sequential read 이벤트를 28초간 대기한 것을 확인할 수 있다. t1_x1 인덱스는 dt 열이 인덱스 선두에 있기 때문에 신규 데이터 입력시 인덱스 우측에만 데이터가 입력된다.^[각주:1] 데이터가 신규 블록에만 입력되기 때문에 기존 인덱스 블록에 대한 액세스가 거의 발생하지 않는다. 반면 t1_x2 인덱스는 cd 열이 인덱스의 선두에 있기 때문에 기존 인덱스 블록을 대부분 갱신해야 하며, 이 과정에서 I/O 경합이 발생한 것이다.

-- 4
WITH w1 AS (
SELECT   event, current_obj#, COUNT (*) AS cn
    FROM v$active_session_history
   WHERE sql_id = '0rjyu8u98c81d'
     AND event IS NOT NULL
GROUP BY event, current_obj#)
SELECT a.*, b.object_name
  FROM w1 a
     , dba_objects b
 WHERE b.object_id = a.current_obj#;

EVENT                   CURRENT_OBJ# CN OBJECT_NAME
----------------------- ------------ -- -----------
db file sequential read        93062 28 T1_X2

1개의 행이 선택되었습니다.

 

성능 개선을 위해 t1_x2 인덱스를 UNUSABLE 상태로 변경하고 데이터를 입력한 후 다시 REBUILD하는 방식을 사용할 수 있다. 테이블 크기에 따라 차이는 있겠지만 아래 케이스의 경우에는 24.55초가 소요되었다.

-- 5
ALTER INDEX t1_x2 UNUSABLE;

인덱스가 변경되었습니다.

경   과: 00:00:00.44

INSERT /*+ MONITOR */
  INTO t1
SELECT DATE '2050-01-01' + 11 AS dt, LPAD (ROWNUM, 6, '0') AS cd, LPAD ('X', 10, 'X') AS vl
  FROM XMLTABLE ('1 to 2000000');

2000000 행이 생성되었습니다.

경   과: 00:00:08.86

ALTER INDEX t1_x2 REBUILD PARALLEL 2 NOLOGGING;

인덱스가 변경되었습니다.

경   과: 00:00:15.25

ALTER INDEX t1_x2 NOPARALLEL LOGGING;

인덱스가 변경되었습니다.

경   과: 00:00:00.00

 

SQL Monitor 레포트에서 IO Waits이 2.02초로 감소한 것을 확인할 수 있다.

-- 6
Global Information
------------------------------
 SQL ID              :  g5gm07hk5a4a1
 SQL Execution ID    :  16777216
 Duration            :  9s

Global Stats
===================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
===================================================================
|    8.84 |    5.88 |     2.02 |     0.94 |   356K |  234 |   2MB |
===================================================================

1. 이런 인덱스를 Right-growing 인덱스 또는 Right-handed 인덱스라고 한다. [본문으로]