이 장의 목적

왜 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사용에 의한 추가적인 비용을 고려하지 않는다.

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

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 |
--------------------------------------------------------------------------

^script_Workload_System_Statistics
^Workload_System_Statistics.sql

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를 이용해 강제로 값을 조작해야 할 경우도 있다.