[SQLD/SQLP] 전문가가이드 3과목 3장 옵티마이저원리 암기 요약 정리 (+오라클성능고도화)

3과목 3장 옵티마이저원리 요약정리

3과목1장의 내용을 3,4,5장에서 풀어 설명하고 있습니다

공부하시면서 지금 내가 공부하는 부분이 어디쯤이고

어디와 연결되어있구나 라는걸 느끼면서 공부하면서 좋을것 같습니다!

---

옵티마이저 행동에 영향을 미치는 요소

1. SQL과 연산자 형태

2. 옵티마이징 팩터(인덱스/IOT/클러스터링/파티셔닝/MV)

3. DBMS 제약설정(PK/FK/Not Null/Check)

4. 옵티마이저 힌트

5. 통계정보

6. 옵티마이저 관련 파라미터

7. DBMS버전과 종류

옵티마이저 모드

DML문장은 일부 데이터만 가공하고 멈출 수 없으므로 옵티마이저 모드에 상관없이 

항상 all_rows모드로 작동. select 문장도 union, minus 같은 집합 연산자나 for update절을

사용하면 all_rows모드로 작동

PL/SQL내에서 수행되는 SQL도 힌트를 사용하거나 기분모드가 rule인경우를 제외하면

항상 all_rows모드로 작동

옵티마이저가 참조하는 통계정보

-테이블 통계

-인덱스 통계

-컬럼 통계

-시스템 통계

등치조건 선택도

- 1 / distinct value

범위조건 선택도

- 조건절 요청값 범위(high value - 비교값) / 전체값 범위(high value - low value)

Null값 제외 선택도

- (1 / distinct value) * (null 제외 로우수 / 총 로우수)

push_subq

- no_unnest상태에서 필터처리될 때 실행계획상 가능한 앞 단계에서

서브쿼리 필터링이 처리되도록 강제하는 것(조건절 pushing과 다름)

use_concat / no_expand

- Union all로 변환유도 / 유도X

인덱스를 통한 Table Access비용

- blevel

   + (유효인덱스 선택도 * 리프블록 수)

   + (유효테이블 선택도 * 클러스터링 팩터)

LNNVL

- LNNVL(job='Cleck') -> job이 Cleck가 아니고 job이 null인것만

- 조건이 false이거나 unknown일 때 true리턴

옵티마이저의 한계

1. 옵티마이징 팩터의 부족

2. 통계정보의 부정확성(결합컬럼...)

3. 바인드변수 사용시 균등분포가정

4. 비현실적인 가정(Single Block, Multi Block I/O같게 평가하거나 캐싱효과 고려 안함)

5. 규칙에 의존하는 CBO(order by컬럼이 인덱스가 있으면 무조건 사용)(알파벳순 인덱스 선택규칙)

6. 하드웨어 성능 특성

View Merging시 성능이 더 낮아질 수 있음(비용낮을떄만 변환)

1. Group by절

2. select -list에 distinct연산포함

View Merging이 불가능한 경우

1. 집합연산자(union, union all)

2. connect by절

3. Rownum컬럼

4. select -list에 집계함수 group by없는

5. 분석함수

힌트암기

- no_unnest는 push_subq와 함께 (서브쿼리 필터링 먼저 처리)

- no_merge는 push_pred와 함께 (조인조건 pushing)

Include Index

- 인덱스 키 외에 미리 지정한 컬럼을 리프레벨에 함께 저장하는 기능 

Covered Index

- 모든 컬럼을 인덱스에 포함

ROWID 구조

- 데이터오브젝터번호(6) +  데이터파일번호(3) + 블록번호(6) + 로우번호(3)

Index Full scan vs. Index Fast FUll Scan

선택도관련

조건절 이행으로 조인조건이 사라질 수도있다.

->

조인조건은 상수와 변수조건처럼 전이되지 않으므로 최적의 조인순서를 결정하고그 순서에따라 조인문을 기술해주는것이 중요

묵시적 형변환 주의사항

- 숫자형 컬럼과 문자형 컬럼을 비교하면 문자형 컬럼이 숫자형으로 변환되는데, 만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 들어 있으면 쿼리 수행 도중 에러

- 단, like로 비교할 때만큼은 숫자형이 문자형으로 변환되므로 위와같은 에러는 발생X

- 아래와 같은 경우 문제점이 뭘까? 다시 생각해보라

-> to_number(NULL) 바꿔줘야함

PK인덱스에는 컬럼추가 불가능

추가하기 위해서는 pk를 드롭하고 인덱스 재생성하고 pk재 생성

선택도 -> 카디널리티 -> 비용 -> 액세스방식, 조인순서, 조인방법등 결정

unnest사용법

push_pred사용법

나아가 

1. Union 집합 연산자를 포함한 뷰에 대한 조인조건  Pushdown

2. Outer조인 뷰에 대한 조인조건 Pushdown

3. Group by절을 포함한 뷰에 대한 조인조건 Pushdown.. 1,2,3 경우에도 정상적으로 작동한다는 의미

성능고도화 튜닝사례 - 조인조건식은 전이되지 않는다

위와 같은 쿼리에서

고객과 주문상세

주문과 주문상세가 조인조건으로

이행조건으로 인하여 고객 = 주문이 자동으로 될것같지만

되지 않아 쿼리에 비효율이 생기기 때문에

아래와 같이 넣어준다 

단 아래처럼 상수 및 변수 조건은 조인문을 타고 전이된다.

Outer Join을 Inner Join으로

Outer Join에서 inner 쪽 테이블에 대한 필터조건을 아래처럼 where절에 기술한다면

Inner 조인할때와 같은 결과 집합을 얻게된다. 따라서 옵티마이저가 Outer조인을 아예 Inner Join으로 변환해버린다

Ansi Outer조인문에서 where절에 기술한 Inner쪽 필터조건이 의미 있게 사용되는 경우는

아래처럼 is null조건을 체크하는 경우뿐, 조인에 실패하는 레코드를 찾고자 할 때 흔히 사용되는 SQL

조인컬럼에 is not null 조건 추가

옵티마이저가 null값이 5%이상이면 자동으로 추가해줌

인덱스 사용이 불가능한 경우 확실하게 정리

http://wiki.gurubee.net/pages/viewpage.action?pageId=26739954