004. 실행계획이 SQL 구문의 성능을 결정 - llighter/database GitHub Wiki

SQL 레벨업

실행계획이 정해졌다 하더라도 데이터가 많거나 지나치게 복잡한 SQL 쿼리의 경우 반응 지연이 발생할 수 있다.

1. 실행 계획 확인 방법

반응 지연이 발생했을 때 가장 먼저 살펴볼 것은 실행계획 이다.

Oracle: set autotrace traceonly

보통 개발자의 경우 명령행 인터페이스 대신 Orange 같은 툴 내에 있는 기능을 사용한다.

3개의 기본적인 SQL 구문의 실행 계획

  1. 테이블 풀 스캔(Full Scan)의 실행 계획
  2. 인덱스 스캔의 실행 계획
  3. 간단한 테이블 결합의 실행 계획

2. 테이블 풀 스캔의 실행 계획

DBMS에서 제공하는 정보

  1. 조작 대상 객체
    • Name 필드에 출력
    • 테이블 이외에도 인덱스, 파티션, 시퀀스 등 SQL 쿼리로 조작할 수 있는 모든 객체
  2. 객체에 대한 조작의 종류
    • Operation 필드에 출력
  3. 조작 대상이 되는 레코드 수
    • rows 필드에 출력
    • 이 값은 Data Dictionary 로 부터 얻은 값이다.
    • 따라서 메타 정보와 실제 정보가 차이가 있을 경우 올바른 추정을 할 수 없다.

Example 12-8 Parallel Query Explain Plan

CREATE TABLE emp2 AS SELECT * FROM employees;
ALTER TABLE emp2 PARALLEL 2;

EXPLAIN PLAN FOR
  SELECT SUM(salary) FROM emp2 GROUP BY department_id;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

--------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU) |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |   107 |  2782 |     3 (34)  |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |             |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |   107 |  2782 |     3 (34)  |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          |   107 |  2782 |     3 (34)  |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |   107 |  2782 |     3 (34)  |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |   107 |  2782 |     3 (34)  |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          |   107 |  2782 |     3 (34)  |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |   107 |  2782 |     2 (0)   |  Q1,00 | PCWP |            |
|   8 |         TABLE ACCESS FULL| EMP2     |   107 |  2782 |     2 (0)   |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

3. 인덱스 스캔의 실행 계획

SELECT *
   FROM EMP
   WHERE emp_id="20180927";
  • 조작 대상이 되는 레코드 수
    • Rows 가 1
  • 접근 대상 객체와 조작
    • 모집합의 데이터가 많을수록 인덱스 스캔이 좋음

풀 스캔과 인덱스 스캔의 데이터 량에 따른 처리 시간 그래프 추가할 것!

4. 간단한 테이블 결합의 실행 계획

결합을 사용하면 실행 계획이 상당히 복잡해지므로, 옵티마이저도 최적의 실행 계획을 세우기 어렵다.

예약이 존재하는 점포를 선택하는 SELECT 쿼리

SELECT shop_name
   FROM Shops INNER JOIN Reservations R
      ON S.shop_id = R.shop_id;

일반적으로 DBMS에서 결합을 할 때 사용하는 세 가지 종류의 알고리즘

  1. Nested Loops
    • cf. 중첩 반복문
  2. Sort Merge
    • 결합 키로 레코드를 정렬
    • 순차적으로 결합
    • 결합 전 전처리로 정렬을 수행해야하는데 이때 워킹 메모리를 사용
  3. Hash
    • 결합 키 값을 해시값으로 맵핑
    • 해시 테이블이 필요하기 때문에 마찬가지로 워킹 메모리를 사용