오라클 성능 고도화 원리와 해법 II (2010년)
I. 조인순서의 중요성
1. 조인순서에 따라 쿼리 수행 성능이 차이가 날 수 있음
- 기본적으로 작은 건수의 테이블을 드라이빙하여 조인을 하는 것이 유리하며 이는 모든 튜닝방법의 정석임
- 즉, A와 B 테이블이 조건없이 조인만 되어 있을 경우 작은테이블을 드라이빙 하는것이 유리하고
필터 조건이 큰 테이블에 있는 경우 NDV에 따라 큰 테이블을 드라이빙 하는 것이 유리하다. - 그러므로 여러 테이블이 조인되어 있고 들어오는 조건도 많을 경우 조건에 따라 전략적인 조인순서를 만들어야 하고
이를 위해 동적 힌트를 사용해서 튜닝을 진행할 수 있다.
II. 필터 조건이 없을 때
1. 샘플 데이터 생성
-- 1. 오라클 버전
SELECT * FROM v$version;
BANNER
---------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
-- 2. create table
DROP TABLE EMP PURGE;
DROP TABLE DEPT PURGE;
CREATE TABLE EMP AS
SELECT -- 1. EMPNO(Unique 컬럼)
LEVEL EMPNO,
-- 2. EMPNO(VARCHAR로 변형)
TO_CHAR(LEVEL) EMPNO_VARCHAR,
-- 3. JOB(10만건 데이터를 20개 그룹으로 분류)
CHR(65 + CEIL(LEVEL / 500000) - 1) JOB,
-- 4. HIREDATE(10개씩 증가하여 날짜 데이터 만듬)
TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 HIREDATE,
-- 5. DEPTNO(자리수 길이를 한 그룹으로 하여 데이터 생성, 분포도 차이나도록)
LENGTH(LEVEL) * 10 DEPTNO
FROM DUAL
CONNECT BY LEVEL <= 100000
;
CREATE TABLE DEPT AS
SELECT LEVEL * 10 DEPTNO,
'SALES_' || LEVEL DNAME,
'ZONE_' || LEVEL LOC
FROM DUAL
CONNECT BY LEVEL <= 9
;
-- 3. create index
CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_U1 NOPARALLEL;
CREATE INDEX EMP_N1 ON EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N1 NOPARALLEL;
CREATE INDEX EMP_N2 ON EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N2 NOPARALLEL;
CREATE INDEX EMP_N3 ON EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;
ALTER INDEX EMP_N3 NOPARALLEL;
CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO);
-- 4. gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);
-- 5. View EMP Table Statistics
*************[ Table Info ]**********************************************************************************************************************************************************
COLUMN_NAME DATA_TYPE DATA_LEN Null DISTINCT_KEY DENSITY NUM_NULLS NUM_BUCKETS SAMPLE_SIZE LAST Analyzed HISTOGRAM Default Value
------------------------------ ---------- -------- ---- -------------- ---------- -------------- ----------- -------------- ------------------- --------------- ---------------------
DEPTNO NUMBER 22 Y 4 .25 0 1 4,996 2010-03-31 19:36:04 NONE
EMPNO NUMBER 22 Y 99,600 .00001004 0 1 4,996 2010-03-31 19:36:04 NONE
EMPNO_VARCHAR VARCHAR2 40 Y 100,000 .00001 0 1 100,000 2010-03-31 19:36:04 NONE
HIREDATE DATE 7 Y 10,000 .0001 0 1 100,000 2010-03-31 19:36:04 NONE
JOB VARCHAR2 3 Y 1 1 0 1 4,996 2010-03-31 19:36:04 NONE
OWNER.TABLE NAME AVG USED_BLOCKS PCTFREE/ INITRAN Init/Next/ FLst/ IOT_Name/ Duration
[TABLESPACE NAME] NUMBER_OF_ROWS ROWLEN EMPTY_BLOCKS DEG CHAIN USED/INCR MAXTRAN MIN/MAX FGrp IOT_Type LAST Analyzed Dropped
---------------------------------------- -------------- ------- ------------- ---- -------- --------- ------- ------------- ----- --------------- ------------------- ---------------
USER.EMP 100,000 23 413 1 0 10//0 1/255 128k/128k / 2010-03-31 19:36:04
[USER_TS_TX_DATA] 0 1/Unlimit NO
NUMBER_ROWS CLUSTERING BLEVEL AvgLfBlperKEY INITRAN Init/Next/ FLst/
OWNER.INDEX_NAME TABLESPACE DISTINCT_KEY FACTOR LEAF_BLOKCS AvgDtBlperKEY DEG LOGGIN MAXTRAN MIN/MAX FGrp LAST Analyzed
---------------------------------------- ---------------------- --------------- -------------- ------------ ------------- ---- ------ ------- ------------- ----- -------------------
USER.EMP_N1 USER_TS_TX_DATA 100,000 401 [1] 32 1 YES 2/255 128k/128k / 2010-03-31 19:36:06
6 196 66 1/unlimit
USER.EMP_N2 USER_TS_TX_DATA 100,000 401 [1] 1 1 YES 2/255 128k/128k / 2010-03-31 19:36:07
10,000 266 1 1/unlimit
USER.EMP_N3 USER_TS_TX_DATA 100,000 19,298 [1] 1 1 YES 2/255 128k/128k / 2010-03-31 19:36:08
100,000 236 1 1/unlimit
USER.EMP_U1 USER_TS_TX_DATA 100,000 399 [1] 1 1 YES 2/255 128k/128k / 2010-03-31 19:36:05
100,000 208 1 1/unlimit
OWNER.INDEX_NAME Uniq Type Status Drop COLUMN LIST
---------------------------------------- ---- ------ ------ ---- --------------------------------------------------------------------------------------------------------------------
USER.EMP_N1 NONU NORMAL VALID NO DEPTNO
USER.EMP_N2 NONU NORMAL VALID NO HIREDATE
USER.EMP_N3 NONU NORMAL VALID NO EMPNO_VARCHAR
USER.EMP_U1 UNIQ NORMAL VALID NO EMPNO
-- 6. View DEPT Table Statistics
*************[ Table Info ]**********************************************************************************************************************************************************
COLUMN_NAME DATA_TYPE DATA_LEN Null DISTINCT_KEY DENSITY NUM_NULLS NUM_BUCKETS SAMPLE_SIZE LAST Analyzed HISTOGRAM Default Value
------------------------------ ---------- -------- ---- -------------- ---------- -------------- ----------- -------------- ------------------- --------------- ---------------------
DEPTNO NUMBER 22 Y 9 .111111111 0 1 9 2010-03-31 19:36:09 NONE
DNAME VARCHAR2 46 Y 9 .111111111 0 1 9 2010-03-31 19:36:09 NONE
LOC VARCHAR2 45 Y 9 .111111111 0 1 9 2010-03-31 19:36:09 NONE
OWNER.TABLE NAME AVG USED_BLOCKS PCTFREE/ INITRAN Init/Next/ FLst/ IOT_Name/ Duration
[TABLESPACE NAME] NUMBER_OF_ROWS ROWLEN EMPTY_BLOCKS DEG CHAIN USED/INCR MAXTRAN MIN/MAX FGrp IOT_Type LAST Analyzed Dropped
---------------------------------------- -------------- ------- ------------- ---- -------- --------- ------- ------------- ----- --------------- ------------------- ---------------
USER.DEPT 9 18 4 1 0 10//0 1/255 128k/128k / 2010-03-31 19:36:09
[USER_TS_TX_DATA] 0 1/Unlimit NO
NUMBER_ROWS CLUSTERING BLEVEL AvgLfBlperKEY INITRAN Init/Next/ FLst/
OWNER.INDEX_NAME TABLESPACE DISTINCT_KEY FACTOR LEAF_BLOKCS AvgDtBlperKEY DEG LOGGIN MAXTRAN MIN/MAX FGrp LAST Analyzed
---------------------------------------- ---------------------- --------------- -------------- ------------ ------------- ---- ------ ------- ------------- ----- -------------------
USER.DEPT_U1 USER_TS_TX_DATA 9 1 [0] 1 1 YES 2/255 128k/128k / 2010-03-31 19:36:09
9 1 1 1/unlimit
OWNER.INDEX_NAME Uniq Type Status Drop COLUMN LIST
---------------------------------------- ---- ------ ------ ---- --------------------------------------------------------------------------------------------------------------------
USER.DEPT_U1 UNIQ NORMAL VALID NO DEPTNO
2. EMP => DEPT
SELECT /*+ ORDERED USE_NL(E D) */
*
FROM EMP E,
DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10001 0.050 1.984 399 120361 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10003 0.050 1.987 399 120361 0 100000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100000 NESTED LOOPS (cr=120361 pr=399 pw=0 time=1210637 us)
100000 TABLE ACCESS FULL EMP (cr=10360 pr=399 pw=0 time=110599 us)
100000 TABLE ACCESS BY INDEX ROWID DEPT (cr=110001 pr=0 pw=0 time=997420 us)
100000 INDEX UNIQUE SCAN DEPT_U1 (cr=10001 pr=0 pw=0 time=403812 us)(Object ID 19697318)
3. DEPT => EMP
SELECT /*+ ORDERED USE_NL(E D) */
*
FROM DEPT D,
EMP E
WHERE E.DEPTNO = D.DEPTNO
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10001 0.050 1.168 196 20576 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10003 0.050 1.171 196 20576 0 100000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100000 TABLE ACCESS BY INDEX ROWID EMP (cr=20576 pr=196 pw=0 time=67708660 us)
100010 NESTED LOOPS (cr=10218 pr=196 pw=0 time=1400730 us)
9 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=235 us)
100000 INDEX RANGE SCAN EMP_N1 (cr=10210 pr=196 pw=0 time=67305741 us)(Object ID 19697315)
4. 정리
- 위에서 보는 것처럼 'EMP -> DEPT' 순서일 경우 조인횟수가 총 20만번이 이루어 졌지만
'DEPT -> EMP'일 경우 10만번에 9번만 추가되어 I/O가 줄어들음을 알 수 있다(I/O : 120,361 -> 20,576)
III. 필터 조건이 있을 때
1. DEPT => EMP
SELECT /*+ ORDERED USE_NL(E D) */
*
FROM DEPT D,
EMP E
WHERE E.DEPTNO = D.DEPTNO
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 10001 0.050 1.168 196 20576 0 100000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 10003 0.050 1.171 196 20576 0 100000
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
100000 TABLE ACCESS BY INDEX ROWID EMP (cr=20576 pr=196 pw=0 time=67708660 us)
100010 NESTED LOOPS (cr=10218 pr=196 pw=0 time=1400730 us)
9 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=235 us)
100000 INDEX RANGE SCAN EMP_N1 (cr=10210 pr=196 pw=0 time=67305741 us)(Object ID 19697315)
;
2. DEPT => EMP(필터조건 존재)
SELECT /*+ ORDERED USE_NL(E D) */
*
FROM DEPT D,
EMP E
WHERE E.DEPTNO = D.DEPTNO
AND E.EMPNO = 1
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.004 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 24 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.000 0.004 0 24 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 NESTED LOOPS (cr=24 pr=0 pw=0 time=185 us)
9 TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=209 us)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=20 pr=0 pw=0 time=168 us)
9 INDEX UNIQUE SCAN EMP_U1 (cr=11 pr=0 pw=0 time=101 us)(Object ID 19697314)
;
3. EMP(필터조건 존재) => DEPT
SELECT /*+ ORDERED USE_NL(E D) */
*
FROM EMP E,
DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.EMPNO = 1
;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.006 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.000 0 5 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.006 0 5 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 NESTED LOOPS (cr=5 pr=0 pw=0 time=109 us)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=82 us)
1 INDEX UNIQUE SCAN EMP_U1 (cr=2 pr=0 pw=0 time=56 us)(Object ID 19697314)
1 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=21 us)
1 INDEX UNIQUE SCAN DEPT_U1 (cr=1 pr=0 pw=0 time=9 us)(Object ID 19697318)
;
4. 정리
- 위에서 보는 것처럼 필터조건이 있을 경우 해당 테이블에서 필터조건의 NDV가 테이블의 로우수와 같은
10만(Unique)이기 때문에 EMP 테이블을 드라이빙 하는 것이 좋다.
IV. 심플 ERD 사용방법 및 실사례 살펴봄(책 이외의 내용)
1. 심플 ERD란?
- Entity Relation Diagram의 약자로 Entity 관계들을 논리적으로 표현할 수 있는 방법론
- 심플 ERD는 필자가 만든 방법론으로 기존 ERD 방법론에서 어려운 내용을 제외하고 쉽게 작성하여
SQL의 액세스 패스를 이해하고자 하는 것이 주요 목적임 - 심플 ERD의 장점은 테이블의 조인관계를 논리적으로 표현하여 옵티마이저가 만든 조인순서를 평가할 수 있으며,
비효율적인 부분이 있을 경우 더 좋게 변경이 가능하고, 마지막으로 프로그램 이력관리가 가능하다.
{info}
이 심플 ERD 방법론은 필자가 만든 방법론이므로 문서작성에 활용될 경우 출처를 남겨주시기 바랍니다.
{info}
2. 심플 ERD 사용방법
- FROM절의 테이블들을 사각형으로 모두 표시해주고, 이 사각형 박스에 테이블 이름 또는 Alias를 명시한다.
- WHERE절의 조인관계를 참고하여 직선을 그려주고, 만약 서브쿼리일 경우는 점선 사각형으로 구분시킨다.
- Outer Join은 기준 테이블에서 대상 테이블로 직선 화살표를 그린다.(Outer Join은 단방향 액세스 패스이므로)
이와 마찬가지로 데이터 타입이 틀려 한쪽을 형변환 했을 경우 형변환 된 테이블에서 조인 테이블로 직선 화살표를 그린다. - View 또는 Inline View를 그릴 경우 구분할 수 있는 박스를 그린다.
3. 심플 ERD 작성 예
| {code:SQL} SELECT /*+ LEADING(WND) USE_NL(WND WDA WDD MP OOH OOL OTT XGSE XLV XCRH XSM XGC XFFH XFFL) */ ............... FROM TABLE_01 XXB, TABLE_02 WND, TABLE_03 WDA, TABLE_04 WDD, TABLE_05 MP, TABLE_06 OOH, TABLE_07 OOL, TABLE_08 OTT, TABLE_09 XGSE, TABLE_10 XLV, TABLE_11 XLV1, TABLE_12 XCRH, TABLE_13 XFFL, TABLE_14 XFFH, TABLE_15 XSM WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE AND XLV1.ORG_ID = OOH.ORG_ID AND EXISTS (SELECT WDA1.DELIVERY_ID FROM TABLE16 WDA1, TABLE17 WDD1 WHERE WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID AND WDA1.DELIVERY_ID = WND.DELIVERY_ID) AND WND.ORGANIZATION_ID = :B1 AND MP.ORGANIZATION_CODE = :B2 AND OOH.ATTRIBUTE1 = :B3 ; {code} |  |
4. 심플 ERD 단계적 작성
| 01. | {code:SQL} WHERE XXB.SR_NO = WND.NAME {code} |  |
| 02. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID {code} |  |
| 03. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID {code} |  |
| 04. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID {code} |  |
| 05. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID {code} |  |
| 06. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID {code} |  |
| 07. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID {code} |  |
| 08. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID {code} |  |
| 09. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID {code} |  |
| 10. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID {code} |  |
| 11. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID {code} |  |
| 12. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) {code} |  |
| 13. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO {code} |  |
| 14. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE {code} |  |
| 15. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE {code} |  |
| 16. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE AND XLV1.ORG_ID = OOH.ORG_ID {code} |  |
| 17. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE AND XLV1.ORG_ID = OOH.ORG_ID AND EXISTS (SELECT WDA1.DELIVERY_ID FROM TABLE16 WDA1, TABLE17 WDD1 WHERE WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID AND WDA1.DELIVERY_ID = WND.DELIVERY_ID) {code} |  |
| 18. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE AND XLV1.ORG_ID = OOH.ORG_ID AND EXISTS (SELECT WDA1.DELIVERY_ID FROM TABLE16 WDA1, TABLE17 WDD1 WHERE WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID AND WDA1.DELIVERY_ID = WND.DELIVERY_ID) AND WND.ORGANIZATION_ID = :B1 AND MP.ORGANIZATION_CODE = :B2 AND OOH.ATTRIBUTE1 = :B3 {code} | |
5. 실사례
- 위의 ERD를 보면 3개의 테이블에 조건이 들어가므로 드라이빙 테이블은 일단 3개의 후보군을 선정할 수 있다.
- 이 때 가장 좋은 NDV를 가지는 WDD.ORGANIZATION_ID의 NDV가 가장 좋으므로 힌트도 이미 WND를 LEADING 한 상태임
- 문제는 WND에서 시작해 WDD 오른쪽 영역으로 넘어갈 수 있는 액세스 패스가 2개인데, 옵티마이저 선택에 따라
'WND -> WDA -> WDD -> ...'로 갈 수도 있고(파란 화살표), 'WND -> XXB -> XLV1 -> OOH -> ...'로 갈 수도 있다(빨간 화살표) - 좋은 액세스 패스는 파란 화살표의 경우이며 빨간 화살표로 갈 경우 데이터를 필터하고 가지 못하기 때문에 성능에 치명적인 패스
- 실제로 평소에 이 SQL을 사용하는 프로그램의 수행속도는 10초 이내였으나, 통계정보 갱신 이후 갑자기 수행속도가 30분으로 늘어나
업무에 지장을 주게 되었는데 그 때 실행계획을 살펴본 결과 파란 화살표가 아닌 빨간 화살표로 액세스 패스가 변경되었음 - 이를 해결하기 위해서는 LEADING 힌트로 항상 파란 화살표 액세스 패턴이 고정되도록 할 수 있었으나, 근본적으로
액세스 패턴이 하나가 아닌 두개 이상이어서 생긴 문제이므로 XLV1과 OOH의 조인을 제거할 수 있는지를 살펴봄 - 이를 확인하기 위해 업무담당자들과 협의한 결과 XLV1과 OOH의 조인은 단순히 Validation 처리를 위한 조인관계라고 하며
이는 변수 선언부에서 값을 할당받아 해당 SQL에서 상수로 값을 넣고 조인은 제거가 가능하다는 회신을 받음 - 결국 힌트는 변경하지 않고 연결고리만 끊어 단방향 액세스 패스를 유도하게 되었고 성능은 이전처럼 10초 이내로 조회가 됨
| 19. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE AND XLV1.ORG_ID = OOH.ORG_ID AND EXISTS (SELECT WDA1.DELIVERY_ID FROM TABLE16 WDA1, TABLE17 WDD1 WHERE WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID AND WDA1.DELIVERY_ID = WND.DELIVERY_ID) AND WND.ORGANIZATION_ID = :B1 AND MP.ORGANIZATION_CODE = :B2 AND OOH.ATTRIBUTE1 = :B3 {code} |  |
| 20. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE --AND XLV1.ORG_ID = OOH.ORG_ID AND XLV1.ORG_ID = :B11 AND EXISTS (SELECT WDA1.DELIVERY_ID FROM TABLE16 WDA1, TABLE17 WDD1 WHERE WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID AND WDA1.DELIVERY_ID = WND.DELIVERY_ID) AND WND.ORGANIZATION_ID = :B1 AND MP.ORGANIZATION_CODE = :B2 AND OOH.ATTRIBUTE1 = :B3 {code} |  |
| 21. | {code:SQL} WHERE XXB.SR_NO = WND.NAME AND WND.DELIVERY_ID = WDA.DELIVERY_ID AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.ORGANIZATION_ID = MP.ORGANIZATION_ID AND WDD.SOURCE_HEADER_ID = OOH.HEADER_ID AND WDD.SOURCE_LINE_ID = OOL.LINE_ID AND OTT.TRANSACTION_TYPE_ID = OOH.ORDER_TYPE_ID AND TO_NUMBER(WDD.ATTRIBUTE6) = XFFH.WH_SHP_HEADER_ID AND TO_NUMBER(WDD.ATTRIBUTE7) = XFFL.WH_SHP_LINE_ID AND XFFL.WH_SHP_HEADER_ID = XFFH.WH_SHP_HEADER_ID AND XFFL.WH_SHP_REQ_HEADER_ID = XCRH.WH_SHP_REQ_HEADER_ID AND WND.NAME = XGSE.SR_NO(+) AND TO_CHAR(OOH.ORDER_NUMBER) = XCRH.ORDER_NO AND XLV1.LOOKUP_CODE = XXB.SHIPPING_LINE_CODE AND XSM.SHIP_METHOD_CODE = XFFH.SHIPPING_METHOD_CODE --AND XLV1.ORG_ID = OOH.ORG_ID AND XLV1.ORG_ID = :B11 AND EXISTS (SELECT WDA1.DELIVERY_ID FROM TABLE16 WDA1, TABLE17 WDD1 WHERE WDA1.DELIVERY_DETAIL_ID = WDD1.DELIVERY_DETAIL_ID AND WDA1.DELIVERY_ID = WND.DELIVERY_ID) AND WND.ORGANIZATION_ID = :B1 AND MP.ORGANIZATION_CODE = :B2 AND OOH.ATTRIBUTE1 = :B3 {code} |  |
6. 정리
- 이처럼 조인순서는 성능에 영향을 많이 줄 수 있으며 힌트가 없을 경우 통계정보 갱신이나
OBJECT 생성 및 변경에 의해서도 조인순서가 변경이 될 수 있기 때문에 들어오는 조건에
최적화가 될 수 있도록 전략적인 방법론이 필요하며, 가급적 액세스 패스는 하나의 통로로만
다닐 수 있도록 설계하는 것이 중요하다.
문서에 대하여
- 최초작성자 : ~xsoft
- 최초작성일 : 2010년 04월 03일
- 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3210
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.