꿈꾸는 개발자, DBA 커뮤니티 구루비

Time 기반의 Cost Mode의 장점
- Single Block I/O Time과 Multi Block I/O Time의 현실적인 비교가 가능해 진다.
- CPU 오버헤드를 Cost 계산에 고려 할 수 있다.
Workload System Statistics을 수집하면 다음과 같은 세 종류의 값이 추가로 계산 된다.
- mbrc : Opimizer가 사용할 MBRC값을 결정
- sreadtime : Single Block I/O의 평균 수행 시간(ms)
- mreadtime : Multi Block I/O의 평균 수행 시간(ms)
sreadtime과 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의 성능 차이를 정확하게 알 수 있음
- mreadtime > sreadtime => Table Full Scan은 불리, Index Lookup은 점점 유리
  mreadtime < sreadtime => Table Full Scan이 점점 유리
I/O Cost Model을 사용하는 경우, (c1 between ... and ...) 조건에서 범위가 커지면은 Index Lookup Cost의 범위가 증가하면서, Index Range Scan이 아닌 Table Full Scan으로 변함
이 경계에서 Single Block I/O의 Cost가 Multi Block I/O의 Cost를 넘어선 것이다.


SQL>drop table t1 purge;

Table dropped.

SQL>create table t1(c1 int, c2 char(100), c3 char(100));

Table created.

SQL>create index t1_n1 on t1(c1);

Index created.


SQL>insert into t1
  2  select level, 'dummy', 'dummy' from dual
  3  connect by level <= 100000;

100000 rows created.

SQL>exec dbms_stats.gather_table_stats('ghlee', 't1');

PL/SQL procedure successfully completed.

SQL>alter session set "_optimizer_cost_model"=io;

Session altered.

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 1000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   988 |   198K|    33 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   988 |   198K|    33 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |   988 |       |     3 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=1000)

Note
-----
   - cpu costing is off (consider enabling it)

18 rows selected.

SQL>explain plan fo
  2
SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 2000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1988 |   399K|    64 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1988 |   399K|    64 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |  1988 |       |     5 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=2000)

Note
-----
   - cpu costing is off (consider enabling it)

18 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 10000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9990 |  2009K|   292 |
|*  1 |  TABLE ACCESS FULL| T1   |  9990 |  2009K|   292 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1">=1 AND "C1"<=10000)

Note
-----
   - cpu costing is off (consider enabling it)

17 rows selected.

SQL>

Time Cost Model에 대해서 수행하지만, System Statistics를 수집하지 않는 Noworkload System Statistics만이 존재하는 경우


SQL>alter session set "_optimizer_cost_model"=cpu;

Session altered.

SQL>exec dbms_stats.delete_system_stats;

PL/SQL procedure successfully completed.

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 1000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   988 |   198K|    33   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   988 |   198K|    33   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |   988 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=1000)

14 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 2000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1988 |   399K|    64   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1988 |   399K|    64   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |  1988 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=2000)

14 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 10000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  9990 |  2009K|   315   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  9990 |  2009K|   315   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |  9990 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=10000)

14 rows selected.


SQL>explain plan for
  2  select /*+ full(t1) */ *
  3  from t1
  4  where c1 between 1 and 10000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9990 |  2009K|   664   (1)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| T1   |  9990 |  2009K|   664   (1)| 00:00:08 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"<=10000 AND "C1">=1)

13 rows selected.

위의 테스트 결과에서 주목할 점
- I/O Cost Model (1~1000) 범위에서 Table Full Scan이 선택된 것에 반해, Time Cost Model(NoWorkload Stats)에서는 (1~1000) 범위에서도 여전히 Index Range Scan이 선택
- Plan의 결과에 Cost항목에 CPU비용이 차지하는 비중이 기록된다. 더불어, Time, 즉 예측 실행 시간이 기록된다.
- Table Full Scan의 비용이 286->649로 훌쩍 증가 했음을 알 수 있다. 반면 Index Lookup 비용은 거의 동일하다. Time Cost Model은 Single Block I/O의 Cost를 낮추는 방식이 아니라, Multi Block I/O의 Cost를 높이는 방식을 사용한다는 것을 의미
Time Cost Model을 사용하되, Workload System Statistics가 수집된 경우


SQL>alter session set "_optimizer_cost_model" = cpu;

Session altered.

SQL>begin
  2  dbms_stats.set_system_stats('cpuspeed', 1680);
  3  dbms_stats.set_system_stats('sreadtim', 5);
  4  dbms_stats.set_system_stats('mreadtim', 10);
  5  dbms_stats.set_system_stats('mbrc', 8);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$
SNAME           PNAME              PVAL1 PVAL2
--------------- --------------- -------- -------------------------
SYSSTATS_INFO   STATUS                   COMPLETED
SYSSTATS_INFO   DSTART                   02-12-2009 00:19
SYSSTATS_INFO   DSTOP                    02-12-2009 00:19
SYSSTATS_INFO   FLAGS                  1
SYSSTATS_MAIN   CPUSPEEDNW          1206
SYSSTATS_MAIN   IOSEEKTIM             10
SYSSTATS_MAIN   IOTFRSPEED          4096
SYSSTATS_MAIN   SREADTIM               5
SYSSTATS_MAIN   MREADTIM              10
SYSSTATS_MAIN   CPUSPEED            1680
SYSSTATS_MAIN   MBRC                   8
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 1000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   988 |   198K|    33   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   988 |   198K|    33   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |   988 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=1000)

14 rows selected.


SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 2000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  1988 |   399K|    64   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  1988 |   399K|    64   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |  1988 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=2000)

14 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where c1 between 1 and 10000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1420382924

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  9990 |  2009K|   316   (1)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  9990 |  2009K|   316   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |  9990 |       |    21   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=10000)

14 rows selected.

SQL>explain plan for
  2  select /*+ full(t1) */ *
  3  from t1
  4  where c1 between 1 and 10000;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9990 |  2009K|   761   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  9990 |  2009K|   761   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"<=10000 AND "C1">=1)

13 rows selected.

Time Cost Mode은 I/O Cost Model에서는 계산이 불가능 했던 CPU Overhead를 어느 정도 고려가 가능
Predicate가 추가되거나 복잡해 짐에 따라 Predicate를 처리하는데 필요한 CPU점점 비용이 증가


SQL>explain plan for
  2  select * from t1;

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    19M|   761   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|    19M|   761   (1)| 00:00:04 |
--------------------------------------------------------------------------

8 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where c2='dummy';

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    19M|   764   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    19M|   764   (2)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"='dummy')

13 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where upper(c2)='DUMMY';

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   201K|   765   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  1000 |   201K|   765   (2)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("C2")='DUMMY')

13 rows selected.

SQL>explain plan for
  2  select * from t1
  3  where lower(upper(c2)) = 'dummy';

Explained.

SQL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   201K|   766   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |  1000 |   201K|   766   (2)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LOWER(UPPER("C2"))='dummy')

13 rows selected.

Fetch 해야 할 Column의 수 또한 Cost 계산에 영향을 미친다


SQL>explain plan for
  2  select c1
  3  from t1;

Explained.

SQL>select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|   488K|   760   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|   488K|   760   (1)| 00:00:04 |
--------------------------------------------------------------------------

8 rows selected.

SQL>explain plan for
  2  select c1, c2
  3  from t1;

Explained.

SQL>select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    10M|   761   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|    10M|   761   (1)| 00:00:04 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

Time Model의 또 하나의 큰 장점은 MBRC의 모순을 해결한다는 것이다. MBRC 모순이란 DB_FILE_MULTIBLOCK_READ_COUNT Parameter에 의하 모순을 의미한다.
- DFMBRC값이 커지면 Table Full Scan의 Cost가 줄어든다. Multi Block I/O 회수가 줄어들기 때문이다. ㄸ라서 이 값을 지나치게 크게 하면 Index Range Scan이 Table Full Scan으로 바뀔 위험성이 있다.
- DFMBRC값이 커지면 Multi Block I/O의 성능이 개선된다.
  즉, 큰 크기의 DFMBRC 값은 Optimizer에게는 부정적인 영향을 미치지만, 실행시에는 매우 유리하다. 이 모순을 System Statistics를 수집하면 mbrc값이 저장된다.
  이 값이 존재하면 Optimier는 더 이상 Cost 계산 시에 DFMBRC값을 참조하지 않는다. 즉, DFMBRC값이 커진다고 해서 실행 계획의 변화가 생기는 부작용이 발생하지 않는다.
  하지만 Query를 실제로 수행할 때는 DFMBRC값을 사용한다. 따라서 실행 시의 성능은 여전히 보장되면서 MBRC 모순이 해결된 것이다. {tip:title=DB_FILE_MULTIBLOCK_READ_COUNT}9i부터 소개된 System Statistics가 있으면 Optimizer에 의해 샐행 계획이 생성될 때는 DB_FILE_MULTIBLOCK_READ_COUNT가 무시되지만 실제 Fetch관정에서는 DB_FILE_MULTIBLOCK_READ_COUNT 크기만큼 Multi Block I/O를 수행 (즉, Optimizer에 의해서는 사용되지 않고 실제 쿼리를 실행하고 Fetch하는 단계에서만 사용)
  
  Orale 10g R1은 System Statistics가 없으면 CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED값과 DB_FILE_MULTIBLOCK_READ_COUNT 값을 함께 이용해서 비용을 계산한다. 즉, DB_FILE_MULTIBLOCK_READ_COUNT 파라미터가 사용되기는 하되 9i 같이 직접 사용되는 것이 아니라 Noworkload 통계 정보와 함께 조합되어서 사용.
  
  Oracle 10g R2는 Multi Block I/O와 관련된 파라미터가 세개로 늘어났다. 즉, db_file_multiblock_read_count라는 파라미터가 Optimizer가 사용할 값(_db_file_optimizer_read_count)과 실행시에 사용할 값(_db_file_exec_read_count)으로 세분화 된 것
db_file_multiblock_read_count : System Statistics가 없는 경우에 Optimizer가 비용을 계산하기 위해 사용한다(Noworkload통계값과 같이 사용). System Statistics가 수집된 경우에는 이 값은 무시
db_file_multiblock_read_count  : 이 파라미터값을 명시적으로 변경하면 \_db_file_optimizer_read_count 값과 \_db_file_exec_read_count 값이 모두 같이 변경된다.
\_db_file_exec_read_ount : 쿼리를 실행하는 과정에서 Multi Block I/O를 수행할 때 한번에 읽을 블록수를 결정한다.{tip}

문서에 대하여

최초작성일 : 2009년 2월 14일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.*

Time(CPU) Cost Model의 장점 (by jongmali) [2009.04.30]

문서에 대하여