개발/DB

쿼리튜닝기 (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

[SQLD] 필기시험 대비 나만의 요약 정리

2023. 6. 6. 17:39

728x90

SQLD 시험 준비

모델링의 정의

정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법
현실세계의 데이터에 대해 약속된 표기법에 의해 표현하는 과정
데이터베이스를 구축하기 위한 분석/설계의 과정

모델링의 특징

추상화 : 현실세계를 일정한 형식에 맞추어 표현
단순화 : 복잡한 현실세계를 약속된 규약에 의해 제한된 표기법이나 언어로 표현하여 쉽게 이해할 수 있도록 하는 개념
명확화 : 누구나 이해하기 쉽게 하기 위해 대상에 대한 애매모호함을 제거하고 정확하게 현상을 기술

모델링의 관점

데이터 관점 : 업무가 어떤 데이터와 관련이 있는지 또는 데이터 간의 관계는 무엇인지?
프로세스 관점 : 실제하고 있는 업무는 무엇인지 또는 무엇을 해야하는지?
데이터와 프로세스의 상관 관점 : 업무가 처리하는 일의 방법에 따라 데이터는 어떻게 영향을 받고 있는지?

모델링이 중요한 이유

파급효과 : 데이터 구조 변경 시, 영향도가 큼
복잡한 정보 요구 사항의 간결한 표현 : 데이터 모델은 건축물로 비유하자면 설계 도면
데이터 품질 : 아래는 유의할 점
1. 중복 : 여러 장소에 같은 정보 저장 x
2. 비유연성 : 데이터의 정의를 데이터의 사용 프로세스와 분리함으로서 영향도 줄임
3. 비일관성 : ex. 신용상태에 대한 갱신 없이 고객의 납부 이력 정보를 갱신 => 납부이력정보와 신용상태는 동시에 갱신되어야 함

데이터 모델링의 3단계 : 아래로 내려갈수록 구체적, 위로 올라갈수록 추상적 => 보통 아래 순서대로 데이터 모델링이 진행됨

개념적 데이터 모델링 : 추상화 수준이 높고 업무 중심적이고 포괄적인 수준의 모델링 진행. 전사적 데이터 모델링, EA 수립 시 많이 이용
논리적 데이터 모델링 : 시스템으로 구축하고자 하는 업무에 대해 Key, 속성, 관계 등을 정확하게 표현, 재사용성이 높음
물리적 데이터 모델링 : 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계

데이터베이스 3단계 구조 : 화면과 데이터베이스 간의 독립성 유지를 위함

외부 스키마 : 개개 사용자 단계로서 개개 사용자가 보는 개인적 DB 스키마
개념 스키마 : 모든 사용자 관점을 통합한 조직 전체의 DB를 기술하는 것
내부 스키마 : 물리적 장치에서 데이터가 실제적으로 저장되는 방법을 표현하는 스키마

데이터 모델링의 세 가지 요소

업무가 관여하는 어떤 것 (Thins)
어떤 것이 가지는 성격 (Attributes)
업무가 관여하는 어떤 것 간의 관계 (Relationships)

ERD 작업순서

엔터티를 그린다.
엔터티를 적절하게 배치한다.
엔터티 간 관계를 설정한다.
관계명을 기술한다.
관계의 참여도를 기술한다.
관계의 필수여부를 기술한다.

엔터티의 특징

반드시 해당 업무에서 필요하고 관리하고자 하는 정보이어야 한다.
유일한 식별자에 의해 식별이 가능해야 한다.
영속적으로 존재하는 인스턴스의 집합이어야 한다.
엔터티는 업무 프로세스에 의해 이용되어야 한다.
엔터티는 반드시 속성이 있어야 한다.
엔터티는 다른 엔터티와 최소 한 개 이상의 관계가 있어야 한다.

엔터티의 관계가 필요 없는 경우

통계를 위한 엔터티
코드를 위한 엔터티
시스템 처리 시 내부 필요에 의한 엔터티

엔터티의 분류

유무형에 따른 분류
1. 유형엔터티 : 물리적인 형태가 있고 안정적이며 지속적으로 활용되는 엔터티 (ex. 사원, 물품, 강사)
2. 개념엔터티 : 물티적인 형태가 없지만 관리해야 할 개념정 정보로 구분되는 엔터티 (ex. 조직, 보험상품)
3. 사건엔터티 : 업무를 수행함에 따라 발생하는 엔터티 (ex. 주문, 청구, 미납)
발생시점에 따른 분류
1. 기본엔터티 : 다른 엔터티와의 관계에 의한 것이 아닌 독립적으로 생성이 가능하고 타 엔터티의 부모 역할이 되는 엔터티 (ex. 사원, 부서, 고객, 상품, 자재)
2. 중심엔터티 : 기본엔터티로부터 발생되고 업무에서 중심적인 역할을 하는 엔터티 (ex. 계약, 사고, 예금원장, 청구, 주문, 매출)
3. 행위엔터티 : 두 개 이상의 부모엔터티로부터 발생하고, 자주 내용이 바뀌거나 데이터 양이 증가하는 엔터티 (ex. 주문목록, 사원변경이력)

속성 : 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더이상 분리되지 않는 최소의 데이터 단위
속성의 특징

반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야 한다.
정규화 이론에 근거하여 정해진 주식별자에 함수적 종속성을 가져야 한다.
하나의 속성은 한 개의 값만을 가진다.

속성의 특성에 따른 분류

기본속성 : 업무로부터 추출한 모든 속성
설계속성 : 업무상 필요한 데이터 이외에 데이터 모델링 및 업무를 규칙화하기 위해 새로 만들거나 변형한 속성 (ex. 일련번호)
파생속성 : 다른 속성에 영향을 받아 발생하는 속성, 보통 계산된 값들이 이에 해당, 가급적 적게 정의하는 것이 좋음.

관계 정의 시, 체크할 사항

두 개의 엔터티 사이에 관심 있는 연관규칙이 존재하는가?
두 개의 엔터티 사이에 정보의 조합이 발생하는가?
업무기술서, 장표에 관계연결에 대한 규칙이 서술되어 있는가?
업무기술서, 장표에 관계연결을 가능하게 하는 동사가 있는가?

식별자의 특징

유일성 : 주식별자에 의해 엔터티 내에 모든 인스턴스들이 유일하게 구분되어야 한다.
최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.
불변성 : 지정된 주식별자의 값은 자주 변하지 않는 것이어야 한다.
존재성 : 주식별자가 지정이 되면 반드시 값이 들어와야 한다.

식별자 분류

대표성 여부
1. 주식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있고, 타 엔터티와 참조관계를 연결할 수 있는 식별자
2. 보조식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있지만, 대표성을 가지지 못해 참조관계 연결을 못하는 식별자
스스로 생성 여부
1. 내부식별자 : 엔터티 내부에서 스스로 만들어지는 식별자
2. 외부식별자 : 타 엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자
속성 수
1. 단일식별자 : 하나의 속성으로 구성된 식별자
2. 복합식별자 : 둘 이상의 속성으로 구성된 식별자
대체 여부
1. 본질식별자 : 업무에 의해 만들어지는 식별자
2. 인조식별자 : 업무적으로 만들어지지는 않지만 원조식별자가 복잡한 구성을 갖고 있기 때문에 인위적으로 만든 식별자

주식별자 도출 기준

해당 업무에서 자주 이용되는 속성을 주식별자로 지정
명칭, 내역 등과 같이 이름으로 기술되는 것들은 가능하면 주식별자로 지정하지 않는다.
복합으로 주식별자로 구성할 경우 너무 많은 속성이 포함되지 않도록 한다.

식별자관계 : 부모로부터 받은 식별자를 자식엔터티의 주식별자로 이용하는 경우는 Null값이 오면 안되므로 반드시 부모엔터티가 생성되어야 자기 자신의 엔터티가 생성되는 관계
- 문제점 : PK수 증가
비식별자관계 : 부모엔터티로부터 속성을 받았지만 자식엔터티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용하는 관계
- 문제점 : 부모엔터티의 PK조건으로 자식엔터티의 속성을 조회해야 할 때, 불필요한 join이 들어감
제1정규형 : 모든 속성은 반드시 하나의 값을 가져야 한다. (Ex. 연락처 = 02-123-4567, 010-1234-5678 => x)(ex. Entity : 주문번호/상품번호1/상품명1/상품번호2/상품명2/고객번호/고객명 => x)
- 부모-자식 엔터티 관계형성으로 해결, 자식엔터티로 생성
제2정규형 : 엔터티의 일반속성은 주식별자 전체에 종속적이어야 한다. (Ex. 상품명은 오직 주식별자인 상품번호에 의해서만 결정된다, 주식별자가 상품번호 1개임을 가정)
- M:M 관계형성으로 해결
제3정규형 : 엔터티 일반속성 간에는 서고 종속적이지 않는다. (Ex. 고객번호는 주문번호에 종속, 고객명은 고객번호에 종속=>고객명은 주문번호에 종속 ==> 이행 종속성이므로 제3정규형 위반)
- 부모-자식 엔터티 관계형성으로 해결, 위 예제에서는 고객을 부모엔터티로 생성
반정규화 : 성능을 위해 데이터 중복을 허용, 정규화의 반대
- 성능이 향상될 수 있는 경우 : 잦은 조회 쿼리의 join 횟수가 많은 경우
- 성능이 저하될 수 있는 경우 : 불필요한 UPDATE로직이 추가될 수 있음
계층형 데이터 모델 : 엔터티의 인스턴스간 계층이 존재할 때의 데이터 모델

데이터 조회 시, 셀프조인 발생

Null 속성의 이해

Null 값의 연산은 언제나 Null
집계함수는 Null 값을 제외하고 처리한다.

SQL의 종류

데이터 조작어 (DML) : SELECT, INSERT, UPDATE, DELETE
데이터 정의어 (DDL) : CREATE, ALTER, DROP, RENAME
데이터 제어어 (DCL) : GRANT, REVOKE
트랜잭션 제어어 (TCL) : COMMIT, ROLLBACK

합성연산자 : || 또는 CONCAT(string1, string2)
단일행 함수의 종류

문자형 함수 : LOWER, UPPER, ASCII, CHR/CHAR, CONCAT, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM
숫자형 함수 : ABS, SIGN, MOD, CEIL/CEILING, FLOOR, ROUND, TRUNC, SIN, COS, TAN, EXP, POWER, SQRT, LOG, LN
날짜형 함수 : SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, ‘YYYY’|’MM’’|DD’))/YEAR|MONTH|DAY
변환형 함수 : (CAST, TO_NUMBER, TO_CHAR, TO_DATE)/(CAST, CONVERT)
NULL 관련 함수 : NVL/ISNULL, NULLIF, COALESCE

ASCII <-> CHR/CHAR
LTRIM : 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거 (디폴트는 공백)
TRIM : 문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자를 제거 (디폴트는 both)
MOD : 숫자를 나누어 나머지 값을 리턴 (%로 대체 가능)
SIGN : 숫자가 양수인지, 음수인지 0인지를 구별
TRUNC : 숫자를 소수 m자리에서 잘라서 버림 (디폴트는 0)
POWER : 숫자의 거듭제곱 값을 리턴
EXTRACT/DATEPART : 날짜 데이터에서 연월일 데이터를 출력
CASE 표현

CASE (표현식) WHEN 기준값1 THEN 값1 WHEN 기준값2 THEN 값2 ELSE 디폴트값 END
Oracle 한정 : DECODE(표현식, [기준값1, 값1, 기준값2, 값2, … , 디폴트값]) : 표현식이 기준값1이면 값1을, 기준값2이면 값2를 리턴하고, 부합하는 기준값이 없을 경우, 디폴트값 리턴

NULLIF(식1, 식2) : 식1이 식2의 결과와 같을 경우 null, 다를 경우 식1 리턴
연산자의 우선순위

괄호
비교 연산자, SQL 연산자
NOT 연산자
AND
OR

집계함수의 종류

COUNT(*) : NULL 값을 포함한 행의 수를 출력
COUNT(표현식) : 표현식의 값이 NULL 값인 것을 제외한 행 수를 출력
STDDEV : 표준 편차를 출력
VARIANCE/VAR : 분산을 출력

집계함수는 WHERE 절에 올 수 없다.
GROUP BY는 NULL을 무시한다.
GROUP BY 보다 WHERE 절이 먼저 수행된다.
HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
SELECT 문장 실행 순서

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

EQUI JOIN : 두 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법
Non EQUI JOIN : 두 개의 테이블 간에 논리적인 연관 관계는 갖고 있으나, 칼럼 값들이 서로 일치하지 않는 경우에 사용 (등호가 아닌 부등호나 BETWEEN 사용)
OUTER JOIN : 조인 조건이 안맞아도 데이터를 조회하려고 할 때 사용되는 방법 (LEFT, RIGHT가 있음, 값이 없을 경우 NULL)
FROM 절의 JOIN 형태

INNER JOIN : 조인 조건을 만족하는 행들만 반환
NATURAL JOIN : 두 테이블 간에 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행
USING 조건절 : NATURAL JOIN에서 USING 조건절을 활용하여 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 수행
ON 조건절 : JOIN 조건 설정, 칼럼명이 달라도 JOIN 가능
CROSS JOIN : 두 테이블간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합 (M * N 건의 데이터 조합 발생)
OUTER JOIN

동작하는 방식에 따른 서브 쿼리 분류

비연관 서브 쿼리 : 서브 쿼리가 메인 쿼리 컬럼을 갖고 있지 않는 형태의 서브 쿼리다. 메인 쿼리에 값을 제공하기 위한 목적으로 주로 사용한다.
연관 서브 쿼리 : 서브 쿼리가 메인 쿼리 칼럼을 갖고 있는 형태의 서브 쿼리다. 일반적으로 메인 쿼리가 먼저 수행돼 읽혀진 데이터를 서브 쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용한다. 서브 쿼리 내에 메인 쿼리 칼럼이 사용된 서브 쿼리

반환되는 데이터의 형태에 따른 서브 쿼리 분류

Single Row 서브 쿼리 : 서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리, 단일 행 비교 연산자와 함께 사용 (ex. =, < 등)
Multi Row 서브 쿼리 : 서브 쿼리의 실행 결과가 여러 건인 서브 쿼리, 다중 행 비교 연산자와 함께 사용 (ex. In, all, exists 등)
Multi Column 서브 쿼리 : 서브 쿼리의 실행 결과로 여러 칼럼을 반환

다중 행 비교 연산자

IN : 서브 쿼리의 결과에 존재하는 임의의 값과 동일한 조건
ALL : 서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건
ANY : 서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건
EXISTS : 서브 쿼리의 결과가 존재하는지 여부를 확인하는 조건

뷰 사용의 장점

독립성 : 테이블 구조가 변경돼도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
보안성 : 숨기고 싶은 정보는 빼고 생성하여 사용자에게 정보를 감출 수 있다.

집합연산자

UNION : 개별 SQL 문의 결과에 대해 합집합 연산을 수행
UNION ALL : 개별 SQL 문의 결과에 대해 합집합 연산을 수행하며, 중복된 행도 그대로 표시
INTERSECT : 개별 SQL 문의 결과에 대해 교집합 연산을 수행
EXCEPT : 개별 SQL 문의 결과에 대해 차집합 연산을 수행

ROLLUP : GROUP BY 칼럼의 GROUP 별 집계 (TOTAL) 수행 및 GROUP 정렬, 상세 칼럼도 정렬이 필요할 경우, ORDER BY 도 병행 사용
GROUPING : 소계가 계산된 결과에는 1이 표시됨, 그렇지 않은 경우는 0이 표시됨
GROUPING SETS : GROUP BY 모든 칼럼에 대해 GROUPING 수행, 칼럼 순서가 바껴도 조회결과는 같음 (ex. GROUP BY GROUPING SETS (A, B) : count(A) + count(B) 개의 칼럼 조회)
WINDOW FUNCTION SYNTAX : SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절]) FROM 테이블명;

ARGUMENTS : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
ORDER BY 절 : 어떤 항목에 대해 순위를 지정할지 ORDER BY 절을 기술한다.
WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

RANK : ORDER BY를 포함한 QUERY 문에서 특정 항목에 대한 순위를 구하는 함수, PARTITION 포함 시, 특정 컬럼별로 RANK가 지정됨
DENSE_RANK : RANK 함수와 유사하나 동일한 순위를 하나의 건수로 취급
ROW_NUMBER : RANK나 DENSE_RANK 함수와는 다르게 동일한 값이라도 고유한 순위를 부여
FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값 (SQL_SERVER (x))
LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값 (SQL_SERVER (x))
LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값 (SQL_SERVER (x))
LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값 (SQL_SERVER (x))
RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있음 (SQL_SERVER (x))
PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 해, 값이 아닌 행의 순서별 백분율을 구한다. (SQL_SERVER (x))
CUME_DIST : 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. 결과 값은 > 0 & <= 1 (SQL_SERVER (x))
NTILE : 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과, N개씩 그룹이 나누어짐
TOP (Expression) [PERCENT] [WITH TIES] : SQL Server 한정

Expression : 반환할 행 수를 지정하는 숫자
PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환
WITH TIES : ORDER BY 절이 지정된 경우에만 사용, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정 가능

ROW LIMITTING절 (ORDER BY 절 다음에 기술)

Syntax1 : [OFFSET offset {ROW | ROWS}]
Syntax2 : [FETCH {FIRST | NEXT} [{row count | percent PERCENT}] {ROW | ROWS} {ONLY | WITH TIES}]
OFFSET offset : 건너뛸 행의 개수를 지정
FETCH : 반환할 행의 개수나 백분율을 지정
ONLY : 지정된 행의 개수나 백분율만큼 행을 반환
WITH TIES : 마지막 행에 대한 동순위를 포함해서 반환

Oracle 계층형 질의
SELECT …
FROM 테이블
WHERE condition
AND condition
START WITH condition
AND condition
CONNECT BY [NOCYCLE] condition
AND condition
[ORDER SIBLINGS BY column, column, …]

START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문
CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문
PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정, (FK) = PRIOR (PK) 형태를 사용하면 부모 데이터에서 자식 데이터 방향으로 전개하는 순방향 전개, (PK) = PRIOR (FK) 형태를 사용하면 반대로 자식 데이터에서 부모 데이터 방향으로 전개하는 역방향 전개
NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타나는 사이클이 발생 시, 런타임 오류가 발생. NOCYCLE을 추가하면 오류를 발생시키지 않고 사이클이 발생한 이후의 데이터를 전개하지 않는다.
ORDER SIBLINGS BY : 형제 노드 사이에서 정렬을 수행
WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출

계층형 질의에서 사용되는 가상 칼럼

LEVEL : 루트 데이터이면 1, 그 하위 데이터이면 2다. 리프(Leaf) 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0이다.
CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0

계층형 질의에서 사용되는 함수

SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시

PIVOT절 : 행을 열로 회전
UNPIVOT절 : 열을 행으로 회전
정규표현식 POSIX 연산자

. : 모든 문자와 일치 (newline 제외)
| : 대체 문자를 구분
\ : 다음 문자를 일반 문자로 취급
^ : 문자열의 시작
$ : 문자열의 끝
? : 0회 또는 1회 일치 (greedy : 패턴 최소 일치)
?? : 0회 또는 1회 일치 (nongreedy : 패턴 최대 일치)
* : 0회 또는 그 이상의 횟수로 일치 (greedy : 패턴 최소 일치)
*? : 0회 또는 그 이상의 횟수로 일치 (nongreedy : 패턴 최대 일치)
+ : 1회 또는 그 이상의 횟수로 일치 (greedy : 패턴 최소 일치)
+? : 1회 또는 그 이상의 횟수로 일치 (nongreedy : 패턴 최대 일치)
{m} : m회 일치 (greedy : 패턴 최소 일치)
{m}? : m회 일치 (nongreedy : 패턴 최대 일치)
{m,} : 최소 m회 일치 (greedy : 패턴 최소 일치)
{m,}? : 최소 m회 일치 (nongreedy : 패턴 최대 일치)
{,m} : 최대 m회 일치 (greedy : 패턴 최소 일치)
{,m}? : 최대 m회 일치 (nongreedy : 패턴 최대 일치)
{m,n} : 최소 m회, 최대 n회 일치 (greedy : 패턴 최소 일치)
{m,n}? : 최소 m회, 최대 n회 일치 (nongreedy : 패턴 최대 일치)
(expr) : 괄호 안의 표현식을 하나의 단위로 취급
[char…] : 문자 리스트 중 한 문자와 일치
[^char…] : 문자 리스트에 포함되지 않은 한 문자와 일치
[-] : [0-9] [a-z] [A-Z] [a-zA-Z] [0-9a-zA-Z] [0-9a-fA-F]
\d : 숫자
\D : 숫자가 아닌 모든 문자
\w : 숫자와 영문자(underbar 포함)
\W : 숫자와 영문자가 아닌 모든 문자(underbar 제외)
\s : 공백 문자
\S : 공백 문자가 아닌 모든 문자

REGEXP_SUBSTR : 문자열에서 일치하는 패턴을 반환
REGEXP_LIKE : 문자열이 패턴과 일치하면 TRUE, 아니면 FALSE 반환
REGEXP_REPLACE : 일치하는 패턴을 replace_string으로 변경한 문자로 반환
REGEXP_SUBSTR : 일치하는 패턴의 문자만을 반환
REGEXP_INSTR : 일치하는 패턴의 시작 위치를 정수로 반환
REGEXP_COUNT : 일치하는 패턴의 횟수를 반환
MERGE : 새로운 행을 입력하거나, 기존 행을 수정하는 작업을 한번에 할 수 있음
트랜잭션의 특성

원자성 : 트랜잭션에서 저으이된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.
일관성 : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 돼 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성 : 트랜잭션이 성공적으로 수행되면, 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

COMMIT or ROLLBACK 이전 상태

이전 상태로 복구 가능
현재 사용자는 SELECT 문장으로 결과를 확인할 수 있다.
다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
변경된 행은 잠금(LOCKING)이 설정돼서 다른 사용자가 변경할 수 없다.

SQL Server에서의 트랜잭션

AUTO COMMIT : SQL Server의 기본 방식, DBMS가 트랜잭션을 컨트롤하는 방식, 명령어가 성공적으로 수행되면 자동으로 COMMIT 수행, 오류가 발생하면 자동으로 ROLLBACK 수행
암시적 트랜잭션 : Oracle과 같은 방식, 트랜잭션의 시작은 DBMS가 처리, 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK 처리
명시적 트랜잭션 : 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식

SAVEPOINT : 저장점(SAVEPOINT)를 지정하면 ROLLBACK할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.
제약조건의 종류

PRIMARY KEY : 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다.
UNIQUE : 테이블에 저장된 행 데이터를 고유하기 식별하기 위한 고유키를 정의한다.
NOT NULL : NULL값의 입력을 금지한다.
CHECK : 입력할 수 있는 값의 범위 등을 제한한다.
FOREIGN KEY : 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다.

Oracle에서 제공하는 유저들

SCOTT : Oracle 테스트용 샘플 계정
SYS : 백업 및 복구 등 데이터베이스 상의 모든 관리 기능을 수행할 수 있는 최상위 관리자 계정
SYSTEM : 백업, 복구 등 일부 관리 기능을 제외한 모든 시스템 권한을 부여받은 DBA 계정

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

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

PREV 이전 1 NEXT 다음

woongs dogfoot

개발/DB

쿼리튜닝기 (2)

1. 배경

2. 튜닝 과정

바인드 변수 쿼리

인덱스 스캔

해결방안

힌트 추가

인덱스 추가

결과

Oracle 쿼리 처리 과정

Syntax Check

Semantic Check

Shared Pool Check

SQL Optimization

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

쿼리튜닝기 (1)

1. Top 쿼리 튜닝기

A. 비효율 성능의 원인

B. 성능개선

인덱스 추가

불필요 JOIN문 제거

국사조건 추가

C. 배운 점

2. 번외

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

[SQLD] 필기시험 대비 나만의 요약 정리

SQLD 시험 준비

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

+ Recent posts

티스토리툴바