|
|
|
![]()
| [일반] COST BASED OPTIMIZATION(CBO) AND HINTS AND RULE BASED OPTIMIZATION |
|
| 호석 님께서 쓴 글입니다 - 121.♡.39.7 |
읽음:3486 |
|
|
COST BASED OPTIMIZATION(CBO) AND HINTS AND RULE BASED OPTIMIZATION
게시일: 2007. 1. 29 오전 12:35 댓글
제품 : SQL*PLUS
작성날짜 : 2002-10-28
COST BASED OPTIMIZATION(CBO) AND HINTS AND RULE BASED OPTIMIZATION
==================================================================
PURPOSE
--------------------------------------------------------------------------------
Cost based optimization, hint 그리고 rule base optimization에
관련된 설명입니다.
Explanation
--------------------------------------------------------------------------------
1. CBO 란?
Cost Based Optimization은 table 내의 row와 column의 분포에 따라서 가장
적합한 path를 찾는 접근 방법이라 할 수 있다.
table 내용에 대한 통계적 정보는 'Analyze' 문장에 의해 구해질 수 있다.
Syntax:
Analyze table <table name> estimate statistics;
일반적으로 큰 table인 경우 full table scan을 피하기 위해 sample data만
가지고 측정하는 'estimate statistics' option을 쓴다.
Analyze index/cluster <index name/cluster name> compute statistics;
index와 cluster는 table 내의 특정 key column으로 구성되어 있기 때문에
full scan으로 계산하는 경우가 많다.
2. Explain Plan 보기
Syntax:
Explain plan set statement_id='<Statement_id>'
<Query>;
예:
Explain plan set statement_id='info1'
For select num from p;
결과:
explain plan 문의 결과는 plan_table 에 저장이 된다.
plan_table으로부터 결과를 select하는 문을 만든다면
select lpad(' ',2*(LEVEL - 1))||cardinality||' '||operation||' '
||options||' '||object_name||' '||decode(id,0,'Cost '||position)
"Query Plan"
from plan_table
start with id=0 and statement_id='info1'
connect by prior id=parent_id and statement_id='info1';
위 Query의 결과:
8 SELECT STATEMENT COST = 1
8 TABLE ACCESS FULL P
여기서 8 이란 table에서 select된 row의 수를 말한다.
3. CBO와 RBO의 차이점
Rule Based Optimization은 미리 정해진 rule에 따라 execution plan을
정하는 방법이다.
따라서, 매우 selectivity가 좋지 않은 index인 경우 full table scan이
오히려 더 빠른 방법이지만 index scan을 선택한다.
(selectivity가 좋다는 것은 distinct한 값이 많다는 의미이다.)
CBO는 현재의 통계정보로부터 모든 search path를 고려하여 실행 계획을 수립
한다. 위의 경우 CBO는 full table scan을 선택한다.
또한 CBO의 cost engine은 I/O cost, Network Cost, CPU cost 등을 계산하도록
design되어 있다.
4. 관련 Parameters
1) OPTIMIZER_MODE
: initialization file(initSID.ora)에 setting되며 RULE, CHOOSE,
ALL_ROWS, FIRST_ROWS의 값을 가질 수 있다.
2) OPTIMIZER_GOAL
: session level로만 사용되어 진다.
Alter session set OPTIMIZER_GOAL = FIRST_ROWS;
FIRST_ROWS 는 Best response time을 위한 접근 방법으로 검색되는
row가 적은 interactive application에 적합하다.
Alter session set OPTIMIZER_GOAL = ALL_ROWS;
ALL_ROWS 는 Best throughput을 위한 접근 방법으로 batch operation
등에 적합하다.
3) SORT_AREA_SIZE
: 큰 값일 수록 sort cost는 더 좋아지고 sort merge join을 더 많이 하게
된다.
4) DB_FILE_MULTIBLOCK_READ_COUNT
: 큰 값일 수록 table scan cost가 더 좋아지고 selectivity가 좋지 않은
index인 경우 index scan보다는 full table scan을 선호한다.
5) HASH_AREA_SIZE
: 큰 값일 수록 sort cost는 더 좋아지고 sort merge join을 더 많이 하게
된다.
6) OPTIMIZER_PERCENT_PARALLEL
: optimizer가 사용하는 parallelism의 양을 정의한다.(0 - 100)
default는 0인데 이 때는 optimizer가 best serial plan을 선택하며,
100인 경우 optimizer는 full table scan의 cost를 계산할 때 각
object의 parallel degree를 사용한다.
따라서 낮을 수록 index를, 높을 수록 full table scan을 선호한다.
5. HINT의 사용
ALL_ROWS, FIRST_ROWS, CHOOSE, RULE, FULL,
ROWID, CLUSTER, HASH, INDEX, INDEX_ASC
예) OPTIMIZER_MODE=CHOOSE (init.ora)
TABLE : P , P2
INDEX : P table의 column num에 index P1
P2 table의 column num에 index i2
SQL> select * from p;
NUM DATA
--------------------------------------------------------------------------------
----------
7 hsdh
6 wqe
1 as
2 sdq
3 sdfcsd
4 sda
5 sad
6 sdfs
7 dsfesdf
9 rows selected.
SQL> select * from p2
NUM DATA
--------------------------------------------------------------------------------
----------
1 sda
2 sda
3 jkj
4 jkj
4 uwqe
8 uwqe
9 wqe
2 wqe
8 rows selected.
아래의 analyze 문을 수행한다.
analyze table p compute statistics for columns num size 10;
analyze table p2 compute statistics for columns num size 10;
column num에 index가 있고, 그 column에 cluster c11이 있는 table gam과
cmg를 만든다.
create cluster c11 (num_c number);
create table gam (num number, data ...
cluster c11(num));
INDEX : cluster c11 에 index ic11
table gam의 num column에 index igam
table icmg의 num column에 index icmg
Table Gam :
--------------------------------------------------------------------------------
NUM DATA
--------------------------------------------------------------------------------
----------
1 2312
1 54
5 54
5 dfg
5 fh
2 fh
7 fh
7 a
Table cmg :
--------------------------------------------------------------------------------
NUM DATA
--------------------------------------------------------------------------------
----------
1 sda
5 hgffadf
2 sda
2 hgffadf
3 sda
3 sdfsadf
3 sadfadf
3 hgffadf
3 hgffadf
6 hgffadf
8 hgffadf
아래의 analyze 문을 수행한다.
analyze table gam estimate statistics for columns num size 10;
analyze table cmg estimate statistics for columns num size 10;
analyze index igam compute statistics;
analyze index icmg compute statistics;
analyze cluster c11 compute statistics;
analyze index ic11 compute statistics;
1) ALL_ROWS
ALL_ROWS는 full table scan을 선호하며 CBO는 default로 ALL_ROWS를
선택한다.
explain plan set statement_id='cbo1' for
select /*+ ALL_ROWS */ num, data from p where num = 7
Query Plan
--------------------------------------------------------------------------------
5 SELECT STATEMENT Cost 1
5 TABLE ACCESS FULL P
cluster index를 가지고 있는 gam table은 full table scan보다는
index scan을 선택한다.
explain plan set statement_id='cbo1' for
select /*+ ALL_ROWS */ num, data from gam where num=7
Query Plan
--------------------------------------------------------------------------------
2 SELECT STATEMENT Cost 1
2 TABLE ACCESS CLUSTER GAM
INDEX UNIQUE SCAN IC11
2) FIRST_ROWS
full table scan보다는 index scan을 선호하며 interactive
application인 경우 best response time을 제공한다. 또한 sort
merge join보다는 nested loop join을 선호한다.
explain plan set statement_id='cbo1' for
select /*+ FIRST_ROWS */ num,data from gam where num=7
Query Plan
--------------------------------------------------------------------------------
2 SELECT STATEMENT Cost 1
2 TABLE ACCESS CLUSTER GAM
INDEX UNIQUE SCAN IC11
explain plan set statement_id='cbo2' for
select /*+ FIRST_ROWS */ p.num,p.data from p,p2
where p.num = p2.num
Query Plan
--------------------------------------------------------------------------------
40 SELECT STATEMENT Cost 1
40 NESTED LOOPS
9 TABLE ACCESS FULL P
8 INDEX RANGE SCAN I2
이 때 cluster의 유무에 따라 Cost가 틀려지는 것을 gam과 cmg table
을 조회해 봄으로써 알 수 있다.
explain plan set statement_id='cbo1' for
select /*+ FIRST_ROWS */ gam.num,gam.data from gam,cmg
where gam.num=cmg.num
Query Plan
--------------------------------------------------------------------------------
15 SELECT STATEMENT Cost 2
15 NESTED LOOPS
8 TABLE ACCESS FULL GAM
11 INDEX RANGE SCAN ICMG
또 select list에 따라 index scan을 하는 table이 바뀔 수도 있다.
explain plan set statement_id='cbo2' for
select /*+ FIRST_ROWS */ p2.num,p2.data from p,p2
where p.num = p2.num
Query Plan
--------------------------------------------------------------------------------
40 SELECT STATEMENT Cost 1
40 NESTED LOOPS
9 TABLE ACCESS FULL P2
8 INDEX RANGE SCAN P1
Group by 가 있는 SQL문은 FIRST_RIWS가 있다 하더라도 index scan을
하지 않는다.
explain plan set statement_id='cbo2' for
select /*+ FIRST_ROWS */ sum(p2.num) from p2 group by p2.num
Query Plan
--------------------------------------------------------------------------------
8 SELECT STATEMENT Cost 5
SORT GROUP BY
8 TABLE ACCESS FULL P2
3) CHOOSE
hint level의 CHOOSE는 RBO인지 CBO인지를 선택한다.
만약 주어진 table의 통계 정보가 없다면 Rule Based 접근 방식을 사용한다.
explain plan set statement_id='cbo2' for
select /*+ CHOOSE */ sum(p2.num) from p2 group by p2.num
Query Plan
--------------------------------------------------------------------------------
8 SELECT STATEMENT Cost 5
SORT GROUP BY
8 TABLE ACCESS FULL P2
4) RULE
Rule Based 접근 방식을 사용하도록 지정한다.
explain plan set statement_id='cbo2' for
select /*+ RULE */ sum(test.num)
from test
group by test.num
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost
SORT GROUP BY
TABLE ACCESS FULL P2
5) FULL
index가 있지만 selectivity가 좋지 않은 경우 full table scan을 선택
하도록 한다.
explain plan set statement_id='cbo2' for
select /*+ FULL(p) */ num,data from p where num=7
Query Plan
--------------------------------------------------------------------------------
5 SELECT STATEMENT Cost 1
5 TABLE ACCESS FULL P
6) ROWID
rowid로 table scan을 하도록 지정한다.
explain plan set statement_id='cbo4' for
select /*+ ROWID(p) */ num, data from p where num=7
Query Plan
--------------------------------------------------------------------------------
5 SELECT STATEMENT Cost 1
5 TABLE ACCESS FULL P
gam table을 사용하는 경우에는 index cluster IC11을 사용하는 것을
알 수 있다.
explain plan set statement_id='cbo1' for
select /* ROWID(gam) */ num,data from gam where num=7;
Query plan
--------------------------------------------------------------------------------
2 SELECT STATEMENT Cost 1
2 TABLE ACCESS CLUSTER GAM
INDEX UNIQUE SCAN IC11
7) CLUSTER
cluster scan을 선택하도록 지정한다. 따라서 clustered object들에만
적용이 된다.
gam table은 cluster scan을 하고 cmg table은 index range scan을
하는 예를 들어 보자.
join operation에서 cluster hint를 사용하면 cost는 비교적 적게 든다.
explain plan set statement_id='cbo1' for
select /*+ CLUSTER(gam) */ gam.num,gam.data from gam,cmg
where gam.num=7
and gam.num=cmg.num
Query Plan
--------------------------------------------------------------------------------
2 SELECT STATEMENT Cost 1
2 NESTED LOOPS
2 TABLE ACCESS CLUSTER GAM
INDEX UNIQUE SCAN IC11
2 INDEX RANGE SCAN ICMG
8) HASH
hash scan을 선택하도록 지정한다. 이 hint는 HASHKEYS parameter를
가지고 만들어진 cluster내에 저장된 table에만 적용이 된다.
eab table과 gsh table이 있고 각각의 table에 있는 num column에는
ieab, igsh라는 index 가 잡혀져 있다고 가정한다.
HASKEY parameter를 2로 한 num column을 가진 hash cluster c21을
만든다. eab table과 gsh table이 cluster c21에 지정된다.
explain plan set statement_id='cbo1' for
select /*+ HASH(eab) */ eab.num,eab.data from eab,gsh
where eab.num=7 and eab.num=gsh.num;
Query Plan
--------------------------------------------------------------------------------
3 SELECT STATEMENT Cost 1
3 NESTED LOOPS
3 TABLE ACCESS HASH EAB
2 INDEX RANGE SCAN IGSH
9) INDEX
지정된 index를 강제적으로 쓰도록 지정한다.
explain plan set statement_id='cbo1' for
select /*+ INDEX(gam igam) */ num from gam where num =7
Query Plan
--------------------------------------------------------------------------------
2 SELECT STATEMENT Cost 1
2 INDEX RANGE SCAN IGAM
10) INDEX_ASC
지정된 index를 오름차순으로 쓰도록 지정한다. default로 index scan
은 오름차순이다.
select /*+ INDEX_ASC(gam igam) */ num from gam where num=7;
11) INDEX_DESC
지정된 index를 내림차순으로 쓰도록 지정한다.
select /*+ INDEX_DESC */ num from gam where num =7;
Query Plan
--------------------------------------------------------------------------------
2 SELECT STATEMENT Cost 1
2 INDEX RANGE SCAN DESCENDING IGAM
6. CBO에서 알아두어야 할 몇 가지
1) CBO는 복잡한 relationship에서 때로는 엉뚱한 plan을 수립할 수도 있
는데, 그런 경우 hint를 사용하여야 한다.
2) CBO는 join에 있어서 driving table을 FROM 절의 왼쪽부터 선택한다.
이는 RBO와는 반대이다.
3) CBO는 NESTED LOOP join보다는 SORT-MERGE join을 권장하므로 first
row를 위한 response time에는 늦어질 수 있다.
4) table에 대한 ANALYZE operation은 자동적으로 관련된 index까지 적용
이 된다.
5) table에 대한 ANALYZE operation은 주기적으로 이루어져야 한다.
6) FIRST_ROWS hint는 order by와 같이 쓰일 때에는 index scan을 하지
않고 SORT operation을 수행한다는 점에 유의해야 한다.
|
|
|
|
|
|
