필자는 가끔 분석함수의 기능에 관해 질문을 받는다. 그때마다 대답을 하지 않고 대신에 질문에 부합하는 Analytic SQL을 보여주고 결과를 분석하게 한다. 바로 답을 주게 되면 개발자가 의존적이 되고 분석함수 각각의 기능도 금방 잊어버리기 때문이다.

 

개발자만 모르는 것이 아니다

얼마 전에 어느 DBA로 부터 요청이 왔다. 자신을 포함한 개발자들이 분석함수를 어려워하니 블로그에 분석함수의 모든 것을 정리한 문서를 올려달라는 것이었다. 물론 오라클 매뉴얼이나 Tomas Kyte Expert One on One 등의 책에 이 함수들의 기능이 있지만 페이지 수가 너무 많고 영문이라는 단점이 있다는 것이었다. 이것은 놀라운 일이다. 개발자뿐 아니라 DBA, 심지어 컨설턴트까지 Analytic Function에 관해 필자에게 질문을 던지곤 한다. Oracle8i 부터 기능이 구현되었으니 기간으로 따지자면 10년 이상 존재했던 함수인데 아직까지......

 

도대체 Analytic Function이 얼마나 어려우면 전문가 까지도 개념이 서지 않는 걸까? 고민 끝에 핵심만 정리한 문서를 올리기로 했다.

 

핵심은 3가지다

분석함수의 기능이 복잡한 것 같지만 사실은 3가지만 알면 90%를 이해한 것이다.

1) Over 절에서 사용하는 Order by의 기능

2) Over 절에서 사용하는 Partition by의 기능

3) Over 절에서 사용하는 Windowing 기능

 

이것이 90% 이다. 대부분의 개발자와 DBA들은 1)번과 2)번에 대해서 많이 알고 있지만 이상하게도 3)번에 대해서 개념이 서질 않는다고 하였다. 따라서 아래의 문서를 다운받아서 공부할 때 3)번을 집중적으로 보기 바란다.

 

그럼 나머지 10%?

나머지는 아래와 같다. 위의 3가지를 안다면 아래의 함수들은 쉽게 이해할 수 있다. 그저 종류가 많을 뿐이다.

 

      RANK, DENSE_RANK, and ROW_NUMBER --> 3가지 함수의 차이점

      FIRST/LAST

      NTILE, WIDTH_BUCKET, CUME_DIST and PERCENT_RANK

      Hypothetical Functions

      FIRST_VALUE/LAST_VALUE , LAG/LEAD

      Reporting Functions/RATIO_TO_REPORT

      Handling null

 

빨강색 부분은 개발자들이 많이 질문하는 것들이다. 참고하기 바란다.

 

Paper라고 다 같은 것은 아니다

매뉴얼이나 관련서적의 문제점은 페이지 수가 많다는 것이다. 예를 들어 분석함수 부분이 60페이지가 넘어간다면 기능을 익히는데 며칠 혹은 몇 주가 걸릴 수 있다. 필자는 페이지 수가 많은 것을 아주 싫어한다. 아래의 문서는 앞쪽의 목차와 중요성, 그리고 뒤쪽의 마무리 부분을 제외하면 9(18 페이지)으로 모든 기능과 개념을 설명하였다. 아마 한 두 시간 이내에 다 볼 수 있을 것이다.

 

Mastering Oracle Analytic Function.pdf

Mastering Oracle Analytic Function ppt 파일



PS

분석함수를 문법이라고 치부해 버리는 사람들이 있다. 그렇지 않다. 많은 경우에 분석함수를 쓰는 것이 SQL 튜닝이 된다. 오죽하면 옵티마이져가 평범한 SQL을 분석함수를 사용하는 것으로 바꾸겠는가?
이제부터 필자에게 분석함수를 질문하는 개발자가 없기를 바란다. ^^


신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2010.03.29 09:38 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요? ^^
    이번에도 좋은 포스팅을 올리셨군요. 감사합니다.

    제 경우도 개발할때 분석함수를 사용해서 유용하게 써먹었었고
    튜닝으로 넘어와서도 분석함수는 여전히 성능을 향상시키는데 아주 좋은 기능으로 자리잡고 있습니다.

    예를들어, GROUP BY -> MAX를 사용하는 인라인 뷰 또는 스칼라 서브쿼리가 PUSH_PRED가 안되서 성능이 안좋아진 경우를 분석함수를 사용하여 PUSH_PRED가 되도록 하는 경우를 들 수 있죠...

    분석함수에 대해서 이런 비교도 재밌는 포스팅이 될 것 같은데요.
    즉, 그룹함수와 분석함수에 대한 비교죠. 이런 그룹함수는 분석함수로 변경이 가능한 Case... 또는 불가능한 Case에 대해서요 ^^

    항상 좋은 내용을 공유해 주셔서 감사드립니다.

    ps. 책은 출판됐나요?

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.03.29 17:30 신고  댓글주소  수정/삭제

      좋은 주제인것 같습니다.
      책은 메켄토시로 변환하는 과정에서 조금 지체가 되었습니다. 그리고 칠레의 지진 때문에 인쇄용 종이 수입이 중단되어 2~3주 정도 늦춰진다고 하는군요. 조금만 기다려 주시기 바랍니다.

  2. 혈기린 2010.03.29 09:44 신고  댓글주소  수정/삭제  댓글쓰기

    오라클분석함수의 정수가 모인 자료네요 감사합니다~~ 테스트 스크립트까지 ^^
    책 너무 기다리고 있습니다 ㅎㅎ

  3. feelie 2010.03.29 10:29 신고  댓글주소  수정/삭제  댓글쓰기

    오늘도 간지러운곳을 글거주시네요..

  4. Favicon of http://precursor.tistory.com BlogIcon 철인3호 2010.03.30 11:44 신고  댓글주소  수정/삭제  댓글쓰기

    아.. 정말 대단하십니다... 그리고 감사합니다.

  5. 김시연 2010.04.01 10:24 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요~! 출간하실 책명이 "The Logical Optimizer" 군요. 좋은 글 잘 읽고 갑니다~!

  6. 2010.04.01 16:12  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  7. baind 2010.04.02 14:22 신고  댓글주소  수정/삭제  댓글쓰기

    오늘도 즐겁게 공부합니다~
    감사드려요~^^ ㅎ_ㅎ
    매번 좋은 글에 진심으로 감사드립니다~

    꼬리 : 오동규님 책 기다리다가 제 목 2Cm늘어났어요~~

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.04.02 15:17 신고  댓글주소  수정/삭제

      너무 걱정하지 마세요.
      내일이 인쇄용 필름이 마감되는 날 입니다. 책은
      15일 정도에 인쇄가 완료될 예정이고 20일 정도에 교보문고나 yes 24 등에서 주문이 가능할 겁니다.
      감사합니다.

  8. 서상서 2010.08.03 23:34 신고  댓글주소  수정/삭제  댓글쓰기

    분석함수 올리신 쿼리중에 KEEP이라는 명령어를 쓰셨는데...오라클 메뉴얼을 찾아보아도 나오지를 않네요.
    그리고 SQL Rerence에 봐도 나와있지 않네요.

    무슨 역할을 하는 놈인가요?

    정말 많은 채찍질을 하게 하는 좋은 글들을 많이 올려주셔서 감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.08.04 12:54 신고  댓글주소  수정/삭제

      분석함수는 OLTP에서도 사용하지만 태생은 DW용이기 때문에 Oracle 10g Data Warehausing Guide의 21장에 자세히 설명되어 있습니다.

      그리고 KEEP의 용도는 분석함수의 OVER와 같다고 생각하시면 됩니다. 다만 FIRST/LAST 함수에서만 사용한다는 것이 다르긴 합니다.

      예를들면 부서별로 급여를 가장 많이 받는 직원의 LIST를 뽑는다면 한부서에 두명 이상이 나올 수 있겠죠. 즉 부서별 최고연봉자는 한명이 아니라 두명(김연아와 박태환이 둘 다 1억원을 받음)이 라고 가정하면
      MIN 값을 적용하면 김연아가 나올것이고 MAX를 적용하면 박태환이 나올것 입니다.

      PDF파일의 FIRST/LAST 예제를 직접 실행하시고 결과를 분석하시면 아실 것 입니다.

  9. 김봉호 2011.08.30 10:45 신고  댓글주소  수정/삭제  댓글쓰기

    검색으로도 여기 들어올 줄이야 ^^;;

  10. Favicon of http://www.abercrombiefitch-saleuk.org.uk BlogIcon abercrombie and fitch uk 2011.09.26 19:30 신고  댓글주소  수정/삭제  댓글쓰기

    예를들면 부서별로 급여를 가장 많이 받는 직원의 LIST를 뽑는다면 한부서에 두명 이상이 나올 수 있겠죠. 즉 부서별 최고연봉자는 한명이 아니라 두명(김연아와 박태환이 둘 다 1억원을 받음)이 라고 가정하면
    MIN 값을 적용하면 김연아가 나올것이고 MAX를 적용하면 박태환이 나올것 입니다.

  11. Favicon of http://www.cheapburberryshop.org BlogIcon burberry outlet 2011.09.26 19:30 신고  댓글주소  수정/삭제  댓글쓰기

    PDF파일의 FIRST/LAST 예제를 직접 실행하시고 결과를 분석하시면 아실 것 입니다.

  12. Favicon of http://www.perfectreplicawatches.org.uk/ BlogIcon replica watches uk 2011.10.13 13:30 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽고 갑니다~!

지난번에 Range 파티션에서 maxvalue 진정한 의미 라는 글에서 Multi-Column으로 Range 파티션을 구성할 때 주의사항에 대하여 알아 보았다. 이 글을 쉽게 이해하려면 위의 글을 먼저 보기 바란다. 테스트용 스크립트도 위의 글에서 사용한 것을 그대로 사용한다.

 

RAC4 Node로 구성되어있는 환경에서 동일한 SQL이 모든 Instance에서 골고루 수행될 때 1 Instance 만 유독 느리다면 무엇을 의심해야 할까? 네트워크 등의 문제일 수 있지만 가장 먼저 조사해야 할 것은 gc_current_grant_busy 이벤트가 발생하느냐 이다.

테스트 환경을 만들어 보자.

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

PARTITION pt_2_3 VALUES LESS THAN ('2', 5),

  ...중간생략

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

 

Table created.

 

---> 여기서 이전 글에서 사용했던 Insert 문과 dbms_stats.gather_table_stats 수행

 

 

상황 : 아래의 SQL 2개가 모든 Instance에서 동시에 여러 번 수행된다.

 

SELECT COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';           --> 3번 파티션

 

UPDATE T

   SET pad = LPAD('A', 4000, 'B')

 WHERE DAY_NUM = '4'          --> 4번 파티션

   AND INST_ID = :V_INST_ID;      --> 현재 수행되고 있는 Instance 번호 대입

 

이 상황에서 1 Instance Update문만 유독 느리게 수행된다. 아래는 개발자와 필자의 대화내용이다.

 

개발자 : Update문의 Bind 변수에 1번만 넣으면 느린가요?


필자    : 1 Instance에서 Update 하려면  다른 Instance에서 Exclusive Mode의 Lock 권한을 받아야 하기 때문으로 추측됩니다.


개발자 : 권한이라뇨?


필자    : SELECT 시에 DAY_NUM 4번에 해당하는 파티션을 5번 이상 Access 했기 때문에 권한이 다른 INSTANCE로 넘어간 것 같습니다. 이 현상을 FDC(Fairness Down Convert) 라고 합니다. FDC가 발생한 후에 DAY_NUM 4번에 해당하는 첫번째 파티션(pt_4_1)의 해당 블록에 UPDATE문을 수행하려면 권한을 받는 작업(gc_current_grant_busy 이벤트)이 필요합니다.


개발자 : 그럴 리가요? Update 문은 DAY_NUM = '4' 조건이고 Select 문은 DAY_NUM = '3' 조건이므로 서로 다른 파티션 입니다. 따라서 SELECT 문과 UPDATE문이 동일 파티션을 Access 할 이유가 없습니다.


필자   : SELECT 문이 실제로는 DAY_NUM = '4' 의 첫번째 파티션을 항상 Access 합니다. MAXVALUE를 지정하지 않았으므로 그런 것 입니다.


개발자 :  그렇군요. 어쩐지 tracegc_current_grant_busy가 많이 보였습니다.

 

아래는 개발자가 제시한 Trace 내용 중 Wait Event 부분을 발췌한 것이다.

 

core1_ora_13638.trc

-----------------------------

WAIT #11: nam='gc current grant busy' ela= 947 p1=28 p2=1672046 p3=33619969 obj#=12043270 tim=12372374088207

WAIT #11: nam='gc current grant busy' ela= 992 p1=29 p2=2310876 p3=33619969 obj#=12070599 tim=12372374089432

...중간생략

WAIT #11: nam='gc current grant busy' ela= 767 p1=28 p2=1673090 p3=33619969 obj#=12043272 tim=12372374096882

 


Fairness Down Convert란 무엇인가?
Exclusive mode의 lock이 Shared lock 모드로 Down Convert 된다는 뜻이다. 다른  Instance의 요청에 의해서 Exclusive mode의 lock 상태에서 블럭을 다른 INSTANCE로 전송하는 작업은 무거운 연산이므로 특정 횟수 이상 블럭을 요청할 경우 Shared lock 모드로 전환하겠다는 뜻이다.  FDC 발생 이후로는 블럭을 요청한 INSTANCE로는 블럭 전송이 불필요 하다. 따라서 성능이 향상된다.  하지만 반대로 원래의 Instance에서
그 블럭을 Update 하려면 권한을 받아야만 하므로 성능이 느려지는 것이다.

FDC Control 할 수 있는 파라미터는 _FAIRNESS_THRESHOLD 이다. 이 파라미터는 Default 4 이다. 즉 특정 블록을 다른 Instance에서 5번 이상 Access 하는 경우 FDC가 발생하여 요청한 Instance로 권한이 넘어간다.

 

결론:

FDC 기능은 성능을 향상 시키기 위한 용도로 만들어 졌다. 하지만 위의 경우와 같이 오히려 느려지는 경우도 있다. Trade Off 특징이 잘 나타난다. 파티션의 특징을 잘 모르고 사용하였기 때문인데 해당 Select 문 뿐만 아니라 DML문까지 성능이 느려질 수 있으므로 주의해야 한다.

신고
Posted by extremedb

댓글을 달아 주세요

"멀티 컬럼으로 Range 파티션을 할 경우 Where 절에 파티션 선두 컬럼에 해당하는 조건만 주어도 Partition Pruning이 수행된다"

위의 말이 사실일까? 어디서 흘러나온 말인지 모르겠으나 위의 경우는 Partition Pruning이 제대로 되지 않는다. 함정이 기다리고 있기 때문이다.

아래의 스크립트를 실행해서 직접 증명해보자.          

 

환경: Oracle 10.2.0.4

       4 Node RAC

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', 5),

  PARTITION pt_3_1 VALUES LESS THAN ('3', 2),

  PARTITION pt_3_2 VALUES LESS THAN ('3', 3),

  PARTITION pt_3_3 VALUES LESS THAN ('3', 4),

  PARTITION pt_3_4 VALUES LESS THAN ('3', 5),  

  PARTITION pt_4_1 VALUES LESS THAN ('4', 2),

  PARTITION pt_4_2 VALUES LESS THAN ('4', 3),

  PARTITION pt_4_3 VALUES LESS THAN ('4', 4),

  PARTITION pt_4_4 VALUES LESS THAN ('4', 5), 

  PARTITION pt_5_1 VALUES LESS THAN ('5', 2),

  PARTITION pt_5_2 VALUES LESS THAN ('5', 3),

  PARTITION pt_5_3 VALUES LESS THAN ('5', 4),

  PARTITION pt_5_4 VALUES LESS THAN ('5', 5),     

  PARTITION pt_6_1 VALUES LESS THAN ('6', 2),

  PARTITION pt_6_2 VALUES LESS THAN ('6', 3),

  PARTITION pt_6_3 VALUES LESS THAN ('6', 4),

  PARTITION pt_6_4 VALUES LESS THAN ('6', 5),   

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

 

Table created.

 

먼저 실습을 진행할 테이블을 생성하였다. day_num 컬럼은 1~7 로 구성되어 있다. 모든 일자 데이터를 7등분 하기 위함이다. inst_id 컬럼은 RAC에서 Instance 번호이며 1~4로 구성된다. gc buffer busy등을 줄이기 위한 용도로 자주 사용된다.

 

이제 테스트용 데이터를 insert 하자.

 

INSERT INTO t

SELECT rownum AS id,

       trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

       to_char(trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4),'d') AS day_num,

       1+mod(rownum,4) AS inst_id,

       dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

 

10000 rows created.

 

commit;

 

BEGIN

  dbms_stats.gather_table_stats(

    ownname          => user,

    tabname          => 'T',

    estimate_percent => 100,

    method_opt       => 'for all columns size skewonly',

    cascade          => TRUE

  );

END;

/

 

PL/SQL procedure successfully completed.

 


각 파티션에 데이터가 골고루 Insert 되었는지 살펴보자.

SELECT partition_name, partition_position, num_rows

 FROM user_tab_partitions

 WHERE table_name = 'T'

 ORDER BY partition_position;

 

PARTITION_NAME                 PARTITION_POSITION   NUM_ROWS

------------------------------ ------------------ ----------

PT_1_1                                          1        356

PT_1_2                                          2        358

PT_1_3                                          3        355

PT_1_4                                          4        355

PT_2_1                                          5        364

PT_2_2                                          6        364

PT_2_3                                          7        362

PT_2_4                                          8        362

PT_3_1                                          9        358

PT_3_2                                         10        355

PT_3_3                                         11        355

PT_3_4                                         12        356

PT_4_1                                         13        357

PT_4_2                                         14        355

PT_4_3                                         15        355

PT_4_4                                         16        358

PT_5_1                                         17        355

PT_5_2                                         18        355

PT_5_3                                         19        357

PT_5_4                                         20        358

PT_6_1                                         21        355

PT_6_2                                         22        355

PT_6_3                                         23        358

PT_6_4                                         24        356

PT_7_1                                         25        355

PT_7_2                                         26        358

PT_7_3                                         27        358

PT_7_4                                         28        355

 

28 rows selected.


모든 파티션에 건수가 골고루 분배되었다. 그럼 이제 파티션의 선두 컬럼 조건만 있을 경우 Partition Pruning이 수행되는지 알아보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';

 

--------------------------------------------------------------------------------------

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

--------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |     115 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    13 |   1424 |     115 |

|*  3 |    TABLE ACCESS FULL      | T    |      5 |     9 |    13 |   1424 |     115 |

--------------------------------------------------------------------------------------


불필요한 파티션을 액세스 한다
Pstart
Pstop 항목을 보면 9번째 파티션부터 13번째 파티션까지 Scan 되었다.

Partition Pruning이 잘된 것처럼 보이지만 자세히 보면 DAY_NUM 3인 파티션은 4개뿐인데 5개의 파티션을 액세스 하였다.

 

어떻게 된 것인가?

DAY_NUM 3인 파티션은 4개 이지만 inst_id 컬럼에 어떤 값이 있을지 알 수 없으므로 13번째 파티션을 액세스 할 수 밖에 없는 것이다. 다시 말하면 DAY_NUM 3이면서 inst_id 6인 데이터는 13번째 파티션에 들어갈 수 있으므로 불필요한 Scan이 일어나는 것이다.

 

물론 아래와 같이 DAY_NUM 조건과 INST_ID 조건을 같이 준다면 이런 현상은 발생하지 않는다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID = 2;

  

------------------------------------------------------------------------------------

| Id  | Operation               | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE         |      |      1 |       |       |      1 |      23 |

|   2 |   PARTITION RANGE SINGLE|      |      1 |    10 |    10 |    355 |      23 |

|*  3 |    TABLE ACCESS FULL    | T    |      1 |    10 |    10 |    355 |      23 |

------------------------------------------------------------------------------------


정확히 10번째 파티션만 액세스 하였다.

만약 INST_ID 조건을 줄 수 없는 경우라면?

모든 경우에 INST_ID 조건을 줄 수는 없을 것이다. 예를 들면 전체를 처리해야 하는 경우는 INST_ID 조건을 줄 수 없을 것이다. 이때 어떻게 하면 비효율을 없앨 수 있겠는가?


해결방법1

특정 inst_id 조건을 주지 못할 때는 inst_id 의 범위를 주어 불필요한 파티션 scan을 방지한다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3'

   AND INST_ID BETWEEN 1 AND 4;

 

--------------------------------------------------------------------------------------

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

--------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

--------------------------------------------------------------------------------------


정확히 4개의 파티션만 액세스 하여 비효율이 사라졌다..

 

해결방법2

조건을 주지 못할 때는 4번째 inst_id 파티션을 maxvalue로 바꾼다.

 

CREATE TABLE t (

  id NUMBER,

  d1 DATE,

  day_num VARCHAR2(2), 

  inst_id NUMBER(1),

  pad VARCHAR2(4000),

  CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

  PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

  PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

  PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

  PARTITION pt_1_4 VALUES LESS THAN ('1', maxvalue),

  PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

  PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

  PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

  PARTITION pt_2_4 VALUES LESS THAN ('2', maxvalue),

  ...중간생략

  PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

  PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

  PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

  PARTITION pt_7_4 VALUES LESS THAN ('7', maxvalue)

);

 

위에서 실행한 insert문과 dbms_stats을 여기서 다시 실행하고 아래의 SQL을 수행하자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       COUNT(*)

  FROM T

 WHERE DAY_NUM = '3';


--------------------------------------------------------------------------------------

| Id  | Operation                 | Name | Starts | Pstart| Pstop | A-Rows | Buffers |

--------------------------------------------------------------------------------------

|   1 |  SORT AGGREGATE           |      |      1 |       |       |      1 |      92 |

|   2 |   PARTITION RANGE ITERATOR|      |      1 |     9 |    12 |   1424 |      92 |

|*  3 |    TABLE ACCESS FULL      | T    |      4 |     9 |    12 |   1424 |      92 |

--------------------------------------------------------------------------------------

 

정확히 4개의 파티션만 액세스 하였다. 비효율이 없어진 것이다.

 

결론

이제 maxvalue의 진정한 의미를 알겠는가?

단순히 마지막 값이라는 의미 이외에 Partition Pruning에 영향을 끼치며 이것은 성능과 직결된다.

또한 멀티 컬럼으로 Range 파티션을 할 경우 where 조건에 따라 불필요한 파티션을 액세스 하므로 주의해야 한다.  

 

PS

위의 해결책 1,2 를 통해 문제를 해결하지 않으면 해당 select문의 성능저하뿐만 아니라 해당 테이블을 사용하는 DML문의 성능이 저하되는 끔찍한 현상이 발생할 수 있다. 다음시간에 이 문제에 대하여 논의 해보자.


신고
Posted by extremedb

댓글을 달아 주세요

  1. 타락천사 2010.03.16 16:25 신고  댓글주소  수정/삭제  댓글쓰기

    MAXVALUE 가 끝임을 알려주는 키워드라는 이야기네요
    감사합니다.

  2. feelie 2010.03.16 17:40 신고  댓글주소  수정/삭제  댓글쓰기

    내용감사합니다.
    궁금한점이 하나 있습니다.
    maxvalue가 끝을알려주는 것이라고 하셨는데요.
    업무가 변경되어 id_num이 새로 2~3개의 값이 추가 되었을 경우
    어떤방법이 일을지 궁금합니다.
    새로 생성을 해야하는지, 아님 기존의 것을 이용할수도 있는지 궁금합니다..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.03.16 17:46 신고  댓글주소  수정/삭제

      id_num은 무엇을 말씀하시는 건지요?
      만약 INST_ID 컬럼을 말씀하는 것이라면 업무와 상관없습니다. INST_ID 는 RAC의 노드번호 입니다. 즉 1,2,3,4 로 구성되지요. 4대신에 maxvalue를 사용한 것 입니다.

      하지만 미래에 노드가 추가된다면 split을 해야합니다.
      즉 노드가 7번까지라면 4, 5,6을 추가하고 maxvalue를 7번으로 사용해야 겠지요.

      감사합니다.

  3. 2010.03.19 08:49 신고  댓글주소  수정/삭제  댓글쓰기

    정말 무엇인가를 느끼게 해주네요.

    최대값을 특정할 수 없을때 아무 생각없이 MAXVALUE를 주긴했지만,
    이런용도로 사용할 수 있는 것이군요..

    잘 읽었습니다.

Dummy Table(흔히 Copy_t 라고 불림) 대신에 9i 이후부터는 Connect By level 문을 사용하곤 한다. 하지만 조심하지 않으면 해당 SQL이 종료되지 않는 장애를 만나게 된다. 오늘은 Connect By level 문을 오용하는 사례와 해결책을 제시하고자 한다.

상황
업무팀에서 새로운 SQL을 작성하고 컴파일하여 운영 시스템에 반영되었다. 문제의 SQL이 실행되자 너무 오래걸려서 Time Out이 발생하였다. 아래는 상황을 최대한 간단히 표현하여 테스트를 수행하기 위한 스크립트 이다.

--입사년도 테이블 생성
create table hire as
select '2006' hire_date from dual union all
select '2003' hire_date from dual union all
select '2002' hire_date from dual union all
select '1999' hire_date from dual union all
select '1997' hire_date from dual ;

--현재년도 에서 입사년도를 빼서 차이(gap)를 나타냄
 select hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap
   from hire   ;


결과:
HIRE THIS        GAP
---- ---- ----------
2006 2010          4
2003 2010          7
2002 2010          8
1999 2010         11
1997 2010         13

5 rows selected.

업무요건
위의 SQL의 결과에서 나타난 GAP만큼 가상의 ROW를 생성하여야 한다. 즉 입사년도가 2006년인 사람은 ROW가 4개로 되어야 하고 2003년인 사람은 ROW가 7개가 되어야 한다. 전체적으로 43건이 나와야 한다. 아래에 원하는 답이 있다.
 
원하는 답
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
2003 2010          7          7
2003 2010          7          6
2003 2010          7          5
2003 2010          7          4
2003 2010          7          3
2003 2010          7          2
2003 2010          7          1
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1
....중간생략

문제의 SQL
아래의 SQL은 gap을 Connect By Level 절에 적용시킨 것이다. 아래처럼 SQL을 작성한다면 지옥을 경험할 수 있다.

select hire_date,
       to_char(sysdate, 'YYYY') this_year,
       to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
      level
  from hire
connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) ;

위의 SQL은 전체건을 Fetch하려면 10분이 걸려도 끝나지 않았다. 시간이 너무 오래 걸리므로 아래처럼 COUNT 로 바꿔서 실행해 보았다.

select /*+ gather_plan_statistics */ count(*)
  from  ( select hire_date,
                 to_char(sysdate, 'YYYY') this_year,
                 to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
                 level
            from hire
         connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date)
        );

       
결과:
  COUNT(*)
----------
   3773280

버그인가?
무려 370만건 이상의 건수가 나왔다. 이상하지 않은가? 건수의 예측도 할 수 없었다. 건수와 관련해서 일정한 규칙도 존재하지 않았다. 버그인지 아닌지 알 수 없지만 결론적으로 위의 SQL처럼 사용하면 안된다는 것을 알 수 있다. 아래는 Count에 대한 실행통계인데 무려 44초나 걸렸다.

-------------------------------------------------------------------------------
| Id  | Operation                      | Name | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |      |      1 |00:00:44.39 |       3 |
|   2 |   VIEW                         |      |   3773K|00:00:45.28 |       3 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |   3773K|00:00:41.51 |       3 |
|   4 |     TABLE ACCESS FULL          | HIRE |      5 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------


해결방법

 select /*+ gather_plan_statistics leading(hire) */
        hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
        num
   from hire,
        (select level as num
           from dual
        connect by level <= 40  --> 충분한 값을 주어야 한다.
        ) b
  where num <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date);  



Connect By 절을 인라인뷰로 만들어 Dummy 테이블처럼 사용하였다. 주의 사항은 connect by level <= 40 에서 숫자값을 충분히 주어야 한다. GAP 중에 가장 큰것이 13 이므로 넉넉히 40을 주었다.    
 
결과:
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
1997 2010         13         13
1997 2010         13         12
1997 2010         13         11
.....중간생략
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1

43 rows selected.  

정상적으로 원하는 결과가 나왔고 성능도 이상적이다.

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                     |      |     43 |00:00:00.01 |       3 |          |
|   2 |   SORT JOIN                     |      |      5 |00:00:00.01 |       3 | 2048  (0)|
|   3 |    TABLE ACCESS FULL            | HIRE |      5 |00:00:00.01 |       3 |          |
|*  4 |   SORT JOIN                     |      |     43 |00:00:00.01 |       0 | 2048  (0)|
|   5 |    VIEW                         |      |     40 |00:00:00.01 |       0 |          |
|   6 |     CONNECT BY WITHOUT FILTERING|      |     40 |00:00:00.01 |       0 |          |
|   7 |      FAST DUAL                  |      |      1 |00:00:00.01 |       0 |          |
-------------------------------------------------------------------------------------------
 
주의사항
만약 위의 SQL처럼 인라인뷰를 사용한다고 해도 Nested Loop 조인으로 풀리고 Connect By문을 사용한 인라인뷰가 후행집합이 된다면 선행집합의 건수만큼 반복해서 Connect By문이 수행되므로 조심해야 한다.


결론
Dummy 테이블을 대신하는 Connect By Level을 사용할 때 주의하지 않으면 SQL이 종료되지 않는다. 이를 방지하려면 반드시 DUAL과 함께 사용해야 하며 인라인뷰를 만들어서 사용해야 된다. 만약 예전처럼 Dummy 테이블을 사용했다면 이런 성능저하는 발생하지 않을 것이다. 아무리 새롭고 좋은것도 오용한다면 결과가 어떻게 되는지 잘 보여주는 예제이다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.03.07 12:34 신고  댓글주소  수정/삭제  댓글쓰기

    잘봤습니다.
    3월이라 책소식이 있을때가 됐는데요. 아직 소식이....^^
    개인적으로 자료를 찾아봤는데 찾을수가 없어 질문합니다.
    요즘에 책을 보니 DMA라는 말을 많이 접하게 됩니다.
    (예전부터 있었는지는 잘 모르겠네요..)
    DMA라는 개념은 알겠는데 오라클에서 사용을 어떻게 할수 있는지 궁금합니다
    DMA라는것이 fixed table을 사용하는지,
    DMA는 쿼리를 사용하지않는다고 하는데,
    그럼 일반 DBA가 DMA을 사용할수는 없는지요?
    사용할수 있다면 어떻게 하는지 궁금합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2010.03.08 10:10 신고  댓글주소  수정/삭제

      안녕하세요.
      책은 표지디자인이 끝나고 인쇄소로 넘어갔습니다.
      DMA관련해서는 ASK 엑셈에 질문하시는 것이 좋을것 같습니다. 제가 모니터링 툴 쪽은 잘 알지 못합니다.
      감사합니다.

  2. 김봉호 2010.03.09 12:46 신고  댓글주소  수정/삭제  댓글쓰기

    오 드디어 100번째 글이군요 +_+;;
    요즘 공부한답시고 처음부터 차근차근 보는중입니다 ^^

  3. Favicon of http://jazzjeon.tistory.com BlogIcon JazzJeon 2010.03.09 18:10 신고  댓글주소  수정/삭제  댓글쓰기

    담아가겠습니다. 좋은글 감사합니다.

  4. baind 2010.03.17 11:05 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 오동규님.
    부족한 제게 오늘도 소중하고 배울점이 많은 글을 볼 수 있도록 블로그에 올려주셔셔 진심으로 감사드립니다.

    다른 이야기이지만 위에 댓글에 인쇄소로 넘어갔다니...출판될 책을 곧 볼 수있다는 사실에 기대 됩니다.^^*
    언제나 건강하시고 귀하의 노고에 진심으로 감사드립니다!

  5. Favicon of http://www.soqool.com BlogIcon 쏘쿨 2010.05.31 15:15 신고  댓글주소  수정/삭제  댓글쓰기

    글 중간 부분에 "건수와 관련해서 일정한 규칙도 존재하지 않았다." 라고 쓰신 것은, 경험한 바에 의해서 쓰신거라고 이해하겠습니다.
    사실 일반적으로 말하면,
    건수와 관련된 규칙이 있고, 위의 건은 정확히 말하면

    3,773,280
    = 5^1 + 5^2 + 5^3 + 5^4
    + 5^4*4^1 + 5^4*4^2 + 5^4*4^3
    + 5^4*4^3*3^1
    + 5^4*4^3*3^1*2^1 + 5^4*4^3*3^1*2^2 + 5^4*4^3*3^1*2^3
    + 5^4*4^3*3^1*2^3*1^1 + 5^4*4^3*3^1*2^3*1^2

    건이 되겠습니다.

  6. 최규정 2010.08.19 14:37 신고  댓글주소  수정/삭제  댓글쓰기

    hire테이블과 connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date)의 결과가 카테시안 곱이 되는거 아닙니까? hire 어떤 테이블일지 모르겠지만 건수가 많으면 느려질것 같은데.. ㅋㅋ