필자는 가끔 분석함수의 기능에 관해 질문을 받는다. 그때마다 대답을 하지 않고 대신에 질문에 부합하는 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 페이지)으로 모든 기능과 개념을 설명하였다. 아마 한 두 시간 이내에 다 볼 수 있을 것이다.

 

invalid-file

테이블 생성 파일

invalid-file

Mastering Oracle Analytic Function ppt 파일



PS

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


Posted by extremedb
,

지난번에 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
,

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
,