튜닝

쿼리튜닝기 (2) 2024.01.23
쿼리튜닝기 (1) 2023.07.27

쿼리튜닝기 (2)

2024. 1. 23. 22:58

728x90

1. 배경

지난 23년 12월 말에 파트 내에서 개발한 UI화면의 조회과정에서 부하가 발생하였습니다.
이에 따라 인프라팀으로부터 쿼리 튜닝 요청이 왔습니다.

먼저 저는 문제의 쿼리를 실행시키는 화면을 먼저 알아봤습니다.
해당 화면은 조회를 위해 여러 키 값을 입력할 수 있는 구조로 되어 있는데,
이 중, 필수 파라미터는 최대 7일까지 입력할 수 있는 기간 데이터와
최대 1개까지 입력할 수 있는 통신국사였습니다.

해당 화면의 조회쿼리 성능이 나쁘다는 전제가 있어서 필수 파라미터만 키 값으로 넣되,
조회 기간은 1일로 설정하여 조회를 시도해봤습니다.

그 결과, 화면은 바로 멈춰버리고...
모니터링 툴인 파로스상으로 확인해봤을 때, 해당 트랜잭션의 실행은 15~20분정도 지나서야 종료가 되었습니다.
기간을 1일로 잡아도 이정도의 성능을 보이는 것을 보니까... 생각보다 심각하구나라는 걸 인지하게 되었어요.

2. 튜닝 과정

먼저 저는 문제의 쿼리를 파악해봤습니다.
아래 쿼리는 파로스상 가장 오랜시간의 지연을 가진 쿼리입니다.

SELECT count(*) totalCount
FROM (
    SELECT crt.*
    FROM PNA_CIRCUITATHN_RQT_TXN crt,
        TB_OFFICE office,
        PNA_ORTR_TELNO_TXN tel,
    WHERE
        crt.OBDNG_ID=office.OFFICESCODE(+)
        AND crt.ORDR_TRT_NO=tel.ORDR_TRT_NO(+)
        AND tel.CHG_BEFAFT_TYPE_CD(+) = '2'
        AND tel.TEL_NO_TYPE_CD(+) = '1'
        AND crt.OBDNG_ID IN ('R02471')
        AND crt.RQT_DT BETWEEN TO_DATE('20240104' || '000000', 'YYYYMMDDHH24MISS')
        AND TO_DATE('20240104' || '235959', 'YYYYMMDDHH24MISS')
);

위 쿼리에서 조건 키값으로 입력한 통신국사의 코드는 OBDNG_ID이고, 기간 데이터는 RQT_DT컬럼의 데이터로 입력됩니다.
지연의 원인을 파악하기 위해 실행계획을 확인해봤습니다.

Plan hash value: 3635163560

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                               |     1 |    39 |            |          |
|   2 |   NESTED LOOPS OUTER          |                               |     1 |    39 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PNA_CIRCUITATHN_RQT_TXN       |     1 |    25 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | PNA_CIRCUITATHN_RQT_TXN_IX_02 |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PNA_ORTR_TELNO_TXN_PK         |     1 |    14 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$ABC8FBB0
   3 - SEL$ABC8FBB0 / CRT@SEL$2
   4 - SEL$ABC8FBB0 / CRT@SEL$2
   5 - SEL$ABC8FBB0 / TEL@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CRT"."RQT_DT">=TO_DATE(' 2024-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "CRT"."OBDNG_ID"='R02471' AND "CRT"."RQT_DT"<=TO_DATE(' 2024-01-04 23:59:59', 'syyyy-mm-dd 
              hh24:mi:ss'))
       filter("CRT"."OBDNG_ID"='R02471')
   5 - access("CRT"."ORDR_TRT_NO"="TEL"."ORDR_TRT_NO"(+) AND "TEL"."TEL_NO_TYPE_CD"(+)='1' AND 
              "TEL"."CHG_BEFAFT_TYPE_CD"(+)='2')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=0) 
   3 - "CRT"."ORDR_TRT_NO"[VARCHAR2,11]
   4 - "CRT".ROWID[ROWID,10]

그런데... 실행계획상 성능이 나쁘진 않았습니다.
해당 실행계획을 가진 쿼리가 15분 이상을 지연시킨 쿼리라고 하니... 이해가 되질 않았어요.
모니터링 툴이었던 파로스가 쿼리 실행시간을 제대로 못 잡은건지도 의심이 들었습니다.

그래서 실제 실행시켰던 쿼리의 실행계획이 궁금했고, 인프라팀에 문의했습니다.
실행계획 성능과 실제 지연시간과 차이가 발생한 이유로 해당 쿼리에 바인드 변수가 사용되었기 때문일 수도 있다는 답변을 받았습니다.

바인드 변수 쿼리

바인드 변수 쿼리는 변수가 사용된 쿼리입니다. 아래와 같은 형태로 사용됩니다.
SELECT * FROM tb_test WHERE id = :id;
위 쿼리에서 조건절의 :id로 표현된 부분에 여러 id값이 입력되는 바인드 변수입니다.

위 쿼리에서처럼 바인드 변수 쿼리로 실행되지 않고, 아래처럼 상수 형태로 입력되어 실행된다면
쿼리는 한번 사용된 실행계획을 재사용하는 것이 아닌 새로 실행계획을 수립하는 Hard parsing이 진행되어 성능상 비효율이 발생합니다.

SELECT * FROM tb_test WHERE id = 1;
SELECT * FROM tb_test WHERE id = 2;
SELECT * FROM tb_test WHERE id = 3;
SELECT * FROM tb_test WHERE id = 4;
SELECT * FROM tb_test WHERE id = 5;

그렇기 때문에 성능상 이점을 위해서는 바인드변수 쿼리를 사용해야 합니다.
바인드변수 쿼리를 사용하게 되면, 바인드 변수의 값만 바꿔서 쿼리를 실행시킬 때, 해당 쿼리에 맞는 이미 실행되었던 실행계획을 재사용하여 실행시키는 Soft parsing이 진행되기 때문에 더욱 빠르게 실행시킬 수 있습니다.

하지만 맹점이 있었습니다.
바인드 변수에 해당하는 조건의 값을 예측하기가 어려워 잘못된 실행계획을 수립할 수도 있습니다.
본 글에서 언급한 쿼리의 비효율도 해당 맹점으로 인해 발생했습니다.
그리고 아래는 바인드 변수 쿼리로 수립된 실행계획입니다.

Plan hash value: 3105806070

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                               |     1 |    39 |  5232   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                |                               |     1 |    39 |            |          |
|*  2 |   FILTER                       |                               |       |       |            |          |
|   3 |    NESTED LOOPS OUTER          |                               |    75 |  2925 |  5232   (1)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| PNA_CIRCUITATHN_RQT_TXN       |    67 |  1675 |  5231   (1)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | PNA_CIRCUITATHN_RQT_TXN_IX_03 | 26649 |       |    33   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | PNA_ORTR_TELNO_TXN_PK         |     1 |    14 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$ABC8FBB0
   4 - SEL$ABC8FBB0 / CRT@SEL$2
   5 - SEL$ABC8FBB0 / CRT@SEL$2
   6 - SEL$ABC8FBB0 / TEL@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_DATE(:3||'235959','YYYYMMDDHH24MISS')>=TO_DATE(:2||'000000','YYYYMMDDHH24MISS'))
   4 - filter("CRT"."RQT_DT">=TO_DATE(:2||'000000','YYYYMMDDHH24MISS') AND 
              "CRT"."RQT_DT"<=TO_DATE(:3||'235959','YYYYMMDDHH24MISS'))
   5 - access("CRT"."OBDNG_ID"=:1)
   6 - access("CRT"."ORDR_TRT_NO"="TEL"."ORDR_TRT_NO"(+) AND "TEL"."TEL_NO_TYPE_CD"(+)='1' AND 
              "TEL"."CHG_BEFAFT_TYPE_CD"(+)='2')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   3 - (#keys=0) 
   4 - "CRT"."ORDR_TRT_NO"[VARCHAR2,11]
   5 - "CRT".ROWID[ROWID,10]

인덱스 스캔

위 쿼리의 비효율은 잘못된 인덱스를 스캔하는 실행계획을 수립했기 때문에 발생하였습니다.
실행계획상으로 볼 수 있다시피 상수쿼리는 PNA_CIRCUITATHN_RQT_TXN_IX_02 인덱스를 스캔한 반면에,
바인드변수 쿼리는 PNA_CIRCUITATHN_RQT_TXN_IX_03 인덱스를 스캔했어요. 3번 인덱스를 스캔한 시점에서 쿼리 실행에 많은 비용이 발생하게 됩니다.

튜닝 대상의 쿼리에는 여러개의 인덱스가 생성되어 있는데,
그 중 대표적으로 봐야할 두 개의 인덱스가 있습니다.

CREATE INDEX CUI_OWN.PNA_CIRCUITATHN_RQT_TXN_IX_02 ON CUI_OWN.PNA_CIRCUITATHN_RQT_TXN(RQT_DT, OBDNG_ID, RQT_TRT_RESLT_SBST)
CREATE INDEX CUI_OWN.PNA_CIRCUITATHN_RQT_TXN_IX_03 ON CUI_OWN.PNA_CIRCUITATHN_RQT_TXN(OBDNG_ID, ORDR_TYPE_ID, ORDR_TRT_TYPE_SEQ)

쿼리에서 확인할 수 있는 것처럼 조건절에는 OBDNG_ID컬럼과 RQT_DT컬럼이 있고,
해당 컬럼이 모두 있는 2번 인덱스를 스캔해야 효율적인 쿼리 실행이 가능합니다.
하지만 바인드변수 쿼리는 OBDNG_ID컬럼만 있는 3번 인덱스를 스캔했는데요.

이러한 잘못된 스캔의 원인으로 바인드변수 쿼리 실행시에는 변수에 어떤 값이 입력될지 몰라서 Optimizer가 조회할 데이터의 양을 예측하지 못하는데 있다고 추측됩니다.
OBDNG_ID컬럼의 경우, in절이라서 변수의 갯수로 어느정도 예측을 할 수 있지만,
RQT_DT컬럼의 경우는 예측이 안되어 OBDNG_ID가 선행컬럼으로 있는 3번 인덱스를 스캔하게 된 것이죠.

해결방안

힌트 추가

첫번째 개선방안은 인덱스 힌트를 추가하여 특정 인덱스를 강제로 스캔하는 방안이었습니다.
아래는 인덱스 힌트가 추가된 쿼리입니다.

SELECT count(*) totalCount
FROM (
    SELECT /*+ index (crt PNA_CIRCUITATHN_RQT_TXN_IX_02) */
        crt.*
    FROM PNA_CIRCUITATHN_RQT_TXN crt,
        TB_OFFICE office,
        PNA_ORTR_TELNO_TXN tel,
    WHERE
        crt.OBDNG_ID=office.OFFICESCODE(+)
        AND crt.ORDR_TRT_NO=tel.ORDR_TRT_NO(+)
        AND tel.CHG_BEFAFT_TYPE_CD(+) = '2'
        AND tel.TEL_NO_TYPE_CD(+) = '1'
        AND crt.OBDNG_ID IN ('R02471')
        AND crt.RQT_DT BETWEEN TO_DATE('20240104' || '000000', 'YYYYMMDDHH24MISS')
        AND TO_DATE('20240104' || '235959', 'YYYYMMDDHH24MISS')
);

힌트는 아래와 같은 형태로 추가되어 쿼리 실행시에 특정 인덱스를 고정적으로 스캔할 수 있도록 강제할 수 있습니다.
/*+ index (crt PNA_CIRCUITATHN_RQT_TXN_IX_02) */
위 인덱스 힌트는 사전에 검증된 성능의 2번 인덱스를 스캔하기 위한 구문입니다.

하지만 힌트가 추가되었을 때의 문제는 바인드 변수에 값이 랜덤하게 입력되는데,
선행컬럼이 OBDNG_ID로 구성되어 있는 인덱스를 스캔하는 것이 더 성능이 좋은 케이스도 분명 존재한다는 것입니다. 3번 인덱스처럼 말이죠.
하지만 3번 인덱스에는 RQT_DT컬럼이 없죠.

인덱스 추가

위에서 언급한 것처럼 바인드 변수에 입력되는 값에 따라 OBDNG_ID 선행컬럼의 인덱스를 스캔하는 것이 더 효율적일 수도 있습니다.
이러한 경우를 대비해서 아래 6번 인덱스를 추가하기로 결정했습니다.

CREATE INDEX CUI_OWN.PNA_CIRCUITATHN_RQT_TXN_IX_06 ON CUI_OWN.PNA_CIRCUITATHN_RQT_TXN(OBDNG_ID, RQT_DT, RQT_TRT_RESLT_SBST)

기존 검증된 성능의 2번 인덱스와 동일한 컬럼 구성의 6번 인덱스를 생성하되, 선행컬럼을 RQT_DT에서 OBDNG_ID로 바꾸어
OBDNG_ID 선행컬럼의 인덱스를 스캔하는 것으로 Optimizer가 실행계획을 수립해도 RQT_DT 컬럼도 인덱스로 같이 스캔할 수 있게끔 할 수 있습니다.

결과

아래는 인덱스 적용 후의 쿼리 실행계획입니다.

Plan hash value: 1313069470

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                               |     1 |    39 |   127   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                |                               |     1 |    39 |            |          |
|*  2 |   FILTER                       |                               |       |       |            |          |
|   3 |    NESTED LOOPS OUTER          |                               |    75 |  2925 |   127   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PNA_CIRCUITATHN_RQT_TXN       |    67 |  1675 |   126   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | PNA_CIRCUITATHN_RQT_TXN_IX_06 |   120 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | PNA_ORTR_TELNO_TXN_PK         |     1 |    14 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$ABC8FBB0
   4 - SEL$ABC8FBB0 / CRT@SEL$2
   5 - SEL$ABC8FBB0 / CRT@SEL$2
   6 - SEL$ABC8FBB0 / TEL@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_DATE(:3||'235959','YYYYMMDDHH24MISS')>=TO_DATE(:2||'000000','YYYYMMDDHH24MISS'))
   5 - access("CRT"."OBDNG_ID"=:1 AND "CRT"."RQT_DT">=TO_DATE(:2||'000000','YYYYMMDDHH24MISS') AND 
              "CRT"."RQT_DT"<=TO_DATE(:3||'235959','YYYYMMDDHH24MISS'))
   6 - access("CRT"."ORDR_TRT_NO"="TEL"."ORDR_TRT_NO"(+) AND "TEL"."TEL_NO_TYPE_CD"(+)='1' AND 
              "TEL"."CHG_BEFAFT_TYPE_CD"(+)='2')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   3 - (#keys=0) 
   4 - "CRT"."ORDR_TRT_NO"[VARCHAR2,11]
   5 - "CRT".ROWID[ROWID,10]

3번 인덱스가 아닌 6번 인덱스를 스캔하는 것을 실행계획상 확인하였고,
UI에서 조회시에도 기존에는 기간을 하루로 설정하여 조회해도 15분 이상이 소요되었는데,
인덱스가 추가 반영된 이후에는 기간을 최대 7일까지 설정해도 조회시간이 최대 3초 이내로 마무리된 것까지 확인하여
성능이 크게 개선된 것을 확인하였습니다.

Oracle 쿼리 처리 과정

https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL175
Oracle 공식 document에 작성되어 있는 가이드를 참고하여 Oracle 쿼리의 처리 과정과 관련 개념에 대해서 간략히 짚고 넘어가겠습니다.

위 사진은 Oracle SQL 처리순서를 표현한 도식입니다.

Application에 의해 SQL이 실행되면 DB서버에서는 PGA(Program Global Area) 내부에 private SQL area를 생성하고, 이를 가리키는 포인터인 Cursor를 생성하여 실행된 SQL을 파싱할 준비를 합니다.

PGA (Program Global Area) : 각각의 Application이 독자적으로 사용하는 Oracle 메모리 영역
private SQL area : 실행된 SQL과 처리를 위한 정보가 담기는 PGA 내부의 메모리 영역, 처리를 위한 정보로 바인드 변수 값과 쿼리 실행 상태 등의 정보가 있음.

Syntax Check

실행된 SQL이 문법상 유효한지 확인하는 과정입니다.
예를들어, 아래 쿼리는 해당 과정에서 오류로 반환될 것입니다. (FORM을 FROM으로 수정 필요)

SELECT * FORM employees;

Semantic Check

실행된 SQL이 문법상 유효하다면, 이제 의미상으로 유효한지 본 과정에서 확인하게 됩니다.
예를들어, 아래 쿼리가 실행된다고 가정해봅시다.

SELECT * FROM nonexistent_table;

위에서 실행된 쿼리의 테이블 nonexistent_table이 실제로 존재하지 않는 테이블이라고 가정했을 때,
위 실행쿼리는 의미상 유효하지 않으므로 본 과정에서 에러를 반환하게 됩니다.

Shared Pool Check

쿼리 성능에 크게 영향을 미치는 과정입니다.
unique한 각각의 SQL에는 해시값의 SQL_ID가 있습니다. 동일한 SQL은 동일한 SQL_ID를 갖죠.

본 과정에서는 실행된 쿼리의 SQL_ID가 Shared SQL Area에 존재하는지 확인하게 되고,
동일한 SQL_ID가 존재하지 않는다면 Hard Parsing을 진행하게 됩니다.

Shared SQL Area : 각 PGA (Program Global Area)에서 실행된 SQL과 실행계획 등과 같은 정보를 공유하는 메모리 공간

동일한 SQL_ID가 존재한다면, 추가적인 semantic check와 environment check를 거칩니다.
실행된 SQL이 동일한 SQL_ID를 가진 SQL과 문법상 같은 SQL을 가졌다고 하더라도 의미상/환경상 차이를 보일 수 있기 때문이죠.
그리고 위의 추가적인 검증절차를 통과하지 못해도 Hard Parsing을 진행하게 됩니다.

모든 검증절차를 통과한 SQL만이 Soft Parsing을 진행할 수 있게 됩니다.

Soft Parsing : 기존 실행되었던 실행계획을 재사용하여 쿼리를 실행
Hard Parsing : Optimization 단계를 거쳐 새로운 실행계획을 수립하여 쿼리를 실행

SQL Optimization

본 과정에서 새로운 실행계획이 수립됩니다.
SQL engine이 바이너리 형태의 초기 실행계획을 row source generator에게 전달하면,
row source generator는 전달받은 바이너리 형태의 초기 실행계획을 row source tree형태로 생성하여
우리가 흔히 알고 있는 실행계획을 생성해줍니다.

그리고 SQL engine이 다시 생성된 실행계획을 실행시키는 것으로 SQL의 처리가 완료됩니다.

위에서 참고한 Oracle 가이드에는 실제로 최적화가 어떻게 이루어져 최적의 실행계획을 추출하는지에 대한 내용이 없어서...
관련 내용은 추가적인 서핑이 필요합니다.
다음 글에서는 SQL engine이 어떤 정보를 기반으로 어떻게 최적화를 진행하는지, 어떻게 쿼리를 작성하고 튜닝하는 것이 바람직한지 알아보겠습니다.

'개발 > DB' 카테고리의 다른 글

쿼리튜닝기 (1) (0)	2023.07.27
[SQLD] 필기시험 대비 나만의 요약 정리 (3)	2023.06.06

쿼리튜닝기 (1)

2023. 7. 27. 00:03

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

PREV 이전 1 NEXT 다음

woongs dogfoot

튜닝

쿼리튜닝기 (2)

1. 배경

2. 튜닝 과정

바인드 변수 쿼리

인덱스 스캔

해결방안

힌트 추가

인덱스 추가

결과

Oracle 쿼리 처리 과정

Syntax Check

Semantic Check

Shared Pool Check

SQL Optimization

'개발 > DB' 카테고리의 다른 글

쿼리튜닝기 (1)

1. Top 쿼리 튜닝기

A. 비효율 성능의 원인

B. 성능개선

인덱스 추가

불필요 JOIN문 제거

국사조건 추가

C. 배운 점

2. 번외

'개발 > DB' 카테고리의 다른 글

+ Recent posts

티스토리툴바