우리 회사 데이터베이스를 티베로로 변경하기
비효율적인 복합 인덱스의 선택 0 0 1,083

by 티베로 티베로전환 티베로성능개선 [2018.09.30]


소속 회사가 제공하는 서비스 중 대다수는 금융 회사가 제출한(매일 혹은 매월) 여러 금융 상품의 정보를 가공한 것이다. 따라서 특정 일자의 여러 가지 상품별 정보를 보여주거나 특정 상품의 기간별 정보를 보여주는 화면이 매우 많아 대부분의 서비스 목적 테이블에는 다음과 같은 컬럼 및 인덱스가 존재한다.

> 수수료 정보 목적 테이블

컬럼 : 기준일자, 상품코드, 상품정보1, 상품정보2, 상품정보N

복합 인덱스 1(IX01_수수료) : 기준일자, 상품코드(선행 컬럼이 기준일자이므로 (조건1) 특정 기준일자에 대한 (조건2) 금융 상품들의 정보를 보여주는 화면을 위해 사용)

복합 인덱스 2(IX02_수수료) : 상품코드, 기준일자(선행 컬럼이 상품코드이므로 (조건1) 특정 상품에 대한 (조건2) 기간별 상품정보를 보여주는 화면을 위해 사용)

만약 위 테이블 및 테이블의 인덱스를 활용하여 다음과 같은 업무 요건의 SQL을 작성하려면 어떠한 인덱스를 선택해야 할까?

>> 업무 요건 : 수수료 정보 목적 테이블에서 보유하고 있는 상품 중 2016년 9월 1일 이전에 상품코드 ‘123456’가 제출한 보고서 중 가장 최근에 제출한 보고서 일자

예상한 것처럼 복합 인덱스 2번을 선택해야 한다. 복합 인덱스 2번은 상품코드가 선행 컬럼이므로 ‘123456’의 위치를 먼저 빠르게 탐색이 가능하다. 그리고 해당 코드에 대한 기준일자 또한 정렬되어 있을 것이므로 2016년 9월 1일 이전의 값에 빠르게 접근할 수 있다

반면 복합 인덱스 1을 선택했을 경우는 선행 컬럼이 기준일자 이므로 2016년 9월 1일부터 해당 코드가 있는지 검색하고 없을 경우 2016년 8월 31일 > 2016년 8월 30일 > 2016년 8월 29일 순으로 찾을 때까지 검색하게 된다.

이러한 이유로 참고 4-12에서 보는 것처럼 2016년 9월 1일에 결과가 찾아진 123456의 상품에 대해선 인덱스를 올바르게 선택했을 때와 유사한 성능(consistent gets 8) 을 보이지만 2016년 1월 10일에서야 결과를 찾은 789ABC의 경우는 많은 성능 차이(consistent gets 17225)를 보이게 된다.

만약 결과가 없는(NULL) 상품코드의 경우는 복합 인덱스 2의 경우 없음을 바로 인지하고 NULL을 리턴하겠지만 복합 인덱스 1은 전체 날짜를 모두 탐색하고 나서야 NULL임을 인지할 수 있으므로 심각한 성능저하가 발생하게 된다.

이처럼 동일한 쿼리가 조건에 따라 성능의 편차가 큰 SQL들은 잘못되었다는 점을 인식하기 힘들어 원인 파악이 어려운 특징이 있다. 따라서 소속 회사의 테이블과 같이 유사한 그러나 그 목적과 결과는 정말 상이한 복합 인덱스를 보유하고 있다면 인덱스 선택 시 신중을 기해야 할 것이다.

잘못된 인덱스 선택 - [기준일자, 상품코드]

SELECT /*+ INDEX_DESC( TBL IX01_수수료 ) */ 기준일자
FROM 수수료정보테이블 TBL
WHERE 기준일자 <= '20160901'
AND 상품코드 = '123456'
AND ROWNUM = 1;

올바른 인덱스 선택 - [상품코드, 기준일자]

SELECT /*+ INDEX_DESC( TBL IX02_수수료 ) */ 기준일자
FROM 수수료정보테이블 TBL
WHERE 기준일자 <= '20160901'
AND 상품코드 = '123456'
AND ROWNUM = 1;

1) 상품코드가 123456일 경우 - 결과 2016년 9월 1일

Execution Plan(올바른 인덱스 선택)
---------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:6, %%CPU:0, Rows:1)
2 INDEX (RANGE SCAN) DESCENDING: IX02_수수료 (Cost:6, %%CPU:0, Rows:306)

NAME VALUE
------------------- ----------
db block gets 0
consistent gets 8
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0


Execution Plan(잘못된 인덱스 선택)
---------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:803, %%CPU:99, Rows:1)
2 PARTITION RANGE (SUBSET PART) (Cost:803, %%CPU:99, Rows:306) PS:1, PE:15)
3 FILTER (Cost:803, %%CPU:99, Rows:306)
4 INDEX (RANGE SCAN) DESCENDING: IX01_수수료 (Cost:3, %%CPU:0, Rows:48532550) 

NAME VALUE
----------------------- ----------
db block gets 0
consistent gets 8
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0

2) 상품코드가 789ABC인 경우 - 결과 2016년 1월 1일

Execution Plan(올바른 인덱스 선택)
---------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:11, %%CPU:0, Rows:1)
2 INDEX (RANGE SCAN) DESCENDING: IX02_수수료 (Cost:11, %%CPU:0, Rows:1163) 

NAME VALUE
------------------- ----------
db block gets 0
consistent gets 8
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0

Execution Plan(잘못된 인덱스 선택)
--------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:803, %%CPU:99, Rows:1)
2 PARTITION RANGE (SUBSET PART) (Cost:803, %%CPU:99, Rows:1163) (PS:1, PE:15)
3 FILTER (Cost:803, %%CPU:99, Rows:1163)
4 INDEX (RANGE SCAN) DESCENDING: IX01_수수료 (Cost:3, %%CPU:0, Rows:48532550) 

NAME VALUE
--------------- ----------
db block gets 0
consistent gets 17225
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0

비효율 4)에 대한 얘기를 끝내기 전에 특정 컬럼의 MAX를 추출할 때 MAX 함수를 사용하는 것과 위 예시와 같이 INDEX_DESC HINT를 사용(MIN의 경우는 MIN함수 혹은 INDEX_ASC HINT)하는 것이 성능상 차이가 있을지 확인해보자.

4-13 결과를 보면 두 방법 모두 큰 성능 차이주4) 없이 MAX 값이 추출되므로 무리없이 사용이 가능한 것으로 확인된다. 다만 인덱스 힌트를 사용하는 경우는 비효율4)와 같은 일이 발생하지 않도록 반드시 알맞은 인덱스를 선택한 것이 맞는지 확인해 볼 필요가 있다.

|주4| MAX 함수 PLAN을 보면 SORT AGGR(집합 함수(COUNT, MAX, MIN등) 처리) OPERATION에 메모리를 67K를 사용한 것으로 나타나는데, 위 쿼리는 MAX 값이 인덱스 레코드 자체에 포함되어 있으므로 SORT AGGR OPERATION이 동작할 필요가 없기 때문에, 실제로는 사용되지 않는 것으로 티베로 연구소와 확인했으며 향후에는 메모리 할당도 하지 않도록 개선될 예정이다.

  • [참고 4-13] MAX 추출
  •  -- TEST TABLE 및 DATA 생성
    CREATE TABLE TIBEROTEST.MAXTEST (PROCESS_DT VARCHAR(8) NOT NULL);
    CREATE INDEX TIBEROTEST.MAXTEST_IDX1 ON TIBEROTEST.MAXTEST(PROCESS_DT);
    
    INSERT INTO TIBEROTEST.MAXTEST 
    SELECT LEVEL 
      FROM DUAL CONNECT BY LEVEL <= 100000; 
    COMMIT;
    
    alter session set GATHER_SQL_PLAN_STAT='Y';
    
    --1) MAX 함수를 통한 MAX VALUE 추출
    
    SELECT --MAXTEST
           MAX (PROCESS_DT) FROM MAXTEST;
    
    
    --2) INDEX_DESC를 통한 MAX VALUE 추출
    
    SELECT --MAXTESTIDX
           /*+ INDEX_DESC(MAXTEST MAXTEST_IDX1) */ PROCESS_DT 
      FROM TIBEROTEST.MAXTEST WHERE ROWNUM < 2;
    
    
    --3) 실제 수행 PLAN 비교
    
    select sql_id 
      from v$sql where sql_text like '%SELECT --MAXTEST%';
      
    (g13kdaunvrf3p) >> select * from table(dbms_xplan.display_
    cursor('g13kdaunvrf3p', 'ALL'));
    
    
    select sql_id 
      from v$sql where sql_text like '%SELECT --MAXTESTIDX%';
    
    (2fmzh2nhk6fpz) >> select * from table(dbms_xplan.display_
    cursor('2fmzh2nhk6fpz', 'ALL')); 
    
    
  • 가. MAX 함수 사용
  • MAX 추출
  • 나. INDEX_DESC 사용
  • INDEX_DESC 사용

  • - 해당 강좌는 도서 " [우리 회사 데이터베이스를 티베로로 변경하기]"의 내용을 옮겼습니다.
  • - 해당 도서는 기간계 DBMS(DATABASE MANAGEMENT SYSTEM)를 티베로로 전환하는 실제 프로젝트를 수행한 실무자가 DBMS 전환 과정과 실제 적용 사례, 문제 해결 과정 등을 자세하게 설명하고 있습니다.

- 강좌 URL : http://www.gurubee.net/lecture/4132

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입