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';
-----------------------------------------------------------
| 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)
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';
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')
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초 정도 느렸다.
| 이름 | 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"
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
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"
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
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 |