Optimizing oracle optimizer

EXEM Knowledge Base

(Book optimizer에서 넘어옴)
Jump to: navigation, 찾기

목차

[편집] Optimizing Oracle Optimizer

그림:O3_Cover.jpg

Optimizing Oracle Optimizer는 모든 Oracle DBA, 개발자, 그리고 성능 관리자를 위한 축복과도 같은 책입니다.

CBO와 Query를 최적화하는 새롭고 체계적인 시각을 소개합니다. 이론적인 설명에 그치지 않고 재현 가능한 많은 수의 데모를 통해 실용적인 지식 습득이 가능하도록 도와드립니다.

이 책을 읽지 않고는 CBO의 성능 문제를 논하지 마십시오.


[편집] 누가 적었는가

저자 [조동욱]Advanced Oracle Wait InterfaceAdvanced OWI, Internals and Performance in Oracle 10g RAC와 통해 OWI를 통한 Oracle 성능 분석 기법을 체계적으로 소개해 왔습니다.


또한 다음과 같은 Seminar 등을 통해 Oracle 성능 관련 지식을 보급하는데 온 정열을 기울이고 있습니다.


욱짜의 Oracle Blog를 통해 저자를 온라인으로 만날 수 있습니다. 욱짜의 Oracle Blog는 가장 활발한 Oracle Blog들 중 하나입니다. 영문 Blog를 통해 전세계 엔지니어들과 교류하려고 노력합니다.

저자에게 연락하려면 ukja.dion@gmail.com으로 Email을 보내시면 됩니다.

[편집] 주문하기

인터파크에서 주문하기

예스24에서 주문하기

교보문고에서 주문하기

강컴에서 주문하기

알라딘에서 주문하기

[편집] 어떤 내용인가

Optimizing Oracle Optimizer는 다음과 같은 내용에 중점을 두고 있습니다.

  1. CBO의 기본 개념 및 핵심 용어들
  2. CBO를 자유롭게 사용하기 위한 핵심적인 Tool들
  3. CBO가 최적의 실행 계획을 수립하지 못하는 이유들
  4. CBO의 오류를 보완하는 핵심적인 기법들

모든 주제는 명확한 개념 설명과 함께 다양한 예제를 통해 이해를 돕습니다. Oracle 성능 문제에 경험이 많은 분들 뿐만 아니라 CBO를 처음 접하는 분들도 쉽게 이해할 수 있도록 구성되었습니다.


[편집] 제 1 장 CBO Concept

제 1 장은 다음과 같은 내용들을 통해 CBO의 기본 개념을 익히도록 도와드립니다.

  • Cost란 무엇인가?
  • Time Model vs. IO Model
  • CBO의 기본 흐름


다음과 같은 예제들로 구성되어 있습니다.

[편집] 제 2 장 CBO Tools

제 2 장은 다음과 같은 내용들을 통해 CBO를 자유롭게 활용하기 위한 다양한 Tool들을 소개합니다.

  • DBMS_XPLAN Package
  • 10046 Event
  • 10053 Event


다음과 같은 예제들로 구성되어 있습니다.


[편집] 제 3 장 Index

제 3 장은 다음과 같은 내용들을 통해 Index가 CBO에서 갖는 의미와 성능 이슈를 소개합니다.

  • Index Scan Cost
  • Clustering Factor
  • Index Cost 제어하기
  • Index Access Pattern
    • Index Range Scan
    • Index Full Scan
    • Index Fast Full Scan
    • Index Skip Scan
    • B*Tree Index Combination
    • Index Join


다음과 같은 예제들로 구성되어 있습니다.


[편집] 제 4 장 Transformation

제 4 장은 다음과 같은 내용들을 통해 Query Transformation의 개념 및 성능 이슈를 소개합니다.

  • Transformation의 의미
  • Subquery Unnesting
  • View Merging
  • Predicate Pushing
  • Star Transformation
  • Cost Based Query Transformation
  • Transformation Troubleshooting 하기


다음과 같은 예제들로 구성되어 있습니다.


[편집] 제 5 장 Cardinality

제 5 장은 다음과 같은 내용들을 통해 Cardinality의 의미와 중요성 및 성능 이슈들을 소개합니다.

  • Cardinality가 왜 중요한가?
  • Cardinality의 기본 개념
  • 기본 법칙들
  • 골치 아픈 Case들
    • Skewed Data
    • Correlated Columns
    • Join Cardinality
    • Partition Key
  • Cardinality 제어하기


다음과 같은 예제들로 구성되어 있습니다.


[편집] 제 6 장 Histogram

제 6 장은 다음과 같은 내용들을 통해 Histogram의 개념과 성능 이슈들을 소개합니다.

  • Histogram 기본 개념
  • Histogram 수집
  • Histogram과 Cardinality의 관계
    • Frequency Histogram
    • Height-Balanced Histogram
  • Histogram을 둘러싼 오해


다음과 같은 예제들로 구성되어 있습니다.


[편집] 제 7 장 Advanced Techniques

제 7 장은 다음과 같은 내용들을 통해 Optimizer와 관련된 다양한 고급 기법들을 소개합니다.

  • Forgotten Hints
  • 통계 정보 관리
  • Manual Histogram
  • Stored Outline 바꿔치기
  • SQL Profile Import


다음과 같은 예제들로 구성되어 있습니다.

[편집] 오타와 오류들

지금까지 발견된 오타와 오류들은 다음과 같습니다.

[편집] 제 1 장. CBO Concept

Page 번호 원문 수정사항 비고
5 모든 Time을 Single Block I/O Time으로 나누는 것이다. 모든 Time의 합을 평균 Single Block I/O Time으로 나누는 것이다. 2쇄 반영됨
8 Noworkload System Statistics 또한 DBMS_STATS Package를 이용해 별도로 수집 가능하다. 하지만 현실 세계에서 그럴 필요성이 있는지는 의문이다. 다음과 같은 상황은 어떨까?
  • 현재 Workload System Statistics를 잘 사용하고 있다.
  • 이번에 Oracle Instance가 설치된 Hardware 교체가 있다. 단, I/O Storage는 그대로 사용한다.
  • 이 경우 Workload System Statistics는 어떻게 하는 것이 좋을까?

한 가지 방법은 다음과 같다.

  1. 일단 Workload System Statistics는 그대로 둔다.
  2. Noworkoad System Statistics를 수집한다. Ex: DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode => 'NOWORKLOAD' )
  3. 1번 과정의 Workload System Statistics 중 CPU 값(CPUSPEED) 값을 Noworkload System Statistics의 CPU 값

(CPUSPEEDNW)로 변경한다. 즉, I/O 성능과 관련된 값들은 그대로 사용하고(I/O Storage는 교체되지 않았으므로), CPU 성능과 관련된 값은(Hardware가 교체되었으므로) 최신 Hardware를 반영한다.

반영안함
11 이런 논란의 본질은 정확한 값을 아무도 모른다는데 있다. V$FILESTAT View는 Single Block I/O Time과 Multi Block I/O Time에 대한 정보를 가지고 있다. 따라서 이 정보들을 이용하면 System Statistics를 사용하는 것과 비슷한 효과를 얻을 수 있다. 2쇄 반영됨


[편집] 제 2 장. CBO Tools

Page 번호 원문 수정사항 비고
41 Id=4에서 Name=T1_N2
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    14 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |     1 |    14 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |     7 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_N2 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Id=4에서 Name=T2_N2
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    14 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |     1 |    14 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |     7 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | <b>T2_N2</b> |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
3쇄 반영예정
Page 번호 원문 수정사항 비고
42 Nested Loops Join은 선행 Table을 읽으면서 후행 Table을 한번씩 Access하는 방식이다. Nested Loops Join은 선행 Table에서 Key 값을 하나씩 읽으면서 후행 Table에서 Key 값에 해당하는 값을 읽어 들이는 방식이다. 2쇄 반영됨
103 반면 Index Fast Full Scan의 Cost는 6.00이다. 반면 Index Fast Full Scan의 Cost는 6.16이다. 2쇄 반영됨


[편집] 제 3 장. Index and CBO

Page 번호 원문 수정사항 비고
117 따라서 Clustering Factor가 지나치게 높거나 Table Selectivity가 Index Selectivity에 비해 지나치게 높다는 의미가 된다. 이런 현상은 대부분 Clustering Factor 값의 차이에 의해 발생한다. 따라서 Clustering Factor가 지나치게 높거나 Table Selectivity가 지나치게 크다는 의미가 된다. Table Selectivity는 Index Selectivity보다 작거나 같은 값이다. 따라서 이런 현상은 대부분 Clustering Factor 값의 차이에 의해 발생한다고 볼 수 있다. 2쇄 반영됨
122 Clutering Factor가 가장 좋은 또 하나의 경우에 대한 그림이 빠져 있음 그림 추가 2쇄 반영됨

[편집] 제 4 장. Transformation

Page 번호 원문 수정사항 비고
133 alter t_clsf2 rename to t_clsf; alter table t_clsf2 rename to t_clsf; 3쇄 반영 예정
234 PL/SQL Block의 Fetch Call"은 1회에 불과하므로... PL/SQL Block내의 내부 Array Size는 Oracle 10g에서 100이라는 사실을 명확하게 설명해야함. Oracle 10g에서 추가된 PLSQL_OPTIMIZE_LEVEL(2) 파라미터 값이 2인 경우에는 내부적으로 100의 Array Size를 사용하기 때문에 위와 같은 효과가 나타나는 것이다. 3쇄 반영 예정
236 Fetch Array Size 1(Array 를 사용하지 않겠다는 의미) 로 설정하면

다음과 같이 function call 회수가 fetch 회수와 동일하게 100 회로 기록된다.

SQL*Plus에서 최소 Fetch Array Size는 2이다. 이 예제에서 function call의 회수는 Array Size를 1,2,3, ..., 10까지 증가시켜도 항상 100이다. 그 이유는 Data가 다음과 같은 순서로 들어가 있기 때문이다.
1,2,3,4,5,6,7,8,9,10,1,2,...

따라서 Array Size가 10이상이 되어야만 Deterministic Function의 Cache 효과가 나타난다.

3쇄 반영 예정

[편집] 제 7 장. Advanced Techniques

Page 번호 원문 수정사항 비고
449, 461 Full Hint Naming Convention Full Hint Naming Convention이라는 표현은 고유 명사가 아닌 일반 보통 명사로 사용되었다. 말 그대로 Oracle이 Hint를 적용할 때 실제로 사용하는 전체 명명 규칙이라는 의미이다. Oracle이 사용하는 공식적인 용어로는 Global Hints로 분류된다. 2쇄 반영됨