OPTIMIZER

Server Process는 자기가 직접 실행계획을 세우지 못하기 때문에 

Optimizer가 실행 계획을 세워 전달해 준다.

일반적으로 SQL은 선언적 언어 = Declarative

원하는 결과만 알려주고 계획은 스스로 판단

즉, 원하는 데이터의 집합만 알려줄 뿐, 그 데이터를 얻기 위한 구체적 방법은 제시하지 않음

모든 처리 과정까지 사용자가 직접 기술하는 것은 절차적 언어 (Procedural)

하나의 SQL을 수행하는 방법은 여러가지가 있지만

그 중에서 어떤 것으로 이용하냐에 따라 실행 효율에 차이가 생긴다.

--> Optimizer는 실행 계획들을 수립하고, 그중 가장 효율적인 실행 계획을 찾는다. 

Parser를 통해 표현식 및 조건 평가

조건이 있는 경우 그에 대해 가능한 계산을 모두 수행

Query Transformer가 효율적인 수행을 위해

주어진 SQL 문당을 동등한 다른 SQL 문장으로 변환

Optimizer는 최적화 접근 방법 및 최적화 목표 결정

RBO? CBO?

최적의 접근 경로 (Index의 유무), 최적의 Join 순서와 방식을 계산해

최고의 실행계획을 결정

이 세가지가 실행계획을 구성하는 가장 중요한 요소!!

Optimizer 모드

RBO와 CBO

RBO = Rule-Based Optimization

Optimizer가 미리 정해진 규칙(15개)에 따라 실행 계획을 수립

9i까지는 RBO 적용

연산자 우선 순위, 접근 경로의 우선 순위, SQL 문장의 syntax 규칙 등 정해진 규칙이 있기 때문에 예측 가능하고, 다른 변수가 없다

하지만 융통성 ZERO -> 상황 판단을 못한다.

밑에서부터 실행됨

예) select * from emp where empno=7901 ;

empno에 index가 생성되어있다고 가정하면 15 -> 9 -> ... 이런식으로 규칙들을 비교해보고 실행 계획을 세운다.

숫자가 적을 수록 빠르고 좋음!

CBO = Cost-Based Optimization

데이터에 대한 각종 통계를 사용, 각종 통계는 Dictionary에 있음

Static Dictionary인 경우 자동 업데이트가 되지 않기 때문에 업데이트를 꼬박꼬박 규칙적으로 해줘야함

통계 정보가 정확하지 않으면 실행계획 엉망!

즉, CBO에서는 통계정보를 맞춰줘야하는 것이 가장 중요하다.

실행비용 즉, cost의 예측을 통한 지능적인 실행 계획 수립

실제 SQL 수행할 때 소요될 비용들을(I/O 횟수 / CPU, 메모리 사용량 / 네트워크 전송량) 예측하고 그 값을 기준으로 실행 계획을 결정한다

즉, RBO보다 현실적이고 지능적인 판단을 내림

전체 처리의 최적화냐 일부 데이터만 처리해 빠른 응답을 유도할 것인가는 사용자가 결정

Oracle 9i 인 경우 RBO가 기본값이지만

최소한 하나의 통계가 준비되어 있다면 CBO가 사용된다.

사용자가 직접 Optimizer 모드 및 목표를 설정할 수 있음

사용되는 파라미터는 OPTIMIZER_MODE

- 인스턴스 레벨 

OPTIMIZER_MODE 파라미터 사용 또는 "alter system" 명령으로 가능

- 세션 레벨

OPTIMIZER_MODE 파라미터 사용 또는 "alter session" 명령으로 가능

- 명령문 레벨

Optimizer Hint 사용

OPTIMIZER_MODE 파라미터 값

CHOOSE -> default, 값

RULE -> 통계 존재 여부에 상관없이 무조건 RBO 사용

ALL_ROWS -> 전체 처리율의 최적화 (CBO)

FIRST_ROWS[_n] -> 처음 결과가 나올 때까지 시간을 줄이기 위해 최적화, n에는 1, 10, 100, 1000 (CBO)

Optimizer Hint 

Optimizer가 반드시 최적의 실행 계획만을 산출하는 것은 아니다.

사용자는 알고 있지만 optimizer가 알 수 없는 정보도 있으므로 Optimizer Hint를 통해

Optimizer의 결정에 영향을 미칠 수 있다.

쓰는 방법은

/*+ HINT */

SQL 문장의 첫 키워드 바로 뒤에 입력

여러개 힌트도 쓸수 있다. 방법은 /*+ HINT여러개 */ -> 힌트들은 공백으로 구분

예)

UPDATE /*+ INDEX(e EMPLOYEES_JOB_IX) */ employees e

SET e.salary = ~~~

RBO 동작 조정

RBO는 내장된 규칙에 따라 동작하기 때문에 조정이 어렵고 조정할 수 있는 범위가 제한적

FROM 절에서 테이블 순서 변경

WHERE절에서의 순서 변경

INDEX 생성 및 삭제

기타 syntax 트릭 사용 

-> 예를 들어,

select * from emp 

where ename = 'FORD'

and sal between 100 and 200 ;

ename, sal 두칼럼 모두 인덱스 존재

동시에  index 읽지 못하니까 많이 걸러낼 수 있는 것 부터 읽어야 빠르다.

그래서 일반적으로 ename -> sal 이런 식으로 읽게되지만 

(RBO는 융통성이 없어서 무조건 ename부터 읽게됨)

막상 뚜껑을 열어보면 sal 칼럼이 더 걸러낼 수있었음 

이 문제를 해겨하기 위해서는 ename 인덱스를 사용하지 못하도록 조치

where substr(ename,1,4) like 'FORD'

이런식으로 쿼리를 수정해주는 것이 syntax 트릭 사용

CBO 동작 조정

CBO가 최적의 실행 계획을 만들기 위해서는 정확한 통계정보가 있어야한다.

하지만 통계정보가 절대적이지는 않음

Optimizer가 모르는 정보가 있을 수 도 있으니까 Hint를 사용해 도와준다.

CBO 행동에 영향을 줄 수 있는 파라미터를 사용해 조정

예) OPTIMIZER_MODE

CURSOR_SHARING

DB_FILE_MULTIBLOCK_READ_COUNT -> FULL SCAN시 한번에 몇개의 블럭을 읽어올건지 설정

INDEX SCAN은 한번에 한 블럭만을 읽어온다 = Single I/O

하지만 FULL SCAN은 한번에 여러개의 블럭을 퍼올림, 이떄 몇개를 퍼올릴건지 설정하는 파라미터이다.

BUT! 몇만개 써놔도 운영체제에 따라 다름, 커널이 한번에 읽어올수 있는 블럭 수가 16개가 MAX 라면, 100으로 설정해도 16개만 읽어옴

SORT_AREA_SIZE -> 정렬 작업 공간 설정

HASH_AREA_SIZE -> Hash 작업 공간 설정

PGA_AGGREGATE_TARGET -> PGA 총량을 결정 sort 작업공간과 hash 작업 공간을 auto로 알아서 관리

(임시 결과를 저장해 두는 곳 = runtime_area = array)

CBO의 내부구조

Estimator는 

Cardinality

Selectivity

Cost

를 계측해서 실행 계획을 세운다.

Cardinality - row들의 개수

cardinality가 높다는 의미는 데이터가 겹치는게 없다, = 분포도가 좋다 = 선택도가 낮다. eg) 주민등록번호

Selectivity - 선택도

optimizer는 통계정보에 들어있는 총 데이터 건수, 해당 데이터 건수를 바탕으로 선택도를 계산

Cost

하나의 SQL 문장을 수행하기 위해 필요한 리소스의 소요량

I/O 횟수(얘가 사실 제일 중요, 이걸 줄여야 속도 up!), CPU와 메모리 사용량, 네트웍 전송량 등

Cardinality와 Selectivity를 기반으로 계산된다.

Cardinality와 Selectivity, Cost의 계산을 위해서는 정확한 통계정보가 필요!

통계 정보는 Static Dictionary에 저장되어 있다.

자동으로 업데이트 되지 않기 때문에 사용자가 직접 관리를 해줘야함

방법에는

ANALYZE 명령

DBMS_STATS패키지

ANALYZE 명령

eg) analyze table scott.emp compute statistics ; -> 통계정보를 새로 계산

analyze table scott.dept estimate statistics ; -> 만들어져 있는 통계정보를 검사, 누락된 정보는 추가 (compute보다 부하가 적읍)

DBMS_STATS패키지

ANALYSE에 비해 우월함! 

테이블, 인덱스 단위 뿐 아니라 schema, DB 전체에 대한 통계수집도 가능하다.

통계 수집작업 시 병렬화 기능도 있음 와우

eg) exec dbms_stats.gather_table_stats('scott') --> scott의 object들은 모두 update

eg) exec dbms_stats.gather_table_stats('scott','emp') --> scott.emp 테이블만 update

문제는 데이터의 분포도가 좋지 못할 경우 (치우칠 경우)

ANALYZE 명령이나 DBMS_STATS패키지가 작동하지 않을 수도 있다.

따라서 데이터 분포의 비대칭성도 나타낼 수 있는 통계 정보가 필요 -> Histogram

Histogram 생성방법

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE n;

또는

EXECUTE DBMS_STATS.GATHER_TABLE_STATS (‘schema_name’, ‘table_name’,

METHOD_OPT => ‘FOR COLUMNS SIZE n column_name’);

해당 칼럼의 데이터 분포가 skewed 되어 있으면

해당 칼럼에 인덱스가 존재하고 일반 통계정보가 있어도

optimizer가 데이터의 skewed를 알지 못하고 Full Scan이 수행되기 때문에

Histogram을 생성해 인덱스를 사용할 수 있도록 조치

하지만 만드는 시간도 용량도 배로 든다는 단점이 있음

또한 bind 변수를 사용하는 경우는 histogram은 사용되지 않는다.

why? 실행계획 생성 이후에 bind 과정이 이루어지므로 histogram은 사용될 수 없음

출처 : http://hayleyfish.tistory.com/124