ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]
3. SQL 쿼리
3.1 튜닝전 일부 쿼리
FROM SDB_FM_INTG_TRXR_INFO A
, FM_COMM_DETL_CD B
WHERE B.COMM_CLSS_CD = '0104600'
AND A.INTG_TRXR_DIV = B.COMM_DETL_CD
AND (A.INTG_TRXR_CD LIKE 'B'|| '%'
OR A.INTG_TRXR_CD LIKE NVL((SELECT SUBSTR(MNG_ARTC_1,1,1)
FROM FM_COMM_DETL_CD X
,FM_ORG_MAST Y
WHERE X.COMM_DETL_CD = Y.LOCA_DIV
AND X.COMM_CLSS_CD = '0100600'
AND Y.ORG_CD = 'B106000390'
AND Y.ESTB_DIV = '2'
AND Y.SCHL_CLSS_DIV IS NOT NULL
AND Y.ORG_USE_YN = 'Y'),'B') || '%')
AND A.INTG_TRXR_DIV LIKE '06' || '%'
AND A.INTG_TRXR_CD LIKE '' || '%'
AND UPPER(A.INTG_TRXR_NM) LIKE '%' || UPPER('') || '%'
AND NVL(A.USE_YN, 'Y') LIKE 'Y' || '%'
) X ,
FM_GENE_TRXR_INFO Y ,
3.2 튜닝후 일부 쿼리
결합 인덱스 생성 : FM_INTG_TRXR_INFO_N02
SQL> CREATE INDEX FM_INTG_TRXR_INFO_N02 ON SDB_FM_INTG_TRXR_INFO
2 (INTG_TRXR_DIV, INTG_TRXR_CD)
3 LOGGING
4 TABLESPACE TS_FM_I_01
5 PCTFREE 10
6 INITRANS 2
7 MAXTRANS 255
8 STORAGE (
9 INITIAL 64K
10 MINEXTENTS 1
11 MAXEXTENTS 2147483645
12 PCTINCREASE 0
13 BUFFER_POOL DEFAULT
14 )
15 NOPARALLEL;
Index created.
4. 실행 PLAN
4.1 튜닝전
Rows Row Source Operation
------- ---------------------------------------------------
192 NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,142,120 us)
192 NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,141,541 us)
192 NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,140,575 us)
192 NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,139,803 us)
192 NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,138,837 us)
192 HASH JOIN OUTER (cr=33593 pr=33468 pw=0 time=5,135,546 us)
192 HASH JOIN (cr=33590 pr=33468 pw=0 time=2,062,612 us)
1 TABLE ACCESS BY INDEX ROWID FM_COMM_DETL_CD (cr=3 pr=0 pw=0 time=62 us)
1 INDEX RANGE SCAN FM_COMM_DETL_CD_PK (cr=2 pr=0 pw=0 time=49 us)(object id 78005)
192 TABLE ACCESS FULL SDB_FM_INTG_TRXR_INFO (cr=33587 pr=33468 pw=0 time=2,061,879 us)
4.2 튜닝 후
Rows Row Source Operation
------- ---------------------------------------------------
192 NESTED LOOPS OUTER (cr=407 pr=5 pw=0 time=8,452 us)
192 NESTED LOOPS OUTER (cr=407 pr=5 pw=0 time=7,491 us)
192 NESTED LOOPS OUTER (cr=407 pr=5 pw=0 time=6,911 us)
192 NESTED LOOPS OUTER (cr=208 pr=5 pw=0 time=4,978 us)
192 NESTED LOOPS OUTER (cr=208 pr=5 pw=0 time=4,019 us)
192 NESTED LOOPS (cr=208 pr=5 pw=0 time=3,056 us)
1 TABLE ACCESS BY INDEX ROWID FM_COMM_DETL_CD (cr=3 pr=0 pw=0 time=61 us)
1 INDEX RANGE SCAN FM_COMM_DETL_CD_PK (cr=2 pr=0 pw=0 time=45 us)(object id 78005)
192 TABLE ACCESS BY INDEX ROWID SDB_FM_INTG_TRXR_INFO (cr=205 pr=5 pw=0 time=2,806 us)
206 INDEX RANGE SCAN FM_INTG_TRXR_INFO_N02 (cr=11 pr=3 pw=0 time=1100 us)(object id 187319)
|