튜닝예제(1)

첫번째로 인덱스 사용에 관한 사항입니다.

다음은 KBORA의 대표적인 튜닝사례입니다.

아래 SQL은 하나의 테이블을 access합니다.

win_pay_info : 약 2100만건
- 인덱스

1. win_pay_info_date: period_flag+pay_date
2. win_pay_info_date_draw: pay_date+sale_draw_no+period_flag
3. win_pay_info_draw: period_flag+sale_draw_no
4. win_pay_info_draw_date: sale_draw_no+pay_date+period_flag
- 컬럼

period_flag: varchar2(2)
pay_date: varchar2(8)
sale_draw_no: number(8)
        
********************************************************************************
/* 튜닝전 */

SELECT sysdate, sysdate, sysdate, state_name,

             to_char(nvl((SUM(win_game_1)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_1), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_2)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_2), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_3)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_3), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_4)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_4), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_5)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_5), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(pay_tickets)), 0) ,'999,999,999')
FROM    win_pay_info
WHERE  period_flag='R' 
and       to_date('2006-03-01', 'YYYY-MM-DD') <= to_date(pay_date, 'YYYYMMDD')

and       to_date(pay_date,'YYYYMMDD') <= to_date('2006-03-21', 'YYYY-MM-DD')
and       retailer_id like '11100003%'
Group by state_name

call         count      cpu      elapsed       disk      query     current     rows
----------------------------------------------------------------------
Parse       2           0.01          0.02            0          0             0           0
Execute    2           0.00          0.00            0          0             0           0
Fetch       4        322.71        348.11     230104     242066          0           2
----------------------------------------------------------------------
total         8        322.72        348.13     230104     242066          0           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows     Row Source Operation
----------------------------------------------------------------------
      1      SORT GROUP BY
     46     TABLE ACCESS BY INDEX ROWID WIN_PAY_INFO
513779    INDEX RANGE SCAN (object id 4028)

********************************************************************************

설명.

where에 있는 pay_date는 날짜를 나타내고 속성는 varchar2입니다. 그런데 이걸 굳이 date로 변경을 해서 date와 비교를 하고 있습니다. to_date('2006-03-01', 'YYYY-MM-DD') <= to_date(pay_date, 'YYYYMMDD') 와 같이 말입니다.
이렇게 되면 첫번째 인덱스인 period_flag+pay_date를 사용할 수 없게 됩니다. 그럼. 위에 plan에서는 513779 INDEX RANGE SCAN (object id 4028) 와 같이 나와 있습니다. 개발자가 생각하기에 인덱스를 사용하고 있지 않나 생각을 할 수 있습니다.
맞습니다. 인덱스를 사용하고는 있습니다. 하지만 period_flag='R'인 모든 날짜에 대해 range scan을 하고 있습니다. 우리가 필요한 '2006-03-01'부터 '2006-03-21'만 뽑아 먹는 것이 아닙니다.
이유는 바로 to_date와 같은 컬럼 suppressing이 발생해서 입니다. 발생하지 않게 다음과 같이 쓰면 됩니다.
'20060301' <= pay_date

********************************************************************************
/* 튜닝후 */

SELECT sysdate, sysdate, sysdate, state_name,

             to_char(nvl((SUM(win_game_1)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_1), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_2)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_2), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_3)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_3), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_4)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_4), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(win_game_5)), 0) ,'999,999,999'),
             to_char(nvl(SUM(win_amount_5), 0),'999,999,999,999,999,999'),

             to_char(nvl((SUM(pay_tickets)), 0) ,'999,999,999')
FROM    win_pay_info
WHERE  period_flag='R' 
and       '20060301' <= pay_date 
and       pay_date <= '20060321' 
and       retailer_id like '11100003%'
Group by state_name

call         count      cpu      elapsed       disk      query     current     rows
----------------------------------------------------------------------
Parse          1        0.01       0.01              0            0           0           0
Execute      1        0.01        0.00              0            0           0           0
Fetch          2        1.61       1.63              3         8672          0           1
----------------------------------------------------------------------
total            4        1.63       1.64              3         8672          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows     Row Source Operation
----------------------------------------------------------------------

       1     SORT GROUP BY
     46     TABLE ACCESS BY INDEX ROWID WIN_PAY_INFO
513779    INDEX RANGE SCAN (object id 4028)

********************************************************************************

결과 : 348.13초 -> 1.64초

********************************************************************************

개선점.

인덱스를 보시면 아시겠지만 같은 컬럼중복이 많이 됐다라는 느낌을 받으실겁니다. 모든 프로그램을 확인해 봤는데 이중 사용하지 않은 인덱스도 존재하는 것 같습니다.
위 sql은 retailer_id라는 똑똑한 조건의 컬럼이 있는데, 이것을 사용할 수 있는 인덱스가 없는 것이 아쉽습니다. 현재 잘 운영되고 있는 시스템이라 물리적인 변경, 즉 인덱스 같은 것은 수정을 하지 않았습니다.

********************************************************************************