오라클 성능 고도화 원리와 해법 II (2010년)
목록
비용 0 0 3,432

by 구루비 I/O비용 [2010.05.07]

{code}
1. 비용 모델
- I/O 비용 모델 : IO요청 횟수(논리적/물리적으로 읽은 블록 개수가 아님)만을 수행 비용으로 간주하여 실행계획 평가
- CPU 비용 모델 : IO요청 횟수에 시간을 더해 비용을 산정하여 실행계획 평가
2. 비용 모델 선택 파라미터 : _optimizer_code_model
- IO : I/O 비용 모델
- CPU : CPU 비용 모델
- CHOOSE : 시스템 통계가 있으면 CPU 비용 모델, 없으면 I/O비용 모델
- 기본값 : CHOOSE
- 9i에서는 시스템 통계를 생성해 줄 때만 CPU비용 모델 방식으로 작동한다.
- 10g에서는 NoWorkLoad 시스템 통계를 도입하여 시스템 통계를 생성하지 않더라도 CPU비용 모델이 선택된다.
3. 쿼리 레벨 단위 비용 모델을 선택하는 힌트
- cpu_costing
- no_cpu_costing
{code}

1. I/O 비용 모델

1. 인덱스를 경유한 테이블 액세스 비용

  • 인덱스를 경유한 테이블 액세스 시에는 Single Block I/O 방식으로 사용되며, 디스크에서 한 블록을 읽을 때마다 한번의 I/O call이 발생한다.
  • 읽게 될 물리적 블록 개수가 액세스 비용과 일치한다.
{code}
SQL> create table t
2 as
3 select * from all_objects;

Table created.

SQL> create index t_owner_idx on t(owner);

Index created.

SQL> begin
2 dbms_stats.gather_table_stats(user, 'T'
3 , method_opt=>'for all columns size 1');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> alter session set "_optimizer_cost_model" = io;

Session altered.

SQL> set autotrace traceonly exp;
SQL> select /*+ index(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 910642575

---

IdOperationNameRowsBytesCost

---

0SELECT STATEMENT2118200K62
1TABLE ACCESS BY INDEX ROWIDT2118200K62
  • 2
INDEX RANGE SCANT_OWNER_IDX21185

---

Predicate Information (identified by operation id):

---

2 - access("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)
|

 *[ I/O 비용 모델의 비용 계산식 ]*
|

비용 = blevel + -- 인덱스 수직적 탐색 비용
(리프 블록 수 * 유효 인덱스 선택도) + -- 인덱스 수평적 탐색 비용
(클러스터링 팩터 * 유효 테이블 선택도) -- 테이블 Random 액세스 비용

  • blevel : 리프 블록에 도달하기 전에 읽게 될 브랜치 블록 개수
  • 유효 인덱스 선택도 : 전체 인덱스 레코드중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율 (인덱스 Access Predicate에 의해 결정)
  • 유효 테이블 선택도 : 인덱스 스캔 후 최종적으로 테이블을 방문할 것으로 예상되는 비율 (인덱스 Access Predicate와 Filter Predicate에 의해 결정)
  • 클러스터링 팩터 : 인덱스 스캔 후 전체 로우를 액세스할때 읽힐 것으로 예상되는 테이블 블록 개수
  • 최종 테이블 선택도 : 테이블 Filter Predicate까지 포함한 모든 조건절에 의해 결정
  • SQL조건절이 인덱스 Access Predicate와 인덱스 Filter Predicate 가 다른 경우를 제외하면 유효 인덱스 선택도와 유효 테이블 선택도는 항상 같다
    참고 인덱스 Access Predicate와 인덱스 Filter Predicate 가 다른 경우
    1) 좌변 컬럼을 가공한 조건절
    2) 왼쪽 % 또는 양쪽 % 기호를 사용한 like 조건절
    3) 같은 컬럼에 대한 조건절이 두 개 이상일 때, 인덱스 액세스 조건으로 선택되지 못한 다른 조건절
  • 참조하는 통계 정보
  • 선택도 : num_distinct 컬럼 통계
  • blevel, 리프 블록 수, 클러스터링 팩터 : 인덱스 통계

SQL> column index_name format a12
SQL> column clustering_factor format 999999 heading "CLUSTERING|_FACTOR "
SQL> column "인덱스 스캔 비용" format 9999.99999 heading "인덱스 |스캔비용"
SQL> column "총 테이블 액세스 비용" format 9999.99999 heading "총 테이블 |액세스 비용"
SQL> select i.blevel, i.leaf_blocks, c.num_distinct, i.clustering_factor
2 , 1 + (i.leaf_blocks * 1/c.num_distinct) "인덱스 스캔 비용"
3 , 1 + (i.leaf_blocks * 1/c.num_distinct)
4 + (i.clustering_factor * 1/c.num_distinct) "총 테이블 액세스 비용"
5 from user_indexes i, user_tab_col_statistics c
6 where i.index_name = 'T_OWNER_IDX'
7 and c.table_name = i.table_name
8 and c.column_name = 'OWNER';

CLUSTERING 인덱스 총 테이블
BLEVEL LEAF_BLOCKS NUM_DISTINCT _FACTOR 스캔 비용 액세스 비용

--
---
--
---
---
1 169 34 1923 5.97059 62.52941

|

h2. 2. Full Scan에 의한 테이블 액세스 비용
 - HWM 아래쪽 블록을 순차적으로 읽어들이는 과정에서 발생하는 I/O 호출 횟수로 비용을 계산한다.
 - 한번의 I/O 호출로써 여러 블럭을 읽어들이는 multiblock I/O방식을 사용하여 총블럭수를 db_file_multiblock_read_count파라메터로 나눈 만큼 I/O호출이 발생한다.( 내부적으로 조정된 값으로 비용을 계산하기 때문에 예상치와 정확하게 일치하지 않음)
 
 * Multiblock I/O단위 증가에 따른 예상비용
  : db_file_multiblock_read_count 파라메터를 2부터 128까지 증가시키면서 I/O비용을 측정한 결과 cost가 계속 감소한다.
|

SQL> select blocks from user_tables where table_name = 'T';

BLOCKS

--
1026

SQL> set autotrace traceonly exp;
SQL> alter session set db_file_multiblock_read_count = 2;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K389
  • 1
TABLE ACCESS FULLT2118200K389

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)

SQL> alter session set db_file_multiblock_read_count = 4;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K247
  • 1
TABLE ACCESS FULLT2118200K247

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)

SQL> alter session set db_file_multiblock_read_count = 8;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K157
  • 1
TABLE ACCESS FULLT2118200K157

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)

SQL> alter session set db_file_multiblock_read_count = 16;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K100
  • 1
TABLE ACCESS FULLT2118200K100

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)

SQL> alter session set db_file_multiblock_read_count = 32;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K64
  • 1
TABLE ACCESS FULLT2118200K64

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)

SQL> alter session set db_file_multiblock_read_count = 64;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K41
  • 1
TABLE ACCESS FULLT2118200K41

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)

SQL> alter session set db_file_multiblock_read_count = 128;

Session altered.

SQL> select /*+ full(t) */ * from t where owner = 'SYS';

Execution Plan

--
Plan hash value: 1601196873

--

IdOperationNameRowsBytesCost

--

0SELECT STATEMENT2118200K27
  • 1
TABLE ACCESS FULLT2118200K27

--

Predicate Information (identified by operation id):

---

1 - filter("OWNER"='SYS')

Note

-

  • cpu costing is off (consider enabling it)
|

h2. 3. I/O 비용 모델의 비현실적인 가정
 - 가정1 : Single Block I/O와 Multiblock I/O는 비용이 같다.
 - 가정2 : 캐싱 효과를 전혀 고려하지 않는다. 

위 두가지 가정은 비현실적인 가정이므로, 이를 보정하기 위해 두 파라미터를 8버젼부터 제공하기 시작하였다.

 * optimizer_index_cost_adj 
 - 인덱스 탐색 비용을 조정할 때 사용한다.
 - 값의 범위는 1~10000 이다
 - 기본값 : 100 ( 한번의 I/O call을 통해 Single Block Read 방식으로 한 블록을 읽는 비용과 Multiblock Read 방식으로 여러 블럭을 읽는 비용을 같게 평가한다.)
 - 예) optimizer_index_cost_adj = 25 : Single Block Read 방식의 I/O call비용을 Multiblock Read 방식의 I/O call비용의 25% 간주한다.
 - 인덱스를 경유할 때의 물리적 I/O비용을 Full Table Scan할 때의 물리적 I/O 비용과 대비한 상태적 비용을 표현한다.
 - 이 값을 낮게 설정할수록 옵티마이저는 테이블 스캔보다 인덱스를 이용한 테이블 액세스를 선호하게 된다.

 * optimizer_index_caching
 - NL조인에서 inner쪽 인덱스 블록이 캐싱돼 있을 가능성을 옵티마이저에게 알려주는 파라미터이다.
 - 값의 범위는 0~100이다.
 - 이 값을 높게 설정할수록 옵티마이저는 인덱스를 이용한 NL조인을 선호하게 된다.


h1. 2. CPU 비용 모델

|
  • 블록 I/O가 소량인데도 쿼리 수행 시간이 오래 걸리는 경우 ( CPU사용량이 증가하는 경우 )
    1) 해시 조인할 때, 해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때
    2) 캐싱된 블록을 반복적으로 읽는데, 한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할 때 ( NL조인 inner쪽 인덱스 선두 컬럼이 between 조건일 때)
    3) 버퍼를 Pin한 상태에서 같은 블록을 반복 액세스할 때
    4) 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
    5) 메모리 소트를 반복할 때
    6) 조건절 개수가 아주 많을 때
    7) 조건절이나 select-list에 연산 집약적인 작업을 많이 포함할 때
|

=> 이에 쿼리 수행에 필요한 I/O뿐만 아니라 예상되는 CPU리소스 사용량까지 비용 계산식에 포함하는 CPU비용 모델을 9i부터 선보였다.

 *[ CPU 비용 모델의 비용 계산식 ]*
|

비용 = ( Single Block I/O 요청 횟수 * Single Block I/O에 소요되는 시간 (ms) +
Multiblock I/O 요청 횟수 * Multiblock I/O에 소요되는 시간 (ms) +
쿼리 수행에 필요한 예상 CPU 사이클 수 / 초당 처리할 수 있는 CPU 사이클 수
) / Single Block I/O에 소요되는 시간

1) Single Block I/O 요청 횟수, Multiblock I/O 요청 횟수에 미리 측정해 놓은 평균 소요시간을 각각 곱합으로써 I/O일량을 시간으로 표현하였다.
2) 인덱스 스캔에 의한 테이블 액세스 비용과 테이블 스캔 비용 간에 상대적인 시간 차이가 있음을 계산식에 포함시켰다.
3) 쿼리 수행에 필요한 예상 CPU 사이클 수를 초당 처리할 수 있는 CPU 사이클 수로 나눈 값으로 CPU비용을 계산하였다.
4) I/O 시간과 CPU연산 시간을 더한 시간 개념을 빌어 쿼리 수행 비용을 평가하는 것이 CPU 비용 모델의 핵심이다.
5) I/O 시간과 CPU 연산 시간을 더한 값을 Single Block I/O에 소요되는 시간으로 나눔으로써 Single Block I/O에 소요되는 시간간의 상대적인 시간 비용을 표현한다.
6) 즉, CPU 비용 모델의 비용은 쿼리의 예상 총 수행 시간을 Single Block I/O 시간 단위로 표현한다.

|

[참고]
 * 10g에서는 user_tab_statistics테이블을 조회하면 테이블별로 캐싱된 블록 수와 캐시 히트율을 얻을 수 있다. 오라클은 캐싱 효과를 비용 계산식에 포함하려는 움직임을 보이고 있으나 아직 반영되지는 않았다.


h2. 문서에 대하여

* 최초작성자 : [~kwlee55]
* 최초작성일 : 2010년 05월 07일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [코어 오라클 데이터베이스 스터디|4차 코어 오라클 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3222

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입