여부 속성으로 인한 성능 저하 사례 #1

여부 속성으로 인한 성능 저하 사례를 살펴보자.

테스트를 위해 아래의 테이블을 생성하자. t1을 전문 전송 테이블로 가정하자.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS id
     , LPAD ('X', 4000, 'X') AS doc
     , CASE WHEN ROWNUM > 9990 THEN 'N' ELSE 'Y' END AS if_yn
  FROM XMLTABLE ('1 to 10000');

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT', 'T1', method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY');

전문 전송은 보통 아래의 철차로 수행된다. if_yn이 N인 미전송 전문을 전송하고, 전송이 완료되면 if_yn 값을 Y로 갱신하는 방식이다.

-- 2
BEGIN
    FOR f1 IN (SELECT * FROM t1 WHERE if_yn = 'N' ORDER BY id)
    LOOP
        -- 전송
        UPDATE t1 SET if_yn = 'Y' WHERE c1 = f1.c1;
    END LOOP;
END;

아래는 CURSOR FOR LOOP 문의 쿼리의 실행 계획이다. 인덱스가 없으므로 t1 테이블을 전체 스캔한다. 전문 데이터를 삭제하지 않는다면 테이블 크기가 선형적으로 증가하므로 블록 I/O도 선형적으로 증가한다.^[각주:1]

-- 3
SELECT * FROM t1 WHERE if_yn = 'N' ORDER BY id;

------------------------------------------------------
| Id  | Operation          | Name | A-Rows | Buffers |
------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     10 |   10012 |
|   1 |  SORT ORDER BY     |      |     10 |   10012 |
|*  2 |   TABLE ACCESS FULL| T1   |     10 |   10012 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("IF_YN"='N')

아래는 if_yn 칼럼의 통계 정보다. num_distinct가 2이므로 인덱스 생성 대상이 아닌 칼럼으로 오판할 수 있다.

-- 4
SELECT num_distinct, histogram FROM user_tab_columns WHERE table_name = 'T1' AND column_name = 'IF_YN';

NUM_DISTINCT HISTOGRAM
------------ ---------
           2 FREQUENCY

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

실제 데이터를 조회해보면 if_yn이 N인 행은 10건에 불과한 것을 확인할 수 있다. 전문 전송의 특성에 따라 미전송 건은 항상 적을 수 밖에 없다.

-- 5
SELECT if_yn, COUNT (*) AS cnt FROM t1 GROUP BY if_yn;

IF_YN  CNT
----- ----
Y     9990
N       10

2 행이 선택되었습니다.

아래와 같이 if_yn 칼럼에 인덱스를 생성하자.

-- 6
CREATE INDEX t1_x1 ON t1 (if_yn);

인덱스가 생성되었습니다.

쿼리를 다시 수행하면 쿼리가 인덱스 스캔으로 수행된다. 블록 I/O가 10,012에서 12로 감소했다. 전문 전송이 1분 간격으로 수행된다면 일별 14,400,000개의 블록 I/O를 감소시킬 수 있다.

-- 7
SELECT * FROM t1 WHERE if_yn = 'N' ORDER BY id;

-----------------------------------------------------------------
| Id  | Operation                    | Name  | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     10 |      12 |
|   1 |  SORT ORDER BY               |       |     10 |      12 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     10 |      12 |
|*  3 |    INDEX RANGE SCAN          | T1_X1 |     10 |       2 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("IF_YN"='N')

추가로 if_yn 칼럼에 히스토그램이 생성하면 인덱스 생성으로 인한 부작용을 방지할 수 있다. 아래 쿼리는 if_yn를 Y로 조회했다. 히스토그램으로 인해 인덱스를 스캔하지 않고 테이블을 전체 스캔한 것을 확인할 수 있다.

-- 8
SELECT * FROM t1 WHERE if_yn = 'Y';

-----------------------------------------------------
| Id  | Operation         | Name | A-Rows | Buffers |
-----------------------------------------------------
|   0 | SELECT STATEMENT  |      |   9990 |   10011 |
|*  1 |  TABLE ACCESS FULL| T1   |   9990 |   10011 |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("IF_YN"='Y')

1. 이런 류의 성능 저하를 시한폭탄에 비유하곤 한다. [본문으로]