3-3. SQL 최적화의 원리

옵티마이저와 실행 계획

옵티마이저(Optimizer)

• 옵티마이저는 데이터베이스 관리 시스템의 소프트웨어로 SQL을 어떻게 실행할지 실행 계획을 세우고 SQL을 수행한다.
• 동일한 결과를 내는 SQL도 어떻게 실행하느냐에 따라 성능이 달라진다.

옵티마이저 특징

• 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해 예상 비용을 산정한다.
• 여러 실행 계획 중 최저비용의 계획을 선택하여 SQL을 실행한다.

옵티마이저의 필요성

• EMP테이블과 DEPT 테이블의 AND 연산을 예시로 보자
  • EMP 테이블을 실행하고 여기서 찾은 행과 동일한 것을 DEPT 테이블에서 찾고 최종 결과를 인출한다.
  • 이 때 EMP 테이블이 건수가 더 많으면 DEPT 테이블을 읽을 때 불필요한 비교가 발생한다.
  • 이 연산은 AND연산이므로 DEPT를 먼저 읽어도 같은 결과를 발생시킨다.
  • 옵티마이저는 이러한 실행 계획을 수립하며 SQL 개발자는 옵티마이저가 실행 계획을 잘 세울 수 있도록 힌트(HINT)를 사용할 수 있다.

옵티마이저 실행 계획 확인

• 옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장한다.
• SQL 개발자는 PLAN_TABLE을 조회하여 실행 계획을 확인할 수 있다.
• DESC PLAN_TABLE; 을 통해 확인할 수 있다.

옵티마이저 종류

옵티마이저의 실행 방법

• 개발자가 SQL을 실행하면 파싱을 통해 SQL의 문법 검사 및 구문 분석을 수행한다.
• 이 후 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다.
• 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해 실행 계획을 수립한다.
• 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고, 실행 후에 데이터를 인출한다.
• 옵티마이저 엔진 구조
  • Query Transformer : SQL을 효율적으로 실행하기 위해 옵티마이저가 변환한다.
  • Estimator : 통계정보를 사용해 실행비용을 계산한다.
  • Plan Generator : SQL을 실행할 실행 계획을 수립한다.

옵티마이저 엔진

• 규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선순위를 갖는다.
  1. ROWID를 사용한 단일 행인 경우
  2. 클러스터 조인에 의한 단일 행인 경우
  3. 유일하거나 기본키를 가진 해시 클러스터 키에 의한 단일 행인 경우
  4. 유일하거나 기본키에 의한 단일 행인 경우
  5. 클러스터 조인인 경우
  6. 해시 클러스터 조인인 경우
  7. 인덱스 클러스터 키인 경우
  8. 복합 칼럼 인덱스인 경우
  9. 단일 칼럼 인덱스인 경우
  10. 인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
  11. 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
  12. 정렬-병합 조인인 경우
  13. 인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
  14. 인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
  15. 전체 테이블을 스캔하는 경우

SELECT /*+ RULE */ * FROM EMP
	WHERE ROWID='AAAHYhAABBAAALNJAAN';
# /*+ RULE */를 사용하여 규칙 기반 옵티마이저로 실행하도록 알려줌
# 이렇게 실행방법을 알려주는 것을 힌트라고 한다.

비용 기반 옵티마이저

• 비용 기반 옵티마이저는 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산한다.
• 비용이란 예상되는 소요시간 혹은 자원의 사용량을 의미한다.
• 통계정보가 부적절한 경우 성능 저하가 발생할 수 있다.

인덱스

인덱스(Index)

• 인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공한다.
• 인덱스는 인덱스 키로 정렬되어 있기 때문에 원하는 데이터를 빠르게 조회한다.
• 인덱스는 오름차순 및 내림차순 탐색이 가능하다.
• 하나의 테이블에 여러 개의 인덱스를 생성 가능하고, 하나의 인덱스는 여러 칼럼으로 구성될 수 있다.
• 테이블을 생성할 때 기본키는 자동으로 인덱스가 만들어지고 이름은 SYSXXXX이다.
• 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성되고
  • Root Block은 가장 상위에 있는 노드를 의미
  • Branch Block은 당므 단계의 주소를 가지고 있는 포인터로 구성
  • Leaf Block은 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어 저장

인덱스 생성

• 인덱스 생성은 CREATE INDEX문을 사용한다.
• 인덱스를 생성할 때는 한 개 이상의 칼럼을 사용해서 생성할 수 있다.
• 인덱스 키는 기본적으로 오름차순으로 정렬하고 DESC를 사용해 내림차순으로 정렬 가능

인덱스 스캔

• 인덱스 유일 스캔(Index Unique Scan)
  • 인덱스 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생한다.
  • 예로, EMPNO가 중복되지 않는 경우 특정 하나의 EMPNO를 조회
  • SELECT * FROM EMP WHERE EMPNO=1000; 를 실행하고 PLAN을 살펴보면 인덱스 유일 스캔으로 실행한다.
• 인덱스 범위 스캔(Index Range Scaen)
  • SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생한다.
  • LIKE나 BETWEEN이 그 예이며, 데이터 양이 적은 경우 인덱스 자체를 실행하지 않고 테이블 전체를 스캔할 수도 있다.
  • SELECT * FROM EMP WHERE EMPNO>=1000; 를 실행하고 PLAN을 살펴보면 인덱스 범위 스캔으로 실행한다.
• 인덱스 전체 스캔(Index Full Scan)
  • 인덱스에서 검색되는 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 읽는다.
  • SELECT * FROM EMP WHERE ENAME LIKE '%' AND SAL>0; 를 실행하고 PLAN을 살펴보면 인덱스 전체 스캔으로 실행한다.

옵티마이저 조인

Nested Loop 조인

• Nested Loop 조인은 하나의 테이블에서 데이터를 먼저 찾고 다음 테이블을 조인하는 방식
• 먼저 조회하는 테이블을 외부 테이블이라고 하고 다음 테이블을 내부 테이블이라고 함
• Nested Loop 조인에서는 외부 테이블의 크기가 작는 것을 먼저 찾는 것이 중요하다.
• Nested Loop 조인은 Random Access가 발생하는데, 이가 많이 발생하면 성능 지연이 발생한다. 그러므로 Random Access를 줄여야 성능이 향상된다.
• /*+ ordered use_nl(b) */ 키워드를 통해 의도적으로 Nested Loop를 실행하도록 힌트를 줄 수 있다.

Sort Merge 조인

• Sort Merge 조인은 두 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩하여 SORT를 수행한다.
• 두 테일블에 대해 SORT가 완료되면 두 테이블을 Merge한다.
• 정렬이 발생하므로 데이터양이 많아지면 성능이 떨어진다.
• 정렬 데이터 양이 너무 많으면 정렬은 임시 영역에서 수행되고, 해당 영역은 디스크에 있으므로 성능이 급격하게 떨어진다.
• /*+ ordered use_merge(b) */ 키워드를 통해 의도적으로 SORT MERGE 조인을 실행하도록 힌트를 줄 수 있다.

Hash 조인

• Hash 조인은 두 테이블 중 작은 테이블을 HASH 메모리에 로딩하고 두 테이블의 조인 키를 사용해서 해시 테이블을 생성한다.
• Hash 조인은 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해 테이블을 조인하므로 CPU연산을 많이 한다.
• 특히 Hash 조인시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다.
• /*+ ordered use_hash(b) */ 키워드를 통해 의도적으로 Hash 조인을 실행하도록 힌트를 줄 수 있다.