쏘니헨리

1. 옵티마이저의 개념 

- 사용자가 실행한 SQL을 해석하고 데이터를 추출하기 위한 실행계획을 수립하는 프로세스

2. 옵티마이저의 종류 

RBO 

- 규칙기준 옵티마이저는 인덱스 구조나 사용하는 연산자에 따라 부여되는 순위가 정해져 있음

CBO 

- 대상 ROWS를 처리하는데 필요한 자원사용을 최소화해서 데이터를 빨리 처리하는데 목적이 있음 

3. 옵티마이저 레벨별 설정 

데이터베이스 전체에 지정 

[initSID.ora를 이용해서 지정]

OPTIMIZER_MODE=[RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]

세션별로 지정 

SQL > ALTER SESSION SET OPTIMIZER_MODE =

         [RULE/CHOOSE/FIRST-ROWS/ALL-ROWS]

각 SQL 별로 지정 

SQL > SELECT/ * first_rows * /

                     ename 

         FROM EMP ; 

RBO와 CBO의 실행계획 비교 

- 동일 SQL에 대해서 각 옵티마이저가 수립한 실행계획은 다를 수 있음 

- SQL 퍼포먼스가 옵티마이저에 따라 다르다는 의미 

- 옵티마이저의 종류에 따라 달라지는 DB성능 차이점을 이해하는 것이 중요 

4. 인덱스 

사용자가 인덱스를 사용하는 이유 

- 데이터베이스에 저장된 자료를 더욱 빠르게 조회하기 위해 인덱스를 생성하여 사용함 

- 모든 SQL이 인덱스를 사용해야만 하는가 ? 

일반적으로 인덱스는 테이블의 전체 데이터 중에서 10 ~ 15% 이하의 데이터를 처리하는 경우에

효율적이며 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 나음 

5. B*Tree 구조 

- 가장 많이 사용되는 인덱스의 구조라 할 수 있으며,

  인덱스의 데이터 저장 방식이기도 함 

- Root(기준)/Branch(중간)/Leaf(말단) Node로 구성됨 

- Branch 노드는 Leaf 노드에 연결되어 있으며

  조회하려는 값이 있는 Leaf 노드까지 도달하기 위해 

  비교/분기해야 될 값들이 저장됨 

Leaf 노드 = 인덱스 칼럼의 값 + ROWID 

인덱스 칼럼의 값 : 오름(내림)차 순으로 Sort되어 저장됨 

ROWID : 테이블에 있는 해당 row를 찾기위해 사용되는 논리적인 정보

Brancg Blocks - RootNode(뿌리),Branch Node

Leaf Blocks - Sorting,Leafnode

6.B*Tree 구조 

B*Tree 구조의 핵심은 Sort 

01 ORDER BY에 의한 Sort를 피할 수 있음 

02 MAX/MIN의 효율적인 처리 가능함 

7.인덱스 선정 절차 

1. 프로그램 개발에 이용된 모든 테이블에 대하여 Access Path 조사

2. 인덱스 칼럼 선정 및 분포도 조사 

3. Critical Access Path 결정 및 우선 순위 선정

4. 인덱스 칼럼의 조합 및 순서 결정 

5. 시험 생성 및 테스트 

6. 결정된 인덱스를 기준으로 프로그램 반영

7. 실제 적용 

8.인덱스 생성 및 변경시 고려할 사항

1. 기존 프로그램 동작에 영향성 검토 

2. 필요할 때마다 인덱스 생성으로 인한 인덱스 개수의 증가와 이로인한 DML 작업의 속도 

3. 비록 개별 칼럼의 분포도가 좋지 않을지라도 다른 칼럼과 결합하여 자주 사용되고 

  결합할 경우에 분포도가 양호하다면 결합 인덱스 생성을 긍정적으로 검토 

9.인덱스 스캔의 원리 

옵티마이저가 인덱스 사용을 위한 실행계획 수립함.

1. 조건을 만족하는 최초의 인덱스 row를 찾음 

2. Access된 인덱스 row의 ROWID를 이용해 테이블에 있는 row 찾음 (Random Access)

3. 처리 범위가 끝날때까지 차례대로 다음 인덱스 row를 찾으면서 스캔을 반복함

인덱스 스캔시에는 한 번의 I/O가 발생할때 마다 한 개의 Block을 처리함 

ROWID의 분해 

ROWID(4개의 정보) = 데이터의 저장 주소 

데이터의 저장 주소 

- 데이터를 가진 테이블 정보 

- 테이블 공간을 구성한 파일 경보 

- Block에 대한 정보 

- Row 

고유(Unique) 인덱스의 Equal(=) 검색

ex) SELECT * FROM emp WHERE empno = 7788;

고유(Unique) 인덱스의 범위(Range)검색 

SELECT * FROM emp WHERE empno >= 7654;

SELECT * FROM emp WHERE empno >= 7654;

중복인덱스의 범위 검색 

CREATE INDEX job_index on emp (job);

SELECT * FROM emp WHERE job Like 'SALE%'

SELE

OR & IN 조건 - 결과의 결합 

1. SELECT * FROM emp WHERE empno IN (7654,7788);

2. SELECT * FROM emp WHERE empno = 7654 OR empno = 778

인덱스 머지 . 결합인덱스 

인덱스 머지란 개별 칼럼에 인덱스가 생성되어 있으면서 모두 where절에 Equal(=)조건으로

사용되어, 각각의 인덱스 조합으로 자료에 접근하는 것을 의미 

일반적으로 결합인덱스를 사용할 경우, 좋은 엑세스 경로를 제공 

결합인덱스의 구성 

결합인덱스 칼럼 순서 결정

- where절 조건에 많이 사용되는 칼럼 

- Equal로 사용되는 칼럼

- 분포도가 좋은 칼럼

- 자주 이용되는 Sort의 순서로 결정

결합인덱스 사용 방법

- 결합인덱스의 첫 번째 칼럼이 where절에서 제외되어 있는 경우, 결합인덱스를 사용할 수 없음 

- 인덱스 스킵 스캐닝 

  결합인덱스의 첫 번째 칼럼이 WHERE절에 제외되어 있고, 두 번째 칼럼부터 WHERE절에 조건으로 기술되어 있는 경우에도 그 인덱스가 사용되는 경우 

결합인덱스 칼럼에 대한 '='의 의미

범위 제한 조건 

- 결합 인덱스의 선행하는 칼럼 순서로 WHERE절에 '='로 연속된 경우,

해당하는 조건을 범위제한 조건이라 함 

- 체크 조건 

- WHERE절 조건에서 선행 칼럼이 '='조건에 없다면 후행 조건은 체크조건이 됨 

- 인덱스 매칭률 =

실행계획 - 데이터를 처리하고자할때, 수립되는 데이터 처리 방법 

실행계획을 하는 이유

- 비효율적인 부분 확인 가능 

- 실행계획을 통해 SQL 튜닝 포인트 도출 

EXPLAIN PLAN - 실행계획만 확인 가능한 방법 

SET AUTOTRACE

- 실행계획과 I/O관련 정보 확인 가능 

- 한 번의 명령으로 여러 개의 SQL에 대한 실행계획 볼 수 있음 

옵티마이저

하나의 프로세스로 사용자가 실행한 SQL을 해석하고 데이터 추출을 위한 실행 계획을 수립하는 프로세스 

오라클 

RBO(초창기버전부터 제공) 

CBO(Vlog부터 기본적인 설정으로 적용)

RBO(Rule Based Optimizer)

- 기본적으로 15개의 순위가 매겨진 규칙이 있음 

  -> 이를 기초로 해서 실행계획을 수립함. 

- SQL에 대한 실행계획이 하나 이상일 경우엔 

  순위가 높은 규칙을 이용

- 수립될 실행계획이 예측 가능하기에 

  개발자가 원하는 처리 경로로 유도 하기 쉬움 

CBO(Cost Based Optimizer)

- 대상 row들을 처리하는데 자원 사용 최소화함 

  궁극적으로 데이터를 빨리 처리하는게 목적 

- CBO에 영향을 미치는 비용 산정 요소 

  각종 통계정보,Hint,연산자,Index

- Cluster,DBMS버전,CPU/Memoy 용량 

Disk I/O등과 같이 매우 다양함 

통계정보 -> 주기적으로 생성

CBO의 성능을 최적의 상태로 유지시키기위해 테이블,

인덱스,클러스터 등을 대상으로 통계 정보 생성 

-> 정기적으로 ANALYZE 작업을 하는 것이 가장 중요 

-> 가장 효율적인 실행계획을 수립하기 위해 

    최소 비용을 계산할때 중요하게 사용

RBO

옵티마이저의 레벨별 설정 

Instance Level : initSID.ora를 이용해서 지정 

OPTIMMZER_MODE = 

[RULE/CHOOSE/FIRST_ROWS/ALL-ROWS(기본값)] 

Sesssion Level 

SQL > ALTER SESSION SET OPTIMIZER_MODE = 

[RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]

State ment Level

SQL>SELECT/*First_rows*/

       ename 

FROM emp; 

RBO와 CBO의 비교 

SELECT e.ename,d.dname 

 FROM emp e,dept d 

WHERE e.deptno = d.depto 

 AND e.deptno = 10; 

CBO