01.CBO_concept.pdf
01.cpu_cost_basic.sql◆ Cost란 무엇인가?
실행 계획을 이해하는 것이고 각 단계별로 Cardinality, 예측 Row수(Estimated Rows)와 실제 Row수(Actual Rows),
그리고 일 량 (Logical Reads, Physical Reads, PGA 사용량 등)을 분석하는 것
1. Time으로서의 Cost
Oracle에서 Query를 수행하는데 걸리는 시간
Total Time = CPU Time + I/O Time + Wait Time
Oracle에서 Query의 수행 예측시간
Estimated Time = Estimated CPU Time + Estimated I/O Time
Oracle에서 I/O 분류
- Single Block I/O : 한 번에 하나의 블록만을 읽어 들이는 방식. Random Access
I/O 에서 발생(Index Lookup, ROWID Lookup)
- Multi Block I/O : 한 번에 여러 개의 인접한 블록을 동시에 읽어 들이는 방식.
Sequential Access I/0에서 발생(Table Full Scan, Index Fast Full Scan)
공식 정리(사실 공식이 중요한 건 아니니 참고용으로만 알고 있자.)
Estimated Time =
Estimated CPU Time + Estimated I/O Time =
Estimated CPU Time + Single Block I/O Time + Multi Block I/O Time
정규화를 수행하는 방법 : 모든 Time의 합을 평균 Single Block I/O Time으로 나누는 것
왜 정규화가 필요한가?
System Statistics의 개념이 소개되기 전(또는 I/O Model이 Cost계산의 기본 Model) Cost란 곧 I/O Count였다.
이 값을 Time으로 변환하는 것이 불가능하기 때문에 역으로 Time을 I/O Count 기준으로 정규화하는 방법을 선택
Cost =
(Estimated Time / Single Block I/O Time) =
(Single Block I/O Count) + (Multi Block I/O Time / Single Block I/O Time) + (CPU Time / Single Block I/O Time) =
Single Block I/O Count + Adjusted Multi Block I/O Count + Adjusted CPU Count
Oracle이 Cost라고 부르는 값
모든 예상 수행 시간(Time)을 Single Block I/O Time에 대한 가중치를 고려한 Count(수행 회수)값으로 변환
2. System Statistics
내가 알던 Cost는 I/O 기준이다. CPU 성능에 대한 수치가 없다면 어떻게 CPU Time을 계산할 수 있는가?
Cost의 의미
"_OPTIMIZER_COST_MODEL "Parameter에 의해 결정(Default : Choose)
System Statistics가 있으면 CPU Model을 사용하고 System Statistics가 없으면 I/O Model을 사용하라.
- System Statistics가 있으면 Time 기반으로 Cost를 계산
- System Statistics가 없으면 I/O 기반으로 Cost를 계산
System Statistics는Oracle9i에서 소개된 개념이며, DBA가 수동으로 수집해야 하는 정보
Oracle은 항상 I/O 기준의 Cost를 사용 : Oracle9i
Oracle10g는 Noworkload System Statistics라는 생소한 개념을 소개한다.
Noworkload System Statistics는 다음과 같이 직접 조회를 해보자.
exec dbms_stats.delete_system_stats;
select * from sys.aux_stats$;
Noworkload System Statistics는 System Statistics를 수집하지 않은 상황에서 Default값으로
사용할 목적(Oracle이 일을 하지 않은상태의 Noworkload)의 System Statistics라는 의미
다음 세 가지의 값으로 구성
1) CPUSPEEDNW(CPU Speed) : CPUSPEEDNW값은 CPU Time을 계산하는데 사용
2) IOSEEKTlM(IO Seek Time), IOTFRSPEED(IO Transfer Speed)
: IOSEEKTIM값과 IOTRFSPEED값은 Single Block l/O Time과 Multi Block I/O Time을 계산하는데 사용
Noworkload System Statistics 또한 DBMS_STATS Package를 이용해 별도로 수집가능
DBA가 수동으로 System Statistics를 수집하지 않더라도 이미 Noworkload System Statistics가 존재하기 때문에
_OPTIMIZER_COST_MODEL Parameter값의 Default값인 CHOOSE는 Oracle9i와 10g에서 전혀 다른 의미를 지니게 된다.
Oracle9i에서는 I/O Model이 사용
Oracle10g에서는 Noworkload System Statistics를 이용하기 때문에 CPU Model을 사용
Oracle10g는 항상 Time 기반으로 Cost 계산 방식을 사용하며 Oracle9i는 System Statistics를
별도로 수집하지 않는 한 I/O 기반의 Cost 계산 방식을 사용한다는 것을 의미
Oracle10g가 Noworkload System Statistics를 기본적으로 사용한다는 사실은 거꾸로 가능하면
System Statistics를 수동으로 수집하는 것이 좋은 습관이라는 것을 의미
Default값은 믿을 수 없는 것(O_O!!!)
DBA가 수동으로 수집 해야 할 System Statistics는 Noworkload System Statistics와
구분하기 위해 Workload System Statistics라고 부른다.
3. I/O로서의 Cost
Oracle9i : Cost Model - I/O기반
(Time과 Cpu성능을 고려하지 않음)
* I/O의 수행 회수(Count)만을 고려함.
I/O기반의 Cost 계산
Cost = Single Block I/O Count + Multi Block I/O Count
Oracle9i에서는 System Statistics가 없으면 항상 I/O Model의 Cost를 사용
Oracle10g에서는 항상 CPU(또는 Time)기반의 Cost를 사용
Version간 실행 계획 호환성을 위해 Oracle10g에서 I/O 기반의 Cost Model을 사용해야 할 경우
다음 방법 사용
-- change _optimizer_cost_model to io
SQL> alter session set "_optimizer_cost_model" = io;
-- use NO CPU COSTING hint
SQL> select /*+ NO CPU COSTING */ ...
4. 왜 Time Model인가?
왜 Oracle은 기존의 I/O 기준의 Cost Model을 버리고 CPU(Time) 기준의 Cost Model을 소개했는가?
I/O Cost Model 의 단점
Cost = Single Block I/O Count + Multi Block I/O Count
Single Block I/O 를 통해 하나의 블록을 읽는 경우 Cost = 1 이다 .
Multi Block I/O 를 통해 8 개의 블록을 한 번에 읽는 경우에도 Cost = 1 이다.
Single Block I/O, 즉 lndex를 경유한 Random Access가 지나치게 불리한 것으로 계산될 우려가 있다.
대표적인 부작용
lndex Lookup이 유리한 것이 명백함에도 불구하고 Table Full Scan을 선호하는 실행계획이 수립되는 것
문제 해결을 위한 두개의 Parameter
- OPTIMIZER_INDEX_COST_ADJ Parameter
- OPTIMIZER_INDEX_CACHING Parameter(이 Parameter만 다룬다.)
의미 : lndex Lookup Cost를 조정(Adjustment)하겠다는 것. 기본값은 100(%)
Single Block I/O를 한번의 Multi Block I/O와 동일하게 취급하겠다는 것
Parameter 값을 50(%)로 변경하면 두 번의 Single Block I/O를 한번의 Multi Block I/O와 동일하게 취급하겠다는 것
lndex Lookup Cost가 50% 낮아지는 효과(가장 치명적인 단점을 보완)
OPTIMIZER_INDEX_COST_ADJ Parameter의 값을 50(%)로 변경함에 따라 Index Lookup Cost가 줄어들고
Table Full Scan 대신 lndex Range Scan을 선호하는 실행 계획이 수립될 것
alter session set optimizer_index_caching = 0 ;
alter session set optimizer_index_cost_adj = 100 ;
explain plan for
select /*+ index (t1 ) */ *
from t1 where c1 > ' ';
@plan
---결과 확인---
alter session set optimizer_index_cost_adj = 50;
explain plan for
select /*+ index (t1) */ *
from t1 where c1 > ' ';
@plan
---결과 확인---
*그렇다 확실하게 반으로 줄어들긴 했다.
optimizer_index_cost_adj Parameter의 최적값은 얼마인가?
기본값은 100(%), 보편적으로 추천되는 값은 5~10(%)정도의 값
Single Block I/O의 비용을 Multi Block I/O의 1/10에서 1/20 사이 정도로 낮게 보겠다는 것
경우에 따라서 1(%)과 같은 극단적으로 낮은 값을 추전
어떤 전문가들은 이 값을 바꾸는 것에 반대(지나치게 광역적인 변화라는 것이다.)
Single Block I/O의 비용과 Multi Block I/O의 비용이 실제로 얼마나 차이가 나는지 누가 알 수 있겠는가?
문제 해결 - Oracle은 System Statistics와 그에 기반한 Time(CPU) 기반의 Cost Model을 소개
I/0 기반의 Cost Model의 또 하나의 단점은 CPU 사용에 의한 추가적인 비용을 고려하지 않는다
select * from t1 where c1 = 1 ;
select * from t1 where f1(c1) = 1 ;
상식적으로 두 번째 SQL문장이 더 많은 CPU 자원을 사용(Function Call이 추가로 발생하기 때문)
I/O기반의 Model에서는 고려 대상이 아니다. 다음과 같은 예를 들면~?
SQL> select c1 from t1 ;
SQL> select c1, c2 from t1 ;
SQL> select c1, c2, c3 from t1 ;
SQL> select c1, c2, c3, c4 from t1 ;
더 많은 Column을 Fetch ---> Block을 Parsing하는 작업이 증가 ---> 더 많은 CPU 자원을 필요
I/O Cost Model에서는 미세한 차이 계산 못함(문제 해결 : CPU Model에서만 가능)
5. Time(CPU) Cost Model의 장점
- Single Block I/O Time과 Multi Block I/O Time의 현실적인 비교가 가능
- CPU 오버헤드를 Cost 계산에 고려
Workload System Statistics를 수집하면 세 종류의 값이 추가로 계산
- mbrc : Optimizer가 사용할 MBRC 값을 결정한다.
- sreadtim : Single Block I/O 의 평균 수행 시간(ms)
- mreadtim : Multi Block I/O 의 평균 수행 시간(ms)
sreadtim과 mreadtim이 I/O Cost Model의 치명적인 단점을 해소 할 수 있다.
I/O Cost Model의 가장 큰 단점
한 번의 Single Block I/O와 한 번의 Multi Block I/O를 동일한 Cost로 처리 한다
OPTIMIZER_INDEX_COST_ADJ Parameter를 사용
반면 Time Cost Model에서는 Single Block I/O의 수행 시간과 Multi Block I/O의 수행 시간을 알기
때문에 두 Operation 의 성능 차이를 정확하게 알 수 있다.
mreadtim 값이 sreadtim 값보다 커질수록 Table Full Scan은 점점 불리 Index Lookup은 점점 유리
mreadtim 값이 sreadtim 값과 비슷해 질수록 Table FulI Scan은 점점 유리
예제를 통해 I/O Cost Model과 CPU Cost Model에서 Single Block I/O와 Multi Block I/O 비용 처리를 확인하자.
drop table t1 purge ;
create table t1(c1 int, c2 char(100), c3 char(100)) ;
create index t1_n1 on t1(c1) ;
-- generate data and gather statistics
insert into t1
select level, 'dummy', 'dummy' from dual
connect by level <= 100000;
@gather t1
I/O Cost Model을 사용하는 경우
{ c1 between ... and ... } 조건에서 범위가 커지면서 Index Lookup Cost 비용이 증가
(1 ~ 10000)의 범위에서 Index Range Scan이 아닌 Table Full Scan으로 선택
Single Block I/O의 Cost가 Multi Block I/O의 Cost를 넘어선 것
-- when cost model is io
alter session set "_optimizer_cost_model" =io;
-- index or fts
explain plan for
select *
from t1
where c1 between 1 and 1000;
@plan
--- 결과 확인 ---
-- index or fts
explain plan for
select *
from t1
where c1 between 1 and 2000;
@plan
--- 결과 확인 ---
-- index or fts
explain plan for
select *
from t1
where c1 between 1 and 10000;
@plan
--- 결과 확인 ---
3개의 Plan을 비교해보자
동일한 테스트를 Time Cost Model에 대해 수행하되 System Statistics를 수집하지 않은 조건
(Noworkload System Statistics만이 존재하는 경우)이다.
alter session set "_optimizer_cost_model" = cpu ;
-- delete system statistics
exec dbms_stats.delete_system_stats;
-- index or fts
explain plan for
select *
from t1
where c1 between 1 and 1000;
@plan
--- 결과 확인 ---
explain plan for
select *
from t1
where c1 between 1 and 2000;
@plan
--- 결과 확인 ---
explain plan for
select *
from t1
where c1 between 1 and 10000;
@plan
--- 결과 확인 ---
explain plan for
select /*+ full (t1) */ *
from t1
where c1 between 1 and 10000;
@plan
--- 결과 확인 ---
테스트 결과에서 주목할 점
- I/0 Cost Model에서는 (1 ~ 1000) 범위에서 Table Full Scan을 선택
Time Cost Model(+No Workload Stats)에서는 (1 ~ 10000) 범위에서도 여전히
Index Range Scan이 선택된다.
- Plan의 결과에 추가적인 정보들이 기록
Cost 항목에 CPU 비용이 차지하는 비중이 기록
Time, 즉 예측 실행 시간이 같이 기록된다. Time 기반의
Cost Model 임을 단적으로 보여줌(출력 편의상 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가 수집된 경우는
mreadtim, sreadtim, mbrc와 같은 값들을 이용해서 Cost를 계산
예제에서는 Manual 하게 System Statistics를 설정하는 방식을 사용
--when cost model is cpu and with system stats
alter session set "_optimizer_cost_model" = cpu;
--set system statistics manually
begin
dbms_stats.set_system_stats('cpuspeed', 1680) ;
dbms_stats.set_system_stats('sreadtim', 5) ;
dbms_stats.set_system_stats('mreadtim', 10) ;
dbms_stats.set_system_stats('mbrc', 8) ;
end;
/
-- index or fts
explain plan for
select * from t1
where c1 between 1 and 1000;
@plan
--- 결과 확인 ---
-- index or fts
explain plan for
select * from t1
where c1 between 1 and 2000;
@plan
--- 결과 확인 ---
-- index or fts
explain plan for
select * from t1
where c1 between 1 and 10000
@plan
--- 결과 확인 ---
-- index of fts
explain plan for
select /*+ full(t1) */ * from t1
where c1 between 1 and 10000
@plan
--- 결과 확인 ---
Manual로 설정한 System Statistics의 영향으로 Table Full Scan의 비용이 649에서 745로 더욱 증가
Time Cost Model이 Multi Block I/O 비용을 조정하는 방식으로 설계
System Statistics를 사용하면 OPTIMIZER_INDEX_COST_ADJ Parameter를 사용 할 필요가 없다.
(sreadtim, mreadtim 값들을 이용해서 이 Parameter의 목적을 충분히 달성)
Time Cost Model의 또 하나의 장점
CPU Overhead를 어느 정도 고려할 수 있다. 예제를 통해서 확인하자.
-- cost of predicate( including function predicate )
explain plan for
select * from t1;
@plan
--- 결과 확인 ---
explain plan for
select * from t1
where c2 = 'dummy';
@plan
--- 결과 확인 ---
explain plan for
select * from t1
where upper(c2) ='DUMMY';
@plan
--- 결과 확인 ---
explain plan for
select * from t1
where lower(upper(c2)) ='dummy';
@plan
--- 결과 확인 ---
테스트 결과 : Predicate가 추가되거나 복잡해짐에 따라 점점 비용이 증가
Predicate를 처리하는데 필요한 CPU 비용이 증가하기 때문
Fetch 해야 할 Column의 수 또한 Cost 계산에 영향을 미친다. 아래 예제를 보자.
예제를 통해서 확인하자.
explain plan for
select c1 frorn t1;
@plan
--- 결과 확인 ---
explain plan for
select c1, c2 from t1;
@plan
--- 결과 확인 ---
Column c1, c2를 Fetch하는 경우, Column c1 하나만 Fetch하는 경우에 비해 비용이 증가
Column c2의 물리적인 순서가 Column c1 다음에 있기 때문에 그만큼 Data를 추가로 추출하는
비용이 증가한다는 가정하에 CPU 비용을 계산하기 때문이다.
Time Model의 또 하나의 큰 장점은
MBRC 모순을 해결(MBRC 모순이란 DB_FILE_MULTI_BLOCK_READ_COUNT Parameter에 의한 모순)
DFMBRC값은 두 가지 면에서 Query의 성능에 영향을 미친다.
- DFMBRC 값이 커지면 Table Full Scan의 Cost가 감소(Multi Block I/O 회수가 감소하기 때문)
따라서 이 값을 지나치게 크게 하면 Index Range Scan이 Table Full Scan 으로 바뀔 위험성이 있다.
- DBMBRC 값이 커지면 Multi Block I/O의 성능이 개선
큰 크기의 DFMBRC 값은 Optimizer에게는 부정적인 영향을 미치지만, 실행 시에는 매우 유리하다.
모순을 어떻게 해결할 것인가?
System Statistics를 수집하면 mbrc 값이 저장된다. 이 값이 존재하면 Optimizer는 더 이상 Cost 계산 시에
DFMBRC 값을 참조하지 않는다.(DFMBRC 값이 커진다고 해서 실행 계획의 변화가 생기는 부작용이 발생하지 않는다.)
그러나 Query를 실제로 수행할 때는 DFMBRC 값을 사용(실행 시의 성능은 여전히 보장)
6. Time Model의 한계
Time Model의 가장 기본적인 한계 : mreadtim과 sreadtim값의 부정확성
Enterprise 환경의 Storage들은 매우 뛰어난 성능의 Sequential I/O를 보장
여기에 매우 큰 크기의 Cache를 사용한다. 이로 인해서 sreadtim이 mreadtim보다 더 높은 다소 비현실적인 현상이
발생 이론적으로는 거의 불가능한 수치이지만 System 구현의 현실적인 제약으로 인해 이런 문제가 발생하는 것
이런 부정확한 mreadtim과 sreadtim값은
"과연 System Statistics 를 사용해야 하는가?" 라는 의문을 낳을 만하다.
결론은 System Statistics를 사용하는 경우 부정확한 mreadtim과 sreadtim이 목격될 수 있다는 것이며,
DBMS_STATS.SET_SYSTEM_STATS Procedure를 이용해 강제로 값을 조작해야 할 경우도 있다.
System Statistics를 사용하고자 한다면 이러한 현실을 정확하게 인식하고 있어야 한다.
CBO의 기본흐름
- 사용자가 SQL 문장을 접수하면 Oracle은 기본적인 Parsing 작업을 수행한다. Syntax를 체크하고 Object 이름과 권한 등을 확인한다.
- Transformation 과정을 수행 Subquery, Inline View와 같은 복잡한 부분들을 해소하고 Predicate를 변환
Star Transformation과 같은 복잡한 Transformation도 이 단계에서 진행(Logical Optimization)
- Transformation이 이루어진 Query를 대상으로 Optimization 작업을 수행 System Statistics와 Object Statistics를 이용해 Optimization에
필요한 기본 정보를 얻고 Access Type과 Join Type을 고려한 비용 계산이 이루어진다. Query의 조건을 만족하는 가장 최소의 비용을
갖는 실행 계획을 도출(Physical Optimization)
- 도출된 실행 계획을 이용해 Query를 실행하고 그 결과를 사용자에게 Return한다.
Optimization 단계에서는 통계 정보에 기반해서 Query의 수행 예측 비용을 계산
System Statistics(Workload / Noworkload), Object Statistics, Dictionary Statistics, Fixed Object Statistics가 모두 CBO에 의해 사용
Object Statistics가 없는 객체에 대해서는 Dynamic Sampling이 수행
Dynamic Sampling 의 동작 방식은 OPTIMIZER_DYNAMIC_SAMPLING Parameter에 의해 결정
Oracle10g의 Parameter의 기본값은 2이며 Object Statistics가 없는 객체에 대해서는 강제로 Sampling을
수행하라는 의미이다.
[그림 3]은 CBO의 기본 흐름의 예제를 좀 더 자세하게 표현한 것이다.
- 사용자가 Inline View가 포함된 간단한 SQL 문장을 수행 요청한다.
- Optimizer는 SQL문장의 구문을 분석해서 Parse Tree를 생성한다. Inline View를 포함하고 있으므로 다층적인 Tree 구조를 가지게 될 것이다.
- Transformation 단계에서는 Inline View를 Main Query안으로 Merging한다.(View Merging)
View Merging이 이루어지면 Inline View는 없어지고 Query가 훨씬 간단한 형태 Optimization을 하기 좋은 형태로 변형
- Optimization 단계에서는 Table, Column, Histogram, System Statistics을 고려하여 최적의 Cost를 갖는 실행 계획을 도출하며
도출 결과 Index Range Scan이 선택되었다.
CBO가 Query를 최적화하는 과정을 10053 Event로 확인해보자.
10053 Event는 CBO의 최적화 과정을 Trace파일에 기록하는 역할을 한다.
Oracle10g R2이전 버전에서는 4번 단계(Optimization)만이 기록
Oracle10g R2부터는 3번 단계(Transformation)가 포함된다. Transformation 과정이 복잡해지는 것에 따라
Troubleshooting 도구를 추가로 제공하는 것이다.
기본적인 용어와 예제
CBO를 이해하기 위한 최소한의 용어들이다.
- NDV : Number of Distinct Value의 약자, 말 그대로 유일하게 구별되는 Column값의 개 수이다. 만일 Column c1이
{ ‘A’, ‘B’, ‘C’, ‘D’, ‘E’ } 다섯 가지 종류의 값으로 이루어져 있다면 NDV(c1) = 5가 된다. 각 값이 차지하는 분포는 고려 대상이
아니라는 것에 유의하자. 이러한 분포는 Histogram에 반영된다. NDV 값은 Object Statistics 수집 시 계산되며
DBA_TAB_COL_STATISTICS_NUM_DISTINCT Column을 통해 조회 가능
- Density : “밀도”, “분포도”라고 하며, 밀도라는 용어가 더 정확하다. Column이 얼마나 밀도가 높은 지를 결정
만일 Column c1이 ‘A’라는 하나의 값만으로 이루어져 있다면 Density = 1이 된다. 만일 Column c1이
{1, 2, 3, …, 10000}의 유일하게 구별되는 10,000 개의 값으로 이루어져 있다면 Density = 1/10000 = 0.0001이 된다.
Density = 1 / NDV 공식을 따른다. Histogram이 없는 경우에는 Density = 1 / NDV의 공식으로 계산된다.
Histogram이 있는 경우에는 분포도를 고려한 보정된 Density값이 계산된다. Density값은 Object Statistics 수집 시 계산되며
DBA_TAB_COL_STATISTICS.DENSITY Column을 통해 조회
- Cardinality : 집합의 크기를 의미, 집합에 속하는 원소의 수가 100개라면 {Cardinality ( 집합 ) = 100}과 같이 표현
Table t1의 전체 Row수가 10,000개라면 { Cardinality (t1) = 10000 }이 된다. 만일 where t1.c1 = 1 이라는 조건이 주어지고 해당 조건을
만족하는 Row수가 1,000개라면 { Cardinality (t1) = 1000 }이 된다. 만일 where t1.c1 = 1 and t1.c2 = 1이라는 조건이 주어지고 해당 조건을
만족하는 Row수가 500개라면 { Cardinality (t1) = 500 }이 된다.
정확하게 표현하면 { Adjusted Cardinality = Base Cardinality * Selectivity }의 공식을 따른다.
- Selectivity : “선택도”라 한다. CBO를 처음 접하는 사람에게 가장 난해한 개념들 중 하나라고 할 수 있다.
풀어 쓰면 특정 조건(Predicate)을 만족할 확률이라고 할 수 있다. Selectivity는 Density와 달리 고정된 값이 아니라 조건에 따라 바뀌는 값이다.
Column c1의 Density가 0.1이라고 하더라도 {c1 = 1}, {c1= :b1}, {c1 between 1 and 10}, {c1 between :b1 and :b2} 조건의 Selectivity는 모두 다르다.
Oracle은 몇 가지의 정형화된 Selectivity 계산 공식을 사용한다. 제5장에서 Selectivity 에 대해 자세하게 논의할 것이다.
- Histogram : ”분포도”이다. “막대 그래프”라고 번역하면 가장 정확할 것이다. Histogram을 수집하면
Column c1의 분포가 { n(‘A’) = 1000, n(‘B’) = 500, n(‘C’) = 200, n(‘D’) = 100, n(‘E’) = 1 }을 따른다는 것을 알 수 있다. Histogram은 Data가
비대칭적 (Skewed)일 때 그 분포를 알 수 있는 유일한 방법이다. 제6장에서Histogram에 대해 자세하게 소개할 것이다.
예제를 통해 용어의 의미를 정리해 보자.
아래와 같이 Column c1은 1 ~ 10,000까지의 값을 가지고,
Column c2는 ‘A’, ‘B’, ‘C’, ‘D’, ‘E’가 각각 {5000, 3000, 1000, 800, 200}건이 존재하는 Data를 생성
-- create objects
drop table t1 purge ;
create table t1(c1 int, c2 char(1));
insert into t1
select level ,
case
when level between 1 and 5000 then 'A'
when level between 5001 and 8000 then 'B'
when level between 8001 and 9000 then 'C'
when leve1 between 9001 and 9800 then 'D'
when level between 9801 and 10000 then 'E'
end
from dual
connect by level <= 10000;
commit;
통계 정보를 수집
Column c1은 Histogram이 없이, Column c2는 Bucket 크기가 5가 되게끔 Histogram을 생성
통계 정보 수집 후 통계 정보를 조회 해보면 다음과 같은 정보를 확인할 수 있다.
- Column c1 의 NDV = 10000, Density = 0.0001(1/10000)이다.
- Column c2 의 NDV = 5, Density = 0.00005이다. Column c2는 Histogram이 있기 때문에 Density = 1 / NDV의 공식을 따르지 않는다.
Histogram이 없을 경우의 Density가 1/5 = 0.2라는 것을 감안하면 매우 낮은 값으로 계산
- Column c2는 5개의 Bucket으로 이루어진 Histogram을 가지고 있다.
-- gather statistics
exec dbms_stats.gather_table_stats(user, ‘t1’, method_opt=>’for columns c1 size 1 c2 size 5’);
@tab_stat t1
-- table stats ( 직접 확인 )
-- column stats ( 직접 확인 )
-- histogram ( 직접 확인 )
Table t1은 전체 Row수가 10,000건(물리적인 Row수가 아니라 통계 정보에 있는 Row수를 의미함)이다. 따라서 Oracle은 다음과 같이 예측한다.
explain plan for
select * from t1
@plan
--- 결과 확인 ---
c1 = :b1이라는 조건이 주어지면 어떨까? Column c1의 Density는 0.0001이다. 따라서
c1 = :b1이라는 단일 조건이 주어진 경우에는 Selectivity = Density = 0.0001이 된다.
따라서 Cardinality = Base Cardinality(10000) * Selectivity(0.0001) 된다.
explain plan for
select * from t1
where c1 = :b1
@plan
--- 결과 확인 ---
c2 = :b1이라는 조건은 어떻게 처리되는가? Column c2의 Density는 0.00005이. 만일 Density가 Selectivity 산출
근거로 산출되었다면 Cardinality = Base Cardinality(10000) * Selectivity(0.00005) = 0.5 = 1이 된다.
하지만 아래 결과를 보면 Cardinality = 2000의 값을 보인다. 그 이유는 Selectivity = 1 / NDV = 1/5 = 0.2의 값으로 계산되었기 때문이다.
Histogram(Frequency Histogram)이 존재하는 경우에는 Selectivity 계산에 Density가 아닌 NDV 값이 사용된다.
explain plan for
select * from t1
where c2 = :b1 ;
@plan
--- 결과 확인 ---
Histogam이 존재하는 Column c2에 대해 c2 = ‘A’ 조건이 사용된 경우는 어떤가?
Histogram 덕분에 Data의 정확한 분포를 알고 있다. 따라서 c2 = ‘A’ 조건에 해당하는
Cardinality는 5000이라는 것도 알 수 있다. 결과는 이 사실을 잘 보여준다.
explain plan for
select * from t1
where c2 =’A’ ;
@plan
--- 결과 확인 ---
c1 = :b1 and c2 = :b2같은 복합적인 조건에 대해서는 제5장에서 자세하게 논의하게 된다.
다음과 같은 공식이 사용되었다는 사실만 기억하자.
Selectivity(p1 and p2) = Selectivity(p1) * Selectivity(p2)
Selectivity(c1=:b1) = 0.0001이다. Selectivity( c2=:b2) = 0.2이다.
Selectivity = 0.0001 * 0.2 = 0.00002가 된다.
Cardinality = 10000 * 0.0001 * 0.2 = 0.2 = 1이 된다.
explain plan for
select * from t1
where c1 = :b1 and c2 = :b2 ;
@plan
--- 결과 확인 ---
c2 like ‘%A%’와 같은 조건은 어떨까? 이런 복잡한 조건을 Oracle은 어떻게 처리할까? 답은 모른다. 모를 때는
상수(Constant, Magic Number)를 이용할 수 밖에 없다. 이 경우에는 Selectivity로 5%라는 고정된 값을 사용한다.
따라서 Cardinality = Base Cardinality(10000) * Selectivity(0.05) = 500 이 된다.
explain plan for
select * from t1
where c2 like '%A%' ;
@plan
--- 결과 확인 ---