TunA :: TunA

Oracle/Tuning2020. 7. 8. 10:24

여부 속성으로 인한 성능 저하 사례 #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')

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

저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

쿼리 변환을 통한 Top-N 쿼리 성능 개선 사례 (0)	2020.09.16
여부 속성으로 인한 성능 저하 사례 #2 (0)	2020.07.09
뷰 병합이 동작하지 않는 사례 (0)	2020.04.07
조인에 따른 사용자 함수의 동작 (0)	2020.03.21
월 기준 이력 조회 (0)	2020.03.16

Posted by 정희락_

«이전 1 2 3 4 5 6 ··· 256 다음»

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

TunA

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

'Oracle > Tuning' 카테고리의 다른 글

카테고리

최근에 올라온 글

글 보관함

달력

링크

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역