매주 각 스터디 팀원들이 담당 분량을 정리해서 올리고 토론식으로 진행합니다.
스터디 포스팅은 팀원들에의해 이루어지지만 스터디 참여는 사이트 회원 모두가 가능합니다.
진행기간: 2009.07 ~ 2009.10. 종료
3.1 SQL의 실행계획 (2/3)
2009.07.25 05:53
3.1.2.3. 옵티마이져 목표(Goal)의 선택
가) 옵티마이져 모드의 종류
나) 옵티마이져 모드의 결정 기준
다) 옵티마이져 모드와 관련된 파라메터 지정
3.1.2.4. 실행계획의 고정화(Stability)
가) 아우트라인의 생성과 조정
나) 아우트라인의 관찰
다) 옵티마이져 업그레이드 시의 적용
3.1.2.5. 옵티마이져의 한계
가) 옵티마이져 모드의 종류
- 초기 결과 최적화(First_rows)
- 일부라도 우선 수행
- 비용기준과 경험적 방법(Heuristic method)을 혼합한 접근 방법
- 주어진 상황에 따라 선택하여 최적화를 수행
- 전체결과 최적화(All_rows)
- 전체가 모두 수행
- CBO의 기본모드
- 사용자 입장에서는 전체를 모두 처리하는것보다 First_Rows 모드가 일반적일수 있음
- 부분처리를 원하는 SQL을 All_rows모드로 처리하면 심각한 속도 저하가 올수 있음
- First_Rows_n ??
- n 은 처리를 원하는 범위
- 비용기준으로 최적화를 접근
나) 옵티마이져 모드의 결정 기준
- 기존 시스템이 있다면
- 'CHOOSE'모드가 있던 버전 - FIRST_ROW
- 신버전에서 개발된 버전 - FIRST_ROWS_N
- 배치처리 위주 - ALL_ROWS
- 옵티마이져 모드 변경
- 충분한 준비후 작업
- 모드 변경후 기존 SQL 을 위한방법
- 파라메터 수정
- 옵티마이져 버전을 지정하는 파라메터를 과거 버전으로 지정
- OPTIMIZER_FEATURES_ENABLE (옵티마이져 버전 지정)
- 기존 방법에서 안정화를 하려는것보다 신기술에대한 적용이 바람직
- OUTLINE 이용
- 요약본을 생성하여 이를 참조
다) 옵티마이져 모드와 관련된 파라메터 지정
- CURSOR_SHARING
- SQL문내의 상수값을 변수로 전환하여 파싱
- 옵션
- FORCE ; 상수로 입력되어도 변수로 인정 (SIMILAR보다 등급이 높음)
- SIMILR ; 상수로 입력되어도 변수로 인정
- EXACT(default) ; 대,소문자,공백,비교 상수값이 달라도 공유 불가
- OUTLINE 적용하려면 FORCE,SIMILAR적용 필수
- DB_FILE_MULTIBLOCK_READ_COUNT
- 전체 테이블 스캔이나 인덱스 고속전체스캔을 할때 한번의 I/O로 읽어야하는 블록수 지정에 사용
- Default 8 (대용량의 배치 위주라면 수치증가 시키는 것이 유리)
- OPTIMIZER_INDEX_CACHING
- 인덱스를 통해 반복 랜덤액세스할때 블럭들이 버퍼에 캐쉬되어있을 확률을 지정 (Nested Loops 조인이나 IN-List 탐침수행시)
- Default 0 (반복랜덤엑세스가 빈번한 인덱스 블록이 많다면 값 증가)
- OPTIMIZER_INDEX_COST_ADJ
- 비용계산시 인덱스의 액세스 비중을 조정하는 역할
- (Default) 100 (정상인 비용을 그대로 적용)
- 10 (1/10으로 계산)
- 인덱스액세스 경우가 전체 테이블 스캔으로 자주 일아날경우 조정할 필요있음
- 'choose'모드
- 옵티마이져의 비용기준으로 이전 과도기에 사용
- 통계정보가 없을경우 규칙으로 전환
- Optimizer_dynamic_sampling
- 동적 표본화 (Dynamic Sampling)
- 기존 choose모드의 대체 개념
- 소량의 표본을 동적으로 자동으로 추출하여 통계정보로 활용 (소량이나 자주 수행될경우 적용하지 않아야함)
- 통계정보 미보유,사용불능(에러등),너무오래된경우 적용
- 파라메트값: 0 사용하지 않음
- 기본블록수 32, 레벨증가시 2의 제곱만큼 증가
- 전체 테이블 표본으로 할경우 10레벨
인용:옵티마이져 모드 변경시 상황을 깊히 고려해야하며 필요에따라 세션만 적용하는 등의 방법으로 사용하여야한다
3.1.2.4. 실행계획의 고정화(Stability)
- 아우트라인(Outline)
- 동일한 실행계획을으로 재현할수 있도록 도와주는 최소한의 참조정보를 보유
- 범용적,개별적으로 관리 하거나 적용,금지 시킬수 있으며 필요시 편집과 익스포트와 임포트역시 가능
- 필요 조건에 따른 카테고리 별로 관리하는것도 좋음
가) 아우트라인의 생성과 조정
- 패키지
- DBMS_OUTLN 과 DBMS_OUTLN_EDIT
- CREATE_OUTLINE: 지정된 건을 공유 커서에서 찾아 아우트라인을 생성
- CLEAR_USED: 지정한 아우트라인을 제거
- DROP_BY_CAT: 지정한 카테고리에 속한 아우트라인을 제거
- DROP_UNUSED: SQL파싱에 사용된적이 없는 아우트라인을 제거
- UPDATE_BY_CAT : 어떤카테고리를 새로운 카테고리로 변경
- GENERATE_SIGNATURE: 지정한 SQL문에 대한 식별자 생성
- 생성
- CREATE_STORED_OUTLINES (파리메터) 를 특정세션에 지정하여 생성 시작,종료 하는것이 일반적임
- TRUE나 FALSE를 지정하거나 카테고리 명 직접 지정가능
- TRUE; 'DEFAULT' 카테고리로 생성
- 사용
- USE_STORED_OUTLINES 를 지정하여 사용
- TRUE나 FALSE, 카테고리 명지정가능
- ALTER OUTLINE 으로 아우트라인 이름이나 카테고리 변경, 재생성도 가능
- PRIVATE 아우트 라인
- 1) 기존 아우트 라인 복제
CREATE PRIVATE OUTLINE prv_ol_1 From outln_1;
2) 아우트라인 수정가능 툴이나 DBMS_OUTLN_EDIT 패키지의 프로시져를 이용해 갱신
REFRESH_PRIVATE_OUTLINE- (수작업으로 갱신하였을경우 사용)
3) 검정
USE_PRIVATE_OUTLINES를 TRUE로 지정후 검증
4) 적용
CREATE OR REPLACE OUTLINE outln_1 From PRIVATE prv_ol_1;
5) 수행종료
USE_PRIVATE_OUTLINES 를 FALSE로 지정
나) 아우트라인의 관찰
- 뷰를 통해 내용 확인 가능
- USER_OUTLINES
- USER_OUTLINE_HINTS
- 권한이 허용하면 DBA나 ALL로도 가능
- A) 아우트라인의 명칭(자동으로 생성된것)
- B) 생성시 부여한 카테고리
- C) CURSOR_SHARING 값이 SIMILAR로 지정, 조건절의 상수값은 바인드 변수로 변경되어 생성
- D) RAW 타입으로 생성된 SQL 식별자 , 동일 SQL을 다른 카테고리에 생성하면 값은 동일 A의 명칭은 변경됨
코드:
수행 SQL
SELECT empno,ename,job,loc
from emp e,dept d
where e.deptno=d.deptno and e.empno=7856
order by loc
NAME : SYS SYS_OUTLINE_050921102126523 -------------(A)
CATEGORY : LHS001 ---------------------------------------(B)
USED : UNUSED
TIMESTAM : 05 / 09 /16
VERSION : 9.2.0.1.0
SQL_TEXT : SELECT EMPNO, ENAME, JOB LOC FROM EMP E,DEPT D WHERE
e.deptno=d.deptno and e.empno=:"SYS_B_0" order b loc ----(C)
SIGNATURE : 2E945C676F84B939E862C3F6D8A4E7CF -----------------(D)
SELECT empno,ename,job,loc
from emp e,dept d
where e.deptno=d.deptno and e.empno=7856
order by loc
NAME : SYS SYS_OUTLINE_050921102126523 -------------(A)
CATEGORY : LHS001 ---------------------------------------(B)
USED : UNUSED
TIMESTAM : 05 / 09 /16
VERSION : 9.2.0.1.0
SQL_TEXT : SELECT EMPNO, ENAME, JOB LOC FROM EMP E,DEPT D WHERE
e.deptno=d.deptno and e.empno=:"SYS_B_0" order b loc ----(C)
SIGNATURE : 2E945C676F84B939E862C3F6D8A4E7CF -----------------(D)
코드:
create outline emp_j_otl
on SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept
WHERE emp.deptno=dept.deptno;
SELECT * FROM USER_OUTLINES WHERE name='EMP_J_OTL';
NAME CATEGORY USED TIMESTAMP VERSION SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
EMP_J_OTL DEFAULT UNUSED 2009.07.21 21:42:14 10.2.0.1.0 SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno AF8B1B1EB1ED3CD0C9523EF77C200ACA COMPATIBLE ENABLED NORMAL
실테이블에서 확인
OUTLN.OL$HINTS
SELECT * FROM OUTLN.OL$HINTS;
_NAME HINT# CATEGORY HINT_TYPE HINT_TEXT TABLE_NAME COST CARDINALITY JOIN_PRED
EMP_J_OTL 1 DEFAULT 19 USE_NL(@"SEL$1" "EMP"@"SEL$1") EMP 4.00691534303308 4 EMP.DEPTNO = DEPT.DEPTNO
EMP_J_OTL 2 DEFAULT 40 LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1") 0 0
EMP_J_OTL 3 DEFAULT 101 INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO")) EMP .000160523044559189 2
EMP_J_OTL 4 DEFAULT 2 FULL(@"SEL$1" "DEPT"@"SEL$1") DEPT 3.0046162831899 2
EMP_J_OTL 5 DEFAULT 111 OUTLINE_LEAF(@"SEL$1") 0 0
EMP_J_OTL 6 DEFAULT 113 ALL_ROWS 0 0
EMP_J_OTL 7 DEFAULT 109 OPTIMIZER_FEATURES_ENABLE('10.2.0.1') 0 0
EMP_J_OTL 8 DEFAULT 108 IGNORE_OPTIM_EMBEDDED_HINTS
실행계획
Execution Plan
----------------------------------------------------------
Plan hash value: 1619650520
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 92 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 24 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 4 | 92 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 2 | 22 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_IDX | 2 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
on SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept
WHERE emp.deptno=dept.deptno;
SELECT * FROM USER_OUTLINES WHERE name='EMP_J_OTL';
NAME CATEGORY USED TIMESTAMP VERSION SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
EMP_J_OTL DEFAULT UNUSED 2009.07.21 21:42:14 10.2.0.1.0 SELECT emp.empno,emp.deptno,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno AF8B1B1EB1ED3CD0C9523EF77C200ACA COMPATIBLE ENABLED NORMAL
실테이블에서 확인
OUTLN.OL$HINTS
SELECT * FROM OUTLN.OL$HINTS;
_NAME HINT# CATEGORY HINT_TYPE HINT_TEXT TABLE_NAME COST CARDINALITY JOIN_PRED
EMP_J_OTL 1 DEFAULT 19 USE_NL(@"SEL$1" "EMP"@"SEL$1") EMP 4.00691534303308 4 EMP.DEPTNO = DEPT.DEPTNO
EMP_J_OTL 2 DEFAULT 40 LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1") 0 0
EMP_J_OTL 3 DEFAULT 101 INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO")) EMP .000160523044559189 2
EMP_J_OTL 4 DEFAULT 2 FULL(@"SEL$1" "DEPT"@"SEL$1") DEPT 3.0046162831899 2
EMP_J_OTL 5 DEFAULT 111 OUTLINE_LEAF(@"SEL$1") 0 0
EMP_J_OTL 6 DEFAULT 113 ALL_ROWS 0 0
EMP_J_OTL 7 DEFAULT 109 OPTIMIZER_FEATURES_ENABLE('10.2.0.1') 0 0
EMP_J_OTL 8 DEFAULT 108 IGNORE_OPTIM_EMBEDDED_HINTS
실행계획
Execution Plan
----------------------------------------------------------
Plan hash value: 1619650520
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 92 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 24 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 4 | 92 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 2 | 22 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_IDX | 2 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
완벽한 실행계획의 모습은 아니지만 조인방법과 조인순서,인덱스,비용,카디널리티 까지 보유하고 있으며
테이블을 수정하여 변경하는것도 가능
다) 옵티마이져 업그레이드 시의 적용
- 규칙기준-> 비용기준
- 기존 실행계획을 유지하면서 점차적으로 비용기준에 맞추어 가도록 하는전략
- 1) 특정 카테고리를 주고 아우트 라인을 생성
- alter session set create_stored_outlines = category_name;
- 전체적으로 적용시 alter system
- 2) 대부분의 SQL대해 아우트 라인이 생성되도록 충분한 기간을 두고 수집
- 3)아우트 라인 종료 ' create_stored_outlines' 파라메터를 'false'로 지정
- 4) dbms_stats 패키지를 이요하여 통계정보를 생성
- 5) 옵티마지여 모드를 Rule에서 Choose로 변경
- 6) 현세션만적용시 Alter Session ,전체 Alter system 을 이용
- 1) 특정 카테고리를 주고 아우트 라인을 생성
- 옵티마져의 버전업등의 각 상황에 맞게 아웃라인을 생성해서 단계적으로 적용하며
- 생성시 작업의 성격, 테이블의 성격등 업무특성에 맞게 카테고리 별로 생성시켜 두고 활용하는게 좋다
3.1.2.5. 옵티마이져의 한계
인용:
비용기준 옵티마이져는 완벽할수 없는통계정보를 토대로 정확한 처리범위를 예측할수 없다는 것
다양한 사용 형태를 만족할 수 있도록 종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준높은 sql을 구사하는것이 반드시 필요하다는 것
다양한 사용 형태를 만족할 수 있도록 종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준높은 sql을 구사하는것이 반드시 필요하다는 것
