비용기반의 오라클 원리 (2009년)
선택도 이슈 0 0 64,633

by 구루비스터디 선택도 [2018.09.27]


  1. 여러가지 데이터타입
  2. '0'으로 문자 앞쪽을 채울 때의 문제점(Leading zeros)
  3. 치명적인 문제를 유발하는 디폴트 값
  4. 이산(discrete)값의 위험성
  5. 놀라운 sysdate
  6. 함수의 특성
  7. 상관관계에 있는 컬럼
  8. 이행적 폐쇄
  9. 제약이 만든 조건
  10. 요약


  • 이 장에서는 표준 선택도가 부적절하게 계산되는 이유와, 이 문제를 피해가는 방법을 논의하고, 옵티마이저가 조건절을 잘못 처리하는 몇가지 기능을 논의한다.


여러가지 데이터타입

선택도 기본공식

  • 선택도 = (required range) / (column high value - column low value) + N / num_distinct


  • 예) 1부터 1000까지 서로 다른 1000개의 정수값을 가진 컬럼에 대해서 colX > 10 and colX <= 20 조건절을 이용하여 머리로 계산하면,
  • 1000개중에 10개 즉, 10/1000 공식으로 계산하면,
  • (20 - 10) / (1000 - 1)\+ 1/1000 = 10/99 + 1/1000 = 0.011


날짜값

  • 예) 2000-01-01 ~ 2004-12-31까지의 날짜만 가지는 컬럼에서 date_col between 2002-12-30 and 2003-01-05 조건절을 이용하여
  • 머리로 계산하면, 1827일중에 7일 즉, 7/1827
  • 공식으로 계산하면, (2003년 01월 05일 - 2002년 12월 30일) / (2004년 12월31일 - 2000년 1월 1일) + 2/1827 = 6/1826 \+2/1827
  • => 데이터에 distinct값이 많아질수록 계산결과의 오차는 작아진다. 그니까, distinct값이 적다면 주의하라고\!\!*


문자열값

  • 문자열을 숫자형태로 표현하고 있는 user_tab_histogram 뷰를 이용하여, 문자열의 전체 범위 측정에 대해서 의미를 살펴보자면,
  • 문자열 앞쪽의 6-7개을 제외한 나머지 글자는 옵티마이저가 사용하는 숫자 형태의 값에 아무런 영향을 주지 못하므로,
  • 다중 바이트 문자 세트를 선택하거나, 문자열이 길어지면, 문자열의 범위기반 쿼리에 정확도가 떨어져서 예측한 결과를 가져오지 못 할 수 있다.



Col   LOW                  LOW_VALUE                HIGH                 HIGH_VALUE
----- -------------------- ------------------------ -------------------- ------------------------
V10   Aardvark             416172647661726B         Zymurgy              5A796D75726779

C10   Aardvark             416172647661726B2020     Zymurgy              5A796D75726779202020


2 개의 행이 선택되었습니다.


PL/SQL 처리가 정상적으로 완료되었습니다.


Col   End no                                        End Value End act val
----- ------ ------------------------------------------------ --------------------------------------
C10        1  339,475,752,638,459,000,000,000,000,000,000,000
           2  339,779,832,781,209,000,000,000,000,000,000,000
           3  344,891,393,972,447,000,000,000,000,000,000,000
           4  469,769,561,047,943,000,000,000,000,000,000,000

V10        1  339,475,752,638,459,000,000,000,000,000,000,000
           2  339,779,832,781,057,000,000,000,000,000,000,000
           3  344,891,393,972,447,000,000,000,000,000,000,000
           4  469,769,561,047,943,000,000,000,000,000,000,000


8 개의 행이 선택되었습니다.

ASCII   256 to the power 15 - N
-----   =======================
65       337,499,295,804,763,795,854,482,261,399,306,240
97         1,967,393,731,554,212,031,122,883,374,743,552
114            9,032,010,526,626,134,485,664,010,338,304
100               30,948,500,982,134,506,872,478,105,600
118                  142,653,246,714,526,242,615,328,768
97                       458,069,548,838,355,585,728,512
114                        2,102,928,824,402,888,884,224
107                            7,710,162,562,058,289,152
32                                 9,007,199,254,740,992
32                                    35,184,372,088,832
0                                                      0
0                                                      0
0                                                      0
0                                                      0
0                                                      0

        Summed value
        ============
         339,475,752,638,459,043,065,991,628,037,554,176

PL/SQL 처리가 정상적으로 완료되었습니다.





부적절한 데이터 타입

  • 예) 2000-01-01 ~ 2004-12-31까지의 날짜만 가지는 컬럼에서 date_col between 2002-12-30 and 2003-01-05 조건절을 이용한다.
  • 단, 날짜를 저장할때, date, number, character 형식으로 각각 저장하고 카디널리티를 비교
  • date 값으로 저장한 경우, 비교적 정확한 카디널리티가 나오나, number나 character 형식은 부정확한 결과가 나온다.
  • (단, 버전이 올라가면서 많이 달라지겠지..)
  • 개선안) 히스토그램을 생성하여 잘못된 카디널리티를 보완하거나, 해당 컬럼에 function-based index를 생성하여 조정을 하는 방법을 사용할수도 있다.


'0'으로 문자 앞쪽을 채울 때의 문제점(Leading zeros)

  • 적절한 PK대신, 숫자의 앞쪽에 '0'을 붙여 문자열로 저장하여 데이터를 사용하는 경우, 범위기반 조건을 사용하는 경우 알수 없는 성능 문제가 나타난다.
  • 개선안) 히스토그램을 생성하여 잘못된 카디널리티를 보완하거나, 해당 컬럼에 function-based index를 생성하여 조정을 하는 방법을 사용할수도 있다.
  • 버전이 올라가면서, 위와 같은 경우는 옵티마이저가 알아서숫자컬럼을 다룰때 처럼 선택도를 계산하나, '0'대신'a'와 같은 문자열로 채워서 사용한다면,위와 같은 문제가 일어날 것이다.


치명적인 문제를 유발하는 디폴트 값

  • 날짜 입력시 NULL로 남겨두는 대신에4000-12-31등으로 세팅한다면, 선택도를 외곡시킬수 있다.
  • 예) 2001-01-01 ~ 2004-12-31치의 데이터에서 2003년도 데이터를 조회하고자 한다면,
  • 머리로 계산하면, 5년치 데이터 중에 1년치 데이터 이므로 1/5(20%)
  • 공식으로 계산하면, 4000년 12월 31일도 날짜의 범주로 생각하므로 (2003년 12월 31일 - 2003년 1월 1일) / (4000년 12월 31일 - 2000년 1월 1일) + 2/1828 = 0.00159 (약 0.15%)
  • 히스토그램을 통해 데이터 분포가 특이하다는 사실을 알려주어, 보정한다.


이산(discrete)값의 위험성

  • NULL을 대체할 Default값의 용도 이외에도, 범주에 벗어나는 특수한 경우를 저장하기 위해 범위에서 멀리 벗어난 값을 사용하는데 이도 같은 문제점을 발생시킨다.
  • 예를 들어, 주기를 저장하는 컬럼이 있다. 이는 일반적으로 1~12의 값을 저장한다.
  • 하지만 특수한 값을 표시하고자, 99라는 값을 추가적으로 사용했을 경우에 비효율적인 실행계획을 유발 할 수 있다.
  • 이것도, 히스토그램을 통해 데이터 분포가 특이하다는 사실을 알려주어, 보정한다.


놀라운 sysdate

  • sysdate를 이용한 조건절에서, 이상한 카디널리티가 잘못되어서 나온다.
  • (10g에서는 이런 문제점이 알려지고 해결되었다.)


함수의 특성

  • SQL에 가공한 컬럼을 조건절로 사용한다면, 옵티마이저는 바인드변수에 적용하는 고정된 백분율을 이용하여 계산한다.


표 6-6
조건절 예시처리방법
function(colx) = 'SMITH'고정된 1% 선택도
not function(colx) = 'SMITH'고정된 5% 선택도
function(colx) > 'SMITH'고정된 5% 선택도
not function(colx) > 'SMITH'고정된 5% 선택도
function(colx) >= 'SMITH' and function(colx) < 'SMITI'유도된 0.25% 선택도(5%*5%)
function(colx) between 'SMITHA' and 'SMITHZ'유도된 0.25% 선택도(5%*5%)
not function(colx) between 'SMITHA' and 'SMITHZ'유도된 9.75% 선택도 (5%+5%-(5%*5%))
function(colx) like 'SMITH%'고정된 5% 선택도
not fucntion(colx) like 'SMITH%'고정된 5% 선택도
function(colx) in ('SMITH', 'JONES')유도된 1.99%(1% + 1% - (1%*1%))


  • 만약,function-based index를 생성하면, 가상컬럼에 인덱스를 생성하는 것과 같고, 통계정보를 수집한다면, 동시에 가상 컬럼에 대해서도 통계정보가 수집된다.
  • 그러므로 이러한 경우는 위의 표와 무관하며 일반 선택도 계산식을 적용할 것이다.


상관관계에 있는 컬럼

  • 같은 테이블 내에서 상관관계에 있는 컬럼이 where절에서 함께 사용되면 항상 문제를 일으킨다.
  • 이는 경우에 따라 optimizer_dynamic)sampling 파라미터 또는 dynamic_sampling 힌트를 사용하여 해결할 수있는데, 이렇게 하면 주요 테이블에 대해 실행시점에 32개 블록을 샘플링함으로써 where 조건에 일치하는 로우의 비율을 할 수 있다.
  • 옵티마이저 프로파일 또는 옵티마이저 프로파일과 같은 역할을 하는 힌트를 사용하여 그러한 효과를 낼 수 있다.


이행적 폐쇄

  • 옵티마이저는 이행적 폐쇄(transitive closure)로 알려진 매커니즘을 사용하여, 사용자가 명시하지 않은 몇개의 조건을 생성한다.
  • 이는 유용하게, 때로는 뜻밖의 부장용을 나타내기도 한다.


제약이 만든 조건

(CASE 1) 컬럼에 함수를 적용하여, 인덱스를 사용하지 못하는 경우.


방법
  • 테이블 생성시 특정 컬럼에 제약조건을 추가하여 테이블 생성
  • 특정 컬럼에 일반 B-Tree 인덱스 생성
  • 쿼리를 수행할때, 인덱스를 생성한 컬럼에 대해서 UPPER()를 사용하여 조회
  • 실행계획 확인



Predicate: upper(v1) = upper('SMITH')


Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    34 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    34 |     2 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |     1 |
---------------------------------------------------------------------

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

   2 - access("V1"='SMITH')
       filter(UPPER("V1")='SMITH')

Note
-----
   - cpu costing is off (consider enabling it)


Predicate: upper(v1) = 'SMITH'


Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    34 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    34 |     2 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |     1 |
---------------------------------------------------------------------

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

   2 - access("V1"='SMITH')
       filter(UPPER("V1")='SMITH')

Note
-----
   - cpu costing is off (consider enabling it)


Predicate: upper(v1) = upper(:bind_var)
Uses index in 9.2, but not in 10.1.0.4


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  3400 |     7 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  3400 |     7 |
----------------------------------------------------------

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

   1 - filter(UPPER("V1")=UPPER(:BIND_VAR))

Note
-----
   - cpu costing is off (consider enabling it)


Predicate: upper(v1) = :bind_var
Uses index in 9.2, but not in 10.1.0.4


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  3400 |     7 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  3400 |     7 |
----------------------------------------------------------

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

   1 - filter(UPPER("V1")=:BIND_VAR)

Note
-----
   - cpu costing is off (consider enabling it)


결과
  • 옵티마이저는 테이블 생성시 추가한 제약조건을 쿼리에 적용한수 새로운 조건을 만들어낸다.
  • 그리하여, 원래는 컬럼에 함수를 적용하여 사용하지 못할뻔 한 인덱스를 사용할 수 있게 되는것이다.


예외
  • 단, Literal만 적용가능하며, 바인드 변수를 이용하면 무용지물이다.
  • 버전마다 계속 진화하므로 확인요망.


(CASE 2) NULL이 아닌 컬럼 값에 대해서 NULL값으로 리턴가능한 내장함수를 포함하는 CHECK 제약에 의해 잘못된 결과가 나타는 경우
  • 쿼리문에 IS NOT NULL을 명시적으로 포함해서 조회한다.



alter table t2 add constraint t2_ck_n1 check (n1 between 0 and 199);

explain plan for
select
	count(t1.v1)	ct_v1,
	count(t2.v1)	ct_v2
from 	t1, t2
where	t2.n2 = 15
and	t1.n2 = t2.n2 
and	t1.n1 = t2.n1 
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 906334482

------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   444 |    25 |
|   1 |  SORT AGGREGATE     |      |     1 |   444 |       |
|*  2 |   HASH JOIN         |      |    15 |  6660 |    25 |
|*  3 |    TABLE ACCESS FULL| T1   |    15 |  3330 |    12 |
|*  4 |    TABLE ACCESS FULL| T2   |    15 |  3330 |    12 |
------------------------------------------------------------

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

   2 - access("T1"."N2"="T2"."N2" AND "T1"."N1"="T2"."N1")
   3 - filter("T1"."N2"=15 AND "T1"."N1">=0 AND "T1"."N1"<=199)
   4 - filter("T2"."N2"=15)


결과
  • 더 정확한 조인 카디널리티를 계산할 수 있다.


요약

  • 옵티마이저가 카디널리티를 계산할 때 놀랄만한 동작을 하는데는 다 이유가 있다. 왜 그런 문제가 발생하는지 안다면, 문제를 현명히 해결할 수있다.
  • 옵티마이저는 문자열에 대한 range scan을 잘 처리하지 못한다.
  • 잘못된 데이터 타입에 데이터를 저장하면 문제가 발생한다.
  • null값 대신 특별한 값을 사용하여 저장한 컬럼에 범위기반 조건을 사용하면 부적절한 실행계획이 만들어진다.
  • function-based index를 정의하지 않은 컬럼에 함수를 적용하면 예상치 못한 선택도가 나타날수 있다.
  • 이행적 폐쇄(transitive closure)에 의해 조건이 만들어지거나 사라질수 있다.
  • 대부분, 히스토그램을 통하여 언급한 문제점들에 대해서 획기적인 개선을 확인할 수 있다. (히스토그램은 Chapter 7에서 설명...)
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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