Optimizing Oracle Optimizer (2009년)
목록
Time(CPU) Cost Model의 장점 0 0 56,611

by 구루비스터디 CBO Optimizer DB_FILE_MULTIBLOCK_READ_COUNT [2018.07.14]


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 모순이 해결된 것이다.


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년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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