[SQLD] 2과목 정리 - SQL 최적화의 원리

옵티마이저와 실행 계획

• 옵티마이저
  • SQL을 작성하여 실행할 때 SQL을 어떻게 실행할 것인지를 계획
  • 실행계획을 수립하고 SQL 실행하는 DBMS의 소프트웨어
  • SQL의 성능에 중요한 역할
• 옵티마이저 특징
  • 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해 예상 비용 산정
  • 여러 개의 실행 계획 중 최저비용을 가지고 있는 계획을 선택해 SQL을 실행
• 옵티마이저의 필요성
  • 두 개의 크기가 다른 테이블 A, B 에서(A > B) 동일한 행을 조회 하는 차는 연산이 있을 때
    A 테이블의 행을 먼저 읽고 B테이블에 동일한 행을 찾게될 때 보다 B 테이블의 행을 먼저 읽고 A테이블에 동일한 행을 찾으면 결과는 동일하지만 불필요한 비교 횟수를 줄일 수 있게 된다.
  • 이 때 옵티마이저가 비효율적인 실행 계획을 수립하면 HINT 를 통해 실행 계획을 변경하도록 요청할 수 있다.
• 옵티마이저 실행 계획 확인
  • SQL 실행 계획을 PLAN_TABLE에 저장해 해당 테이블을 조회하여 실행 계획을 확인할 수 있다.

 

---

옵티마이저 종류

• 옵티마이저의 실행 방법
  • SQL을 실행하면 파싱을 실행해 SQL의 문법 검사 및 구문 분석을 수행
  • 구문 분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립
  • 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해 실행 계획을 수립, 비용 기반 옵티마이저는 통계정보를 활용해 최적의 실행 계획을 수립한다.
  • 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(fetch)
• 옵티마이저 엔진
  • Query Transformer - SQL문을 효율적으로 실행하기 위해 옵티마이저가 변환 (변환돼도 결과는 동일)
  • Estimator - 통계정보를 사용해 SQL 실행비용 계산, 총비용은 최적의 실행 계획을 수립하기 위해 사용
  • Plan Generator - SQL을 실행할 싱핼 계획을 수립
  • 규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획 수립
• 비용 기반 옵티마이저
  • 오브젝트 통계 및 시스템 통계를 사용해 총 비용을 계산
  • 총비용이라는 것은 SQL문을 실행하기 위해 예상되는 소요시간 혹은 자원의 사용량을 의미
  • 총비용이 적은 쪽으로 실행 계획을 수립, 단 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생할 수 있음

 

---

인덱스

• 인덱스는 데이터를 빠르게 검색할 수 있는 방법 제공
• 인덱스 키로 정렬되어 있기 때문에 원하는 데이터를 빠르게 조회 (오름, 내림차순)
• 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있음
• 테이블을 생성할 때 기본키는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX이다.
• 인덱스는 Root Block, Branch Block, Leaf Block 으로 구성
  • Root Block - 인덱스 트리의 가장 상위에 있는 노드
  • Branch Block - 다음 단계의 주소를 가지고 있는 포인터
  • Leaf Block - 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어 저장되어 있다.
    • Double Linked List 형태로 되어 있어 양방향 탐색이 가능
    • 인덱스 키를 읽으면 ROWID를 사용해 테이블의 행을 직접 읽을 ㅅ ㅜ있다.
• 인덱스 생성
  • CREATE INDEX
  • 생성할 때 한 개 이상의 칼럼을 사용해 생성할 수 있다.
  • 기본적으로 오름차순으로 정렬하고 DESC 를 포함하면 내림차순으로 정렬
• 인덱스 스캔
  • 인덱스 유일 스캔 - 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생
  • 인덱스 범위 스캔 - SELECT 문에서 특정 범위를 조회하는 WHERE 문을 사용할 경우 발생(LIKE, BETWEEN) 하여 Leaf Block의 특정 범위를 스캔한다.
  • 인덱스 전체 스캔 - 인덱스에서 검색되는 인덱스 키가 많은 경우 Leaf Block의 처음부터 끝까지 전체를 읽는다.

* Table full scan - 테이블의 모든 데이터를 읽는 것
* High watermark - 테이블에 데이터가 저장된 블록에서 최상위 위치를 의미, 데이터가 삭제되면 High Watermark 도 변경된다.

 

---

옵티마이저 조인

• Nested Loop 조인
  • 하나의 테이블을 먼저 찾고 그다음 테이블을 조인하는 방식
  • 먼저 조회되는 테이블을 외부 테이블(Outer Table), 다음 조회되는 테이블을 내부 테이블(Inner Table)
  • 데이터 스캔 범위를 줄이기 위해 외부 테이블의 크기가 작은 것을 먼저 찾는 것이 중요
  • Random access가 발생, Random Access가 많이 발생하면 성능 지연이 생긴다.
  • ordered 힌트 - FROM 절에 나오는 테이블 순서대로 조인을 하게 하는 힌트, use_nl, use_merge, use_hash 힌트와 함께 사용
• Sort Merge 조인
  • 두 개의 테이블을 SORT_AREA 라는 메모리 공간에 모두 로딩하고 SORT를 수행
  • 두 개의 테이블에 대해 SORT가 완료되면 두 개의 테이블을 병합
  • 정렬이 발생해 데이터양이 많아지면 성능이 떨어진다.
  • 정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행 -> 임시 영역은 디스크에 있어 성능이 급격히 떨어짐
  • use_merge 힌트를 통해 사용, ordered 힌트와 같이 사용
• Hash 조인
  • 두 개의 테이블 중 작은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해 해시 테이블을 생성
  • 해시 함수를 사용해 주소를 계산하고 해당 주소를 사용해 테이블을 조인하기 때문에 CPU 연산을 많이 함
  • 선행 테이블이 충분히 메모리에 로딩되는 크기여야한다.