[DB/SQLD] 옵티마이저(optimizer)

옵티마이저(optimizer)

옵티마이저는 sql의 실행계획을 수립하고 sql을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다.

sql개발자가 sql을 작성하여 실행할 때, 옵티마이저는 sql을 어떻게 실행할 것인지를 계획한다. 즉, sql 실행계획(execution plan)을 수립하고 sql을 수행한다.

동일한 결과가 나오는 sql도 어떻게 실행하느냐에 따라서 성능이 달라진다. 따라서 옵티마이저의 실행계획은 sql 성능에 아주 중요한 역할을 한다.

만약 옵티마이저가 비효율적인 실행계획을 수립하면, sql개발자는 sql을 개선해야 한다. 이때 옵티마이저에게 실행계획을 변경하도록 요청할 수 있는데 이때 힌트(HINT)를 사용한다.

# 옵티마이저의 특징

• 옵티마이저는 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다.
• 옵티마이저는 여러 개의 실행 계획 중에서 최저비용을 가지고 있는 계획을 선택해서 sql을 실행한다.

옵티마이저 실행계획 확인

DESC PLAN_TABLE;

옵티마이저는 sql실행계획을 plan_table에 저장한다. 그래서 sql개발자는 plan_table을 조회해서 실행 계획을 확인할 수 있다.

옵티마이저 실행 방법

1. 개발자가 sql을 실행하면 파싱(parsing)을 실행해서 sql의 문법 검사 및 구문분석을 수행한다.

2. 구문분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용기반으로 실행계획을 수립. 옵티마이저는 기본적으로 비용기반 옵티마이저를 사용해 실행계획을 수립한다. 비용기반 옵티마이저는 통계정보를 활용해 최적의 실행계획을 수립하는 것이다.

3. 실행계획수립이 완료되면 최종적으로 sql을 실행하고 실행이 완료되면 데이터를 인출(fetch)한다.

옵티마이저 엔진

옵티마이저	설명
Query Transformer	sql문을 효율적으롤 실행하기 위해서 옵티마이저가 변환한다. sql이 변환되어도 그 결과는 동일하다.
Estimator	통계정보를 이용해 sql 실행비용을 계산한다. 총비용은 최적의 실행계획을 수립하기 위해서이다.
Plan Generator	sql을 실행할 실행 계획을 수립한다.

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

규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행계획을 수립한다.

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

최신 오라클 데이터베이스는 규칙기반 옵티마이저보다 비용기반 옵티마이저를 기본적으로 사용한다.

비용 기반 옵티마이저는 오브젝트 통계 및 시스템 통계를 사용해서 총비용을 계산한다.

총비용이라는 것은 sql문을 실행하기 위해서 예상되는 소요시간 혹은 자원 사용량을 의미한다.

총비용이 적은 쪽으로 실행계획을 수립. 단, 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생할 수 있다.