SQL 튜닝 사례

haisins

9년 ago

SQL Tuning CASE

[ CASE1: 적절한 인덱스의 사용 ]

<SQL>

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 테이블 인덱스 정보

PK_CCPIDSCNT	NODE_TYPE, NODE_ID, DC_PLAN_ID, START_DATE
TX_CCPIDSCNT_01	DC_PLAN_ID
TX_CCPIDSCNT_02	CUST_NO

1. 현재 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

<수정 Execution Plan>

UPDATE STATEMENT Optimizer Mode=RULE

UPDATE TB_CCPIDSCNT

INLIST ITERATOR CONCATENATED

TABLE ACCESS BY INDEX ROWID TB_CCPIDSCNT

INDEX RANGE SCAN PK_CCPIDSCNT

<튜닝 가이드라인>

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

[ CASE2: INDEX SCAN 을 통한 TABLE ACCESS 가 불리한 경우]

<SQL>

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

<튜닝포인트>

TB_CWKONCALL 테이블 인덱스정보

PK_CWKONCALL

TRAN_TYPE, NAT_ID, LINE_CHG_SEQ, TRAN_NO

1. [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%’)

<수정 Execution Plan>

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 SQL*Net to client

1413 bytes received via SQL*Net 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 이 보다 효율적일 수도 있음.

[ CASE3: 불필요한 ACCESS 가 일어나지 않도록 주의 ]

<SQL>

SELECT 1

FROM DUAL

WHERE EXISTS (

SELECT ‘X’

FROM (SELECT TABLE_ID

,WO_NO

,TO_CHAR (MAX (REG_DATE), ‘YYYYMMDDHH24MISS’)

REG_DATE

,ORD_NO

FROM VI_CWKOWO

WHERE (((WO_RSLT_FLAG = ‘Y’

AND WO_RSLT_CHK_MODE = ‘A’)

AND WO_RSLT_APPLY_DATE IS NULL)

AND TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’))

GROUP BY TABLE_ID

,WO_NO

,REG_DATE

,ORD_NO) A

,TB_CORDORDINFO B

,TB_CSYSORDTYPE C

WHERE ((B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)

AND C.SA_CD(+) = B.SA_CD)

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD))

SELECT STATEMENT Optimizer Mode=RULE

FILTER

TABLE ACCESS FULL DUAL

NESTED LOOPS OUTER

NESTED LOOPS

VIEW

SORT GROUP BY

VIEW VI_CWKOWO

UNION-ALL

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODELMONS

INDEX RANGE SCAN TX_CWKODELMONS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTSSA

INDEX RANGE SCAN TX_CWKOTSSA_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSN

INDEX RANGE SCAN TX_CWKOSN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080FP

INDEX RANGE SCAN TX_CWKO080FP_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080DN

INDEX RANGE SCAN TX_CWKO080DN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO700

INDEX RANGE SCAN TX_CWKO700_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODVPN

INDEX RANGE SCAN TX_CWKODVPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDSKE

INDEX RANGE SCAN TX_CWKOCARDSKE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDS

INDEX RANGE SCAN TX_CWKOCARDS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDC

INDEX RANGE SCAN TX_CWKOCARDC_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTHIRD

INDEX RANGE SCAN TX_CWKOTHIRD_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPV

INDEX RANGE SCAN TX_CWKOPV_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOVOT

INDEX RANGE SCAN TX_CWKOVOT_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPN

INDEX RANGE SCAN TX_CWKOPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPSDNS

INDEX RANGE SCAN TX_CWKOPSDNS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOLANS

INDEX RANGE SCAN TX_CWKOCOLANS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO060TS

INDEX RANGE SCAN TX_CWKO060TS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOATMS

INDEX RANGE SCAN TX_CWKOATMS_02

FILTER

TABLE ACCESS FULL TB_CWKOSNPLUS

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOHOMEPAGE

INDEX RANGE SCAN TX_CWKOHOMEPAGE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDAIN

INDEX RANGE SCAN TX_CWKOCARDAIN_01

FILTER

TABLE ACCESS FULL TB_CWKOSNMENU

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOENGCALL

INDEX RANGE SCAN TX_CWKOENGCALL_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOPCALL

INDEX RANGE SCAN TX_CWKOCOPCALL_01

TABLE ACCESS BY INDEX ROWID TB_CORDORDINFO

INDEX UNIQUE SCAN PK_CORDORDINFO

INDEX UNIQUE SCAN PK_CSYSORDTYPE

<튜닝포인트>

테이블 인덱스 정보

1. INLINE VIEW 를 사용하고 맨 바깥쪽에서 FROM DUAL WHERE EXISTS 문장을 사용하고 있는데 ROWNUM = 1 조건을 이용하면 DUAL 테이블은 사용할 필요 없음

<수정SQL>

SELECT 1

FROM (SELECT TABLE_ID

,WO_NO

,TO_CHAR (MAX (REG_DATE), ‘YYYYMMDDHH24MISS’) REG_DATE

,ORD_NO

FROM VI_CWKOWO

WHERE (((WO_RSLT_FLAG = ‘Y’

AND WO_RSLT_CHK_MODE = ‘A’)

AND WO_RSLT_APPLY_DATE IS NULL)

AND TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’))

GROUP BY TABLE_ID

,WO_NO

,REG_DATE

,ORD_NO) A

,TB_CORDORDINFO B

,TB_CSYSORDTYPE C

WHERE ((B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)

AND C.SA_CD(+) = B.SA_CD)

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD)

AND ROWNUM = 1

<수정 Execution Plan>

SELECT STATEMENT Optimizer Mode=RULE

COUNT STOPKEY

NESTED LOOPS OUTER

NESTED LOOPS

VIEW

SORT GROUP BY

VIEW VI_CWKOWO

UNION-ALL

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODELMONS

INDEX RANGE SCAN TX_CWKODELMONS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTSSA

INDEX RANGE SCAN TX_CWKOTSSA_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSN

INDEX RANGE SCAN TX_CWKOSN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080FP

INDEX RANGE SCAN TX_CWKO080FP_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080DN

INDEX RANGE SCAN TX_CWKO080DN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO700

INDEX RANGE SCAN TX_CWKO700_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODVPN

INDEX RANGE SCAN TX_CWKODVPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDSKE

INDEX RANGE SCAN TX_CWKOCARDSKE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDS

INDEX RANGE SCAN TX_CWKOCARDS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDC

INDEX RANGE SCAN TX_CWKOCARDC_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTHIRD

INDEX RANGE SCAN TX_CWKOTHIRD_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPV

INDEX RANGE SCAN TX_CWKOPV_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOVOT

INDEX RANGE SCAN TX_CWKOVOT_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPN

INDEX RANGE SCAN TX_CWKOPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPSDNS

INDEX RANGE SCAN TX_CWKOPSDNS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOLANS

INDEX RANGE SCAN TX_CWKOCOLANS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO060TS

INDEX RANGE SCAN TX_CWKO060TS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOATMS

INDEX RANGE SCAN TX_CWKOATMS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSNPLUS

INDEX RANGE SCAN TX_CWKOSNPLUS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOHOMEPAGE

INDEX RANGE SCAN TX_CWKOHOMEPAGE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDAIN

INDEX RANGE SCAN TX_CWKOCARDAIN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSNMENU

INDEX RANGE SCAN TX_CWKOSNMENU_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOENGCALL

INDEX RANGE SCAN TX_CWKOENGCALL_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOPCALL

INDEX RANGE SCAN TX_CWKOCOPCALL_01

TABLE ACCESS BY INDEX ROWID TB_CORDORDINFO

INDEX UNIQUE SCAN PK_CORDORDINFO

INDEX UNIQUE SCAN PK_CSYSORDTYPE

<튜닝 가이드라인>

불필요한 TABLE ACCESS 는 가능하면 일어나지 않도록 주의해야 한다

[ CASE4: 불필요한 작업이 일어나지 않도록 주의 ]

<SQL>

SELECT 1

FROM (SELECT TABLE_ID

,WO_NO

,TO_CHAR (MAX (REG_DATE), ‘YYYYMMDDHH24MISS’) REG_DATE

,ORD_NO

FROM VI_CWKOWO

WHERE (((WO_RSLT_FLAG = ‘Y’

AND WO_RSLT_CHK_MODE = ‘A’)

AND WO_RSLT_APPLY_DATE IS NULL)

AND TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’))

GROUP BY TABLE_ID

,WO_NO

,REG_DATE

,ORD_NO) A

,TB_CORDORDINFO B

,TB_CSYSORDTYPE C

WHERE ((B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)

AND C.SA_CD(+) = B.SA_CD)

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD)

AND ROWNUM = 1

SELECT STATEMENT Optimizer Mode=RULE

COUNT STOPKEY

NESTED LOOPS OUTER

NESTED LOOPS

VIEW

SORT GROUP BY

VIEW VI_CWKOWO

UNION-ALL

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODELMONS

INDEX RANGE SCAN TX_CWKODELMONS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTSSA

INDEX RANGE SCAN TX_CWKOTSSA_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSN

INDEX RANGE SCAN TX_CWKOSN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080FP

INDEX RANGE SCAN TX_CWKO080FP_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080DN

INDEX RANGE SCAN TX_CWKO080DN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO700

INDEX RANGE SCAN TX_CWKO700_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODVPN

INDEX RANGE SCAN TX_CWKODVPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDSKE

INDEX RANGE SCAN TX_CWKOCARDSKE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDS

INDEX RANGE SCAN TX_CWKOCARDS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDC

INDEX RANGE SCAN TX_CWKOCARDC_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTHIRD

INDEX RANGE SCAN TX_CWKOTHIRD_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPV

INDEX RANGE SCAN TX_CWKOPV_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOVOT

INDEX RANGE SCAN TX_CWKOVOT_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPN

INDEX RANGE SCAN TX_CWKOPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPSDNS

INDEX RANGE SCAN TX_CWKOPSDNS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOLANS

INDEX RANGE SCAN TX_CWKOCOLANS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO060TS

INDEX RANGE SCAN TX_CWKO060TS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOATMS

INDEX RANGE SCAN TX_CWKOATMS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSNPLUS

INDEX RANGE SCAN TX_CWKOSNPLUS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOHOMEPAGE

INDEX RANGE SCAN TX_CWKOHOMEPAGE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDAIN

INDEX RANGE SCAN TX_CWKOCARDAIN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSNMENU

INDEX RANGE SCAN TX_CWKOSNMENU_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOENGCALL

INDEX RANGE SCAN TX_CWKOENGCALL_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOPCALL

INDEX RANGE SCAN TX_CWKOCOPCALL_01

TABLE ACCESS BY INDEX ROWID TB_CORDORDINFO

INDEX UNIQUE SCAN PK_CORDORDINFO

INDEX UNIQUE SCAN PK_CSYSORDTYPE

<튜닝포인트>

1. 이 SQL 문장은 특정 조건에 해당하는 데이타가 존재하는지 체크하기 위한 것이므로 GROUP BY 절이 필요없음

<수정SQL>

SELECT 1

FROM VI_CWKOWO A

,TB_CORDORDINFO B

,TB_CSYSORDTYPE C

WHERE A.WO_RSLT_FLAG = ‘Y’

AND A.WO_RSLT_CHK_MODE = ‘A’

AND A.WO_RSLT_APPLY_DATE IS NULL

AND A.TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’)

AND B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)

AND C.SA_CD(+) = B.SA_CD

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD

AND ROWNUM = 1

<수정 Execution Plan>

SELECT STATEMENT Optimizer Mode=RULE

COUNT STOPKEY

NESTED LOOPS OUTER

NESTED LOOPS

VIEW VI_CWKOWO

UNION-ALL

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODELMONS

INDEX RANGE SCAN TX_CWKODELMONS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTSSA

INDEX RANGE SCAN TX_CWKOTSSA_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSN

INDEX RANGE SCAN TX_CWKOSN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080FP

INDEX RANGE SCAN TX_CWKO080FP_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO080DN

INDEX RANGE SCAN TX_CWKO080DN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO700

INDEX RANGE SCAN TX_CWKO700_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKODVPN

INDEX RANGE SCAN TX_CWKODVPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDSKE

INDEX RANGE SCAN TX_CWKOCARDSKE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDS

INDEX RANGE SCAN TX_CWKOCARDS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDC

INDEX RANGE SCAN TX_CWKOCARDC_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOTHIRD

INDEX RANGE SCAN TX_CWKOTHIRD_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPV

INDEX RANGE SCAN TX_CWKOPV_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOVOT

INDEX RANGE SCAN TX_CWKOVOT_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPN

INDEX RANGE SCAN TX_CWKOPN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOPSDNS

INDEX RANGE SCAN TX_CWKOPSDNS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOLANS

INDEX RANGE SCAN TX_CWKOCOLANS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKO060TS

INDEX RANGE SCAN TX_CWKO060TS_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOATMS

INDEX RANGE SCAN TX_CWKOATMS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSNPLUS

INDEX RANGE SCAN TX_CWKOSNPLUS_02

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOHOMEPAGE

INDEX RANGE SCAN TX_CWKOHOMEPAGE_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCARDAIN

INDEX RANGE SCAN TX_CWKOCARDAIN_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOSNMENU

INDEX RANGE SCAN TX_CWKOSNMENU_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOENGCALL

INDEX RANGE SCAN TX_CWKOENGCALL_01

FILTER

TABLE ACCESS BY INDEX ROWID TB_CWKOCOPCALL

INDEX RANGE SCAN TX_CWKOCOPCALL_01

TABLE ACCESS BY INDEX ROWID TB_CORDORDINFO

INDEX UNIQUE SCAN PK_CORDORDINFO

INDEX UNIQUE SCAN PK_CSYSORDTYPE

<튜닝 가이드라인>

이 경우에는 불필요한 GROUP BY 절에 의해 내부적으로 SORT 작업이 발생하게 되어 부분범위처리가 가능함에도 불구하고 부분범위처리가 불가능하게 되고, 데이터 처리량도 증가하게 되었음. 같은 결과를 얻을 수 있다면 불필요한 SORT 작업등이 일어나지 않도록 주의하고 컬럼 데이타에 따라서 사용하지 않아도 되는 함수(예: NVL) 등은 사용하지 않도록 한다

[ CASE5: 드라이빙 테이블 선정 / 동일데이타의 반복 ACCESS 회피 ]

<SQL>

SELECT LIST_FLAG

,LIST_CNT

FROM (SELECT /*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*/

‘1’ LIST_FLAG

,COUNT (DISTINCT B.SA_ID) LIST_CNT

FROM TB_CCPI115A A

,TB_CCPI115 B

WHERE ((((A.BUSI_CD = :b2

AND A.TOP_SA_ID = B.SA_ID)

AND A.START_ORD_NO = B.START_ORD_NO)

AND B.STATUS_CD IN

(’02’, ’03’, ’04’, ’06’, ’07’, ’08’, ’09’, ’13’, ’15’))

AND B.DELIVER_HOPE_DATE < TO_CHAR (SYSDATE, ‘YYYYMMDD’))

UNION ALL

SELECT /*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*/

‘2’ LIST_FLAG

,COUNT (DISTINCT B.SA_ID) LIST_CNT

FROM TB_CCPI115A A

,TB_CCPI115 B

WHERE ((((A.BUSI_CD = :b2

AND A.TOP_SA_ID = B.SA_ID)

AND A.START_ORD_NO = B.START_ORD_NO)

AND B.STATUS_CD NOT IN (’01’, ’12’))

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’))

UNION ALL

SELECT /*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*/

‘3’ LIST_FLAG

,COUNT (DISTINCT B.SA_ID) LIST_CNT

FROM TB_CCPI115A A

,TB_CCPI115 B

WHERE ((((A.BUSI_CD = :b2

AND A.TOP_SA_ID = B.SA_ID)

AND A.START_ORD_NO = B.START_ORD_NO)

AND B.STATUS_CD IN (’10’, ’11’, ’16’))

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’))

UNION ALL

SELECT /*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*/

‘4’ LIST_FLAG

,COUNT (DISTINCT B.SA_ID) LIST_CNT

FROM TB_CCPI115A A

,TB_CCPI115 B

WHERE ((((A.BUSI_CD = :b2

AND A.TOP_SA_ID = B.SA_ID)

AND A.START_ORD_NO = B.START_ORD_NO)

AND B.STATUS_CD IN

(’02’, ’03’, ’04’, ’06’, ’07’, ’08’, ’09’, ’13’, ’15’))

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’)))

SELECT STATEMENT Optimizer Mode=RULE 4 15020

UNION-ALL

SORT GROUP BY 1 28 K

NESTED LOOPS 556 28 K 3755

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115A 750 14 K 5 1 3

INDEX RANGE SCAN TX_CCPI115A_02 750 4 1 3

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115 7 K 231 K 5 1 3

INDEX RANGE SCAN PK_CCPI115 7 K 4 1 3

SORT GROUP BY 1 1 K

NESTED LOOPS 28 1 K 3755

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115A 750 14 K 5 1 3

INDEX RANGE SCAN TX_CCPI115A_02 750 4 1 3

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115 371 11 K 5 1 3

INDEX RANGE SCAN PK_CCPI115 371 4 1 3

SORT GROUP BY 1 5 K

NESTED LOOPS 111 5 K 3755

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115A 750 14 K 5 1 3

INDEX RANGE SCAN TX_CCPI115A_02 750 4 1 3

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115 1 K 46 K 5 1 3

INDEX RANGE SCAN PK_CCPI115 1 K 4 1 3

SORT GROUP BY 1 5 K

NESTED LOOPS 111 5 K 3755

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115A 750 14 K 5 1 3

INDEX RANGE SCAN TX_CCPI115A_02 750 4 1 3

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115 1 K 46 K 5 1 3

INDEX RANGE SCAN PK_CCPI115 1 K 4 1 3

Statistics

———————————————————-

80 recursive calls

0 db block gets

253749 consistent gets

2407 physical reads

0 redo size

336 bytes sent via SQL*Net to client

2308 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

4 rows processed

<튜닝포인트>

TB_CCPI115 테이블 인덱스정보

PK_CCPI115	SA_ID, START_ORD_NO, START_DATE
TX_CCPI115_04	CHAKSIN_OFC_CD, SA_ID
TX_CCPI115_05	RECEIPT_CD, STATUS_CD
TX_CCPI115_06	PRINT_RESV_DATE, START_ORD_NO, STATUS_CD
TX_CCPI115_07	CHAKSIN_OFC_CD, EQUIP_ID, PRINT_RESV_DATE, PRINT_SEQ
TX_CCPI115_08	CHAKSIN_OFC_CD, DELIVER_HOPE_DATE, START_ORD_NO
TX_CCPI115_09	START_DATE, RECEIPT_CD
TX_CCPI115_10	END_DATE
TX_CCPI115_11	DELIVER_HOPE_DATE
TX_CCPI115_12	FIRST_ORD_NO
TX_CCPI115_13	STATUS_CD, CHAKSIN_OFC_CD, PRINT_FLAG, PRINT_RESV_DATE
TX_CCPI115_14	START_ORD_NO, START_DATE, STATUS_CD
TX_CCPI115_16	REQER_WEB_ID, START_DATE

TB_CCPI115A 테이블 인덱스정보

PK_CCPI115A	SA_ID, START_ORD_NO, START_DATE
TX_CCPI115A_01	TOP_SA_ID
TX_CCPI115A_02	BUSI_CD

1. LIST_FLAG 값이 ‘2’, ‘3’, ‘4’ 인 경우 TB_CCPI115A 테이블의 TX_CCPI115A_02 인덱스를 사용하고 있으나, 이 경우에는 인덱스 효율이 떨어짐

DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’) 조건에서 실제 처리하는 날짜가 하루치 이므로 TB_CCPI115 테이블을 드라이빙 테이블로 하고

TX_CCPI115_11(TX_CCPI115_11) 인덱스를 이용하도록 한다

2. LIST_FLAG 값이 ‘2’, ‘3’, ‘4’ 인 경우 같은데이타를 세번 읽어서 UNION ALL 처리를 하고 있음

CARTESIAN PRODUCT 를 이용하여 데이터를 한번 읽고 난후 LIST_FLAG 값이 ‘2’, ‘3’, ‘4’ 인 경우에 따라 결과를 다르게 처리하여 BLOCK ACCESS 횟수를

많이 줄일 수 있음

실제 데이터 조회시 데이터 처리량에서 3배 이상의 개선이 나타나고 있음

<수정SQL>

SELECT /*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*/

‘1’ LIST_FLAG

,COUNT (DISTINCT B.SA_ID) LIST_CNT

FROM TB_CCPI115A A

,TB_CCPI115 B

WHERE ((((A.BUSI_CD = ‘C0078’

AND A.TOP_SA_ID = B.SA_ID)

AND A.START_ORD_NO = B.START_ORD_NO)

AND B.STATUS_CD IN

(’02’, ’03’, ’04’, ’06’, ’07’, ’08’, ’09’, ’13’, ’15’))

AND B.DELIVER_HOPE_DATE < TO_CHAR (SYSDATE, ‘YYYYMMDD’))

UNION ALL

SELECT /*+ LEADING(B) USE_HASH(B A) INDEX(A TX_CCPI115A_02)*/

C.LIST_FLAG

,COUNT(DISTINCT DECODE(C.LIST_FLAG,

‘2’, DECODE(SIGN(INSTR(’01’,B.STATUS_CD)) +

SIGN(INSTR(’12’,B.STATUS_CD)), 0, B.SA_ID),

‘3’, DECODE(SIGN(INSTR(’10’,B.STATUS_CD)) +

SIGN(INSTR(’11’,B.STATUS_CD)) +

SIGN(INSTR(’16’,B.STATUS_CD)), 1, B.SA_ID),

‘4’, DECODE(SIGN(INSTR(’02’,B.STATUS_CD)) +

SIGN(INSTR(’03’,B.STATUS_CD)) +

SIGN(INSTR(’04’,B.STATUS_CD)) +

SIGN(INSTR(’06’,B.STATUS_CD)) +

SIGN(INSTR(’07’,B.STATUS_CD)) +

SIGN(INSTR(’08’,B.STATUS_CD)) +

SIGN(INSTR(’09’,B.STATUS_CD)) +

SIGN(INSTR(’13’,B.STATUS_CD)) +

SIGN(INSTR(’15’,B.STATUS_CD)), 1, B.SA_ID)

,0) ) LIST_CNT

FROM TB_CCPI115A A

,TB_CCPI115 B

,(SELECT ‘2’ LIST_FLAG FROM DUAL

UNION ALL

SELECT ‘3’ LIST_FLAG FROM DUAL

UNION ALL

SELECT ‘4’ LIST_FLAG FROM DUAL

) C

WHERE A.BUSI_CD = :b2

AND A.TOP_SA_ID = B.SA_ID

AND A.START_ORD_NO = B.START_ORD_NO

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’)

GROUP BY C.LIST_FLAG

<수정 Execution Plan>

SELECT STATEMENT Optimizer Mode=RULE 67 M 9509331

UNION-ALL

SORT GROUP BY 1 28 K

NESTED LOOPS 559 28 K 3775

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115A 754 14 K 5 1 3

INDEX RANGE SCAN TX_CCPI115A_02 754 4 1 3

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115 7 K 231 K 5 1 3

INDEX RANGE SCAN PK_CCPI115 7 K 4 1 3

SORT GROUP BY 67 M 6G 9505556

HASH JOIN 67 M 6G 5120

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115 148 K 12 M 5 1 3

INDEX RANGE SCAN TX_CCPI115_11 148 K 4 1 3

MERGE JOIN CARTESIAN 4 M 112 M 2267

PARTITION CONCATENATED 1 3

TABLE ACCESS BY LOCAL INDEX ROWID TB_CCPI115A 754 14 K 5 1 3

INDEX RANGE SCAN TX_CCPI115A_02 754 4 1 3

SORT JOIN 6 K 35 K 2262

VIEW 6 K 35 K

UNION-ALL

TABLE ACCESS FULL DUAL 2 K 1

Statistics

———————————————————-

24 recursive calls

9 db block gets

77334 consistent gets

1668 physical reads

0 redo size

336 bytes sent via SQL*Net to client

2299 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

4 rows processed

<튜닝 가이드라인 1>

FROM 절에 두개 이상의 테이블이 사용될 경우, HASH JOIN 이나 SORT MERGE JOIN 이 아닌 NESTED LOOP 조인인 경우에는 사용된 조건으로 보아 처리건수가 가장 적은 테이블이 가장 DRIVING 테이블로 사용되어야 이후의 JOIN 작업의 횟수를 줄일 수 있으므로 가장 효율적임

<튜닝 가이드라인 2>

같은 데이터를 두번이상 ACCESS 하는 것은 DISK I/O 또는 Memory I/O 를 발생하게 되므로 불가피한 경우가 아니면 최대한 피해야 함, 한번의 데이터 ACCESS 를 통하여 필요한 데이터를 추출할 수 있도록 SQL 작성에 주의를 기울여야 함

[ CASE6: CONCATENATION 의 적절한 사용 ]

<SQL>

SELECT COUNT (*)

FROM TB_CCPI115

WHERE (RECEIPT_CD IN (’05’, ’06’)

AND STATUS_CD = ’01’)

SELECT STATEMENT Optimizer Mode=RULE

SORT AGGREGATE

PARTITION CONCATENATED

INDEX FAST FULL SCAN TX_CCPI115_05

Statistics

———————————————————-

0 recursive calls

4 db block gets

25940 consistent gets

25871 physical reads

200 redo size

229 bytes sent via SQL*Net to client

120 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

<튜닝포인트>

TB_CCPI115 테이블 인덱스정보

PK_CCPI115	SA_ID, START_ORD_NO, START_DATE
TX_CCPI115_04	CHAKSIN_OFC_CD, SA_ID
TX_CCPI115_05	RECEIPT_CD, STATUS_CD
TX_CCPI115_06	PRINT_RESV_DATE, START_ORD_NO, STATUS_CD
TX_CCPI115_07	CHAKSIN_OFC_CD, EQUIP_ID, PRINT_RESV_DATE, PRINT_SEQ
TX_CCPI115_08	CHAKSIN_OFC_CD, DELIVER_HOPE_DATE, START_ORD_NO
TX_CCPI115_09	START_DATE, RECEIPT_CD
TX_CCPI115_10	END_DATE
TX_CCPI115_11	DELIVER_HOPE_DATE
TX_CCPI115_12	FIRST_ORD_NO
TX_CCPI115_13	STATUS_CD, CHAKSIN_OFC_CD, PRINT_FLAG, PRINT_RESV_DATE
TX_CCPI115_14	START_ORD_NO, START_DATE, STATUS_CD
TX_CCPI115_16	REQER_WEB_ID, START_DATE

1. TX_CCPI115_05 인덱스를 FAST FULL SCAN 으로 읽어서 조건에 맞는 건수만 구하고 있는데, 실제로 테이블 총건수는 2백만건 이상이고, 조건에 맞는 건수는 94 건에 불과하므로 INDEX FULL SCAN 은 매우 비효율적임

<수정SQL>

SELECT /*+ INDEX(TB_CCPI115 TX_CCPI115_05) USE_CONCAT */

COUNT (*)

FROM TB_CCPI115

WHERE (RECEIPT_CD IN (’05’, ’06’)

AND STATUS_CD = ’01’)

SELECT STATEMENT Optimizer Mode=RULE

SORT AGGREGATE

CONCATENATION

PARTITION CONCATENATED

INDEX RANGE SCAN TX_CCPI115_05

PARTITION CONCATENATED

INDEX RANGE SCAN TX_CCPI115_05

Statistics

———————————————————-

56 recursive calls

0 db block gets

144 consistent gets

1 physical reads

0 redo size

230 bytes sent via SQL*Net to client

382 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

<튜닝 가이드라인>

인덱스 컬럼중 하나를 IN 연산자나 OR 연산자를 사용하는 조건이 있을 경우에는 CONCATENATION 이 사용되고 있는지를 체크하고, 사용되지 않고 있을 경우에는 USE_CONCAT 힌트를 사용하여 CONCATENATION 이 사용될 수 있도록 유도한다

[ CASE7: 조회조건별 UNION ALL 의 적절한 사용 ]

<SQL>

SELECT /*+ INDEX (A PK_MSYSOFC) (B TX_CFACPDA_01) +*/

A.TEL_OFC_NAME

,B.MNG_OFC_CD

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0))

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0))

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0))

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0))

,COUNT (B.MNG_OFC_CD)

FROM TB_MSYSOFC A

,TB_CFACPDA B

WHERE ((:b0 = ‘1’

AND A.TEL_OFC_CD = :b1)

OR (:b0 = ‘0’

AND DECODE (:b1, ‘200375’, A.TEL_OFC_CD, A.HQ_OFC_CD) = :b1

AND A.TEL_OFC_TYPE_CD IN

(’02’, ’03’, ’07’, ’11’, ’12’, ‘M2’, ‘M3’)))

AND B.MNG_OFC_CD = A.TEL_OFC_CD(+)

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD) IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)

GROUP BY A.TEL_OFC_NAME

,B.MNG_OFC_CD

SELECT STATEMENT Optimizer Mode=RULE 1 K 133

SORT GROUP BY 1 K 157 K 133

FILTER

NESTED LOOPS OUTER

INDEX FAST FULL SCAN TX_CFACPDA_01 79 2 K 2

TABLE ACCESS BY INDEX ROWID TB_MSYSOFC 2 K 130 K 1

INDEX UNIQUE SCAN PK_MSYSOFC 2 K

<튜닝포인트>

TB_CFACPDA 테이블 인덱스정보

PK_CFACPDA	RCV_DATE, SERIAL_NO
TX_CFACPDA_01	MNG_OFC_CD, PDA_STATUS_CD, PDA_QUALITY_CD
TX_CFACPDA_02	PDA_STATUS_CD, PDA_QUALITY_CD
TX_CFACPDA_03	SERIAL_NO
TX_CFACPDA_04	MNG_OFC_CD, RCV_DATE, SERIAL_NO
TX_CFACPDA_05	MNG_OFC_CD, MNG_CHNG_DATE, SERIAL_NO
TX_CFACPDA_06	MNG_OFC_CD, MODEL_CD, PDA_STATUS_CD, PDA_QUALITY_CD, MNG_CHNG_FLAG, RCV_DATE, SERIAL_NO

1. TX_CFACPDA_01 인덱스를 FAST FULL SCAN 하고 있으나 조건으로 보아 :b0 값과 :b1 값에 따라 각각 다른 인덱스를 사용하는 SQL 을 작성하여

UNION ALL 처리하여 경우에 따라 유리한 인덱스를 사용하도록 하는 것이 유리함

– :b0 = ‘1’ 인 경우에는 B.MNG_OFC_CD =
:b1 조건을 사용하여 TX_CFACPDA_01 인덱스를 사용하는 것이 좋음

– :b0 = ‘0’ 이면서 :b1 = ‘200375’
인
경우에는 TB_MSYSOFC 를 먼저 UNIQUE SCAN 하여 가져온 값으로 TX_CFACPDA_01 인덱스를 사용하는 것이 좋음

– :b0 = ‘0’ 이면서 :b1 = ‘200375’
이
아닌 경우에는 TB_MSYSOFC 를 먼저 FULL SCAN 하여 TB_CFACPDA 테이블과 조인하는 것이 좋음

각각의 경우에 해당하는 SQL 로 분리하여 UNION ALL 로 처리한다

<수정SQL>

SELECT /*+ ORDERED USE_NL(X Y) */

X.MNG_OFC_CD

,Y.TEL_OFC_NAME

,X.PG_SUM

,X.PB_SUM

,X.CG_SUM

,X.SG_SUM

,X.CNT

FROM (SELECT B.MNG_OFC_CD

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0)) PG_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0)) PB_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0)) CG_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0)) SG_SUM

,COUNT(B.MNG_OFC_CD) CNT

FROM TB_CFACPDA B

WHERE :b0 = ‘1’

AND B.MNG_OFC_CD = :b1

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD)IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)

GROUP BY B.MNG_OFC_CD

UNION ALL

SELECT B.MNG_OFC_CD

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0)) PG_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0)) PB_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0)) CG_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0)) SG_SUM

,COUNT(B.MNG_OFC_CD) CNT

FROM TB_CFACPDA B

WHERE :b0 = ‘0’

AND :b1 = ‘200375’

AND B.MNG_OFC_CD = (SELECT A.TEL_OFC_CD FROM TB_MSYSOFC A WHERE A.TEL_OFC_CD = :b1)

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD)IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)

GROUP BY B.MNG_OFC_CD

UNION ALL

SELECT /*+ ORDERED INDEX(B TB_CFACPDA_01) */

B.MNG_OFC_CD

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0)) PG_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0)) PB_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0)) CG_SUM

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0)) SG_SUM

,COUNT(B.MNG_OFC_CD) CNT

FROM (SELECT A.TEL_OFC_CD FROM TB_MSYSOFC A WHERE A.HQ_OFC_CD = :b1) A,

TB_CFACPDA B

WHERE :b0 = ‘0’

AND :b1 <> ‘200375’

AND B.MNG_OFC_CD(+) = A.TEL_OFC_CD

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD)IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)

GROUP BY B.MNG_OFC_CD

) X

,TB_MSYSOFC Y

WHERE Y.TEL_OFC_CD = X.MNG_OFC_CD

<수정 Execution Plan>

SELECT STATEMENT Optimizer Mode=RULE 8 K 457

NESTED LOOPS 8 K 800 K 457

VIEW 402 29 K 55

UNION-ALL

SORT GROUP BY 1 35 2

FILTER

INDEX RANGE SCAN TX_CFACPDA_01 1 35 2

SORT GROUP BY 1 35 2

FILTER

INDEX RANGE SCAN TX_CFACPDA_01 1 35 2

INDEX UNIQUE SCAN PK_MSYSOFC 1 10 2

SORT GROUP BY 400 19 K 51

FILTER

NESTED LOOPS OUTER

TABLE ACCESS FULL TB_MSYSOFC 20 200 3

INDEX RANGE SCAN TX_CFACPDA_01 2 K 78 K 2

TABLE ACCESS BY INDEX ROWID TB_MSYSOFC 2 K 52 K 1

INDEX UNIQUE SCAN PK_MSYSOFC 2 K 2

<튜닝 가이드라인>

조회조건이 경우에 따라 달라질 경우에는 각각의 경우에 최적의 인덱스를 사용할 수 있도록 SQL 을 분리하여 각각을 최적화한 다음 UNION ALL 로 묶어서 처리한다

[ CASE8: HASH JOIN 의 사용 ]

<SQL>

SELECT DISTINCT A.NODE_OFC_CD

,LPAD (B.A_NO, 4, ‘0’)

FROM TB_CFACNODEOFC A

,TB_MSYSOFC B

WHERE (A.NODE_OFC_CD = B.TEL_OFC_CD

AND A.SA_CD = :b0)

SELECT STATEMENT Optimizer Mode=RULE

SORT UNIQUE

NESTED LOOPS

TABLE ACCESS BY INDEX ROWID TB_CFACNODEOFC

INDEX RANGE SCAN PK_CFACNODEOFC

TABLE ACCESS BY INDEX ROWID TB_MSYSOFC

INDEX UNIQUE SCAN PK_MSYSOFC

Statistics

———————————————————-

0 recursive calls

0 db block gets

810 consistent gets

6 physical reads

0 redo size

2825 bytes sent via SQL*Net to client

985 bytes received via SQL*Net from client

9 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

98 rows processed

<튜닝포인트>

1. 현재 TB_CFACNODEOFC 테이블을 INDEX 를 통해 읽은 다음 TB_MSYSOFC 테이블과 NESTED LOOP 조인이 일어나고 있는데 TB_CFACNODEOFC 테이블에서 A.SA_CD = ‘0577’ 만족하여 NESTED LOOP 조인이 수행되는 횟수는 아래의 데이터 자료를 볼 때 평균적으로 400 회 정도이므로 NESTED LOOP 조인의 부담이 많은 상태임

select SA_CD, count(*)

from TB_CFACNODEOFC

group by SA_CD

SA_CD COUNT(*)

——————

0511 434

0513 394

0577 269

0821 423

0825 11

2186 410

2189 410

C600 425

CB00 112

따라서 TB_MSYSOFC 테이블은 건수가 매우 작은 테이블이므로 이 테이블을 먼저 FULL SCAN 한 다음 HASH JOIN 으로 풀리는 것이 NESTED LOOP 에 의한 많은 TABLE ACCESS 를 줄일 수 있으므로 FROM 절 순서를 바꾸고 HINT 를 사용하여 SQL 을 수정한다

<수정SQL>

SELECT /*+ ORDERED USE_HASH(B A) */

DISTINCT A.NODE_OFC_CD

,LPAD (B.A_NO, 4, ‘0’)

FROM TB_MSYSOFC B

,TB_CFACNODEOFC A

WHERE (A.NODE_OFC_CD = B.TEL_OFC_CD

AND A.SA_CD = :b0)

<수정후 Execution Plan>

SELECT STATEMENT Optimizer Mode=RULE

SORT UNIQUE

HASH JOIN

TABLE ACCESS FULL

TABLE ACCESS BY INDEX ROWID TB_CFACNODEOFC

INDEX RANGE SCAN PK_CFACNODEOFC

Statistics

———————————————————-

0 recursive calls

3 db block gets

317 consistent gets

6 physical reads

0 redo size

2825 bytes sent via SQL*Net to client

1021 bytes received via SQL*Net from client

9 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

98 rows processed

<튜닝 가이드라인>

NESTED LOOP 조인에서 드라이빙 테이블로 적합한 처리범위를 많이 줄여줄 수 있는 조건이 없을 경우에는 HASH JOIN 을 사용할 것을 고려해보는데, HASH JOIN 은 항상 적은 건수의 테이블이 먼저 풀리도록 하여야 PGA 영역의 HASH 메모리를 적게 사용하고 HASH JOIN 시 찾을 데이터 건수가 적어지므로 작은 테이블을 먼저 읽을 수 있도록 주의한다.

<참고자료>

– An Introduction to Database Systems, Addison Wesley

– Oracle Performance Tuning, O’reilly

– Effective Oracle By Design, McGraw Hill

– Oracle Expert One-On-One, Apress

– Oracle Concepts Manual, Oracle

– Oracle Administrator’s Guide Manual, Oracle

– Oracle SQL Reference Manual, Oracle

– Oracle Tuning Manual, Oracle

– Oracle SQL*Plus User’s Guide and Reference, Oracle

– Oracle Internal, Oracle Education Center