옵티마이저(Optimizer)

  • SQL의 실행 계획을 수립하고 SQL을 실행하는 DBMS의 소프트웨어
  • 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용하여 예상되는 비용을 산정
  • 여러 실행 계획 중 최저비용을 가지는 계획을 선택하여 SQL 실행
  • SQL 실행 계획을 PLAN_TABLE에 저장
  • PLAN_TABLE을 조회하여 실행 계획 확인 가능

TABLE ACCESS FULL: 테이블의 전체를 모두 읽었다는 의미

옵티마이저 엔진

옵티마이저 설명
Query Transformer - SQL문을 효율적으로 실행하기 위해 옵티마이저가 변환
- SQL이 변환되어도 결과는 동일
Estimator - 통계정보를 사용하여 SQL 실행비용 계산
- 총 비용은 최적의 실행 계획을 수립하기 위함
Plan Generator SQL을 실행할 실행 계획 수립

규칙 기반 옵티마이저(Rule base Optimizer)

실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획 수립

  1. ROWID를 사용한 단일 행인 경우
  2. 클러스터 조인에 의한 단일 행인 경우
  3. 유일하거나 기본키를 가진 해시 클러스터 키에 의한 단일 행인 경우
  4. 유일하거나 기본키에 의한 단일 행인 경우
  5. 클러스터 조인인 경우
  6. 해시 클러스터 조인인 경우
  7. 인덱스 클러스터 키인 경우
  8. 복합 칼럼 인덱스인 경우
  9. 단일 칼럼 인덱스인 경우
  10. 인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
  11. 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
  12. 정렬-병합 조인(Sort Merge)인 경우
  13. 인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
  14. 인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
  15. 전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

비용 기반 옵티마이저(Cost base Optimizer)

  • 오브젝트 통계 및 시스템 통계를 사용하여 총비용을 계산
  • 총비용이 적은 쪽으로 실행 계획 수립
  • 통계정보가 부적절한 경우 성능 저하가 발생할 수 있음

총비용: SQL문을 실행하기 위해 예상되는 소요시간 혹은 자원의 사용량

인덱스(Index)

  • 데이터를 빠르게 검색할 수 있는 방법 제공
  • 인덱스 키로 정렬되어 있기에 원하는 데이터를 빠르게 조회
  • 오름차순 및 내림차순 탐색 가능
  • 하나의 테이블에 여러 인덱스 생성 가능, 하나의 인덱스는 여러 칼럼으로 구성될 수 있음
  • 테이블 생성시 기본키는 자동으로 인덱스가 만들며, 인덱스의 이름은 SYSXXXX
  • 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성

인덱스의 구조

구조 설명
Root Block 인덱스 트레에서 가장 상위에 있는 노드
Branch Block 다음 단계의 주소를 가지고 있는 포인터로 구성
Leaf Block - 인덱스 키와 ROWID로 구성되며, 인덱스 키는 정렬되어 저장되어 있음
- Double Linked List 형태로 되어있어 양방향 탐색 가능
- 인덱스 키를 읽으면 ROWID를 사용하여 테이블의 행을 직접 읽을 수 있음

인덱스 생성

CREATE INDEX 인덱스이름 테이블 ON 테이블(칼럼1 정렬방법, 칼럼2 정렬방법)

정렬 방법을 생략하면 오름차순으로 정렬하며 'DESC'를 사용하면 내림차순 정렬 가능

인덱스 스캔

스캔 설명
인덱스 유일 스캔
(Index Unique Scan)		 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생
인덱스 범위 스캔
(Index Range Scan)		 SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생
인덱스 전체 스캔
(Index Full Scan)		 인덱스에서 검색되는 인덱스 키가 많은 경우 Leaf Block의 처음부터 끝까지 전체를 읽음

Hight Watermark: 테이블에 데이터가 저장된 블록에서 최상위 위치

실행 계획(Execution Plan)

  • 부모자식 중에는 자식이우선
  • 형제간에는 형이 우선

옵티마이저 조인(Optimizer Join)

Nested Loop 조인

  • 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식으로 실행
  • 먼저 조회되는 테이블을 외부 테이블(Outer Table)이라 하며 그다음 테이블을 내부 테이블(Inner Table)이라 함
  • 외부 테이블(선행테이블)의 크기가 작은 것을 먼저 찾는 것이 중요(데이터가 스캔되는 범위를 줄일 수 있기 때문)
  • RANDOM ACCESS가 발생하며 RANDOM ACCESS가 많이 발생하면 성능 지연 발생

Sort Merge 조인

  • 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩한 후 정렬(Sort) 수행
  • 두 테이블의 정렬이 완료되면 두 개의 테이블을 병합(Merge)
  • Sort Merge 조인은 정렬이 발생하기에 데이터양이 많아지면 성능이 떨어짐
  • 정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행
  • 임시 영역은 디스크에 있기에 성능이 급격히 떨어짐

Hash 조인

  • 두 개의 테이블 중 작은 테이블을 HASH 메모리에 로딩후 두 테이블의 조인 키를 사용하여 해시 테이블 생성
  • 해시 함수를 사용하여 주소를 계산하고 해당 주소를 사용하여 테이블을 조인하기에 많은 CPU 연산 발생
  • 선행 테이블이 충분히 메모리에 로딩되는 크기여야 함