[SQL개발자] SQL 최적화 기본 원리 - 옵티마이저와 실행계획

✔ 옵티마이저와 실행계획

옵티마이저란

• 사용자가 질의한 SQL문에 대해 최적의 실행 방법(실행계획)을 결정하는 역할을 수행
• 다양한 실행 방법들 중에서 최적의 실행 방법을 결정
• 관계형 데이터베이스는 옵티마이저가 결정한 실행 방법대로 실행 엔진이 데이터를 처리하여 결과 데이터를 사용자에게 전달
• SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해 주는 DBMS 내부의 핵심 엔진
• 옵티마이저가 최적의 실행 방법을 결정하는 방식에 따라 규칙기반 옵티마이저와 비용기반 옵티마이저로 구분

규칙기반 옵티마이저 (RBO)

• 규칙을 가지고 우선순위를 기반으로 실행계획을 생성
• 데이터베이스 관리자가 사전에 정의해 둔 규칙에 의거하여 실행계획을 선택하는 옵티마이저
• 숫자가 낮을수록 높은 우선순위를 가짐
• 제일 높은 우선순위는 행에 대한 고유 주소를 사용하는 방법 (Single row by rowid 액세스 기법)

비용기반 옵티마이저 (CBO)

• SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택
• 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될
  처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저
• 통계 정보 변경되면 SQL의 실행계획이 달라질 수 있으며 통계정보가 없는 경우 정확한 비용 예측이 불가능

실행계획

• SQL에서 요구한 사항을 처리하기 위한 절차와 방법
• SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업
• 예상 정보를 뜻함
• DBMS의 옵티마이저가 수립한 SQL 코드의 처리를 위한 실행 절차와 방법을 표현
• EXPLAIN 명령어를 통해 확인할 수 있음
• 동일 SQL문에 대해 실행계획이 달라진다고 하더라도 실행 결과가 달라지지 않음
• 조인 순서, 조인 기법(NL Join, Hash Join, Sort Merge Join), 액세스 기법, 최적화 정보, 연산, 질의 처리 예상 비용으로 구성
• 위에서 아래로, 안에서 밖으로 실행계획을 읽음

// 실행계획의 실행순서
NESTED LOOPS (6)
   HASH JOIN (3)
    TABLE ACCESS (FULL) TAB1 (1)
    TABLE ACCESS (FULL) TAB2 (2)
   TABLE ACCESS (BY ROWID) TAB3 (5)
    INDEX (UNIQUE SCAN) PK_TAB3 (4)

SQL 처리 흐름도

• SQL의 내부적인 처리 절차를 시각적으로 표현한 도표
• 실행계획을 시각화해서 표현
• 인덱스 스캔, 테이블 전체 스캔과 같은 액세스 기법과 조인 기법 등을 표현할 수 있음
• 성능적인 관점을 살펴보기 위해서 일량을 함께 표시할 수 있음