옵티마이저 종류

• 비용기반(Cost-Based) 옵티마이저 (CBO)
  • 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출
    ⇒ 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정
    ⇒ 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저.
  • CBO가 사용하는 통계정보로는 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.
• 규칙기반(Rule-Based) 옵티마이저 (RBO)
  • 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 과거의 옵티마이저
  • 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대량 데이터를 처리하는 데 부적합.

옵티마이저 모드

최적화 목표를 설정하는 기능으로서 아래 세가지 옵티마이저 모드 중 하나를 선택하면 된다.

• ALL_ROWS
  • 전체 처리속도 최적화
  • 옵티마이저는 쿼리 결과집합 ‘전체를 읽는 것을 전제로’ 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택
  • 즉, 전체 처리속도 최적화가 목표
• FIRST_ROWS
  • 최초 응답속도 최적화
  • 옵티마이저는 전체 결과집합 중 ‘앞쪽 일부만 읽다가 멈추는 것을 전제로’ 응답 속도가 가장 빠른 실행계획을 선택
  • 즉, 최초 응답속도 최적화가 목표
  • ALL_ROWS와 비교하면, Table Full Scan 보다 인덱스를 더 많이 선택하고,
    해시 조인, 소트 머지 조인보다 NL 조인을 더 많이 선택하는 경향을 보임
  • 사실 이 옵티마이저 모드는 앞으로 사라지게 될(deprecated) 옵티마이저 모드이기 때문에, FIRST_ROWS_N을 사용해야 한다.
• FIRST_ROWS_N
  • 최초 N건 응답속도 최적화
  • 옵티마이저는 사용자가 ‘앞쪽 N개 로우만 읽고 멈추는 것을 전제로’ 응답 속도가 가장 빠른 실행계획을 선택
  • ALTER SYSTEM 또는 ALTER SESSION 명령어로 옵티마이저 모드를 설정할 때 N으로 지정할 수 있는 값은 아래와 같이 1, 10, 100, 1000 네 가지다.

alter session set optimizer_mode = first_rows_1;
alter session set optimizer_mode = first_rows_10;
alter session set optimizer_mode = first_rows_100;
alter session set optimizer_mode = first_rows_1000;

FIRST_ROWS(n) 힌트로 설정할 때는 괄호 안에 ()보다 큰 어떤 정수 값이라도 입력할 수 있다.

select /*+ first_rows(30) */ col1, col2, col3 from t where ...

• FIRST_ROWS는 사용자가 데이터를 어느 정도 읽다가 멈출지를 지정하지 않았으므로 정확한 비용 산정이 어려움
• FIRST_ROWS_N은 읽을 데이터 건수를 지정하였으므로 더 정확한 비용 산정이 가능

옵티마이저에 영향을 미치는 요소

SQL과 연산자 형태

결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자 (=, IN, LIKE, BETWEEN, 부등호 등)를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있거, 궁극적으로 쿼리 성능에 영향을 미침.

인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터

쿼리를 똑같이 작성해도 인덱스, IOT, 클러스터, 파티션, MV 등을 구성했는지, 그리고 어떤 식으로 구성했는지에 따라 실행계획과 성능이 달라짐

제약 설정

DBMS에 설정한 PK, FK, CHECK, NOT NULL 같은 제약들은 데이터 무결설 보장해주고 옵티마이저가 쿼리 성능을 최적화하는데 중요한 메타정보로 활용됨.

통계정보

통계정보는 옵티마이저에 매우 강력한 영향을 미침.

SQL 옵티마이저가 사용하는 통계정보에 문제가 생기면 애플리케이션 성능이 갑자기 느려지고 심할땐 장애 상황으로 이어진다.

시스템 장애가 종종 발생하는 다양한 원인

• 특정 테이블 통계정보를 갑자기 삭제한다
• 대량 데이터를 지웠다 다시 입력하기 직전, 데이터가 없는 상태에서 자동으로 통계정보가 수집한다
• 3년간 갱신하지 않던 특정 테이블 통계정보를 갑자기 재수집한다
• 통계정보 없이 관리하던 테이블에 인덱스를 재생성한다
• 테이블이나 인덱스를 재생성하면 파티션 단위로만 통계정보를 수집한다.

옵티마이저 힌트

옵티마이저에게 가장 절대적인 영향을 미치는 요소.

옵티마이저는 힌트를 명령어로 인식하고 그대로 따른다.

옵티마이저 힌트가 동작하지 않는 이유

• 문법이 맞지 않음
• 잘못된 참조
• 의미적으로 맞지 않은 힌트 기술
• 논리적으로 불가능한 액세스 경로
• 버그

옵티마이저 관련 파라미터

옵티마이저 행동에 영향을 미치는 파라미터 목록을 살펴볼 수 있는 쿼리

select name, value, isdefault, default_value
from v$sys_optimizer_env

옵티마이저의 한계

• 네비게이션도 실수하듯 옵티마이저도 완벽하지 않고 한계가 있다.
• 옵티마이저 핸동에 가장 큰 영향을 미치는 통계정보를 ‘필요한 만큼 충분히’ 확보하는 것은 불가능한 일이다.
• 통계정보를 완벽히 수집해도 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 단점도 있음
• 기본적으로 비용기반으로 작동하나, 내부적으로 여러 가정과 정해진 규칙을 이용해 기계적인 선택을 하는것도 한계다.

개발자의 역할

DB 세계에서도 불완전한 옵티마이저에만 의존하는게 아니라 개발자 스스로 옵티마이저가 되어야 한다.

고성능, 고효율 DB 애플리케이션을 구축하려면, 소수 DBA나 튜너보다 다수 개발자의 역할이 중요하다. 따라서 새로운 개발 언어를 익히는 노력 이상으로 SQL 수행원리와 튜닝 방법을 익히는데도 많은 노력과 시간을 투자해야 한다.

장인정신을 가져라

기본적으로 옵티마이저에게 많은 일을 맡기는 RDBMS 환경에서 SQL 성능을 높이기 위해 개발자가 할 일은 다음과 같다.

• 필요한 최소 블록만 읽도록 쿼리 작성
• 최적의 옵티마이징 팩터를 제공한다
• 필요하다면 옵티마이저 힌트를 이용해 최적의 액세스 경로로 유도한다.

필요한 최소 블록만 읽도록 쿼리 작성

SQL 작성자 스스로 결과집합을 논리적으로 잘 정의하고, 그 결과집합을 만들기 위해 DB 프로세스가 최소한의 일만 하도록 쿼리를 효율적으로 작성하는 것이 중요하다.

데이터베이스 성능은 I/O 효율에 달려있으므로 동일한 레코드를 반복적으로 읽지 않고, 필요한 최소 블록만 읽도록 해야한다.

최적의 옵티마이징 팩터 제공

• 전략적 인덱스 구성 ⇒ 옵티마이저를 돕는 가장 기본적인 옵티마이저 팩터
• DBMS가 제공하는 다양한 기능 활용 ⇒ 파티션, 클러스터, IOT, MV, Result Cache
• 옵티마이저 모드 설정 ⇒ 옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정해야함
• 정확하고 안정적인 통계정보 ⇒ 통계정보를 잘 수집하자

필요하다면 옵티마이저 힌트를 이용해 최적의 액세스 경로로 유도

옵티마이저가 최적의 실행계획을 수립하지 못하면, 개발자가 힌트를 이용해 직접 데이터 액세스 경로를 선택해 줄 수 있다.

절대 다른 방식으로 바뀌지 않게 실행계획을 고정해야 하는 시스템들도 있음.

DB 애플리케이션 개발자라면 오티마이저가 생각못한 최적의 액세스 경로를 찾고, 그 방식으로 실행계획을 유도할 수 있어야 한다.

튜닝 전문가가 되는 공부 방법

데이터베이스 튜닝이란?

• (= 성능 튜닝) DBMS 성능 튜닝이라는 말이 정확하다.
• SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동

데이터베이스 튜닝 종류

• SQL 튜닝 : I/O 효율화, DB Call 최소화, SQL 파싱 최소화
• DB 설계 : 논리적 데이터 구조 설계, 물리적 저장 구조 설계 등
• 인스턴스 튜닝 : Lock/Latch 모니터링 및 해소, 메모리 설정, 프로세스 설정 등

데이터베이스에서 좋은 소스란, 옵티마이저가 효율적으로 처리할 수 있게 작성한 SQL을 말한다.

그리고 효과적인 데이터 구조가 중요하다.

DBA가 되고 싶다면?

• 데이터베이스 자체에 대한 연구가 중요하다.
• 데이터베이스 설치, 백업/복구, 오브젝트 생성/변경, 보안 등은 기본소양
• 데이터베이스 아키텍처를 완벽 숙지
• 데이터베이스를 운영하면서 생기는 여러 장애, 상황을 모니터링하고 해결하는 기술력과 자신만의 스크립트를 꾸준히 개발해야 한다.

SQL 튜너가 되고 싶다면?

• SQL 중심으로 공부해야 한다.
• 옵티마이저가 SQL을 파싱하고 통꼐정보를 활용해 실행계획을 생성하는 원리
• 옵티마이저 쿼리 변환 원리를 바탕으로 실행계획을 분석하는 방법
• 옵티마이저 힌트를 이용해 실행계획을 제어하는 방법
• 옵티마이저가 좋은 실행계획을 생성하도록 유도하기 위한 효과적인 SQL 작성법
• 애플리케이션에서 SQL을 실행할 때 사용하는 프로그래밍 인터페이스
• SQL을 빠르게 처리할 수 있는 좋은 데이터 구조와 파티션/인덱스 설계
• 정확성과 안정성을 확보할 수 있는 통계정보 수립 정책

SQL 튜닝이 데이터베이스 튜닝

데이터베이스 튜닝은 SQL이 병목이나 지연 없이 빠르고 안정적으로 수행되도록 조치하는 모든 활동이다.

• SQL과 친숙해져야 한다.
• 수많은 SQL과 실행계획을 분석하라!
• 좋은 데이터 구조를 설계하는 방법에 관심을 가져라!
• 수많은 시간과 노력을 기울여라!