옵티마이저

옵티마이저란?

  -  DBMS의 두뇌라고 할 수 있다.

 

SOFT PASING -

1. SQL 문법적 오류 검사

2. SQL을 이전에 실행한 적이 있는지 메모리 검사

3, 이전에 실행한 기록이 있으면 실행했던 방식으로 실행

HARD PASING - 실행 계획이 없다면 SQL에서 사용하고 있는 객체들에 대한 접근권한이 있는지를 체크 후 어떤 방식으로 처리할 것인지 실행 계획을 세우는것.

 SQL 처리과정

1. SQL문 사용자가 실행

2. 데이터 딕셔너리 정보를 참조하여 SQL문에 대한 구문분석(Syntax와 Symantics)을 수행 이 결과를 파스-트리(Parse-Tree)라 한다.

3. 파스트리는 옵티마이저에게 전달되고 옵티마이저에는 비용기반 또는 규칙기반을 선택함.

4. 비용기반 옵티마이저에 의해 산출된 적정 플랜은 로우소스 생성기에 전달

5. 이것은 실행 계획이 된다.

 

소트프 파싱

1. SQL 인입

2. 문법검사

3. SHARED POOL 안에 동일 SQL 실행 기록이 있는지 확인

4. SHARED POOL 안에 동일한 SQL이  있으면 실행

하드 파싱

1. SQL 인입

 

2. 문법검사

3. SHARED POOL 안에 동일 SQL 실행 기록이 있는지 확인

4. 실행 기록이 없으면 객체 및 컬럼에 대한 접근 권한 검사

5. 옵티마이저 모드에 따른 최적의 실행 계획 수립

6. SHARED POOL에 실행 계획 저장

7. 실행 

 

옵티마이저의 종류

1. 규칙기반 옵티마이저(Rule-Based Optimizer, RBO)

 - 특정 규칙을 기반으로 계획을 세운다. 

 - 처리 방식의 우선순의를 정해 놓고 가능한 우선순위가 앞서는 방식을 채택하도록 한다.

 - 이 우선 순위를 RANK라 한다.

1. ROWID에 의한 단일 실행

2. 클러스터 조인에 의한 단일행 실행

3. HASH CLUSTER KEY에 의한 단일행 실행

4. UNIQUE KEY 또는 PRIMARY KEY에 의한 단일행 실행

5. 클러스터 조인

6. HASH CLUSTER KEY

7. INDEXED CLUSTER KEY

8. 결합 인덱스

9. 단일 컬럼 인덱스

10. 인덱스에 의한 컬럼의 BOUNDED  RANGE

11. 인덱스에 의한 컬럼의 UNBOUNDED RANGE

12. SORT MERGE JOIN

13. 인덱스로 구성된 컬럼의 MAX 또는 MIN 처리

14. 인덱스로 구성된 컬름의 ORDER BY

15. FULL TABLE SCAN

2. 비용기반 옵티마이저(Cost-Based Optimizer, CBO)

 - 비용을 계산해서 최소의 비용으로 처리할 수 있는 실행계획을 세운다.

 - 비용은 옵티마이저가 문장을 수행하는데 걸릴것이라고 예상되는 추측 시간.

 - ORACLE에서 비용이란 실행에 필요한 예측시간을 Single Block I/O Time으로 나눈 값이다.

 - I/O는 Single Block I/O 와 Multi Block I/O로 분류한다.

 - Single Block I/O는 한 번에 하나의 블록만 읽는 방식이고 Multi Block I/O는 한 번에 인접해 있는 여러 개의 블록을 읽는 방식이다.

 -  즉 Single Block 방식을 채택했을때 수행 시간으로 나눈 값이 비용이 된다.

 - 예측시간 = CPU 시간 + I/O 시간 이므로 결국 데이터 건수, 데이터를 보유하고 있는 불록의 수, 데이터의 분포도, CPU, I/O등 다양한 통계 정보를 참고한다.

 (1) 동작 방식

 

Query Transformer(쿼리 변형기)

 -  파서에 의해 구문 분석된 결과를 전달 받아 잘못 작성된 SQL문을 정확한 문장으로 변형시키는 역할을 수행

예를 들면

SQL> SELECT * FROM emp WHERE s_date = '1999-01-01';
--> SQL> SELECT * FROM emp WHERE s_date = TO_DATE('1999-01-01');

? LIKE 연산자는 %(와일드 카드)와 함께 검색하는 경우 사용되지만, 그렇지 않은 경우
=(동등) 조건으로 변형되어 검색된다.

--> SQL> SELECT * FROM emp WHERE ename = ‘주종면’;

Estimator(비용계산기)

 - 옵티마이저가 가지고 있는 비용 계산 공식에 의해 다양한 실행방법 중 좋은 성능의 실행계획을 찾아 주는 알고리즘

 - 시스템상의 통계정보와 SQL에서 사용하고자 하는 객체들의 통계정보를 딕셔너리로 부터 수집하여 SQL을 실행할 때 소요되는 총 비용 계산

Plan Generator(실행 계획 생성기)

 - Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행 계획을 도출

 - 최종적으로 선발된 실행 계획은 Row Source Generator를 통하여 출력 하능한 코드 형태로 바꾼다.

(2) 모드

 - 비용기반 옵티마이저도 여러 종류로 나뉜다. 이를 모드라하며 이 모드에 따라 최적의 비용을 구하는 옵티마이저의 결정 방식이 조금씩 달라진다.

 -  OPTIMIZER_MODE 라는 파라미터로 각각의 모드를 지정할 수 있다.

•   CHOOSE

              - 도입 초기에는 비용 또는 규칙 기반을 선택 할 수 있도록 하는 CHOOSE 모드를 제공했다.

              - 현재는 거의 사용하지 않는다.

• FIRST_ROWS

  - 과거 버전과의 호환을 위해 제공하는 모드로, 비용기반 과 규칙기반 옵티마이저를 혼용한 형태.

  - 가급적 사용하지 않을 것을 권장.

• FIRST_ROWS_n

              - FIRST_ROWS 모드의 단점을 보완한 비용 기반 옵티마이저

  - SQL의 실행 결과를 출력하는 데까지 걸리는 응답 속도를 최적화 하는것이 목표이고 최초에 출력할 수 있는 행의 수를 조정할 수 있는게 특징

• ALL_ROWS

  - SQL 실행 결과 전체를 빠르게 처리하는데 최적화된 실행 계획을 세우는것이 목표

 - ORACLE 10g 버전 이후로는 이 값이 기본값이다.

 

옵티마이저 최척화를 위한 고려사항

 - 몇 가지 유의해야할 사항들

(1) 통계정보

 - 비용기반 옵티마이저가 실행 계획을 수립하기 위해 참고하는 통계정보 

구분	세부정보내역
테이블	테이블의 전체 행의 수
	테이블이 차지하고 있는 전체 블럭수
	테이블의 행들이 갖는 평균 길이
컬럼	컬럼 값의 종류
	컬럼 내 NULL 값 분포도
	컬럼 값의 평균 길이
	컬럼 내 데이터 분포의 추정치
인덱스	LEAF BLOCK수 : 데이터를 보관하는 블록 수
	LEVELS : 인덱스 트리의 LEVEL 정보
	CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여 있는 밀집도
시스템	I/O 성능 및 사용률
	CPU 성능 및 사용률 등

 

 - 지속적으로 데이터가 입력,수정, 삭제 되는 데이터베이스라면 그 값이 지속적으로 변할 것이며, 값이 주기적으로 업데이트 되지 않으면 통게 정보로써 가치도 없어지는 것이다.

 - 통계정보를 생성하는 방법은 2가지가 있다. 하나는 ANALIZE명령문을 사용하는 방법과 ORACLE이 제공하는 DBMS_STATS 패키지를 사용하는 방법

 - DBMS_STATS 패키지는 병렬로 통계정보를 생성할 수 있고 파티션에 대한 글로벌 통계 정보 수집이 가능하기 때문에 가급적 DBMS_STATS 를 사용할것을 권장

 - 어떤 방법으로 통계정보를 수집할 것인가를 결정했으면 어느 시점에 통계 정보를 수집할지 결정해야 한다. 왜냐하면 통계정보 생성 시점이 서비스에 부하및 성능저하에 원인이 되기 때문이다.

 - 그다음엔 자동 또는 수동 방식으로 할지 정책을 세워 관리해야 한다.

• 자동 통계 정보 생성

- 통계정보의 생성 주기 및 수행 시간을 스케줄러에 등록하여 일정 주기마다 수집할 수 있음.

- 명령문 예시

: 시작

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE (

client_name => ‘auto optimizer stats collection’,

operation => NULL,

window_name >= NULL);

END;

:  종료

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE (

client_name => ‘auto optimizer stats collection’,

operation => NULL,

window_name => NULL);

END;

- 장점 : 관리자의 번거로움을 덜어주고, 주기적으로 통계정보를 업데이트 할 수 있음.

- 단점 : 시작 및 종료시점에 대한 정확한 계산을 하지 않을 경우 장애의 원인이 될 수 있음.

• 수동 통계 정보 생성

- 객체들에 대한 정확한 이해를 바탕응로 개별 관리를 하고자 하는 경우에 사용됨.

- 데이터 베이스, 스키마 및 계정, 테이블, 인덱스 단위로 구분하여 통계 정보 수집 가능.

- 스키마나 테이블 단위로 통계정보를 생성하는 경우, LOCKING 기능 설정 가능. 

                                           (특정 테이블에 대해 통계정보 수집 작업을 막을 수 있음 -> 용도에 따른 정책 수립)

- 명령문 예시

: 특정 스키마에 대한 통계정보 생성 잠금 및 해제

  EXEC DBMS_STATS.LOCK_SCHEMA_STATS(’schema_name’);

  EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS(‘shcema_name’);

: 특정 테이블에 대한 통계 정보 생성 잠금 설정 및 해제

  EXEC DBMS_STATS.LOCK_TABLE_STATS(’schema_name’, ‘table_name’);

  EXEC DBMS_STATS.UNLOCK_TABLE_STATS(‘schema_name’, ‘table_name’);

: 특정 스키마에 대한 통계 정보 생성

  EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ’schema_name’, 

  CASCADE => TRUE, ESTIMATE_PERCENT => 10, DEGREE => 4);

: 특정 테이블에 대한 통계 정보 생성

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

  ESTIMATE_PERCENT => 5, DEGREE => 6, CASCADE => FALSE);

: 특정 인덱스에 대한 통계 정보 생성

  EXEC DBMS_STATS.GATHER_INDEX_STATS(’schema_name’, ‘index_name’,        

   ESTIMATE_PERCENT => 5, DEGREE => 6);

—> CASCADE : 해당 스키마와 연관이 있는 객체들에 대해서 통계 정보를 생성할지 결정하는 옵션

—> ESTIMATE_PERCENT : 통계 정보를 수집할 샘플 데이터의 비율

—> DEGREE : 병렬처리 정도

(2) 옵티마이저 파라미터

•  OPTIMIZER_MODE

    - 옵티마이저 모드를 결정하는 파라미터 기본값은 ALL_ROWS

    - 모드를 변경하면, 실행계획이 전혀 다르게 바뀔 수 있기 때문에 주의

    - 모드는 DBMS단위, 세션 단위, SQL 단위로 설정하는것이 가능 - > SQL 단위의 옵티마이저가 최우선 적용(세션,DBMS 단위 순으로 적용)

•  OPTIMIZER_DYNAMIC_SAMPLING

- PASING하는 동안 통계정보가 없는 객체를 발견하면, 해당 객체의 통계정보를 자동으로 수집하는 기능

 - DYNAMIC_SAMPLING 기능을 사용할지 여부를 결정하는 파라미터 기본값은 2(사용하겠다는 의미)

    0 : DINAMIC_SAMPLING 기능을 사용하지 않음

    2 : 통계 정보가 없는 모든 테이블을 대상으로 통계정보 자동 수집

    4 : WHERE절에 두 개 이상의 컬럼을 참조하고 있는 모든 테이블을 대상으로 통계 정보를 자동 수집

    10 : SQL에서 사용된 모든 테이블 대상으로 통계정보 자동 수집

• _OPTIM_PEEK_USER_BIND

- 바인드 변수를 사용하여 값을 입력 받는 SQL에 대해 처음 입력된 값을 통해 생성된 실행계획을 가지고 있다가, 이후 같은 SQL이 실행되면 바인드 변수 값에 상관없이 이 실행 계획을 다시 사용하게 하는 기능 

 - BIND_PEEKING 기능의 사용 여부를 결정하기 위한 파라미터 기본값은 TRUE

 -  BIND_PEEKING 기능을 사용하면 특정 값에 편향된 실행 계획이 수립될 가능성이 높기 때문에 유의 필요

예) SELET E.ename FORM emp E WHERE E.city = :1;

 - 처음 문장을 실행 했을때 '속초'로 변수값이 입력됫고 실제 사원들의 대부분이 서울에 거주하고 있다고 가정하면 적은 수의 사원이 속초에 살고 있으므로 옵티마이저는 INDEX_RANGE_SCAN 방식으로 검색하는 것이 유리하다고 판단 할것이고 이후 '서울' 이라는 값을 입력받으면 오라클은 같은 SQL이라고 판단하므로 FULL_SACN이 유리함에도 불구하고 INDEX_RANGE_SCAN 방식으로 사용하여 결과 도출 함,

 - 따라서 데이터의 비율이 고르지 않고 특정 데이터에 집중되어 있는 상황이라면 이 기능은 가급적 활성화하지 않을 것을 권장함.

(3) 옵티마이저의 한계

• 바인드 변수 사용의 한계

 - 비교할 컬럼의 데이터 분포도가 고르지 못한 경우, 비용이 지나치게 많거나 적은 데이터에 치중하여 실행계획이 수립될 수 있다.

  - 바인드 변수의 예측 가능한 경우, 적절한 힌트를 사용하거나 ADAPTIVE_CURSOR_SHARING 기능(11g 이후)를 사용할 수 있다,

  - ADAPTIVE_CURSOR_SHARING :  입력받은 데이터의 분포도를 고려하여 실행계획을 공유하는 기능.

• 잘못된 비용 계산의 한계

 - 조건절의 조건이 복잡해질수록 통계 정보를 통해 예측하는 옵티마이저의 비용 계산도 정확도가 떨어진다.

•  동시성을 배제한 비용 계산의 한계

- 옵티마이저는 비용 예측시 해당 SQL이 단독으로 실행된다는 전제로 계산함.

 - 여러 SQL이 동시에 실행되면서 동일한 블록에 동시에 접근하는 상황이 발생할 경우, 대기 이벤트가 발생

 - 이런 경우는 대기 이벤트에 따라 해결방법을 모색해야 함.

• 히스토그램의 한계

- 히스토그램의 정보를 수집하기 위해 설정할 수 있는 버킷의 수는 최대 254개

- 254개 이상의 값을 갖는 컬럼의 경우 비용 예측결과가 정확하지 않을 수 있다.

- 문자타입 경우 최초 32개 문자에 대한 통계 정보만으로 히스토그램을 관리 함.

• 날짜 유형에 대한 비용 예측의 한계

                 - 날짜 유형에 대한 데이터를 DATE 타입이 아닌 문자 타입의 컬럼으로 사용할 경우, 정확한 비용 예측이 어렵다.

• 통계정보의 한계

- 데이터 수가 급격하게 변하는 테이블의 경우, 통계정보 생성 주기에 유의해야 함.

 

 

이미지  : http://5dol.tistory.com/28

참고문헌 :   실전 사례로 살펴보는 SQL 튜닝 비법 - 저자 강남이, 권지윤, 김남훈, 김윤성, 박중건

참고URL :