[📕 친절한 SQL 튜닝] 1-1. SQL 파싱과 최적화

1장에서는 본격적으로 시작하기 전에 옵티마이저가 SQL을 어떻게 처리하는지, 서버 프로세스는 데이터를 어떻게 읽고 저장하는지를 알아보자. 

 

1. SQL 파싱과 최적화

✳️ SQL 파싱  

• 파싱 트리 생성 : SQL문을 이루는 개별 구성 요소를 분석해서 파싱 트리 생성 
• Syntax 체크 : 문법적 오류 확인 👉 사용할 수 없는 키워드 사용 또는 순서, 누락된 키워드 확인
• Semantic 체크 : 의미상 오류 확인 👉 존재하지 않는 테이블 또는 컬럼 사용여부, 사용한 오브젝트에 대한 권한 확인

 

✳️ SQL 최적화  

파싱 다음 단계로서 DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정 👉 옵티마이저가 역할 수행

• 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행 경로 생성해서 비교한 후 가장 효율적인 하나 선택
• 데이터베이스 성능을 결정하는 가장 핵심적인 엔진

 

✳️ 로우 소스 생성  

• SQL 옵티마이저가 선택한 실행 경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅
• 로우 소스 생성기 (Row-Source Generator)가 역할 수행

 

 

 

SQL 옵티마이저

사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 액세스 경로를 선택해 주는 DBMS 핵심 엔진

 

• 사용자로부터 받은 쿼리를 수행하는 데 후보군이 될만한 실행 계획들을 찾아냄
• 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행 계획의 예상 비용 산정
• 최저 비용을 나타내는 실행 계획 선택 

 

 

실행 계획과 비용

✳️ 실행 계획 (Execution plan)  

ID  OPERATION                          OBJECT NAME        CARD  COST
-------------------------------------------------------------------
  0  SELECT STATEMENT                                    1000   500
  1    NESTED LOOPS                                      1000   400
  2      NESTED LOOPS                                    1000   300
  3        TABLE ACCESS FULL        EMPLOYEES            1000   200
  4        TABLE ACCESS FULL        DEPARTMENTS          50     10
  5      SORT AGGREGATE                                  1       5
  6        TABLE ACCESS FULL        SALARIES             10     50
  7      SORT ORDER BY FETCH FIRST  ROW ONLY            1       8
  8        INDEX RANGE SCAN        SALARIES_IDX         10      5

• SQL 옵티마이저가 생성한 처리 절차를 사용자가 확인할 수 있게 트리 구조로 표현 
• 테이블을 스캔하는지, 인덱스를 스캔하는지 등을 확인 
• 옵티마이저가 특정 실행계획을 선택하는 근거는 ? 👉 비용으로 선택 ! 

SET AUTOTRACE TRACEONLY EXPLAIN;

SELECT * FROM t
WHERE deptno = 10;
and no = 1;

Execution Plan
----------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     3 |   123 |     2 |
|   1 |  INDEX RANGE SCAN | IDX_EMP_DEPTNO    |     3 |   123 |     2 |
----------------------------------------------------------

 

🔹 비용 (Cost) 이란 ?

• 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요 시간을 표현
• 실측치가 아닌 예상치이므로 실제 실행할 때 발생하는 I/O 또는 시간과 많은 차이 발생 가능
• 비용에 대한 정확한 의미는 추후에 정리 ...!

 

 

 

옵티마이저 힌트

SQL이 복잡할수록 실수할 가능성도 크기 때문에 통계 정보에 담을 수 없는 데이터 또는 업무 특성을 활용해 개발자가 직접 더 효율적인 액세스 경로를 찾아낼 수 있다. 이럴 때 옵티마이저 힌트를 이용해 데이터 액세스 경로를 바꿀 수 있다. 

 

👉 힌트 사용법은 주석 기호에 '+'를 붙이면 됨 

SELECT /*+ INDEX(emp idx_emp_deptno) */ *
FROM emp
WHERE deptno = 10;

---

🔹 힌트 사용 주의사항 

• 힌트 안에 인자를 나열할 때는 ',' (콤마)를 사용할 수 있지만, 힌트와 힌트 사이에 사용하면 안됨

/*+ INDEX(A A_X01) INDEX(B, B_X03) */ -> 모두 유효
/*+ INDEX(C), FULL(D) */              -> 첫 번째 힌트만 유효

• 테이블을 지정할 때 스키마명까지 명시하면 안됨

SELECT /*+ FULL(SCOTT.EMP) */ -> 무효
FROM EMP

• FROM 절 테이블명 옆에 ALIAS를 지정했다면 힌트에도 반드시 ALIAS 사용

SELECT /*+ FULL(EMP) */ -> 무효
FROM EMP E

---

🔹 옵티마이저가 자율 또는 강제적으로 실행할 수 있게 만들기

SELECT /*+ INDEX(A (주문일자)) */
	A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
FROM 주문 A, 고객 B
WHERE A.주문일자 = :ORD_DT
	AND A.고객ID = B.고객ID

• 주문 테이블을 액세스할 때 주문일자 컬럼이 선두인 인덱스를 사용하도록 힌트로 지정
• 조인 방식과 순서, 고객 테이블 액세스 방식은 옵티마이저가 알아서 판단하도록 남겨둠

SELECT /*+ LEADING(A) USE_NL(B) INDEX(A (주문일자)) INDEX(B 고객_PK) */
	A.주문번호, A.주문금액, B.고객명, B.연락처, B.주소
FROM 주문 A, 고객 B
WHERE A.주문일자 = :ORD_DT
	AND A.고객ID = B.고객ID

• 첫 번째 SQL과 달리 절대 다른 방식을 선택하지 못하도록 힌트를 빈틈없이 지정
• LEADING(A) : A 테이블을 첫 번째로 처리 👉 조인 순서 지정
• USE_NL(B) : A와 B를 중첩 루프 방식으로 조인하도록 지정
  👉 A 테이블의 각 행에 대해 B 테이블을 검색 (B 테이블이 더 작거나 인덱스를 활용할 수 있을 때 사용)

✨ 자주 사용하는 힌트들은 이후에 차근차근 정리