[SQL] SQL Booster - 성능 개선을 위한 기본 지식

    2024, Mar 15

     목차



    SQL Booster 책으로 공부하며 정리해보는 내용


    5.1.2 실행계획

    실행계획이란?

    • SQL을 처리하기 위해 내부적으로 만드는 ‘작업계획’ 또는 ‘작업절차서’
    • SQL을 실행하면 구문을 분석하고, 문제가 없으면 실행계획을 만든다. 만들어진 실행계획대로 SQL을 처리한다.


    실행계획이 중요한 이유 (ChatGPT 답변 참고)

    1. 성능 최적화
      • 실행 계획을 통해 데이터베이스 엔진은 효율적으로 쿼리를 처리하는 방법을 결정한다.
    2. 인덱스 활용
      • 어떤 인덱스가 사용되는지를 보여주기 때문에, 인덱스의 효과적인 활용 여부를 확인할 수 있다.
    3. 부하 예측
      • 어떻게 데이터에 접근하고 조인하는지를 파악할 수 있다.
    4. 최적화 기회 제공
      • 개발자가 실행 계획을 확인하면서 쿼리를 최적화할 수 있는 기회를 제공한다.
    5. 비용 예측
      • 어떤 부분에서 시간이 많이 소요되는지 예측할 수 있다.



    5.1.3 실행계획 확인하기

    EXPLAIN PLAN FOR
SELECT * FROM T_ORD WHERE ORD_SEQ = 4;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

    Plan hash value: 2027818626
 
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    44 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |     1 |    44 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - access(""ORD_SEQ""=4)"

    항목의 의미

    • ID : 오퍼레이션 ID (실행순서 X)
    • Operation : 해당 단계에 수행한 작업 내용
    • Name : 해당 단계에 작업을 수행한 대상 오브젝트(테이블 또는 인덱스)
    • Rows : 해당 단계 수행 시 조회될 예상 데이터 건수
    • Bytes : 해당 단계까지 사용될 예상 데이터양(누적)
    • Cost : 해당 단계까지 사용될 예상 비용(누적)
    • Time : 해당 단계까지 사용될 예상 시간(누적)



    5.1.4 실행계획의 순서

    Plan hash value: 3240201901
 
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   170 | 20740 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN         |       |   170 | 20740 |    11  (10)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| M_CUS |    60 |  4680 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T_ORD |   252 | 11088 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"   2 - filter(""T2"".""CUS_GD""='A')"
"   3 - filter(""T1"".""ORD_DT""<TO_DATE(' 2017-02-01 00:00:00', "
"              'syyyy-mm-dd hh24:mi:ss') AND ""T1"".""ORD_DT"">=TO_DATE(' 2017-01-01 "
"              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))"
    • 실행 순서
      • 2 → 3 → 1 → 0
    Plan hash value: 1463172817
 
-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |    33 |    17  (18)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT         |           |     1 |    33 |    17  (18)| 00:00:01 |
|   2 |   MERGE JOIN                  |           |    10 |   330 |    17  (18)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| M_ITM     |    10 |   130 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_M_ITM  |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |           |    80 |  1600 |    15  (20)| 00:00:01 |
|   6 |     VIEW                      | VW_GBC_9  |    80 |  1600 |    14  (15)| 00:00:01 |
|   7 |      HASH GROUP BY            |           |    80 |  2080 |    14  (15)| 00:00:01 |
|*  8 |       HASH JOIN               |           |   313 |  8138 |    13   (8)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL      | T_ORD     |   252 |  3024 |     7   (0)| 00:00:01 |
|  10 |        TABLE ACCESS FULL      | T_ORD_DET |  3224 | 45136 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   3 - filter(""T3"".""ITM_TP""='ELEC')"
"   5 - access(""ITEM_1""=""T3"".""ITM_ID"")"
"       filter(""ITEM_1""=""T3"".""ITM_ID"")"
"   8 - access(""T1"".""ORD_SEQ""=""T2"".""ORD_SEQ"")"
"   9 - filter(""T1"".""ORD_DT""<TO_DATE(' 2017-02-01 00:00:00', 'syyyy-mm-dd "
"              hh24:mi:ss') AND ""T1"".""ORD_DT"">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd "
              hh24:mi:ss'))
    • 실행계획 순서 해석 (내 생각)
      1. 9 → ORD_DT 필터링
      2. 8 → 9, 10 조인
      3. 5 → GROUP BY 적용된 가상 뷰(VW_GBC_9)를 생성해 그룹화된 결과를 조인 (그룹화된 결과를 조인할 땐 주로 SORT JOIN이 발생한다.)
      4. 3→ ITM_TP 필터링
      5. 2 → M_ITM과 가상 뷰 조인



    5.1.5 실제 실행계획 만들기

    SELECT  /*+ GATHER_PLAN_STATISTICS */
		*
FROM    T_ORD T1
        ,M_CUS T2
WHERE   T1.CUS_ID = T2.CUS_ID
AND     T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')
AND     T2.CUS_GD = 'A';

SELECT  T1.SQL_ID ,T1.CHILD_NUMBER ,T1.SQL_TEXT
FROM    V$SQL T1
WHERE   T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.LAST_ACTIVE_TIME DESC;

-- 위에서 조회된 SQL ID 사용
SELECT  *
FROM    TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ad1p7thaxs821',0,'ALLSTATS LAST'));

    "SQL_ID  ad1p7thaxs821, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS */   * FROM    T_ORD T1         
",M_CUS T2 WHERE   T1.CUS_ID = T2.CUS_ID AND     T1.ORD_DT >= "
"TO_DATE('20170101','YYYYMMDD') AND     T1.ORD_DT < "
"TO_DATE('20170201','YYYYMMDD') AND     T2.CUS_GD = 'A'"
 
Plan hash value: 3240201901
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    165 |00:00:00.01 |      31 |       |       |          |
|*  1 |  HASH JOIN         |       |      1 |    170 |    165 |00:00:00.01 |      31 |   779K|   779K| 1255K (0)|
|*  2 |   TABLE ACCESS FULL| M_CUS |      1 |     60 |     60 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T_ORD |      1 |    252 |    243 |00:00:00.01 |      24 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - access(""T1"".""CUS_ID""=""T2"".""CUS_ID"")"
"   2 - filter(""T2"".""CUS_GD""='A')"
"   3 - filter((""T1"".""ORD_DT""<TO_DATE(' 2017-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "
"              ""T1"".""ORD_DT"">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))"
    • ID : 실행계획의 오퍼레이션 ID
    • Operation : 해당 단계에 수행한 작업 내용
    • Name : 해당 단계에 작업을 수행한 대사 오브젝트(테이블 또는 인덱스)
    • Starts : 해당 단계를 수행한 횟수
    • E-Rows : 해당 단계의 예상 데이터 건수
    • A-Rows : 해당 단계의 실제 데이터 건수
    • A-Time : 해당 단계까지 수행된 실제 시간(누적)
    • Buffers : 해당 단계까지 메모리 버퍼에서 읽은 블록 수 (논리적 IO 횟수, 누적)
    • Reads : 해당 단계까지 디스크에서 읽은 블록 수 (물리적 IO 횟수, 누적)

    → 표시한 항목들이 높으면, 원인을 찾아 성능 개선을 하면 된다.

    운영에서 서비스될 SQL에는 GATHER_PLAN_STATISTICS 힌트가 포함되지 않게 주의가 필요.
불필요한 수치 수집은 성능상의 손해를 볼 수 있음.



    5.2.1 옵티마이저

    : SQL을 실행하기 전에 실행계획을 만드는 역할

    • 비용 기반 옵티마이저, 규칙 기반 옵티마이저로 나눠짐 → 보통 비용 기반 옵티마이저를 사용한다.

    비용 기반 옵티마이저

    • 비용? → IO의 횟수, CPU Time, 메모리 사용량



    5.2.2 소프트 파싱, 하드 파싱

    구문 분석 후 실행계획이 메모리에 있는지 검색함

    • 소프트 파싱 : 메모리에 이미 있는 실행계획을 재사용 (구문 분석만 하고 재사용)
    • 하드 파싱 : 메모리에 없으면 실행계획을 새로 만듦 (구문 분석부터 실행계획까지 만듦)

    바인드 변수를 사용해야 첫 실행때만 하드 파싱됨

    SELECT * FROM T_ORD T1 WHERE T1.CUS_ID = :v_CUS_ID;


    5.2.3 IO (Input, Output)

    • SQL 성능 개선 핵심은 IO - 가장 많이 발생하는 작업이라서
    • 최소의 IO로 처리하려면 인덱스가 필수


    5.2.4 블록(Block) - 페이지

    • IO를 처리하는 최소 단위
    • 대부분 8KB 크기의 블록을 사용하며, 한 블록에는 약 100건 정도를 저장할 수 있다.
    SELECT  /*+ GATHER_PLAN_STATISTICS */
        COUNT(*)
FROM    T_ORD T1
WHERE   T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170201','YYYYMMDD');

SELECT  T1.SQL_ID ,T1.CHILD_NUMBER ,T1.SQL_TEXT
FROM    V$SQL T1
WHERE   T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.LAST_ACTIVE_TIME DESC;

SELECT  *
FROM    TABLE(DBMS_XPLAN.DISPLAY_CURSOR('441bj327z7bzw',0,'ALLSTATS LAST'));

    "SQL_ID  441bj327z7bzw, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS */         COUNT(*) FROM    T_ORD 
"T1 WHERE   T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD') AND     "
"T1.ORD_DT < TO_DATE('20170201','YYYYMMDD')"
 
Plan hash value: 1511355189
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      1 |00:00:00.01 |      23 |
|   1 |  SORT AGGREGATE    |       |      1 |      1 |      1 |00:00:00.01 |      23 |
|*  2 |   TABLE ACCESS FULL| T_ORD |      1 |    252 |    243 |00:00:00.01 |      23 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - filter((""T1"".""ORD_DT""<TO_DATE(' 2017-02-01 00:00:00', 'syyyy-mm-dd "
"              hh24:mi:ss') AND ""T1"".""ORD_DT"">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd "
              hh24:mi:ss')))



    5.2.5 논리적 IO와 물리적 IO

    논리적 IO → Buffers

    • 메모리 영역에서 데이터를 읽고 쓰는 작업

    물리적 IO → Reads

    • 디스크 영역에서 데이터를 읽고 쓰는 작업

    • 버퍼캐시에 데이터가 없으면, 디스크에서 읽어옴. 물리적 IO를 수행하며 사용한 블록은 버퍼캐시에 저장하여, 이후에 데이터가 필요하면 논리적 IO로 처리함


    5.2.6 부분 범위 처리

    • 총 500건이면, 화면에 보여줄 부분 50건만 우선 전송하는것
    SELECT  /*+ GATHER_PLAN_STATISTICS */
        TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD
        ,T1.CUS_ID
        ,SUM(T1.ORD_AMT) ORD_AMT
FROM    T_ORD T1
WHERE   T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMMDD')
        ,T1.CUS_ID;
        
SELECT  T1.SQL_ID ,T1.CHILD_NUMBER ,T1.SQL_TEXT
FROM    V$SQL T1
WHERE   T1.SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY T1.LAST_ACTIVE_TIME DESC;

SELECT  *
FROM    TABLE(DBMS_XPLAN.DISPLAY_CURSOR('djmw6zdnw5ha4',0,'ALLSTATS LAST'));

    "SQL_ID  djmw6zdnw5ha4, child number 0"
-------------------------------------
SELECT  /*+ GATHER_PLAN_STATISTICS */         
"TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD         ,T1.CUS_ID         "
",SUM(T1.ORD_AMT) ORD_AMT FROM    T_ORD T1 WHERE   T1.ORD_DT >= "
"TO_DATE('20170301','YYYYMMDD') GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMMDD') "
"        ,T1.CUS_ID"
 
Plan hash value: 3828596082
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |    501 |00:00:00.01 |      23 |       |       |          |
|   1 |  HASH GROUP BY     |       |      1 |   2569 |    501 |00:00:00.01 |      23 |   927K|   927K|          |
|*  2 |   TABLE ACCESS FULL| T_ORD |      1 |   2569 |   2606 |00:00:00.01 |      23 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - filter(""T1"".""ORD_DT"">=TO_DATE(' 2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))"
    • A-Rows 에 있는게 부분 범위 처리된 건수
    • Order by, Group by가 포함되어 있으면 부분처리가 이루어지지 못함
    # SQL # Oracle

    다른 글

    [김영한의 실전 자바] 중급 1편 - 래퍼 클래스

    [김영한의 실전 자바] 중급 1편 - String 클래스

    [김영한의 실전 자바] 중급 1편 - 불변 객체

    [VS Code] 자주 쓰는 단축키 (윈도우)