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

1.About RBO(Rule-Based Optimizer (규칙기반 최적화)
2. About CBO(Cost-Based Optimizer (비용기반 최적화))
1. 정의
3. 테스트

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

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

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

정의

장점 : 통계정보와 I/O와 CPU 비용을 계산하여 실행계획을 예측
단점 : 원하는 경로로 유도하기 어려운 단점
"왜 우리는 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