SQL 튜닝 사례

김하영·2021년 4월 2일
0

1.적절한 인덱스의 사용

UPDATE 
    TB_CCPIDSCNT
SET 
    END_DATE = DECODE(
        SIGN((NVL(END_DATE, TO_CHAR((ADD_MONTHS(TO_DATE(START_DATE, 'YYYYMMDD'), 36) - 1), 'YYYYMMDD')) - :b0)), 1, :b0,
        END_DATE),
    END_ORD_NO = :b2
WHERE NODE_TYPE = 'S'
  AND NODE_ID = :b3
  AND DC_PLAN_ID IN ('0680', '0964')
  AND END_ORD_NO IS NULL

[ 실행 계획 ]

UPDATE STATEMENT Optimizer Mode=RULE UPDATE TB_CCPIDSCNT 

**CONCATENATION **

TABLE ACCESS BY INDEX ROWID TB_CCPIDSCNT 

**INDEX RANGE SCAN TX_CCPIDSCNT_01 **

TABLE ACCESS BY INDEX ROWID TB_CCPIDSCNT 

**INDEX RANGE SCAN TX_CCPIDSCNT_01**

[ 튜닝포인트 ]

TB_CCPIDSCNT 테이블 인덱스 정보

현재 TX_CCPIDSCNT_01 인덱스를 사용하고 있으므로 DC_PLAN_ID 컬럼 하나만 처리범위를 줄이는데 사용되고 있다.
그러나 WHERE 조건에 사용된 NODE_TYPE, NODE_ID, DC_PLAN_ID 컬럼은 앞의 두개가 EQUAL 조건이고 세번째 컬럼이 IN 조건이므로 PK_CCPIDSCNT 인덱스를 사용하는 것이 훨씬 효율적이다.

[ 수정SQL ]

UPDATE /*+ INDEX(TB_CCPIDSCNT PK_CCPIDSCNT) */
    TB_CCPIDSCNT
SET END_DATE   = DECODE(
        SIGN((NVL(END_DATE, TO_CHAR((ADD_MONTHS(TO_DATE(START_DATE, 'YYYYMMDD'), 36) - 1), 'YYYYMMDD')) - :b0)), 1, :b0,
        END_DATE),
    END_ORD_NO = :b2
WHERE NODE_TYPE = 'S'
  AND NODE_ID = :b3
  AND DC_PLAN_ID IN ('0680', '0964')
  AND END_ORD_NO IS NULL

[ 실행계획 ]

UPDATE STATEMENT Optimizer Mode=RULE 

UPDATE TB_CCPIDSCNT 

**INLIST ITERATOR CONCATENATED **

TABLE ACCESS BY INDEX ROWID TB_CCPIDSCNT 

**INDEX RANGE SCAN PK_CCPIDSCNT**

[ 튜닝 가이드라인 ]

조건에 사용되는 컬럼을 최대한 사용할 수 있는 인덱스를 사용하고 있는지 확인하고,
그렇지 않을 경우 원하는 인덱스를 사용할 수 있도록 HINT를 사용한다.

2. INDEX SCAN 을 통한 TABLE ACCESS 가 불리한 경우


SELECT TRAN_NO
     , TO_CHAR(REF_DT, 'YYYY/MM/DDHH24:MI:SS')
     , TO_CHAR(CHG_SCHE_DT, 'YYYY/MM/DDHH24:MI:SS')
     , ACC_TELCODE
     , REMK
     , TO_CHAR(REG_DATE, 'YYYY/MM/DDHH24:MI:SS')
FROM TB_CWKONCALL
WHERE ((TRAN_TYPE > '0'
    AND PROCESS_FLAG IS NULL)
    AND TRAN_NO LIKE 'K%')

[ 실행 계획 ]

SELECT STATEMENT Optimizer Mode=RULE 
**TABLE ACCESS BY INDEX ROWID TB_CWKONCALL** 
**INDEX RANGE SCAN PK_CWKONCALL**

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

40731 consistent gets

6312 physical reads

0 redo size

3016 bytes sent via SQL Net to client

1373 bytes received via SQL Net from client

2 SQL Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

[ 튜닝포인트 ]

[TRAN_TYPE < '0' ] 조건에 의해 PK_CWKONCALL 인덱스를 사용하고 있는데, '0' 보다 큰 값은 전체를 의미하므로 결국 전체 테이블을 INDEX SCAN -> 테이블 ACCESS 하고 나머지 조건을 체크하게 되므로 TABLE FULL SCAN 보다 훨씬 비효율적이다. 따라서 FULL SCAN 을 타도록 HINT 를 사용한다.

[ 수정SQL ]

  SELECT /*+ FULL(TB_CWKONCALL) */
  TRAN_NO
  ...
  ,TO_CHAR (REQ_DT, 'YYYY/MM/DDHH24:MI:SS')
  ,TO_CHAR (ACCEPT_DT, 'YYYY/MM/DDHH24:MI:SS')
  ,TO_CHAR (HOPE_DT, 'YYYY/MM/DDHH24:MI:SS')
  ,TO_CHAR (CHG_DT, 'YYYY/MM/DDHH24:MI:SS')
  ,PAY_OFF_CODE
  ,PAY_OFF_NAME
  ,PAYER
  ,PAY_ADDR
  ,PAY_ZIP
  ,RENT_1
  ,RENT_2
  ,RENT_3
  ,RENT_4
  ,VERI_ST
  ,ERROR_CD
  ,REF_YN
  ,TO_CHAR (REF_DT, 'YYYY/MM/DDHH24:MI:SS')
  ,TO_CHAR (CHG_SCHE_DT, 'YYYY/MM/DDHH24:MI:SS')
  ,ACC_TELCODE
  ,REMK
  ,TO_CHAR (REG_DATE, 'YYYY/MM/DDHH24:MI:SS')
  FROM TB_CWKONCALL
  WHERE ((TRAN_TYPE < '0' AND PROCESS_FLAG IS NULL) AND TRAN_NO LIKE 'K%')

[ 실행 계획 ]

SELECT STATEMENT Optimizer Mode=RULE 
** TABLE ACCESS FULL TB_CWKONCALL**

Statistics

----------------------------------------------------------

38 recursive calls

3 db block gets

7681 consistent gets

2 physical reads

0 redo size

3016 bytes sent via SQLNet to client

1413 bytes received via SQLNet from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed


[ 튜닝 가이드라인 ]

인덱스 SCAN 을 통한 TABLE ACCESS 는 INDEX ACCESS + RANDOM TABLE ACCESS 를 수행하게 되므로 매우 비용이 많이드는 ACCESS PATH 이다.
따라서 조건에 의해서 INDEX SCAN 의 범위를 많이 줄일 수 있을 경우에 사용했을 때만 속도향상을 가져올 수 있고 그렇지 않을 경우에는 TABLE FULL SCAN 이 보다 효율적일 수도 있다.

profile
Back-end Developer

0개의 댓글