4. oracle optimizer(미완)

1. Optimizer 개요

▶Optimizer의 역할

* SQL은 선언적 언어이다.

 - 절차적(procedural)언어는 모든 처리 과정을 사용자가 기술한다.

 - 선언적(Declarative)언어인 SQL문장이 표현하는 것은 : 원하는 데이터의 집합이지, 그 데이터를 얻기위한 구체적인 방법이 아니다.

* 하나의 SQL을 수행하는 방법은 매우 다양한데, 그 다양한 방법 중 어떤 것을 이용하느냐에 따라 실행 효율에 커다란 차이가 존재한다.

* SQL의 실행계획을 수립하는데, 가장 효율적인 실행계획을 찾는 것이 목표이다.

- 다양한 요소들이 전체적으로 고려되어야 하는 복잡한 문제로, RDBMS의 핵심요소이다.

* 최적화에는 크게 두가지의 접근방법이 존재한다.

Rule-based optimization (RBO)	미리 정해진 규칙에 따라 실행 계획을 수립 (ex.수동카메라)
Cost-based optimization (CBO)	실행비용(cost)의 예측을 통한 지능적인 실행계획의 수립 (ex.자동카메라)

 

 

▶SQL처리구조와 optimizer

 

 

 

▶Optimizer가 수행하는 연산

* 표현식(expression) 및 조건 평가

 - 상수를 포함하는 수식 또는 조건이 있는 경우 그에 대해 가능한 계산을 모두 수행

* 효율적인 수행을 위해 필요한 경우 주어진 SQL문장을 동등한 다른 SQL문장으로 변환

 - OR Expansion

 - Subquery Unnesting

 - View Merging

 - Predicate pushing

 - query rewrite

* 최적화 접근방법 및 최적화 목표의 결정

 - RBO 또는 CBO

 - CBO를 선택한 경우 → 전체 처리의 최적화? 아니면 가장 빠른 응답?

* 실행 계획의 결정

 - 최적의 접근경로

 - 최적의 join순서

 - 최적의 join방식

 

▶SQL실행계획

* 하나의 SQL문장을 실행하기 위해 oracle 서버는 여러 단계의 작업들을 수행하는데, 이러한 step들의 조합이 실행계획(Execution plan)이다.

* 실행계획을 구성하는 가장 중요한 요소 - 접근경로 / join순서 / join방식

* ex) 다음과 같은 SQL문장을 실행하는 경우

- 항상 바로 붙어있는 두개만 비교하면 된다.

- 순서 : 3→5→4→6

- 설명 : 붙어있는 3과 4만 비교했을 때 3이 먼저이므로, 3부터하고, 4와 5비교해서 5가 그다음인 것이다. 3과 5를 비교하는 것 아님!

 

 

2. Rule-based optimization(RBO)

▶Rule-based optimization(RBO)의 특징

* 미리 정해진 규칙에 의한 실행 계획 결정 → 상식에 의거하지만 융통성은 없다.

 - 연산자 우선순위

 - 접근 경로의 우선순위

 - SQL문장의 syntax규칙

* 데이터에 대한 통계 내지 실제 SQL을 수행할 경우에 소요될 비용에 대한 고려를 하지 않음.

 ex) 인덱스가 있다면 무조건 사용한다. 테이블의 크기, 인덱스의 효율등은 무시해서 인덱스를 사용하지 않아야 더 빠를 수도 있는데..

* oracle 버전 6이하와의 역 호환성을 위해 지원

 

▶RBO 순위 결정 방식 - 밑에서부터 시작한다.

순위	접근경로
1	Single row by ROWID
2	Single row by cluster join
3	Single row by hash cluster key with unique or primary key
4	Single row by unique or primary key
5	cluster join
6	Hash cluster key
7	Indexed cluster key
8	Composite index
9	Single-column index
10	Bounded range search on indexed columns
11	Unbounded range search on indexed columns
12	sort-merge join
13	MAX or MIN of indexed column
14	ORDER BY on indexed column
15	Full table scan

 

 

 

3. Cost-based optimization(CBO)

▶Cost-based optimization(CBO)의 특징

* 데이터에 대한 각종 통계를 사용한다.

* 실제 SQL을 수행할 때 소용될 비용을 예측하고 그 값을 기준으로 실행계획결정 → 보다 현실적이고 지능적인 판단 내린다.

 - I/O 횟수

 - CPU사용량

 - 메모리 사용량

 - 네트워크 전송량

* oracle7에서 처음 도입.

 - 지속적으로 향상.

 - 일반적으로 RBO보다 우수하다.

 

▶ CBO에서만 사용가능한 기능들

Ÿ테이블 및 인덱스의 Partitioning

Ÿ인덱스 구성 테이블 (Index-organized table)

ŸReverse key 인덱스

ŸFunction-based 인덱스

ŸSELECT 문장에서의 SAMPLE 절

Ÿ병렬 Query 및 병렬 DML

ŸStar Join 및 Star 변형

ŸOptimizer 확장

ŸMaterialized View를 이용한 Query rewrite

ŸEnterprise Manager progress meter

Ÿ해쉬 Join

Ÿbitmap 인덱스 및 bitmap Join 인덱스

Ÿ인덱스 skip scan 알고리즘

 

 

▶RBO와 CBO의 차이점 ★★ (다시 조사)

CBO가 개선이됐지만 통계정보등의 문제점이 있다. 해결하려면 대안도 얘기해줘야된다.

 

 

 

4. Optimizer 동작의 조정

▶Optimizer 모드 및 목표의 선택

* oracle 9i optimizer의 기본동작

→ 통계 정보를 update 많이 쳐줘야 한다. 그러면 속도가 느려져.(11g부터는 자동으로 업데이트치는 기술 등장AWR?)

 - CBO : 최소한 하나의 참조 개체에 대한 통계가 준비되어 있다면 사용한다. (일반적으로 사용)

- 기본적으로는 최적의 throughput이 목표이다. (전체 row의 빠른 처리를 지향)  - 빠른 Response time을 목표로 할 수 있도록 조정 가능 (처음 몇 row의 반환을 지향)

 - RBO : 해당 SQL문이 참조하는 개체들에 대해 사용할 수 있는 통계가 전혀 없는 경우 사용한다.

*사용자도 optimizer 모드 및 목표를 설정할 수 있다.

인스턴스 레벨	OPTIMIZER_MODE 파라미터의 설정 (파라미터 파일의 수정 또는 alter system 명령)
세션 레벨	OPTIMIZER_MODE 파라미터의 설정 (alter session 명령)
명령문 레벨	optimizer hint 사용

 

▶인스턴스 또는 세션 레벨

OPTIMIZER_MODE 파라미터
CHOOSE	oracle9i optimizer의 기본 동작에 따름. (default 값)
RULE	통계 존재 여부에 상관없이 RBO 사용.
ALL_ROWS	전체 처리율의 최적화
FIRST_ROWS[_n]	처음 결과가 나올 때까지의 시간을 줄이기 위해 최적화.  n=1, 10, 100, 또는 1000

 

▶SQL 문장 레벨 

Optimizer hint
/*+ CHOOSE */	oracle 9i optimizer의 기본동작에 따름.
/*+ RULE */	통계 존재 여부에 상관없이 RBO사용.
/*+ ALL_ROWS */	전체 처리율의 최적화
/*+ FIRST_ROWS[(n)] */	처음 결과가 나올 때까지의 시간을 위해 최적화 (n=임의의 자연수)

 

▶Join Key인덱스와 Join순서

 

 

74~81page

 

 

 

 

 

 

 

 

 

 

▶RBO동작의 조정

* RBO는 SQL의 syntax에 기반하며, 내장된 규칙에 따라 동작하므로, 조정이 어려우며 조정할 수 있는 범위 또한 제한적이다.

 - FROM 절에서 테이블 순서 변경

 - WHERE 절에서 순서 변경

 - 인덱스의 생성 및 삭제

 - 기타 syntax 트릭의 사용

*참조* syntax트릭에 대해서..

select * from emp  where ename='FORD'  and sal between 1000 and 2000  → 상식적으로는 sal이 1000과 2000사이인 데이터보다 ename이 FORD인 데이터가 더 적을 것이라 예상하기 때문에, RBO는 앞에 있는 문장(인덱스 있는 것)을 먼저 읽게 된다. 그런데 예상외로 ename이 FORD인 데이터가 더 많을 수도 있다. 그래서 DBA가 미리 예상하고 쿼리를 아래처럼 바꿔줘야한다.     select * from emp  where substr(ename,1,4)='FORD'  and sal between 1000 and 2000  → 인덱스 걸려있는 것에 함수를 써버리면, RBO가 인덱스를 못쓰니까 sal을 먼저 읽게 만들 수도 있다.

 

▶ CBO 동작의 조정

 * 데이터에 대한 통계 수집 (가장 원칙적인 방법)

  - CBO가 최적의 실행계획을 산출하기 위해서 정확한 통계 정보의 제공이 필수적이다.

 * Hint의 사용 - SQL문장 레벨

 * CBO의 행동에 영향을 주는 파라미터 설정 - 인스턴스 또는 세션 레벨 

 

Optimizer Hint

* 실행 계획의 결정은 원래 optimizer의 역할이나 optimizer가 반드시 최적의 실행계획만을 산출하는 것은 아니다.  * 사용자가 optimizer가 알 수 없는 정보를 알고 있을 수 있으므로 사용자는 Hint를 이용하여 optimizer의 결정에 영향을 미칠 수 있다.  * 필요한 경우 Hint를 전역적으로 저장하는 방법에 의해 실행 계획의 일관성을 제공해주는 Stored outline 기능을 사용할 수 있다.  * Hint는 /*+ RULE */ 을 제외하면 항상 CBO를 호출한다.

hint문법

select /*+ index(e idx_emp_ename) */    -- 올바른 문법  select /* + index(e idx_emp_ename) */    -- 이렇게 /*+ 사이에 공백이 들어가면 안된다.

Hint 사용의 규칙

* SQL 블록의 첫 키워드 바로 뒤에 입력  * 각 블록에는 하나의 힌트 주석만 있어야 하지만, 하나의 힌트 주석은 여러 개의 힌트 포함 가능하다.   ex) /*+ a b c */  * 힌트는 해당 블록에만 적용  * 문장에 alias 를 사용하는 경우 힌트는 그 alias를 참조해야 한다.  * 힌트는 Optimizer에 의해 무시될 수 있다.   - syntax 또는 semantics상 잘못 지정된 hint   - 비효율적인 실행 계획을 지정하는 hint

Hint 사용 예

UPDATE /*+ INDEX(e EMPLOYEES_JOB_IX) */        employees e    SET e.salary =        (SELECT /*+ INDEX(m EMPLOYEES_SALARY_IX) */               (e.salary + m.salary)/2           FROM employees m          WHERE m.emp_id = e.mgr_id               AND m.salary > e.salary)  WHERE e.job = 'INSTRUCTOR'    AND e.hiredate > TO_DATE('01/01/1998',                             'dd/mm/yyyy');

 

CBO에 영향을 주는 파라미터들

ŸOPTIMIZER_MODE  ŸOPTIMIZER_FEATURES_ENABLE  ŸCURSOR_SHARING  ŸDB_FILE_MULTIBLOCK_READ_COUNT  ŸSORT_AREA_SIZE, HASH_AREA_SIZE, PGA_AGGREGATE_TARGET   ŸHASH_JOIN_ENABLED  ŸOPTIMIZER_INDEX_CACHING  ŸOPTIMIZER_INDEX_COST_ADJ  ŸOPTIMIZER_MAX_PERMUTATIONS  ŸPARTITION_VIEW_ENABLED  ŸQUERY_REWRITE_ENABLED  ŸSTAR_TRANSFORMATION_ENABLED

 

 

5. CBO 통계의 수집

▶CBO 내부 구조

 

* Estimator : 다음의 것들을 예측한다. (caldinality, selectivity, cost)

* plan의 조사는 정교한 내부 알고리즘에 의해 일정시간 내에 완료한다.

* 최종적으로 cost가 가장 작은 plan이 선택된다.

 

▶Cost 계산의 기본 요소 : 데이터 볼륨

* cardinality : 어떤 row set에 속하는 row들의 개수

* selectivity(선택도) : 어떤 row set에 특정한 조건을 적용하는 경우 그 조건을 만족하는 row들의 비율

 - Query의 술어(predicate), 칼럼의 값들의 분포도 들과 밀접한 관련이 있다.

 

▶CBO와 통계정보

* cardinality, selectivity, 궁극적으로 cost계산을 위해서는 데이터의 정확한 통계정보가 필요하다.

* 통계정보는 static dictionary에 저장하는데, 자동으로 되지 않으므로 사용자에 의한 수집이 필요하다.

 - ANLYZE 명령과 DBMS_STATS 패키지 (단, 10g부터는 AWR이라는 기능 때문에 정해진 시간에 자동으로 update친다.)

 

ANLYZE 명령

- estimate : 기존에 만들어져 있는 통계정보가 유효한지 안한지 검증한다.

DBMS_STATS패키지

* oracle 8.1.6에서 도입  * 통계 정보의 수집 외에도 개별 통계값의 설정, Dictionary와 사용자 정의 테이블 간의 통계 이동과 같은 기능도 제공한다.  * ANALYZE에 비해 우월하다.   - 다양한 작업이 가능   - 테이블, 인덱스 단위 뿐 아니라 schema, 데이터베이스 전체에 대한 통계 수집 가능   - 통계 수집 작업의 병렬화 가능

 

▶통계 정보 수집 예

ANALYZE 명령을 사용하는 경우

ANALYZE TABLE sh.customers  ESTIMATE STATISTICS ;

DBMS_STATS 패키지를 사용하는 경우

dbms_stats.GATHER_TABLE_STATS (    ‘sh‘,  -- schema    ‘customers‘,  -- table    NULL,  -- partition    20,  -- sample size(%)    TRUE,  -- block sample?    'FOR ALL COLUMNS‘,  -- column spec    4,  -- degree of parallelism    'DEFAULT‘,  -- granularity    TRUE  -- cascade to indexes  );

 

▶테이블 통계 (아직은 세부적인 내용 몰라도되)

DBA_TABLES  ALL_TABLES  USER_TABLES

- 행 수

- 사용된 블록 수 (항상 정확)

- 빈 블록 수 (항상 정확)

- 블록 내의 평균 사용 가능 공간

- 체인화 되거나 이전된 행 수

- 평균 행 길이

- 마지막 ANALYZE 날짜 및 표본 크기

 

▶인덱스 통계 (아직은 세부적인 내용 몰라도되)

DBA_INDEXES  ALL_INDEXES  USER_INDEXES

- 인덱스 레벨 (항상 정확)

- Leaf 블록 수

- Distinct key의 수

- key당 평균 leaf 블록 수

- key당 평균 데이터 블록 수

- 인덱스 항목 수

- clustering factor

- 마지막 ANALYZE 날짜 및 표본 크기

 

▶데이터 분포와 Histogram

* 데이터의 분포가 균등한 경우 동등 조건의 selectivity

selectivity = 1 / # distinct values

- 하지만 실제 데이터의 분포도는 그렇지 않을 수 있다. 따라서 데이터 분포의 비대칭성을 나타낼 수 있는 통계 정보가 필요.

 

▶Histogram의 생성

ANALYZE 명령을 이용한 histogram 생성

ANALYZE TABLE 테이블명  COMPUTE STATISTICS FOR COLUMNS 칼럼명 SIZE n;

DBMS_STATS 패키지를 이용한 histogram 생성

EXECUTE DBMS_STATS.GATHER_TABLE_STATS (    ‘schema_name’,    ‘table_name’,    METHOD_OPT => ‘FOR COLUMNS SIZE n column_name’  );

 

▶인덱스와 데이터 분포도 예제

→ 통계정보도 있고, 인덱스도 존재하는데, 실행계획을 인덱스로 하지 않고, full scan했다. 왜냐하면 데이터의 분포도에 대한 정확한 통계 정보가 없었기 때문이다.     → 그래서 분포도를 조사해보니 REGI에 다 몰려있는 것으로 확인된다. 이런 상황을 skewed되어있다고 하는데, 이 경우에 histogram을 생성해준다.     → histogram을 생성해주니 인덱스를 사용하면서 속도가 굉장히 빨라졌다.     → HISTOGRAM의 문제점 : bind 변수를 쓸 수 없다.   → bind는 사용자에게 입력을 받는 것인데, histogram으로 만들어 버리면 사용자에게 입력받으면 무조건 full scan을 해야 되므로..

 

 

▶Histogram 가이드라인

* 데이터 분포의 불균형이 심한 칼럼에만 사용

* 추가적 저장 공간이 필요하므로 상당한 성능 개선이 있는 경우에만 사용

* 바인드 변수를 사용하는 경우 histogram은 사용되지 않는다.