Optimizing Oracle Optimizer (2009년)
Cardinality 0 0 51,663

by 구루비스터디 Histogram Cardinality [2018.07.14]


  1. Cardinality
    1. Frequency Histogram
    2. Height-Balanced Histogram


Cardinality

  • Histogram이 Cardinality의 계산에 매우 큰 영향을 주며, Histogram의 종류(Frequency/Height-Balanced)에 따라 미치는 영향이 다르다.
  • (아래의 Plan은 앞 단락의 "기본개념"편에서 수집한 Histogram을 기준으로 실행한 것이므로, 참고하여 보길 바람)


Frequency Histogram

  • 특정 값(Value)에 해당하는 빈도(Frequency)를 정확하게 알 수 있다.
  • Bind 변수를 사용한다면 Cardinality는 Density대신 1/NDV를 사용한다.
  • Bind 변수가 Range Predicate와 사용되는 경우 5% Rule 적용.
  • Bind 변수가 Between Predicate와 사용되는 경우 5% Rule 적용.
  • 존재하지 않은 값에 대해서는 0으로 계산(10.2.0.4버전부터는 Bucket의 최소빈도/2의 값을 사용)
  • Histogram에 존재하지 않은 값에 대한 Cardinality의 계산은 어떻게 해야할까? => Manual하게 Histogram생성(7장에서 알려줌)


1) Literal(c1=1)

SQL> explain plan for
  2  select * from t1
  3  where c1=1;

SQL> @plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 60000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 | 60000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C1"=1)

  • Cardinality 정확히 10000개로 예측!!!


2) Literal(c1=2)

SQL> explain plan for
  2  select * from t1
  3  where c1=2
  4  ;

SQL> @plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |  6000 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1000 |  6000 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C1"=2)

  • Cardinality 정확히 1000개로 예측!!!(얘도 Literal값이니까)


3) Bind 변수(c1=:b1)

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

SQL> @plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2222 | 13332 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2222 | 13332 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C1"=TO_NUMBER(:B1))

  • Bind변수에서는 Cardinality계산에 Density대신 1/NDV값을 사용. (11111*1/5 = 2222)


4) Bind 변수-Range Predicate (c1>:b1)

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

SQL> @plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2223 | 13338 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2223 | 13338 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C1">TO_NUMBER(:B1))


  • 책에선 5%의 Rule이 적용된다 했으나, 적용안됨. 어떤값을 근거로 위의 Row수를 산출한걸까..
  • 5% Rule이 적용되었을 경우, 11111*0.05=556 정도..


5) Bind변수-Range Predicate (c1>=:b1)

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

SQL> @plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2223 | 13338 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2223 | 13338 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C1">=TO_NUMBER(:B1))


  • 책에선 5%의 Rule이 적용된다 했으나, 적용안됨. 어떤값을 근거로 위의 Row수를 산출한걸까..
  • 5% Rule이 적용되었을 경우, 11111*0.05=556 정도..


6) Bind변수-Between Predicate(c1 between :b1 and :b2)

SQL> explain plan for
  2  select * from t1
  3  where c1 between :b1 and :b2;

SQL> @plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   445 |  2670 |     7  (15)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   445 |  2670 |     7  (15)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
   2 - filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))

  • 책에선 5%의 Rule이 적용된다 했으나, 적용안됨. 어떤값을 근거로 위의 Row수를 산출한걸까..
  • 5% Rule이 적용되었을 경우, 11111*0.05*0.05=28 정도..


7) 존재하지 않는 값 (c1=-1)

SQL> explain plan for
  2  select *
  3  from t1
  4  where c1 = -1;

SQL> @plan

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     6 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C1"=(-1))

  • 존재하지 않는 값에 대해서는 0으로 계산, 0대신 1로 사용(Oracle 10.2.0.4부터는 1이 아닌 Bucket의 최소빈도/2를 사용)


Height-Balanced Histogram

  • Popular Value에 대해서 Cardinality = Base Cardinality * (bucket count / bucket size)
  • Non Polpular Value에 대해서 Cardinality = Base Cardinality * Density (Bind변수를 사용한 경우도 동일)
  • Height-Balanced Histogram이 생성되면 Density가 1/NDV와는 전혀다른 값을 지닐수 있다. 그러므로 예상치 못한 실행계획의 변화가 생길수 있으므로 이점에 유의한다.
  • Bind 변수가 Range Predicate와 사용되는 경우 5% Rule 적용.
  • 존재하지 않은 값에 대해서는 최대/최소값을 기준으로 선형적으로 감소하는 방식으로 계산됨


1) Literal-Popular Value(c2=1)

explain plan for
select * from t1
where c2 = 1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4943 | 29658 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  4943 | 29658 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=1)

  • =>Cardinality는 Base Cardinality * (bucket Count / bucket Size)이므로, 11111*113/254=4943로 계산


2) Literal-Non Popular Value(c2=10)

explain plan for
select * from t1
where c2 = 10;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    24 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |    24 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=10)

  • Non Polular Value에 대해서 Cardinality는 Density를 이용하므로, 11111*0.00036=4로 계산


3) Bind 변수(c2=:b1)

explain plan for
select * from t1
where c2 = :b1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    37 |   222 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    37 |   222 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=TO_NUMBER(:B1))

  • 책에선 Bind 변수도 Non Popular Value의 Rule을 따른다 하였으나, 안따름.... 따랐으면 4


4) Bind 변수-Range Predicate (c2>:b1)

explain plan for
select * from t1
where c2 > :b1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   556 |  3336 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   556 |  3336 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2">TO_NUMBER(:B1))

  • =>5% Rule적용하여, 11111*0.05=556


5) Bind 변수-Range Predicate (c2>=:b1)

explain plan for
select * from t1
where c2 >= :b1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   556 |  3336 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   556 |  3336 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2">=TO_NUMBER(:B1))

  • =>5% Rule적용하여, 11111*0.05=556


6) Bind 변수-Between Predicate (c2 between :b1 and :b2)

explain plan for
select * from t1
where c2 between :b1 and :b2;

@plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    28 |   168 |     7  (15)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    28 |   168 |     7  (15)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
   2 - filter("C2">=TO_NUMBER(:B1) AND "C2"<=TO_NUMBER(:B2))

  • =>5% Rule적용하여, 11111*0.05*0.05=28


6) 존재하지 않는 값(c2=301)

explain plan for
select * from t1
where c2 = 301;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    24 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |    24 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=301)


  • => 존재하지 않는 값에 대하여 최대/최소 값을 기준으로 선형적으로 감소하는 방식으로 계산된다


8) 존재하지 않는 값(c2=400)

explain plan for
select * from t1
where c2 = 400;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    18 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |    18 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=400)


  • => 존재하지 않는 값에 대하여 최대/최소 값을 기준으로 선형적으로 감소하는 방식으로 계산된다


8) 존재하지 않는 값(c2=599)

explain plan for
select * from t1
where c2 = 599;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     6 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=599)

  • => 존재하지 않는 값에 대하여 최대/최소 값을 기준으로 선형적으로 감소하는 방식으로 계산된다


최대/최소 값을 기준으로 선형적으로 감소하는 방식
  • 예) C2 컬럼
  • 최대-최소구간 : (1~300)
  • 위의 Frequency : 4515
  • (-300~0) (301~600)구간의 Frequncy는 선형적으로 감소한다고 가정, 감소크기는 4515/300=15 즉, 15씩 감소한다고 가정한다.
  • (-300~0) (301~600) 구간을 벗어나는 값은 동일하게 Cardinality 1로 사용.
  • => 내가 수행한 결과로는 Density가 아주 작은 값으로 나와(0.00036), Frequency가 4로 나와서, 가시적으로 책과 같이 나오지는 않았음.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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