by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

RBO vs CBO




1.About RBO(Rule-Based Optimizer (규칙기반 최적화)

  1. SQL 문을 중심으로 규칙(rule)에 의해서만 실행계획을 예측 & 결정 : 통계정보, 비용계산 사용 NO & 항상 인덱스를 사용하므로 비효율적일 수 있음
  2. 규칙에 의해서 결정되므로 SQL 구문을 변경할 경우 성능이 향상될 가능성 있음

2. About CBO(Cost-Based Optimizer (비용기반 최적화))

  1. 정의
    1. 장점 : 통계정보와 I/O와 CPU 비용을 계산하여 실행계획을 예측
    2. 단점 : 원하는 경로로 유도하기 어려운 단점
    3. "왜 우리는 CBO의 작동원리를 알아야 하는가?"라는 질문에 Cost-Based Oracle Fundamentals 의 저자 Jonathan Lewis는 다음과 같이 말했다.
      OPTIMIZER가 잘못된 실행계획을 생성하여 어떤 문제가 발생했을 때, 그 문제를 제대로 파악하고 올바른 해결책을 제시하기 위함이다.
          SQL에 몇 개의 힌트를 추가하거나 쿼리문을 일부 다시 작성하여 당장의 문제를 해결할 수 있지만 그런 접근법을 사용하게 되면 여기 저기서 동일한 방식의 조치를 취해야 한다.
          반면에 CBO의 근본적인 동작을 교정하면 한 번 조치로 문제가 발생하는 모든 경우를 해결 할 수 있다.

3. 테스트

1. RBO

select /*+ RULE */ t1.object_name, t2.object_name
     from big_table t1, big_table t2
     where t1.object_id= t2.object_id and t1.owner='WMSYS';

     1) Explain on SQLPLUS

-----------------------------------------------------------
| Id  | Operation                   | Name                |
-----------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |
|*  1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE           |
|   2 |   NESTED LOOPS              |                     |
|   3 |    TABLE ACCESS FULL        | BIG_TABLE           |
|*  4 |    INDEX RANGE SCAN         | BIG_TABLE_OWNER_IDX |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   4 - access("T1"."OWNER"='WMSYS')

Note
-----
   - rule based optimizer used (consider using cbo)

     2) TKPROF

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.04          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.02       0.04          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 54  (SCOTT)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: HINT: RULE
      0   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE)
      0    NESTED LOOPS
      0     TABLE ACCESS   MODE: ANALYZED (FULL) OF 'BIG_TABLE' (TABLE)
      0     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'BIG_TABLE_OWNER_IDX'(INDEX)


2. CBO

select t1.object_name, t2.object_name
     from big_table t1, big_table t2
     where t1.object_id= t2.object_id and t1.owner='WMSYS';

     1) Explain on SQLPLUS

Elapsed: 00:00:26.20
------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |  2057K|   133M|       |  8094   (2)| 00:01:38 |
|*  1 |  HASH JOIN                   |                     |  2057K|   133M|  4000K|  8094   (2)| 00:01:38 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG_TABLE           | 83473 |  3016K|       |  2527   (1)| 00:00:31 |
|*  3 |    INDEX RANGE SCAN          | BIG_TABLE_OWNER_IDX | 83473 |       |       |   199   (2)| 00:00:03 |
|   4 |   TABLE ACCESS FULL          | BIG_TABLE           |  1001K|    29M|       |  3289   (2)| 00:00:40 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - access("T1"."OWNER"='WMSYS')

     2) TKPROF

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.06          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.04       0.06          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  (SCOTT)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   HASH JOIN
      0    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'BIG_TABLE' (TABLE)
      0     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'BIG_TABLE_OWNER_IDX' (INDEX)
      0    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'BIG_TABLE' (TABLE)


3. 비교

Statistics RBO CBO
recursive calls 0 0
db block gets 0 0
consistent gets 108030537 17219
physical reads 3475 13265
redo size 404 580
bytes sent via SQL*Net to client 954630 954630
bytes received via SQL*Net from client 46211 46211
SQL*Net roundtrips to/from client 4168 4168
sorts (memory) 0 0
sorts (disk) 0 0
rows processed 62500 62500
Elapsed 00:40:11.14 00:00:26.20
Join Operation NESTED LOOPS HASH JOIN
Optimizer mode RULE ALL_ROWS


1. 개요
  1) Logical Read = db block gets + consistent gets : 메모리 상의 buffer에서 엑세스 되는 block의 수
  2) PHYSICAL READS = 디스크를 읽어 데이터 파일에서 loading 되는 block 의 합
  3) consistent gets : 쿼리 수행 시 buffer에 access한 block 수
    (select~~ for update~~ 는 제외. TKPROF 레포트에서 query에 해당하는 값)
  4) db block gets : insert, update, select for update시 buffer에 access한 block 수
    (TKPROF 레포트에서 current에 해당하는 값)

2. DML과 consistent gets
  1) DML문장 수행 = 수정할 데이터를 찾기 위한 부분(consistent gets) + 실제 데이터를 수정하기 위한 부분(db block gets)
  2) 데이터를 찾기 위해 읽는 부분 = INSERT문의 sub query부분, delete문과 update문의 where 조건절에 해당하는 부분 혹은 sub query에 해당하는 부분
  3) 예제

update t set value = value + 5 where value > 10; 

    ① value가 10보다 큰 row들 찾기
    ② 각각의 row를 실제로 update하는 value = value + 5를 실행 할 때는 current mode에서 수행
       ☞ current mode : 현재의 data를 읽는 것. 과거 이미지의 데이터 NO!!
    ③ 조건에 해당하는 row를 이미 읽었지만 수정하기 위해서 다시 또 읽음 : 실제 데이터를 수정해야 하기 때문에 current mode의 데이터를 읽음
  4) update문 수행 시 db block gets의 숫자가 더 큰 경우
    ① consistent gets : 수정할 데이터를 찾는 것이므로 읽은 block의 수(block 단위 io)
    ② db block gets : 실제 데이터를 수정해야 하므로 block 단위로 IO가 일어나더라도 row의 수만큼 읽음
    ③ trace의 db block gets의 수 ≒ 실제 수정될 row의 수

3. redo size
  1) redo : commit은 되었으나 디스크에 쓰여지지 않은 내용은 복구하고, commit이 안되어 있는 내용은 rollback함
      Data Files과 Control File의 모든 변경사항을 Redo Log에 기록
  2) redo size : redo가 발생한 전체량
  3) redo entries : redo entry를 redo log buffer로 복사한 횟수
  4) redo entry : 변경된 row 당 1개씩 생성
  5) update의 경우 : undo entry의 양이 변경된 행의 수만큼 발생하므로 redo entry도 비슷하게 증가
      select에서 redo 발생 : select ~~ for update ~~ 실행 혹은 delayed block cleanout발생 때문
  cf) undo : commit 이전(변경 전) 정보를 보관하므로써 consistent read(읽기 일관성) 유지

4. 기타
  1) recursive calls : SQL 수행을 위해 내부적으로 SQL문을 반복 수행 횟수(PL/SQL을 통해 수행되는 부분이 포함)
  2) bytes sent via SQL*Net to client : client로 보내진 bytes의 총 갯수
  3) bytes received via SQL*Net from client : sql*net을 통해 client로 받아들인 bytes 총 갯수
  4) SQL*Net roundtrips to/from client : client와 오고간 sql*net message의 총 갯수
  5) rows processed : 작업으로 발생된 rows 수



4. CBO가 RBO보다 정말 성능이 좋을까?
  - 아래 쿼리를 실행 시켰더니, RULE 힌트를 준 쿼리는 바로 결과를 리턴한 반면, RULE힌트를 주지 않은 쿼리는 시간이 좀 지나서야 결과를 리턴하기 시작했다. 그 때 든 생각이 어떤 경우에는 RBO가 더 빠르지 않을까라는 생각을 하게 되었고 다음의 테스트를 했다.
시스템 통계 정보를 생성 전후의 결과는 달랐다.
시스템 통계 정보 생성 전 결과는 99999행을 가져오는데 CBO가 더 빨랐다. Start는 RBO가 빠르나, 골인선에 먼저 도착한 건 CBO였다.
시스템 통계 정보 생성 후 결과는 99999행을 가져오는데 CBO가 RBO보다 약 3초 정도 느렸다.

   - 구조 : desc research

이름 Null 유무 유형
COH_NO NOT NULL VARCHAR2(3)
PAT_NO NOT NULL VARCHAR2(20)
QUE_FORM_NO NOT NULL VARCHAR2(10)
QUE_NO NOT NULL VARCHAR2(15)
ANSWER   VARCHAR2(1000)
ANSWER_EXP   VARCHAR2(100)
SYNC   VARCHAR2(5)
MEM_NO   VARCHAR2(15)



CASE 1. 시스템 통계 정보 생성 전
     1) RBO : 99999행을 가져오는데 소요시간은 "00:07:32.06"
     2) CBO : 99999행을 가져오는데 소요시간은 "00:02:43.99"

CASE 2. 시스템 통계 정보 생성 후
     1) RBO : 99999행을 가져오는데 소요시간은 "00:00:40.84"
      a) Autotrace 결과

select /*+ RULE */ t1.answer, t2.pat_no from research t1, research t2 where t1.pat_no=t2.pat_no and t1.coh_no='031' and rownum < 100000;

OBJECT_NAME                     OBJECT_NAME                    
-----------------------------  ---------------
V$DBLINK                       V$DBLINK
V$DBLINK                       V$DBLINK
V$DBLINK                       V$DBLINK
V$DBLINK                       V$DBLINK
V$DBLINK                       V$DBLINK
V$DBLINK                       V$DBLINK
                                                                                
99999 개의 행이 선택되었습니다.
 
경   과: 00:00:40.84

------------------------------------------------------------
| Id  | Operation                     | Name               |
------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |
|*  1 |  COUNT STOPKEY                |                    |
|   2 |   NESTED LOOPS                |                    |
|   3 |    TABLE ACCESS BY INDEX ROWID| RESEARCH           |
|*  4 |     INDEX RANGE SCAN          | INDEX_RESEARCH     |
|*  5 |    INDEX RANGE SCAN           | INDEX_RESEARCH_PAT |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<100000)
   4 - access("T1"."COH_NO"='031')
   5 - access("T1"."PAT_NO"="T2"."PAT_NO")

Note
-----
   - rule based optimizer used (consider using cbo)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       8230  consistent gets
          3  physical reads
          0  redo size
    3248401  bytes sent via SQL*Net to client
      73711  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99999  rows processed



      b) TKPROF 결과

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.28       0.39          3       8230          0       99999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.28       0.39          3       8230          0       99999

Misses in library cache during parse: 1
Optimizer mode: RULE

Rows     Row Source Operation
-------  ---------------------------------------------------
  99999  COUNT STOPKEY (cr=8230 pr=3 pw=0 time=600289 us)
  99999   NESTED LOOPS  (cr=8230 pr=3 pw=0 time=300200 us)
    194    TABLE ACCESS BY INDEX ROWID RESEARCH (cr=391 pr=0 pw=0 time=2929 us)
    194     INDEX RANGE SCAN INDEX_RESEARCH (cr=197 pr=0 pw=0 time=1180 us)(object id 54437)
  99999    INDEX RANGE SCAN INDEX_RESEARCH_PAT (cr=7839 pr=3 pw=0 time=115435 us)(object id 54436)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: HINT: RULE
  99999   COUNT (STOPKEY)
  99999    NESTED LOOPS
    194     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'RESEARCH' (TABLE)
    194      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'INDEX_RESEARCH' (INDEX)
  99999     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'INDEX_RESEARCH_PAT' (INDEX)



     2) CBO : 99999행을 가져오는데 소요시간은 "00:00:48.62"
      a) Autotrace 결과

select t1.answer, t2.pat_no from research t1, research t2 where t1.pat_no=t2.pat_no and t1.coh_no='031' and rownum < 100000;

OBJECT_NAME                       OBJECT_NAME                       
--------------------------------- ---------------------------------
/6b398bf1_RBCollationTablesBui    /6b398bf1_RBCollationTablesBui
/6b398bf1_RBCollationTablesBui    /6b398bf1_RBCollationTablesBui
/6b398bf1_RBCollationTablesBui    /6b398bf1_RBCollationTablesBui
/6b398bf1_RBCollationTablesBui    /6b398bf1_RBCollationTablesBui
/6b398bf1_RBCollationTablesBui    /6b398bf1_RBCollationTablesBui
/6b398bf1_RBCollationTablesBui    /6b398bf1_RBCollationTablesBui

99999 개의 행이 선택되었습니다.
경   과: 00:00:48.62

----------------------------------------------------------------------------------------------------                                                                                
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                
----------------------------------------------------------------------------------------------------                                                                                
|   0 | SELECT STATEMENT              |                    | 99999 |  4003K|   725   (1)| 00:00:05 |                                                                                
|*  1 |  COUNT STOPKEY                |                    |       |       |            |          |                                                                                
|   2 |   NESTED LOOPS                |                    |   100K|  4015K|   725   (1)| 00:00:05 |                                                                                
|   3 |    TABLE ACCESS BY INDEX ROWID| RESEARCH           |  3162K|    69M|   122   (0)| 00:00:01 |                                                                                
|*  4 |     INDEX RANGE SCAN          | INDEX_RESEARCH     |       |       |     4   (0)| 00:00:01 |                                                                                
|*  5 |    INDEX RANGE SCAN           | INDEX_RESEARCH_PAT |   836 | 15048 |     5   (0)| 00:00:01 |                                                                                
----------------------------------------------------------------------------------------------------                                                            
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   1 - filter(ROWNUM<100000)                                                    
   4 - access("T1"."COH_NO"='031')                                              
   5 - access("T1"."PAT_NO"="T2"."PAT_NO")                                      

Statistics
----------------------------------------------------------                      
        269  recursive calls                                                    
          0  db block gets                                                      
       8320  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
    3248401  bytes sent via SQL*Net to client                                   
      73711  bytes received via SQL*Net from client                             
       6668  SQL*Net roundtrips to/from client                                  
          5  sorts (memory)                                                     
          0  sorts (disk)                                                       
      99999  rows processed           



      b) TKPROF 결과

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.09       0.18          0       8230          0       99999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.09       0.22          0       8230          0       99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

Rows     Row Source Operation
-------  ---------------------------------------------------
  99999  COUNT STOPKEY (cr=8230 pr=0 pw=0 time=500308 us)
  99999   NESTED LOOPS  (cr=8230 pr=0 pw=0 time=300214 us)
    194    TABLE ACCESS BY INDEX ROWID RESEARCH (cr=391 pr=0 pw=0 time=3711 us)
    194     INDEX RANGE SCAN INDEX_RESEARCH (cr=197 pr=0 pw=0 time=1185 us)(object id 54437)
  99999    INDEX RANGE SCAN INDEX_RESEARCH_PAT (cr=7839 pr=0 pw=0 time=103883 us)(object id 54436)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
  99999   COUNT (STOPKEY)
  99999    NESTED LOOPS
    194     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'RESEARCH' (TABLE)
    194      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'INDEX_RESEARCH' (INDEX)
  99999     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'INDEX_RESEARCH_PAT' (INDEX)


  3) 비교

Statistics RBO CBO
recursive calls    
db block gets    
consistent gets    
physical reads    
redo size    
bytes sent via SQL*Net to client    
bytes received via SQL*Net from client    
SQL*Net roundtrips to/from client    
sorts (memory)    
sorts (disk)    
rows processed 99999 99999

문서에 대하여

  • 최초작성자 : 박혜은
  • 최초작성일 : 2009년 11월 19일
  • 이 문서에 있는 테스트 결과는 DBMS버전과 구성된 환경에 따라 다를 수 있습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 11월 20, 2009

    이현석 says:

    글로 설명되어진 것이랑 TKPROF 결과랑 서로 다르네요. 3.테스트 4. CBO가 RBO보다 정말 성능이 좋을까? 에서 RB...
    • 글로 설명되어진 것이랑 TKPROF 결과랑 서로 다르네요.
    • 3.테스트 4. CBO가 RBO보다 정말 성능이 좋을까? 에서
      RBO는 부분범위처리, CBO는 전체 범위처리를 하기 때문에 당연한거 아닌가요?
      CBO라서 먼저 도착한 것이 아니라 이건 부분범위처리와 전체범위처리의 차이라 생각됩니다.
    • 대용량데이터 베이스에서 나왔던, "RBO일 때 더 안좋을 수 있는 사례" 같은 예제가 좀 아쉽네요...
    1. 11월 20, 2009

      H.E Park says:

      analyze와 시스템 통계 정보를 생성하기 전에 TKPROF 결과를 추출한거예요. 그래서 아래 내용과는 좀 다를 수 있습니다.

      analyze와 시스템 통계 정보를 생성하기 전에 TKPROF 결과를 추출한거예요.
      그래서 아래 내용과는 좀 다를 수 있습니다.