태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'CBO'에 해당되는 글 2건

  1. 2008.05.16 Date vs. Number(or Varchar2) - Who wins? (5)
  2. 2008.01.24 Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback

Date vs. Number(or Varchar2) - Who wins?

오라클 2008.05.16 16:31
날짜를 표현하는 데이터 타입으로 Date를 사용해야 하는가? 혹은 Number나 Varchar2를 사용해야하는가?라는 해묵은 논쟁이 있다.

"너무나 간단하지 않은가?"라고 반문하겠지만, 의의로 적용되는 상황을 보면 그리 쉬운 질문은 아닌거 같다. 여러가지 판단 기준이 있겠지만, 가장 중요한 기준은 이것이어야 한다.

"어느 쪽이 CBO가 더 이해하기 쉬운가?"

이 기준으로 생각하면 선택은 언제나 Date 타입이어야 한다. 거의 반론의 여지가 없다고 본다. 간단한 테스트를 통해 확인해보자.

drop table t1 purge;
create table t1 (
  c_date      date,
  c_char      varchar2(8),
  c_numb      number
);

-- 1,000일간의 데이터를 생성한다.
-- 동일한 데이터를 Date, Varchar2, Number 타입으로 생성한다.
insert into t1
select
  sysdate - level,
  to_char(sysdate-level, 'yyyymmdd'),
  to_number(to_char(sysdate-level,'yyyymmdd'))
from
  dual
connect by level <= 1000
;

-- Date와 Number 기준으로 2007년 10월 1일 ~ 2008년 5월 1일까지의 범위를 구한다.
with v as (
select
  max(c_date) as max_date, min(c_date) as min_date,
  max(c_char) as max_char, min(c_char) as min_char,
  max(c_numb) as max_numb, min(c_numb) as min_numb,
  count(*) as total_rows
from
  t1
)
select
  total_rows *
    (to_date('20080501','yyyymmdd')-to_date('20071001','yyyymmdd'))
      /(max_date - min_date) as diff_d,
  total_rows * (20080501-20071001) / (max_numb-min_numb) as diff_n
from
  v
;

    DIFF_D     DIFF_N
---------- ----------
213.213213 319.919178   <-- 날짜는 213일이지만, 숫자값으로는 320만큼 차이가 난다.


이제 다음과 같이 각 데이터 타입별로 2007년 10월 1일 ~ 2008년 5월 1일까지의 날수를 누가 더 정확하게 표현하는지 확인해본다.

-- Date 타입
explain plan for

select *
from t1
where
  c_date between to_date('20071001','yyyymmdd') and
                to_date('20080501', 'yyyymmdd')
;

@plan

-- Varchar2 타입
explain plan for

select *
from t1
where
  c_char between '20071001' and
                '20080501'
;

@plan

-- Number 타입
explain plan for

select *
from t1
where
  c_numb between 20071001 and
                20080501
;

@plan


결과는 다음과 같다(Cardinality의 차이에 주의하자)

-- Date 타입인 경우
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   215 |  4945 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   215 |  4945 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- Varchar2 타입인 경우
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   321 |  7383 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   321 |  7383 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- Number 타입인 경우
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   321 |  7383 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   321 |  7383 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------



위의 결과를 보면 오직 Date 타입을 사용한 경우에만 실제 데이터 분포를 합리적으로 해석하는 것을 알 수 있다. 이유는 간단하다. CBO가 "날짜"를 "날짜"로 해석할 수 있기 때문이다.
(Oracle이 예측한 Cardinality가 우리가 Min/Max를 이용해 구한 값과 거의 일치하는 것에 유의하자)

반면 Number나 Vachar2 타입을 사용한 경우에는 "날짜"로 해석할수 없기 때문에 단순히 범위만을 고려한 값을 예측하게 된다. 이런 차이들이 실행 계획의 합리성을 결정하기 때문에 그 효과는 하늘과 땅 차이라 하겠다.

하지만, 여기서 다시 한번 이런 복잡한 질문을 던질 수 있다.

"지난 몇 년 동안 날짜를 표현하는데 Varchar2나 Number를 사용해왔지만, 아무런 문제도 없었다. 이것은 어떻게 해석할 것인가?"


답변은 운이 좋았거나, Bind 변수를 사용했기 때문이라는 것이다.

Oracle은 다음과 같이 Between Range 조건을 Bind 변수로 사용한 경우에는 0.25%라는 고정된 값을 Selectivity(선택도)로 사용한다.
(이것은 단일 Range 조건이 5%의 선택도를 사용하기 때문이다. Between Range 조건(And 조건의 일종)은 5%*5% = 0.25%가 된다)

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

@plan

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


전체 Row수가 1,000개 이므로 1,000*0.0025 = 2.5 = 3이 되는 것을 알 수 있다.

아마 예외없이 Bind 변수를 사용함으로써 항상 고정된 Cardinality가 나왔을 것이고, 이로 인해 잘못된 데이터 타입으로 인한 부작용을 확인할 길이 없었을 것이다.

Range Predicate는 Histogram과 더불어 CBO에게 가장 골치아픈 문제 중 하나이다. 오라클을 오라클답게 사용하려면 선택의 기준을 항상 "CBO에게" 맞추는 습관이 필요할 것이다.




신고
Trackback 0 : Comments 5
  1. 김영우 2008.05.16 19:31 신고 Modify/Delete Reply

    안녕하세요, 좋은 글 잘 봤습니다.
    저도 평소에 많이 생각했던 문제였는데 정리를 잘 해주셨네요.

    저의 개인적인 생각은... 'Data'에 적용가능한 "Database DataType'이 이미 해당 DBMS에 존재한다면 그 타입을 사용해야 한다.' 입니다.

    날짜를 integer나 varchar 타입으로 '묵시적'으로 '표현'은 가능하겠지만 DBMS 입장에서 보자면 우리가 사용하는 '표현'의 실제 도메인을 이해하지 못하겠지요.

    날짜가 Date 타입인 경우 얻는 '데이터 정합'에 대한 보장은 varchar나 integer로 날짜를 '표현'한 경우에는 얻지 못하는 DBMS의 기본 기능이 아닐까 생각합니다.

  2. 임재승 2008.05.27 09:33 신고 Modify/Delete Reply

    안녕하세요?
    오라클 성능 관련 부분 보다가 우연히 찾게되었는데 지금껏 오라클을 너무 모르고 사용해온걸 새삼 느낍니다.

    기존에 회사에서 개발하던 오라클에 붙는 어플리케이션에서 날짜값을 전부 VARCHAR2로 사용해왔었는데
    그게 잘못되었단 점을 깨닫게 되었네요

    아직 DBMS를 다룬지 6개월정도밖에 안되서 지식이 거의 전무한 상태인데 종종 들러서 조동욱님이 쓰신 글
    읽어 보면서 공부해야겠네요...저희 회사에 DBMS에 대해 잘 알고 있는 사람들이 없기도 해서
    교육말고는 따로 배울 곳 이 없었는데 정말 좋네요 ^^

  3. 욱짜 2008.05.27 11:34 신고 Modify/Delete Reply

    도움이 되었다니 다행입니다. 혹시나 해서 첨부하면, Varchar2를 쓰는게 나쁘다는 의미는 아닙니다. 함정이 있다는 것, 그리고 그 함정을 피할 수 있으면 된다고 봅니다. 물론 함정에 안빠지는게 제일 좋겠지만요. ^^

  4. oracler 2008.06.01 18:47 신고 Modify/Delete Reply

    date type 에 대하여 아래와 같이 테스트하셨는데

    between to_date('20071001','yyyymmdd') and
    to_date('20080501', 'yyyymmdd')

    이렇게 코딩하면
    범위 뒷부분(두번째 변수) 에 주어진 마지막 날짜의 데이타는
    0시 정각 데이타외에는 아마 검색이 안될건데요. 한번 테스트해보세요.(10g)

    앞부분 데이타가 안나오는것이 아니라 뒷부분 마지막 데이타가 안나오는것이므로
    깜빡 속아넘어가기 쉽죠.

    date 필드는 대부분 시분초 데이타가 섞여 있지만
    또 대부분 일자 부분만 검색하므로 date 필드에 대한 검색은
    필드 속성과 현업업무간의 괴리때문에 태생적으로 이런 함정이 있을수 밖에 없습니다.

  5. 욱짜 2008.06.02 08:57 신고 Modify/Delete Reply

    Date Type은 사용시에 주의할점이 많죠. 아마 그런 이유때문에 Varchar나 Number타입으로 많이 사용하는 거 같습니다. 장단점을 정확하게 알고, 비정상적인 행동을 보이는 것을 제어만 할 수 있다면 어떤 쪽이든 합리적이라는게 제 생각입니다.

Write a comment


Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback

오라클 2008.01.24 20:10
Wolfgang Bretiling이라는 스위스를 기반으로 활동하는 Consultant가 있다. 이 사람의 전문 분야는 Oracle + PeopleSoft의 튜닝이다.

PeopleSoft과 같은 ERP 솔루션들은 Oracle과 무관하게, 아니 Oracle을 무시해서 설계된 Application으로 유명하다. 모든 DBMS에서 일관되게 동작 가능한 Application에 의한 필요악이라고나 할까... 이런 3rd Party Application의 튜닝에서 가장 큰 애로 사항이 쿼리를 직접 수정하는 것이 불가능하다는 것이다. 때문에 간혹 Oracle 관점에서의 튜닝이 불가능한 것으로 간주하기도 한다.

이 Wolfgang이라는 사람은 이런 상황에 착안해서 자신만의 쿼리 튜닝 기법을 개발했다. 하지만 쿼리 수정이 전혀 불가능한 상태에서 어떻게 (비교적) 자유롭게 쿼리를 튜닝할 수 있단 말인가?
놀랍게도 이 사람은 많은 시스템에서의 경험과 Oracle Optimizer에 대한 심도 깊은 지식을 기반으로 이것을 가능하게 하는 단순하면서도 심오한 방법을 체계화했다.

그 이름이 바로 TCF - Tuning by Cardinality Feedback이다. 풀어쓰면 "실행 예상 계획과 실제 실행 계획의 차이(Cardinality Feeback)에 의한 쿼리 튜닝 기법"이다. TCF는 다음과 같은 가정에 근거한다.

  • Oracle의 CBO는 Cardinality만 정확하면 나름대로 최적의 실행 계획을 생성한다.
  • Oracle의 통계 해석에서 오는 몇 가지 오류로 인해 Cardinality 계산이 비현실적인 경우가 있다.
  • 따라서, Oracle이 Cardinality를 잘 계산할 수 있도록 힌트를 주면 많은 경우 정상적인 실행 계획을 만들 것이다.


이런 가정 하에서 DBMS_STATS.SET_XXX 류의 메소드를 이용해서 Oracle이 최적의 Cardinality를 계산할 수 있도록 통계 값을 보정시켜주는 것이 이 TCF의 핵심이다. Oracle이 필요로 하는 Cardinality를 추론하게 해주는 것이 Explain Plan(실행 예상 계획)과 Execution Plan(실제 실행 계획)을 비교하고 해석하는 능력이다.

마침 Oracle 10g부터는 gather_plan_statistics 힌트나 statistics_level = all 과 함께 dbms_xplan.display_cursor 함수를 사용하면 TCF 적용에 필요한 데이터(실행 예상 로우 건수와 실제 실행 로우 건수)를 손쉽게 구할 수 있으니 이 Wolfgang의 TCF의 뛰어난 점을 Oracle이 수용한 것처럼 생각될 정도이다.

아래 URL에서 TCF 사용에 필요한 모든 이론적 도구를 얻을 수 있다.
http://www.centrexcc.com/papers.html

쿼리를 직접 수정할 수 없는 3rd party Application 튜닝에 최적의 도구를 하나 확보하게 된 셈이다.

PS) 이 TCF는 실제로 많은 엔지니어들이 자신도 인식하지 못하고 사용하고 있는 방법이다. 이런 것을 체계화하고 이론적 무장을 갖추는 능력이 부족한 우리 현실이 아쉬울 뿐이다.
신고
Trackbacks 7 : Comment 0

Write a comment

티스토리 툴바