오라클 성능 고도화 원리와 해법 II (2016년)
h1.비용
- I/O :I/O 비용모델
- CPU :CPU 비용모델
- CHOOSE : 시스템 통계가 있으면 CPU 비용 모델 없으면 I/O 비용 모델 (opfimizer_mode를 choose로 설정했을 때 오브젝트 통계가 있으연 CBO, 없으면 EBO로 동작하는 것과 같은 개념)
- 기본 값은 choose
- 9i은 관리자가 시스템통계를 생성해 줄때만 CPU 비용모델 사용
- 10g 에서는 NoWorkload 시스템통계 도입 ( 관리자가 따로 시스템통계를 생성하지않아도 CPU 기반모델 사용)
-쿼리 레벨로도 비용 모댈을 선택 - cpu_costing , no_cpu_costing
h3.(1) I/O 비용 모델
- I/O 비용 모델에서의 비용은 디스크 I/O Call 횟수(논리적/물리적으로 읽은 블록 개수가 아닌 I/0 Call 횟수))
h5.인덱스를 경유한 테이블 액세스 비용
-- 인텍스를 경유한 테이블 액세스 시 에는 Single Block 1/0 방식 이 사용
-- 디스크에서한 블록을 읽을 때 마다 한 번의 I/O Call을 일으키는 방식이므로 읽게 될 물리적 블록 개수가 액세스 비용 = I/O Call 횟쉬과 일치한다
create table t as select * from all_objects;
Table T이(가) 생성되었습니다.
create index t_owner_idx on t(owner) ;
Index T_OWNER_IDX이(가) 생성되었습니다.
begin
dbms_stats.gather_table_stats(user, 'T', method_opt=> ' for all columns size 1' );
end;
PL/SQL 프로시저가 성공적으로 완료되었습니다.
alter session set "_optimizer_cost_model" = io;
Session이(가) 변경되었습니다.
set autotrace traceonly explain;
Traceonly 옵션은 현재 지원되지 않습니다.
EXPLAIN PLAN FOR
select /* + index (t) */* from t where owner = 'SYS' ;
select * from table(dbms_xplan.display)
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1292 | 113K| 37 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1292 | 113K| 37 |
|* 2 | INDEX RANGE SCAN | T_OWNER_IDX | 1292 | | 4 |
---------------------------------------------------------------------------
==========================================================================================
index scan 단계에서 cost 4 발생
table access 단계에서 37 - 4(index scan) = 34 single block 발생
row가 1292인데 I/O call 이 34번인건 클러스트링 팩터가 비용계산식에 고려 되어있기때문
비용 = blevel + -- 인텍스 수직적 탐색 비용
(리프 블록 수 × 유효 인텍스 선택도) + -- 인텍스 수평적 탐색 비용
(클러스터링 팩터 × 유효 태이블 선택도) -- 테이블 Random 액세스 비용
* blevel : 브랜치 레벨을 의미하며
* 유효 인텍스 선택도 : 전체 인텍스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
* 유효 테이블 선택도 : 전체 레묘드 중에서 인텍스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)
select i.blevel , i.leaf_blocks, c .num_distinct, i.clustering_factor
, 1 + (i.leaf_blocks * 1 / c.num_distinct) "인텍스 스캔 비용"
, 1 + (i.leaf_blocks * 1 / c.num_distinct)
+ (i.clustering_factor * 1 / c.num_distinct) "총 테이블 액세스 비용"
from user_indexes i , user_tab_col_statistics c
where i.index_name = 'T_OWNER_IDX'
and c.table_name = i.table_name
and c.column_name = 'OWNER' ;
BLEVEL LEAF_BLOCKS NUM_DISTINCT CLUSTERING_FACTOR 인텍스 스캔 비용 총 테이블 액세스 비용
---------- ----------------------- ------------------------ ------------------------- ---------------------- ---------------------------------------
1 44 14 452 4.1428571428571 36.428571428571428571
h5.Full Scan에 의한 테이블 액세스 비용
-- 테이블을 Full Scan할 때는 HWM 아래쪽 블록을 순차적으로 읽어 들이는 과정에서 발생하는 I/O Call 횟수로 비용을 계산
-- Full Scan할 때는 한번의 I/O Call로써 여러 블록을 읽어 들이는 Multiblock I/O 방식을 사용
-- 총블럭수 / db_file_multiblock_read_count = I/O call <= 가 일치하진 않는다
select blocks from user_tables where table_name = 'T' ;
BLOCKS
----------
242
alter session set db_file_multiblock_read_count = 2; -- 멀티블록을 두개씩 읽어라
Session이(가) 변경되었습니다.
EXPLAIN PLAN FOR
select /*+ full(t) */ * from t where owner = 'SYS' ;
select * from table(dbms_xplan.display);
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1292 | 113K| 93 |
|* 1 | TABLE ACCESS FULL| T | 1292 | 113K| 93 |
----------------------------------------------------------
db_file_multiblock_read_count = 4
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1292 | 113K| 60 |
|* 1 | TABLE ACCESS FULL| T | 1292 | 113K| 60 |
----------------------------------------------------------
db_file_multiblock_read_count = 8
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1292 | 113K| 38 |
|* 1 | TABLE ACCESS FULL| T | 1292 | 113K| 38 |
----------------------------------------------------------
h5.I/O 비용 모델의 비현실적인 가정
디스크 I/O Call 횟수로써 테이블 액세스 비용을 평가 의미
- Single Block I10와 Multiblock I/O 는 비용이 같다.
- 캐싱 효과를 전혀 고려하지 않는다.
위의 가정을 보정하는 오라클 파라미터
http://wiki.gurubee.net/pages/viewpage.action?pageId=1507599
h3.(2) CPU 비용 모델
블록 I/O가 소량인데도 쿼리 수행 시간이 상당히 오래 걸리는 경우
- 해시 조인할 때，해시 체인에 달린 레코드가 많아 해시 체인을 스캔하는 부하가 심할 때(2장)
- 캐싱된 블록을 반복적으로 읽는데,한 블록 내에서 매번 비효율적으로 많은 레코드를 스캔할때
ex) NL 조인 Inner쪽 인텍스 선두 컬렁이 between 조건일 때 - 버퍼를 Pin 한상태에서 같은블록을 반복액세스 할때
- 다량의 레코드를 읽으면서 건건이 여러 개의 사용자 정의 함수를 반복 호출할 때
- 메모리 소트를 반복할 때
CPU 비용 모댈에서의 비용계산식
Cost = ( #SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
- #SRds - Single Block I/0 요청 횟수
- #MRds - Multiblock I/O 요청 횟수
- #CPUCycles - 쿼리 수행에 필요한 CPU 사이클수
- sreadtim - Single Block i/O 에 소요되는 시간(m잉
- mreadtim - Multiblock I/O 에 소요되는 시간(ms)
- cpuspeed- 초당 처리할 수 있는 CPU 사이클 수
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3354
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.