Optimizing Oracle Optimizer (2009년)
목록
기본적인 용어와 예제 0 0 96,172

by 구루비스터디 CBO Optimizer [2018.07.14]

NDV
  • Number of Distinct Value의 약자이다. 말 그래도 유일하게 구별되는 Column값의 개수이다.
  • 만일 Column c1이 {'A', 'B', 'C', 'D', 'E'} 다섯 가지 종류의 값으로 이루어져 있다면 NDV(C1) = 5가 된다.
  • 각 값이 차지하는 본포는 고려대상이 아니다. 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값이 계산 된다.
  • Object Statistics 수집 시 계산되며, DBA_TAB_COL_STATISTICS.DENSITY Column을 통해 조회 가능


Cardinality
  • Cardinality는 집합의 크기를 의미한다.
  • 즉 집합에 속하는 원소의 수가 100개라면 { Cardinality(집합)=100 } 과 같이 표현된다.
  • Tabel t1의 전체 Row수가 10,000개 라면 {Cardinality(t1) = 10000}이 된다.
  • 만일, Where t1.c1=1이라는 조건이 주어지고 해당 조건을 만족하는 Row수가 1,000개라면 {Cardinality(t1) = 1000}이 된다.
  • {Adjusted Cardinatliy = Base Cardinality * Selectivity}의 공식을 따른다.


Selectivity
  • "선태도"이다. 특정 조건(Predicate)을 만족할 확율이다. Selectivity는 Density와 달리 고정된 값이 아니라 조건에 따라 바뀌는 값이다.
  • 만일, Column c1의 Density가 0.1이라고 하더라도 {c1 = 1 }, {c1 = :b1}, {c1 between 1 and 10 }, {c1 etween :b1 and :b2 } 조건의 Selectivity는 모두 다르다.


Histogram
  • Histogram은 분포도라고 할 수 있다.
  • 가령, Histogram을 수집하면 Column c1의 분포가 { n('A') = 1000, n('B') = 500, n('C')-=200, n('D')=100, n('E') = 1}을 따른다는 것을 알 수 있다.
  • Histogram은 Data가 비대칭적일 때 그 분포를 알 수 있는 유일한 방법이다.




-- create objects
SQL>drop table t1 purge;
Table dropped.

SQL>create table t1(c1 int, c2 char(1));
Table created.

SQL>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 level between 9001 and 9800 then 'D'
    when level between 9801 and 10000 then 'E'
   end
from dual
connect by level <= 10000
10000 rows created.

SQL>commit;

--통계 정보 수집 (Column c1은 Histogra이 없이, Columnc c2는 Bucket크기가 5가 되게끔 Histogram을 생성)
SQL>exec dbms_stats.gather_table_stats('ghlee', 't1', method_opt=>'FOR COLUMNS C2 T');

SQL>select table_name, num_rows, blocks, sample_size, last_analyzed from user_tables;
TABLE   NUM_ROWS     BLOCKS SAMPLE_SIZE LAST_ANALYZED
----- ---------- ---------- ----------- -------------------
T1         10000         20       10000 2009-02-14 02:18:50

SQL>select table_name, column_name, num_distinct, num_nulls, density
  2  from user_tab_columns
  3  where table_name = 'T1';

TABLE COLUM NUM_DISTINCT  NUM_NULLS    DENSITY
----- ----- ------------ ---------- ----------
T1    C1           10000          0      .0001
T1    C2               5          0     .00005

- Column c1의 NDV = 10000, Density = 0.0001(1/10000)
- Column c2의 NDV = 5, Density = 0.00005이다. Column c2는 Histogram이 있기 때문에 Density = 1/NDV의 공식을 따르지 않는다.
- istogram이 없을 경우의 Density가 1/5 = 0.2라는 것을 감안하면 매우 낮은 값으로 게산되었다는 것을 알 수 있다.

SQL>select table_name, column_name, endpoint_number, endpoint_value
  2  from user_tab_histograms
  3  where table_name = 'T1'
  4  order by 3
  5  /

TABLE COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
----- ----- --------------- --------------
T1    C1                  0              1
T1    C1                  1          10000
T1    C2               5000     3.3815E+35
T1    C2               8000     3.4334E+35
T1    C2               9000     3.4854E+35
T1    C2               9800     3.5373E+35
T1    C2              10000     3.5892E+35

** Column c2는 5개의 Bucket으로 이루어진 Histogram을 가지고 있다.
 

SQL>explain plan for
  2  select * from t1;
Explained.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 50000 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 50000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
- Table t1은 전체 Row수가 10,000건(물리적인 Row수가 아니라 통계 정보에 있는 Rwo수를 의미)
- c1=:b1이라는 조건이 주어지면 Column c1의 Density는 0.0001이다. 따라서 c1=:b1이라는 단일 조건이 주어진 경우네는 Selectivity = Density 즉, 0.0001이 된다.
  따라서 Cardinality = Base Cardinality (10000) * Selectivity (0.001) =1
 


SQL>variable b1 number;

SQL>explain plan for
  2  select * from t1
  3  where c1 = :b1;
Explained.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     5 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"=TO_NUMBER(:B1))
13 rows selected.
 
- 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이 존재하는 경우네는 Selectivity계산에 Density가 아닌 NDV값이 사용된다.

SQL >explain plan for
  2  select * from t1
  3  where c2 = 'A';
Explained.


SQL >select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 | 25000 |     8  (13)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 | 25000 |     8  (13)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"='A')
13 rows selected.

- Histogram이 존재하는 C2에 대해 Data의 정확한 분포도를 알고 있다.  따라서 c2 = 'A'조건에 해당하는 Cardinality = 5000이라는 것도 알 수 있다.
 
SQL>variable a1 number;
SQL>variable b2 varchar2(20);
SQL>explain plan for
  2  select * from t1
  3  where c1 = :b1 and c2 = :b2;
Explained.

SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     5 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=:B2 AND "C1"=TO_NUMBER(:B1))
13 rows selected.

- Selectivity(p1 and p2) = Selectivity(p1) * Selectivity(p2)공식을 이용한다.
  Selectivity(c1 = :b1) = 0.001이고, Selectivity(c2 = :b2) = 0.2가 된다. Selectivity = 0.001 * 0.2* 10000 = 0.2 = 1이 된다.
 
SQL>explain plan for
  2  select * from t1
  3  where c2 like '%A%';
Explained.
SQL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |  2500 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   500 |  2500 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2" LIKE '%A%')

13 rows selected.
  • c2 like '%A%와 같은 조건의 처리는 알 수 없다. 모를 때는 상수(Constant, Magic Number)를 이용할 수 밖에 없다.
  • 이 경우에는 Selectivity로 5%라는 고정된 값을 사용한다.
  • 따라서 Cardinality = Base Cardinality(10000) * Selectivity(0.05) = 500이 된다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3848

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입