728x90
반응형
사내업무 중 쿼리 튜닝작업을 진행하면서 생겼던 작은 고민들에 대해 공유해볼까 합니다.
본 글과 관련하여 잘못된 부분이나 의견이 있으시다면 언제든 댓글로 남겨주세요!
사용 DB는 Oracle입니다.
1. Top 쿼리 튜닝기
23년 봄 어느 날, 인프라 부서로부터 CUI DB Top 쿼리 list를 받았습니다.
저희 파트의 쿼리가 당당하게(?) 1등을 차지했더라구요.
해당 쿼리가 1등을 할 수 있었던 비결을 먼저 파악해보겠습니다.
A. 비효율 성능의 원인
- Buffer Gets
먼저 저는 TOP 쿼리가 어떤 기준으로 추출되는지 파악하였습니다. TOP 쿼리는 Buffer Gets라는 수치를 기준으로 추출되는데요,
Buffer Gets란? SQL이 처리되는 과정에서 엑세스한 메모리 블록의 수를 의미합니다. 해당 수치가 높을수록 CPU에 많은 부하를 주고있다는 것을 의미하죠.
이번 TOP 쿼리 list에서 1등을 차지한 쿼리는 위 Buffer Gets라는 수치가 압도적으로 높았습니다. 그래서 해당 수치를 줄이는 것을 목표로 쿼리 성능개선에 돌입하였습니다.
- 실행계획 ( Explain Plan )
실행계획을 통해 SQL의 처리절차와 상세 성능지표를 확인할 수 있습니다.
실행계획이란? 말그대로 SQL을 DB에서 어떻게 처리할지에 대한 계획입니다. DB에서 SQL이 처리될 때, 파싱 > 최적화 > 실행 과정을 거치는데요.
이 중, 최적화 과정에서 DB Optimizer가 SQL이 실행되는데 필요한 Cost를 계산하여 최적의 실행계획을 수립하게 됩니다. 이러한 실행계획을 참고하여 SQL의 어떤 부분이 많은 Cost를 발생시키는지 상세히 파악할 수 있습니다.
실행계획은 아래 쿼리를 통해 확인할 수 있습니다.
-- 실행계획 작성 쿼리
EXPLAIN PLAN
SET STATEMENT_ID = 'HR_PLAN'
FOR ({ 성능 확인이 필요한 쿼리 });
-- 실행계획 조회 쿼리
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'HR_PLAN', 'ALL'));
실행계획은 아래 포맷으로 추출됩니다.
Plan hash value: 2689199366
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9174 | 3252K| | 16119 (1)| 00:00:01 |
|* 1 | FILTER | | | | | | |
| 2 | SORT ORDER BY | | 9174 | 3252K| 3504K| 16119 (1)| 00:00:01 |
| 3 | HASH UNIQUE | | 9174 | 3252K| 3504K| 15405 (1)| 00:00:01 |
| 4 | NESTED LOOPS | | 9174 | 3252K| | 14690 (1)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 9174 | 2893K| | 14689 (1)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 9174 | 2221K| | 12854 (1)| 00:00:01 |
|* 7 | HASH JOIN | | 9174 | 1549K| | 11018 (1)| 00:00:01 |
|* 8 | HASH JOIN | | 9174 | 689K| | 6183 (1)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 9175 | 447K| | 1352 (1)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_03 | 9175 | | | 15 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | PNA_NGOSIIF_TXN | 29602 | 780K| | 4831 (1)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | PNA_NGOSIIF_TXN | 325K| 29M| | 4834 (1)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 75 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 75 | | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | 40 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
......
......
......1.Operation : SQL이 실행되는 순서를 확인할 수 있습니다.
2.Name : 접근한 테이블이나 Access한 인덱스 등에 대한 정보를 확인할 수 있습니다.
3.Rows : 예상 출력 건수
4.Bytes : 예상 조회 데이터 크기
5.Cost : 예상 쿼리 실행 비용 ( 예상 값들은 실제 값과 차이가 있을 수 있습니다. )
- 무출동 쿼리 및 실행계획 분석
개선대상의 쿼리인 아래 쿼리는 무출동 SMS 발송대상 오더를 조회하는 쿼리입니다. PNA_NGOSIIF_TXN 테이블 LAST_CHG_DT 값을 기준으로 현재날짜( sysdate )보다 이전 날짜 대상의 row를 조회하는데요.
해당 쿼리의 실행계획은 위 실행계획 참고바랍니다.
SELECT DISTINCT D.NGOSI_SEQ
,D.ORDR_TRT_NO
,D.OBDNG_ID
,D.CUST_ORDR_VER_NO
,D.SVC_CONT_ID
,D.ORDR_TYPE_ID
,D.FIRST_CRET_DT
,D.ORDR_TRT_TYPE_SEQ
,TO_CHAR(D.ORDR_RCP_DATE,'YYYY-MM-DD HH24:MI:SS') AS ORDR_RCP_DATE
,D.NGOSI_TYPE_CD
,D.TRT_RESLT
,D.TRT_RESLT_SBST
,TO_CHAR(D.LAST_CHG_DT,'YYYY-MM-DD HH24:MI:SS') AS LAST_CHG_DT
,NVL(NVL(NVL(NVL(NVL(NCUST.IST_CNTPLC_TEL_NO, NCUST.GENL_TEL_NO), NCUST.GENL_2_TEL_NO), NCUST.GENL_3_TEL_NO), NCUST.GENL_4_TEL_NO), NVL(NVL(NVL(NVL(CCUST.IST_CNTPLC_TEL_NO, CCUST.GENL_TEL_NO), CCUST.GENL_2_TEL_NO), CCUST.GENL_3_TEL_NO), CCUST.GENL_4_TEL_NO)) AS GENL_TEL_NO
FROM PNA_ORTR_DTL A
INNER JOIN PNA_PROD_ORD_BAS BAS ON A.CUST_ORDR_NO = BAS.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = BAS.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = BAS.PROD_ORDR_SEQ
INNER JOIN PNA_NGOSIIF_TXN C ON A.CUST_ORDR_NO = C.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = C.CUST_ORDR_VER_NO AND C.NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK'
INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID
LEFT OUTER JOIN PNA_CUST_ORD_DTL NCUST ON A.CUST_ORDR_NO = NCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = NCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = NCUST.PROD_ORDR_SEQ AND NCUST.CHG_BEFAFT_TYPE_CD = '2'
LEFT OUTER JOIN PNA_CUST_ORD_DTL CCUST ON A.CUST_ORDR_NO = CCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = CCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = CCUST.PROD_ORDR_SEQ AND CCUST.CHG_BEFAFT_TYPE_CD = '1'
WHERE 1 = 1
AND A.OBDNG_ID = 'R01465'
AND D.TRT_RESLT = 'WAIT'
AND D.LAST_CHG_DT < SYSDATE
ORDER BY D.FIRST_CRET_DT;위 쿼리의 실행계획을 보면 PNA_NGOSIIF_TXN 테이블에서 2건의 Full Scan을 확인하실 수 있습니다. ( TABLE ACCESS FULL, PNA_NGOSIIF_TXN )
Full Scan은 조회조건에 해당하는 인덱스가 생성되어있지 않아서, 조건에 맞는 row를 찾기 위해 테이블 전체 값을 조회하는 것을 의미합니다. 테이블 전체 Block에 접근하기 때문에 실행계획상의 Cost도 많이 발생하는 것을 확인하실 수 있습니다.
해당 Full Scan을 개선하면 Cost가 감소되어 쿼리 성능이 크게 향상될 것으로 예상되는 상황입니다.
B. 성능개선
인덱스 추가
첫 시도는 인덱스를 추가하여 Full Scan이 아닌 인덱스 스캔이 진행되도록 개선하는 것이었습니다.
추가 인덱스는 아래와 같습니다.
CREATE INDEX CUI_OWN.PNA_NGOSIIF_TXN_IX_04 ON CUI_OWN.PNA_NGOSIIF_TXN (OBDNG_ID ASC, TRT_RESLT ASC, LAST_CHG_DT ASC);
CREATE INDEX CUI_OWN.PNA_NGOSIIF_TXN_IX_05 ON CUI_OWN.PNA_NGOSIIF_TXN (CUST_ORDR_NO ASC, CUST_ORDR_VER_NO ASC, NGOSI_TYPE_CD ASC, ORDR_TYPE_ID ASC);그리고 운영환경 상에서 실제 성능개선 효과가 있는지 인프라팀이 확인을 요청하였습니다. 결과는 아래와 같습니다.Buffer Gets : 63948 → 55962
- 인덱스 반영 후 실행계획
===================================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) | ===================================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 6 | | | | | 1 | SORT ORDER BY | | 9174 | 14136 | 1 | +0 | 1 | 6 | 2048 | | | | 2 | HASH UNIQUE | | 9174 | 13421 | 1 | +0 | 1 | 6 | 2M | | | | 3 | NESTED LOOPS | | 9174 | 12707 | 1 | +0 | 1 | 6 | | | | | 4 | NESTED LOOPS OUTER | | 9174 | 12706 | 1 | +0 | 1 | 6 | | | | | 5 | NESTED LOOPS OUTER | | 9174 | 10870 | 1 | +0 | 1 | 6 | | | | | 6 | HASH JOIN | | 9174 | 9035 | 1 | +0 | 1 | 6 | 1M | | | | 7 | NESTED LOOPS | | 9174 | 5023 | 1 | +0 | 1 | 64 | | | | | 8 | NESTED LOOPS | | 9175 | 5023 | 1 | +0 | 1 | 68 | | | | | 9 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL | 9175 | 1352 | 1 | +0 | 1 | 12134 | | | | | 10 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_03 | 9175 | 15 | 1 | +0 | 1 | 12134 | | | | | 11 | INDEX RANGE SCAN | PNA_NGOSIIF_TXN_IX_05 | 1 | 1 | 1 | +0 | 12134 | 68 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | PNA_NGOSIIF_TXN | 1 | 1 | 1 | +0 | 68 | 64 | | | | | 13 | TABLE ACCESS FULL | PNA_NGOSIIF_TXN | 269K | 4011 | 1 | +0 | 1 | 20973 | | | | | 14 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 1 | 1 | +0 | 6 | 6 | | | | | 15 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | 1 | 1 | +0 | 6 | 6 | | | | | 16 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 1 | 1 | +0 | 6 | 6 | | | | | 17 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | 1 | 1 | +0 | 6 | 6 | | | | | 18 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | 1 | 1 | +0 | 6 | 6 | | | | =====================================================================================================================================================================
보시는 것처럼 Buffer Gets 상으로 개선효과는 있으나 미미한 상태로 확인되었습니다.
뿐만 아니라 추가 적용한 PNA_NGOSIIF_TXN_IX_05 인덱스는 SCAN이 진행되지만, PNA_NGOSIIF_TXN_IX_04 인덱스는 스캔이 안되고 있었습니다.
4번 인덱스 스캔이 안되고 있는 이유는 해당 쿼리가 대상유무를 조회하는 쿼리이기 때문에
대상이 없을 경우, 인덱스 스캔이 아닌 테이블 Full Scan으로 진행하는 것이 성능상 더 낫다고 DB Optimizer가 판단했을 가능성이 큽니다.
위와 같은 결과로 인해 인덱스 추가로인한 조회성능 향상보단 INSERT/UPDATE/DELETE 성능의 하락이 더 클 것으로 예상이되어
인덱스 추가로 인한 성능개선은 반려하는 것으로 결정하였습니다.
불필요 JOIN문 제거
비즈니스 로직을 들여다봐야할 때라고 생각되었습니다.
그리고 PNA_NGOSIIF_TXN 테이블 JOIN이 두번이나 필요한가?에 대해 고민하기 시작했습니다.
PNA_NGOSIIF_TXN 테이블에 대한 JOIN문으로 아래 두 개의 JOIN문이 있습니다.
INNER JOIN PNA_NGOSIIF_TXN C ON A.CUST_ORDR_NO = C.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = C.CUST_ORDR_VER_NO AND C.NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK'
INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID첫 번째 JOIN문은 PNA_NGOSIIF_TXN 테이블의 NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK' 조건에 부합하는 CUST_ORDR_NO를 뽑아내기 위한 JOIN문 이구요.
두 번째 JOIN문은 단순 PNA_NGOSIIF_TXN 테이블 JOIN문으로써, 위 첫번째 JOIN문으로 뽑아낸 CUST_ORDR_NO를 대상으로 JOIN을 수행하는 구문입니다.
하지만 중복되는 부분이 있었습니다.
아래 WHERE 절을 보면, 아래와 같은 조건이 있습니다.
AND D.TRT_RESLT = 'WAIT'
해당 조건은 비즈니스로직상 C.NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK' 조건에 부합되는 CUST_ORDR_NO에 대해 모두 설정되어있는 조건들입니다.
그래서 위 첫번째 JOIN문의 조건과 중복되는 조건이라고 할 수 있는데요.
이를 AP코드상으로 확인을 했고, 해당하는 첫번째 JOIN문이 불필요한 JOIN문이라고 판단되어 제외하기로 결정하였습니다.
또한, 중복을 제거하는 구문인 DISTINCT역시 제외하기로 결정하였습니다.
PNA_NGOSIIF_TXN 테이블의 PK인 NGOSI_SEQ 컬럼이 출력대상 컬럼에 포함되어있기 때문입니다.
- 개선후 쿼리
SELECT D.NGOSI_SEQ, D.ORDR_TRT_NO, D.OBDNG_ID, D.CUST_ORDR_NO, D.CUST_ORDR_VER_NO, D.SVC_CONT_ID, D.ORDR_TYPE_ID, D.FIRST_CRET_DT, D.ORDR_TRT_TYPE_SEQ, TO_CHAR(D.ORDR_RCP_DATE,'YYYY-MM-DD HH24:MI:SS') AS ORDR_RCP_DATE, D.NGOSI_TYPE_CD, D.TRT_RESLT, D.TRT_RESLT_SBST, TO_CHAR(D.LAST_CHG_DT,'YYYY-MM-DD HH24:MI:SS') AS LAST_CHG_DT, COALESCE(NCUST.IST_CNTPLC_TEL_NO, NCUST.GENL_TEL_NO, NCUST.GENL_2_TEL_NO, NCUST.GENL_3_TEL_NO, NCUST.GENL_4_TEL_NO, CCUST.IST_CNTPLC_TEL_NO, CCUST.GENL_TEL_NO, CCUST.GENL_2_TEL_NO, CCUST.GENL_3_TEL_NO, CCUST.GENL_4_TEL_NO) AS GENL_TEL_NO FROM PNA_ORTR_DTL A INNER JOIN PNA_PROD_ORD_BAS BAS ON A.CUST_ORDR_NO = BAS.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = BAS.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = BAS.PROD_ORDR_SEQ INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID LEFT OUTER JOIN PNA_CUST_ORD_DTL NCUST ON A.CUST_ORDR_NO = NCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = NCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = NCUST.PROD_ORDR_SEQ AND NCUST.CHG_BEFAFT_TYPE_CD = '2' LEFT OUTER JOIN PNA_CUST_ORD_DTL CCUST ON A.CUST_ORDR_NO = CCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = CCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = CCUST.PROD_ORDR_SEQ AND CCUST.CHG_BEFAFT_TYPE_CD = '1' WHERE 1=1 AND A.OBDNG_ID = 'R00439' AND D.TRT_RESLT = 'WAIT' AND D.LAST_CHG_DT < SYSDATE ORDER BY D.FIRST_CRET_DT;
그리고 아래와 같은 개선효과를 볼 수 있었습니다.
Plan hash value: 798785828
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9175 | 3010K| | 10520 (1)| 00:00:01 |
|* 1 | FILTER | | | | | | |
| 2 | SORT ORDER BY | | 9175 | 3010K| 3200K| 10520 (1)| 00:00:01 |
| 3 | NESTED LOOPS | | 9175 | 3010K| | 9860 (1)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 9175 | 2652K| | 9859 (1)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 9175 | 1980K| | 8023 (1)| 00:00:01 |
|* 6 | HASH JOIN | | 9175 | 1308K| | 6187 (1)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 9175 | 447K| | 1352 (1)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_03 | 9175 | | | 15 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PNA_NGOSIIF_TXN | 325K| 29M| | 4834 (1)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 75 | | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 75 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | 40 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
......
......
......실행계획 Cost상으로 16119 → 10520, 5299의 개선효과를 확인하였습니다.
그리고 쿼리 조건이 바뀌었기 때문에 아래와 같은 조건으로 테스트를 진행하여 개선전/후 쿼리에서 추출되는 row의 갯수와 값들을 비교하였습니다.
- 단수개의 국사조건, LAST_CHG_DT : sysdate + 1
- 단수개의 국사조건, LAST_CHG_DT : sysdate + 10
- 복수개의 국사조건, LAST_CHG_DT : sysdate + 1
- 복수개의 국사조건, LAST_CHG_DT : sysdate + 10
그리고 SMS발송 및 연관로직들에 대한 테스트도 별도로 진행되어 이상없음을 확인하고 5월 정기적용 진행하였습니다. 이때는 이렇게 행복하게 마무리되는줄 알았습니다.
국사조건 추가
6월 어느날, TOP 쿼리 list를 또 받았습니다. 위 무출동 쿼리의 2연패였습니다..
하지만 Buffer Gets상으로 다소 개선되었음을 확인하였습니다.
Total Buffer Gets는 448,510,521 → 403,747,191로 감소하였고, 1회 실행당 Buffer Gets를 나타내는 Gets per Exec는 52,884 → 38.103으로 감소하였음을 확인하였습니다.
해당 쿼리의 실행회수가 이전보다 증가되어 Total Buffer Gets상으로는 개선효과가 미미하다고 판단되었으나, Gets per Exec상으로는 적지않은 개선효과가 있었다는 것을 확인하였습니다.
하지만 추가적인 개선이 필요했습니다.
이번에도 역시 Full Scan에 포커싱을 하였습니다. 그리고 안보이던게 보이기 시작했습니다. ( 한창 SQLD 공부하던 시절... )
주테이블인 PNA_ORTR_DTL 테이블엔 국사조건( OBDNG_ID )이 있는데, PNA_NGOSIIF_TXN 테이블에도 똑같이 OBDNG_ID 컬럼이 있음에도 불구하고 동일한 조건이 없는 것이었습니다.
그리고 PNA_NGOSIIF_TXN 테이블에도 OBDNG_ID 컬럼 조건의 인덱스가 있었습니다.
OBDNG_ID 조건을 아래와 같이 PNA_NGOSIIF_TXN 테이블에도 추가하여 실행계획을 확인하였습니다.
SELECT D.NGOSI_SEQ,
D.ORDR_TRT_NO,
D.OBDNG_ID,
D.CUST_ORDR_NO,
D.CUST_ORDR_VER_NO,
D.SVC_CONT_ID,
D.ORDR_TYPE_ID,
D.FIRST_CRET_DT,
D.ORDR_TRT_TYPE_SEQ,
TO_CHAR(D.ORDR_RCP_DATE,'YYYY-MM-DD HH24:MI:SS') AS ORDR_RCP_DATE,
D.NGOSI_TYPE_CD,
D.TRT_RESLT,
D.TRT_RESLT_SBST,
TO_CHAR(D.LAST_CHG_DT,'YYYY-MM-DD HH24:MI:SS') AS LAST_CHG_DT,
COALESCE(NCUST.IST_CNTPLC_TEL_NO, NCUST.GENL_TEL_NO, NCUST.GENL_2_TEL_NO, NCUST.GENL_3_TEL_NO, NCUST.GENL_4_TEL_NO, CCUST.IST_CNTPLC_TEL_NO, CCUST.GENL_TEL_NO, CCUST.GENL_2_TEL_NO, CCUST.GENL_3_TEL_NO, CCUST.GENL_4_TEL_NO) AS GENL_TEL_NO
FROM PNA_ORTR_DTL A
INNER JOIN PNA_PROD_ORD_BAS BAS ON A.CUST_ORDR_NO = BAS.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = BAS.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = BAS.PROD_ORDR_SEQ
INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID
LEFT OUTER JOIN PNA_CUST_ORD_DTL NCUST ON A.CUST_ORDR_NO = NCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = NCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = NCUST.PROD_ORDR_SEQ AND NCUST.CHG_BEFAFT_TYPE_CD = '2'
LEFT OUTER JOIN PNA_CUST_ORD_DTL CCUST ON A.CUST_ORDR_NO = CCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = CCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = CCUST.PROD_ORDR_SEQ AND CCUST.CHG_BEFAFT_TYPE_CD = '1'
WHERE 1=1
AND A.OBDNG_ID = 'R00439'
AND D.OBDNG_ID = 'R00439'
AND D.TRT_RESLT = 'WAIT'
AND D.LAST_CHG_DT < SYSDATE
ORDER BY D.FIRST_CRET_DT;아래는 실행계획입니다.
Plan hash value: 3620360198
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 775 | 254K| 916 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT ORDER BY | | 775 | 254K| 916 (1)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 775 | 254K| 915 (1)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 775 | 197K| 760 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 775 | 140K| 605 (1)| 00:00:01 |
| 6 | NESTED LOOPS | | 775 | 110K| 604 (1)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PNA_NGOSIIF_TXN | 775 | 74400 | 293 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PNA_NGOSIIF_TXN_IX_02 | 3101 | | 5 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 1 | 50 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_01 | 1 | | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | 40 | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 75 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 75 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
......
......
......결과는 성공이었습니다.
OBDNG_ID 조건을 하나 추가했을 뿐인데, Full Scan이 아닌 인덱스 스캔으로 유도할 수 있었고, Cost를 크게 감소시켰습니다. ( 10520 → 916 )
그리고 6월 정기 반영 후 인프라팀 확인 결과, Gets per Exec 값도 38.103 → 2,350 으로 크게 개선됨을 확인하였습니다.
하지만 쿼리 수행 빈도수는 여전히 높아서 Total Buffer Gets 수치도 여전히 높을 수 있다고 하였습니다.
추후 TOP 쿼리로 해당쿼리가 또 대상으로 잡히면, 쿼리 성능이 아닌 쿼리 수행 빈도수에 포커싱을 할 예정입니다.
C. 배운 점
배운점은 쿼리 개선방안을 "멀리서 찾지말고 가까이서 찾자." 입니다.
비효율 쿼리가 보이면, 인덱스를 추가하고 비즈니스 로직을 보기 전에 아래처럼 기본에 충실한 쿼리인지 먼저 확인할 것입니다.
1.실행계획 확인을 통한 비효율 포인트 파악
2.기존 생성되어 있는 인덱스가 적극 활용되고 있는지? => 이 부분 확인이 선행되지 않아서, 실질적인 쿼리개선까지 많이 돌아왔습니다.
3.쿼리 특징 파악 => 쿼리 특징에 따라 인덱스 추가 여부 결정
4.비즈니스 파악 => 중복 조건이 있는지? 요구사항이 과하게 담겨있는 쿼리인지 등 파악
2. 번외
최근 번호이동지연명령UI 조회쿼리에서 비효율이 발견되었습니다.
결론적으로는 해당 쿼리가 과한 쿼리로 판단되어 두 개의 쿼리로 분리한 후, Full Scan이 일어났던 부분을 단건조회로 전환하는 것을 통해 개선하였지만,
위 쿼리튜닝기에서처럼 조건을 추가하여 쿼리 개선한 결과를 공유드립니다.
- As-Is
Plan hash value: 924581685
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | 164K (2)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
| 2 | VIEW | | 2 | 26 | | 164K (2)| 00:00:09 |
| 3 | UNION-ALL | | | | | | |
| 4 | SORT AGGREGATE | | 1 | 271 | | | |
|* 5 | HASH JOIN | | 5760 | 1524K| | 82309 (2)| 00:00:05 |
| 6 | NESTED LOOPS OUTER | | 2 | 528 | | 240 (1)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 2 | 444 | | 239 (1)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 2 | 360 | | 238 (1)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 2 | 344 | | 237 (1)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 2 | 316 | | 236 (1)| 00:00:01 |
| 11 | NESTED LOOPS ANTI | | 2 | 288 | | 235 (1)| 00:00:01 |
| 12 | NESTED LOOPS | | 168 | 21336 | | 201 (1)| 00:00:01 |
| 13 | NESTED LOOPS | | 168 | 14616 | | 200 (1)| 00:00:01 |
| 14 | INLIST ITERATOR | | | | | | |
|* 15 | INDEX UNIQUE SCAN | TB_OFFICE_PK | 38 | 266 | | 1 (0)| 00:00:01 |
| 16 | INLIST ITERATOR | | | | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 4 | 320 | | 5 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PNA_ORTR_DTL_UK_01 | 13 | | | 3 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | 40 | | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_PRCS_DTL | 172K| 2861K| | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PNA_ORTR_PRCS_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | 14 | | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | 14 | | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | TB_SVCTYPECODE_PK | 1 | 8 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | 42 | | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | 42 | | 1 (0)| 00:00:01 |
| 27 | VIEW | | 451K| 3089K| | 82067 (2)| 00:00:05 |
| 28 | SORT GROUP BY | | 451K| 25M| | 82067 (2)| 00:00:05 |
| 29 | VIEW | | 879K| 50M| | 82067 (2)| 00:00:05 |
|* 30 | FILTER | | | | | | |
| 31 | WINDOW SORT | | 879K| 44M| 60M| 82067 (2)| 00:00:05 |
| 32 | VIEW | | 879K| 44M| | 70575 (2)| 00:00:04 |
| 33 | UNION-ALL | | | | | | |
|* 34 | TABLE ACCESS FULL | PNA_ORTR_PRCS_DTL | 399K| 12M| | 50861 (2)| 00:00:03 |
|* 35 | TABLE ACCESS FULL | PNA_ORTR_SBPRCS_DTL | 479K| 15M| | 19714 (3)| 00:00:02 |
| 36 | SORT AGGREGATE | | 1 | 271 | | | |
|* 37 | HASH JOIN | | 5760 | 1524K| | 82209 (2)| 00:00:05 |
| 38 | NESTED LOOPS OUTER | | 1 | 264 | | 140 (0)| 00:00:01 |
| 39 | NESTED LOOPS OUTER | | 1 | 222 | | 139 (0)| 00:00:01 |
| 40 | NESTED LOOPS OUTER | | 1 | 180 | | 138 (0)| 00:00:01 |
| 41 | NESTED LOOPS OUTER | | 1 | 172 | | 137 (0)| 00:00:01 |
| 42 | NESTED LOOPS OUTER | | 1 | 158 | | 136 (0)| 00:00:01 |
| 43 | NESTED LOOPS ANTI | | 1 | 144 | | 135 (0)| 00:00:01 |
| 44 | NESTED LOOPS | | 84 | 10668 | | 102 (1)| 00:00:01 |
| 45 | NESTED LOOPS | | 84 | 7308 | | 101 (1)| 00:00:01 |
| 46 | INLIST ITERATOR | | | | | | |
|* 47 | INDEX UNIQUE SCAN | TB_OFFICE_PK | 38 | 266 | | 1 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL | 2 | 160 | | 3 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | PNA_ORTR_DTL_UK_01 | 6 | | | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | 40 | | 1 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_PRCS_DTL | 331K| 5503K| | 1 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | PNA_ORTR_PRCS_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | 14 | | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | 14 | | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | TB_SVCTYPECODE_PK | 1 | 8 | | 1 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | 42 | | 1 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | 42 | | 1 (0)| 00:00:01 |
| 58 | VIEW | | 451K| 3089K| | 82067 (2)| 00:00:05 |
| 59 | SORT GROUP BY | | 451K| 25M| | 82067 (2)| 00:00:05 |
| 60 | VIEW | | 879K| 50M| | 82067 (2)| 00:00:05 |
|* 61 | FILTER | | | | | | |
| 62 | WINDOW SORT | | 879K| 44M| 60M| 82067 (2)| 00:00:05 |
| 63 | VIEW | | 879K| 44M| | 70575 (2)| 00:00:04 |
| 64 | UNION-ALL | | | | | | |
|* 65 | TABLE ACCESS FULL | PNA_ORTR_PRCS_DTL | 399K| 12M| | 50861 (2)| 00:00:03 |
|* 66 | TABLE ACCESS FULL | PNA_ORTR_SBPRCS_DTL | 479K| 15M| | 19714 (3)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------------------- To-Be
Plan hash value: 949097900
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1137 | 3185K| | 59793 (1)| 00:00:04 |
| 1 | UNION-ALL | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | TB_COMMON_CD_BAS | 1 | 26 | | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | TB_COMMON_CD_BAS_PK | 1 | | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | TB_COMMON_CD_BAS | 1 | 26 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | TB_COMMON_CD_BAS_PK | 1 | | | 1 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 588 | 663K| | 24637 (1)| 00:00:02 |
| 7 | TABLE ACCESS FULL | PNA_ORDRPRCSSERR_CTG | 710 | 126K| | 12 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 588 | 558K| | 24625 (1)| 00:00:02 |
| 9 | TABLE ACCESS FULL | PNA_ORDRPRCSSERR_CTG | 710 | 126K| | 12 (0)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 588 | 453K| | 24613 (1)| 00:00:02 |
| 11 | TABLE ACCESS FULL | PNA_ORDRPRCSSERR_CTG | 710 | 126K| | 12 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 588 | 347K| | 24601 (1)| 00:00:02 |
| 13 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 14 | HASH JOIN RIGHT OUTER | | 588 | 335K| | 24589 (1)| 00:00:02 |
| 15 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 16 | HASH JOIN RIGHT OUTER | | 588 | 323K| | 24577 (1)| 00:00:02 |
| 17 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 18 | HASH JOIN RIGHT OUTER | | 588 | 311K| | 24565 (1)| 00:00:02 |
| 19 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 20 | HASH JOIN | | 588 | 299K| | 24553 (1)| 00:00:02 |
| 21 | NESTED LOOPS OUTER | | 2 | 890 | | 3744 (1)| 00:00:01 |
| 22 | NESTED LOOPS | | 2 | 806 | | 3743 (1)| 00:00:01 |
| 23 | NESTED LOOPS OUTER | | 2 | 772 | | 3742 (1)| 00:00:01 |
| 24 | NESTED LOOPS OUTER | | 2 | 702 | | 3741 (1)| 00:00:01 |
| 25 | NESTED LOOPS ANTI | | 2 | 632 | | 3740 (1)| 00:00:01 |
| 26 | NESTED LOOPS OUTER | | 168 | 50232 | | 3706 (1)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 168 | 39816 | | 3673 (1)| 00:00:01 |
| 28 | NESTED LOOPS | | 168 | 29400 | | 3639 (1)| 00:00:01 |
| 29 | INLIST ITERATOR | | | | | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL | 168 | 20664 | | 3605 (1)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_06 | 17432 | | | 732 (1)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | PNA_PROD_ORD_BAS | 1 | 52 | | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | | | 1 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 62 | | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 62 | | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
|* 38 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_PRCS_DTL | 172K| 2861K| | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | PNA_ORTR_PRCS_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_TELNO_TXN | 1 | 35 | | 1 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | | | 1 (0)| 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_TELNO_TXN | 1 | 35 | | 1 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | | | 1 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | TB_OFFICE | 1 | 17 | | 1 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | TB_OFFICE_PK | 1 | | | 1 (0)| 00:00:01 |
| 46 | TABLE ACCESS BY INDEX ROWID | TB_SVCTYPECODE | 1 | 42 | | 1 (0)| 00:00:01 |
|* 47 | INDEX UNIQUE SCAN | TB_SVCTYPECODE_PK | 1 | | | 1 (0)| 00:00:01 |
| 48 | VIEW | | 46114 | 3467K| | 20809 (1)| 00:00:02 |
| 49 | SORT GROUP BY | | 46114 | 2701K| | 20809 (1)| 00:00:02 |
| 50 | VIEW | | 46114 | 2701K| | 20809 (1)| 00:00:02 |
|* 51 | FILTER | | | | | | |
| 52 | WINDOW SORT | | 46114 | 2521K| 3456K| 20809 (1)| 00:00:02 |
| 53 | VIEW | | 46114 | 2521K| | 20177 (1)| 00:00:02 |
| 54 | UNION-ALL | | | | | | |
| 55 | NESTED LOOPS | | 12176 | 737K| | 7726 (1)| 00:00:01 |
| 56 | NESTED LOOPS | | 13732 | 737K| | 7726 (1)| 00:00:01 |
| 57 | INLIST ITERATOR | | | | | | |
|* 58 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 3433 | 117K| | 3605 (1)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_06 | 17432 | | | 732 (1)| 00:00:01 |
|* 60 | INDEX RANGE SCAN | PNA_ORTR_PRCS_DTL_PK | 4 | | | 1 (0)| 00:00:01 |
|* 61 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_PRCS_DTL | 4 | 108 | | 1 (0)| 00:00:01 |
| 62 | NESTED LOOPS | | 33938 | 1955K| | 12451 (1)| 00:00:01 |
| 63 | NESTED LOOPS | | 33938 | 1955K| | 12451 (1)| 00:00:01 |
| 64 | INLIST ITERATOR | | | | | | |
|* 65 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 14739 | 460K| | 3605 (1)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_06 | 17432 | | | 732 (1)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | PNA_ORTR_SBPRCS_DTL_PK | 2 | | | 1 (0)| 00:00:01 |
|* 68 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_SBPRCS_DTL | 2 | 54 | | 1 (0)| 00:00:01 |
| 69 | TABLE ACCESS BY INDEX ROWID | TB_COMMON_CD_BAS | 1 | 26 | | 1 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | TB_COMMON_CD_BAS_PK | 1 | | | 1 (0)| 00:00:01 |
| 71 | TABLE ACCESS BY INDEX ROWID | TB_COMMON_CD_BAS | 1 | 26 | | 1 (0)| 00:00:01 |
|* 72 | INDEX UNIQUE SCAN | TB_COMMON_CD_BAS_PK | 1 | | | 1 (0)| 00:00:01 |
|* 73 | HASH JOIN RIGHT OUTER | | 549 | 619K| | 35155 (1)| 00:00:02 |
| 74 | TABLE ACCESS FULL | PNA_ORDRPRCSSERR_CTG | 710 | 126K| | 12 (0)| 00:00:01 |
|* 75 | HASH JOIN RIGHT OUTER | | 549 | 521K| | 35143 (1)| 00:00:02 |
| 76 | TABLE ACCESS FULL | PNA_ORDRPRCSSERR_CTG | 710 | 126K| | 12 (0)| 00:00:01 |
|* 77 | HASH JOIN RIGHT OUTER | | 549 | 423K| | 35131 (1)| 00:00:02 |
| 78 | TABLE ACCESS FULL | PNA_ORDRPRCSSERR_CTG | 710 | 126K| | 12 (0)| 00:00:01 |
|* 79 | HASH JOIN RIGHT OUTER | | 549 | 324K| | 35119 (1)| 00:00:02 |
| 80 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 81 | HASH JOIN RIGHT OUTER | | 549 | 313K| | 35107 (1)| 00:00:02 |
| 82 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 83 | HASH JOIN RIGHT OUTER | | 549 | 302K| | 35095 (1)| 00:00:02 |
| 84 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 85 | HASH JOIN RIGHT OUTER | | 549 | 291K| | 35083 (1)| 00:00:02 |
| 86 | TABLE ACCESS FULL | PNA_ORDRPRCSSTYPE_CTG | 23 | 483 | | 12 (0)| 00:00:01 |
|* 87 | HASH JOIN | | 549 | 279K| | 35071 (1)| 00:00:02 |
| 88 | NESTED LOOPS OUTER | | 1 | 445 | | 146 (1)| 00:00:01 |
| 89 | NESTED LOOPS OUTER | | 1 | 403 | | 145 (1)| 00:00:01 |
| 90 | NESTED LOOPS OUTER | | 1 | 368 | | 144 (1)| 00:00:01 |
| 91 | NESTED LOOPS ANTI | | 1 | 333 | | 143 (1)| 00:00:01 |
| 92 | NESTED LOOPS OUTER | | 42 | 13272 | | 126 (0)| 00:00:01 |
| 93 | NESTED LOOPS OUTER | | 42 | 10668 | | 118 (1)| 00:00:01 |
| 94 | NESTED LOOPS | | 42 | 8064 | | 109 (0)| 00:00:01 |
| 95 | NESTED LOOPS | | 42 | 5880 | | 101 (1)| 00:00:01 |
| 96 | INLIST ITERATOR | | | | | | |
| 97 | TABLE ACCESS BY INDEX ROWID | TB_OFFICE | 38 | 646 | | 2 (0)| 00:00:01 |
|* 98 | INDEX UNIQUE SCAN | TB_OFFICE_PK | 38 | | | 1 (0)| 00:00:01 |
|* 99 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL | 1 | 123 | | 3 (0)| 00:00:01 |
|*100 | INDEX RANGE SCAN | PNA_ORTR_DTL_UK_01 | 6 | | | 1 (0)| 00:00:01 |
| 101 | TABLE ACCESS BY INDEX ROWID | PNA_PROD_ORD_BAS | 1 | 52 | | 1 (0)| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | PNA_PROD_ORD_BAS_PK | 1 | | | 1 (0)| 00:00:01 |
| 103 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 62 | | 1 (0)| 00:00:01 |
|*104 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
| 105 | TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL | 1 | 62 | | 1 (0)| 00:00:01 |
|*106 | INDEX UNIQUE SCAN | PNA_CUST_ORD_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
|*107 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_PRCS_DTL | 331K| 5503K| | 1 (0)| 00:00:01 |
|*108 | INDEX RANGE SCAN | PNA_ORTR_PRCS_DTL_PK | 1 | | | 1 (0)| 00:00:01 |
| 109 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_TELNO_TXN | 1 | 35 | | 1 (0)| 00:00:01 |
|*110 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | | | 1 (0)| 00:00:01 |
| 111 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_TELNO_TXN | 1 | 35 | | 1 (0)| 00:00:01 |
|*112 | INDEX UNIQUE SCAN | PNA_ORTR_TELNO_TXN_PK | 1 | | | 1 (0)| 00:00:01 |
| 113 | TABLE ACCESS BY INDEX ROWID | TB_SVCTYPECODE | 1 | 42 | | 1 (0)| 00:00:01 |
|*114 | INDEX UNIQUE SCAN | TB_SVCTYPECODE_PK | 1 | | | 1 (0)| 00:00:01 |
| 115 | VIEW | | 86207 | 6482K| | 34925 (1)| 00:00:02 |
| 116 | SORT GROUP BY | | 86207 | 5051K| | 34925 (1)| 00:00:02 |
| 117 | VIEW | | 86207 | 5051K| | 34925 (1)| 00:00:02 |
|*118 | FILTER | | | | | | |
| 119 | WINDOW SORT | | 86207 | 4714K| 6456K| 34925 (1)| 00:00:02 |
| 120 | VIEW | | 86207 | 4714K| | 33749 (1)| 00:00:02 |
| 121 | UNION-ALL | | | | | | |
| 122 | NESTED LOOPS | | 52269 | 3011K| | 21297 (1)| 00:00:02 |
| 123 | NESTED LOOPS | | 58956 | 3011K| | 21297 (1)| 00:00:02 |
| 124 | INLIST ITERATOR | | | | | | |
|*125 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 14739 | 460K| | 3605 (1)| 00:00:01 |
|*126 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_06 | 17432 | | | 732 (1)| 00:00:01 |
|*127 | INDEX RANGE SCAN | PNA_ORTR_PRCS_DTL_PK | 4 | | | 1 (0)| 00:00:01 |
|*128 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_PRCS_DTL | 4 | 108 | | 1 (0)| 00:00:01 |
| 129 | NESTED LOOPS | | 33938 | 1955K| | 12451 (1)| 00:00:01 |
| 130 | NESTED LOOPS | | 33938 | 1955K| | 12451 (1)| 00:00:01 |
| 131 | INLIST ITERATOR | | | | | | |
|*132 | TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL | 14739 | 460K| | 3605 (1)| 00:00:01 |
|*133 | INDEX RANGE SCAN | PNA_ORTR_DTL_IX_06 | 17432 | | | 732 (1)| 00:00:01 |
|*134 | INDEX RANGE SCAN | PNA_ORTR_SBPRCS_DTL_PK | 2 | | | 1 (0)| 00:00:01 |
|*135 | TABLE ACCESS BY INDEX ROWID | PNA_ORTR_SBPRCS_DTL | 2 | 54 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------As-Is의 쿼리를 보시면 PNA_ORTR_PRCS_DTL 테이블과 PNA_ORTR_SBPRCS_DTL 테이블에서 Full Scan이 확인됩니다.
이를 해결하기 위해 PNA_ORTR_DTL 테이블을 추가로 JOIN하였고, 추가 JOIN한 테이블에 조건을 추가로 걸어서 Full Scan을 방지하였습니다. 그리고 To-Be의 결과를 도출할 수 있었습니다. ( Cost : 164,000 → 59,793 )
아래는 수정된 쿼리 이력입니다. ( 전체쿼리는 길이가 너무 길어 첨부하지 않고, 일부만 첨부하겠습니다. )
- As-Is
......
......
......
SELECT
ORDR_TRT_NO,
DECODE(ORDR_PRCSS_TRT_STTUS_ID,'2',ORDR_PRCSS_TYPE_ID,'7',ORDR_PRCSS_TYPE_ID,'') AS WRKCSTAT,
DECODE(ORDR_PRCSS_TRT_STTUS_ID,'0',ORDR_PRCSS_TYPE_ID,'1',ORDR_PRCSS_TYPE_ID,'3',ORDR_PRCSS_TYPE_ID,
'4',ORDR_PRCSS_TYPE_ID,'5',ORDR_PRCSS_TYPE_ID,'6',ORDR_PRCSS_TYPE_ID,'8',ORDR_PRCSS_TYPE_ID,'') AS WRKSTAT,
DECODE(ORDR_PRCSS_TRT_STTUS_ID,'2',ORDR_PRCSS_ST_DT,'7',ORDR_PRCSS_ST_DT,TO_DATE('00010101000000', 'yyyymmddhh24miss')) AS WRKCSTATDT,
DECODE(ORDR_PRCSS_TRT_STTUS_ID,'0',ORDR_PRCSS_ST_DT,'1',ORDR_PRCSS_ST_DT,'3',ORDR_PRCSS_ST_DT,
'4',ORDR_PRCSS_ST_DT,'5',ORDR_PRCSS_ST_DT,'6',ORDR_PRCSS_ST_DT,'8',ORDR_PRCSS_ST_DT,TO_DATE('99991231232359', 'yyyymmddhh24miss')) AS WRKSTATDT,
ORDR_PRCSS_TRT_STTUS_ID,
ORDR_PRCSS_ERR_ID
FROM PNA_ORTR_PRCS_DTL
WHERE
OBDNG_ID IN (
)
AND ORDR_PRCSS_ST_DT IS NOT NULL
AND ORDR_PRCSS_TYPE_ID NOT IN ('LORD', 'PHRD')
......
......
......- To-Be
......
......
......
SELECT
ORTR.ORDR_TRT_NO,
DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'2',PRCS.ORDR_PRCSS_TYPE_ID,'7',PRCS.ORDR_PRCSS_TYPE_ID,'') AS WRKCSTAT,
DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'0',PRCS.ORDR_PRCSS_TYPE_ID,'1',PRCS.ORDR_PRCSS_TYPE_ID,'3',PRCS.ORDR_PRCSS_TYPE_ID,
'4',PRCS.ORDR_PRCSS_TYPE_ID,'5',PRCS.ORDR_PRCSS_TYPE_ID,'6',PRCS.ORDR_PRCSS_TYPE_ID,'8',PRCS.ORDR_PRCSS_TYPE_ID,'') AS WRKSTAT,
DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'2',PRCS.ORDR_PRCSS_ST_DT,'7',PRCS.ORDR_PRCSS_ST_DT,TO_DATE('00010101000000', 'yyyymmddhh24miss')) AS WRKCSTATDT,
DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'0',PRCS.ORDR_PRCSS_ST_DT,'1',PRCS.ORDR_PRCSS_ST_DT,'3',PRCS.ORDR_PRCSS_ST_DT,
'4',PRCS.ORDR_PRCSS_ST_DT,'5',PRCS.ORDR_PRCSS_ST_DT,'6',PRCS.ORDR_PRCSS_ST_DT,'8',PRCS.ORDR_PRCSS_ST_DT,TO_DATE('99991231232359', 'yyyymmddhh24miss')) AS WRKSTATDT,
PRCS.ORDR_PRCSS_TRT_STTUS_ID,
PRCS.ORDR_PRCSS_ERR_ID
FROM PNA_ORTR_DTL ORTR
JOIN PNA_ORTR_PRCS_DTL PRCS ON ORTR.ORDR_TRT_NO=PRCS.ORDR_TRT_NO
WHERE
ORTR.OBDNG_ID IN (
'R00432'
)
AND 10 < (
(
SYSDATE - ORTR.ORDR_RCP_DATE
) * 24 * 60
)
AND NVL(ORTR.PRVT_ESS_CIRCUIT_TYPE_CD, '*') <> '3'
AND NVL(ORTR.PROD_ID, '*') NOT IN ('0756', '0513')
AND NOT (
ORTR.PROD_ID = '0757'
AND ORTR.PRVT_ESS_CIRCUIT_TYPE_CD = '2'
)
AND PRCS.ORDR_PRCSS_ST_DT IS NOT NULL
AND PRCS.ORDR_PRCSS_TYPE_ID NOT IN ('LORD', 'PHRD')
AND ORTR.ORDR_TYPE_ID IN ('PB', 'PC')
......
......
......반응형
'개발 > DB' 카테고리의 다른 글
| 쿼리튜닝기 (2) (0) | 2024.01.23 |
|---|---|
| [SQLD] 필기시험 대비 나만의 요약 정리 (3) | 2023.06.06 |