Optimizing Oracle Optimizer (2011년)
이 장의 목적
- 왜 Oracle은 기존의 I/O 기준의 Cost Model을 버리고 CPU(Time) 기준의 Cost Model을 소개했는가?
- Time 기반의 Cost Model은 어떤 장점이 있고, I/O 기반의 Cost Model은 어떤 단점이 있는가?
I/O Cost Model의 단점
- I/O Cost Model의 단점은 I/O 회수를 기반으로 한 계산 방식이다.
- ( Cost = Single Block I/O Count + Multi Block I/O Count )
- Single Block I/O를 통해 하나의 블록을 읽는 경우나 Multi Block I/O를 통해서 8개의 블록을 읽은 경우 동일하게 Cost=1이다.
- 따라서 Single Block I/O를 통해서 인덱스를 경우한 Random Access가 지나치게 불리하게 계산된다.
- I/O Count를 기반으로 한 Cost 계산 방식은 Index Lookup이 유리함에도 table Full Scan을 선호하는 실행계획을 수립한다.
- 이러한 단점을 극복하기 위해서 OPTIMIZER_INDEX_COST_ADJ Parameter와 OPTIMIZER_INDEX_CACHING Parameter을 소개함.
- CPU사용에 의한 추가적인 비용을 고려하지 않는다.
OPTIMIZER_INDEX_COST_ADJ
Time(CPU) Cost Model의 장점
- Single Block I/OTime과 Multi Block I/O Time의 현실적인 비교가 가능해진다.
- CPU오버헤드를 Cost 계산에 고려할 수 있다.
Workload System Statistics을 수집하면 다음과 같은 세 종류의 값이 추가로 계산 된다.
- mbrc (Multi Block Read Count) : Opimizer가 사용할 MBRC값을 결정
- sreadtim : Single Block I/O의 평균 수행 시간(ms)
- mreadtim : Multi Block I/O의 평균 수행 시간(ms)
sreadtim과 mreadtime이 중요한 이유?
- I/O Cost Mode의 가장 큰 단점인 Single Block I/O와 Multi Block I/O를 동일한 Cos로 처리하는 치명적인 단점을 해소
- Time Cost Model에서는 Single Block I/O의수행시간과 Multi Block I/O의 수행 시간을 알기 때문에 두 Operation의 성능 차이를 정확하게 알 수 있음
- mreadtim > sreadtim => Table Full Scan은 불리하고 Index Lookup은 점점 유리
- mreadtim 과 sreadtim 비슷해질 수록 Table Full Scan이 점점 유리
- 중요한 것은 이런 가중치가 우리가 임의로 지정하는 값에 따라 결정되는 것이 아니라 Oracle이 실제로 일을 수행하는(Workload)상황에서 실제 데이터에 기반해서 결정된다.즉, 시스템의 성능을 고려한 현실적인 가중치가 도출된다
예제) I/O Cost Model과 CPU Cost Model에서 Single Block I/O와 Multi Block I/O 비용이 어떻게 처리되는지 알아보자.
I/O Cost Model
select * from t1
where c1 between 1 and 1000;
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 985 | 198K| 32 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 985 | 198K| 32 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 985 | | 3 |
---------------------------------------------------------------------
select * from t1
where c1 between 1 and 2000;
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1986 | 399K| 64 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1986 | 399K| 64 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 1986 | | 5 |
---------------------------------------------------------------------
select * from t1
where c1 between 1 and 10000;
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 9988 | 2009K| 292 |
|* 1 | TABLE ACCESS FULL| T1 | 9988 | 2009K| 292 |
----------------------------------------------------------
Noworkload System Statistics
select * from t1
where c1 between 1 and 1000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 985 | 198K| 32 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 985 | 198K| 32 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 985 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select * from t1
where c1 between 1 and 2000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1986 | 399K| 64 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1986 | 399K| 64 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 1986 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select * from t1
where c1 between 1 and 10000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9988 | 2009K| 315 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 9988 | 2009K| 315 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 9988 | | 21 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select /*+ full(t1) */ * from t1
where c1 between 1 and 10000;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9988 | 2009K| 664 (1)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| T1 | 9988 | 2009K| 664 (1)| 00:00:08 |
--------------------------------------------------------------------------
- script_io_cost_model.sql
- io_cost_model.sql
- script_Noworkload_System_Statistics.sql
- Noworkload_System_Statistics.sql
위의 테스트 결과에서 주목할 점
- 1. Time Cost Model 에서는 1~10000범위에서도 INDEX RANGE SCAN을 선택한다.
- 2. Plan의 결과에 추가적인 정보들이 기록된다.
- CPU비용이 차지하는 비중이 기록된다
- Time, 즉 예측 실행 시간이 기록된다.
- 3. Table Full Scan의 비용이 292에서 664로 증가되었다.반면 Index Lookup 비용은 거의 동일하다 (292:315).
- Time Cost Model은 Single Block I/O의 Cost를 낮추는 방식이 아니라, Multi Block I/O의 Cost를 높이는 방식을 사용한다는 것을 의미한다 (315:664)
Workload System Statistics
alter session set "_optimizer_cost_model"=cpu;
--set system statistics manually
begin
dbms_stats.set_system_stats('cpuspeed', 1680);
dbms_stats.set_system_stats('sreadtim', 5);
dbms_stats.set_system_stats('mreadtim', 10);
dbms_stats.set_system_stats('mbrc', 8);
end;
/
select * from t1
where c1 between 1 and 1000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 985 | 198K| 32 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 985 | 198K| 32 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 985 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select * from t1
where c1 between 1 and 2000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1986 | 399K| 64 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1986 | 399K| 64 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 1986 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select * from t1
where c1 between 1 and 10000;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9988 | 2009K| 316 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 9988 | 2009K| 316 (1)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T1_N1 | 9988 | | 21 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
select /*+ full(t1) */ * from t1
where c1 between 1 and 10000;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9988 | 2009K| 761 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 9988 | 2009K| 761 (1)| 00:00:04 |
--------------------------------------------------------------------------
- Manual로 설정한 System Statistics의 영향으로 table Full Scan비용이 664에서 761로 증가하였다.
Time Model의 한계
- Time Model의 가장 기본적인 한계는 mreadtim과 sreadtim의 값의 부정확성에 있다
- Enterprise 환경의 Storage들은 매우 뛰어난 성능의 Sequential I/O를 보장한다.
- 여기에 매우 큰 크기의 Cache를 사용한다. 이로 인해서 sreadtim이 mreadtim보다 더 높은 다소 비현실적인 현상이 발생할 수 있다.
- System Statistics를 사용하는 경우 부정확한 mreadtim과 sreadtim이 목격될 수 있으므로, 필요하다면 DBMS_STATS.SET_SYSTEM_STATS procedure를 이용해 강제로 값을 조작해야 할 경우도 있다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3901
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.