가능한가?

SQL문은 실행순서가 있다. 일반적으로 Order By가 가장 마지막에 수행된다. 만약 Order By가 없다면 마지막에 실행되는 것은 Group By이다. 데이터를 처리하는 작업은 Order By Group By 작업을 수행하기 이전에 끝난다. 상식적으로 보더라도 데이터를 읽는 작업과 조인작업을 먼저 처리해야 전체 데이터를 Grouping 할 수 있기 때문이다. 그런데 만약 데이터를 처리하는 작업이 Group By 이후에 발생한다면? 이런 일은 논리적으로 발생할 수 없다. 하지만 오늘 한가지 경우를 보여주려 한다.

 

먼저 오라클 SH 스키마의 Sales 테이블에 Local Partition 인덱스를 하나 생성한다.

Sales 테이블은 Time_id로 분기별 Range Partition이 되어있다.

 

CREATE INDEX SALES_TIME_CHANNEL_IX ON SALES

(TIME_ID, CHANNEL_ID) LOCAL;

                                 

먼저 정상적으로 처리되는 경우를 보자. IN 조건을 Pair로 여러개 주어본다. 

 

SELECT /*+ gather_plan_statistics */ TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

  

위의 SQL에서 주의해야 될 것은 IN 조건이 TIME_ID CHANNEL_ID 로 동시에 공급된다는 것이다.

 

TIME_ID  COUNT(TIME_ID)

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

98/02/14            391

1 row selected.

 

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

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

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

|   0 | SELECT STATEMENT        |                       |      1 |      1 |       3 |

|   1 |  SORT GROUP BY NOSORT   |                       |      1 |      1 |       3 |

|   2 |   PARTITION RANGE SINGLE|                       |      1 |    391 |       3 |

|*  3 |    INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    391 |       3 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("TIME_ID"=TO_DATE('19980214'))

       filter(("CHANNEL_ID"=2 OR "CHANNEL_ID"=3 OR "CHANNEL_ID"=4))


정상적으로 Grouping 되어 1건이 나왔고 Plan상에 Group By도 마지막에 수행되었다.

 
INLIST ITERATOR ! 니가 나를 배신하다니...

이제 1998 2 12일의 데이터를 추가로 공급해보자.

 

SELECT /*+ gather_plan_statistics */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14             54

98/02/14            287

98/02/14             50

 

4 rows selected.

 

답이 틀리다(Wrong Result Bug)

뭔가 이상하다. TIME_ID Grouping 하였으므로 결과건수는 1998 2 12일과 1998 2 14일로 두건만 나와야 한다. 그런데 2 14일 데이터가 Grouping 되지 않고 3건이 나와버렸다. 답이 틀리므로 이것은 버그이다.

 

왜 이런 일이 발생할까?

비밀은 실행계획에 있다.


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

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

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

|   0 | SELECT STATEMENT          |                       |      1 |      4 |       7 |

|   1 |  INLIST ITERATOR          |                       |      1 |      4 |       7 |

|   2 |   PARTITION RANGE ITERATOR|                       |      4 |      4 |       7 |

|   3 |    SORT GROUP BY NOSORT   |                       |      4 |      4 |       7 |

|*  4 |     INDEX RANGE SCAN      | SALES_TIME_CHANNEL_IX |      4 |    441 |       7 |

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

  

상식적으로는 Grouping을 가장 마지막에 한번만 해야 한다. 하지만 실행계획을 보면 그렇지 못하고 INLIST ITERATOR 작업 이전에 Grouping을 해버린다. ID 기준으로 3번이 그것인데 INLIST의 개수만큼 SORT GROUP BY가 반복된다. Starts 항목이 그것을 증명한다. 개념적으로 말하면 각각의 WHERE 조건마다 Group By를 수행하고 그 결과들을 Union 한 것이다. 

 

항상 버그가 발생하는 것은 아니다

파티션과 IN 조건이 만나야 버그가 발생한다. 또한 WHERE 조건이 변경되지 않고 INLIST ITERATOR가 발생해야 버그가 발생한다. 이 글에서 최초로 실행 시킨 SQL WHERE 조건은 TIME_ID = TO_DATE('19980214') AND CHANNEL_ID IN (2,3,4) 로 바뀌어 INLIST ITERATOR로 처리되지 않고 INDEX RANGE SCAND으로 처리되었으므로 버그가 없다.

 

해결책은 처리순서를 변경하는 것

이런 경우의 해결책은 아주 간단하다. 우리의 상식대로 하면 된다. 즉 데이터를 모두 처리하고 Grouping을 가장 마지막에 실행하는 것이다. 아래의 SQL이 그것이다.

 

SELECT TIME_ID, COUNT(*)

  FROM ( SELECT /*+ NO_MERGE */  TIME_ID

           FROM SALES

           WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                             (TO_DATE('19980214'), 2),

                                             (TO_DATE('19980214'), 3),

                                             (TO_DATE('19980214'), 4) )

       )

GROUP BY TIME_ID; 

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 

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

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

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

|   0 | SELECT STATEMENT           |                       |      1 |      2 |       9 |

|   1 |  SORT GROUP BY NOSORT      |                       |      1 |      2 |       9 |

|   2 |   INLIST ITERATOR          |                       |      1 |    441 |       9 |

|   3 |    PARTITION RANGE ITERATOR|                       |      4 |    441 |       9 |

|   4 |     VIEW                   |                       |      4 |    441 |       9 |

|*  5 |      INDEX RANGE SCAN      | SALES_TIME_CHANNEL_IX |      4 |    441 |       9 |

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


 

예상대로 Group By는 가장 마지막에 처리되었으므로 결과도 정상적으로 두건이 출력되었다. SQL의 실행순서만 알고 있으면 이 정도의 버그는 패치가 없어도 해결이 가능하다. NO_MERGE 힌트를 사용한 이유는 View Merge(뷰 해체)를 방지하기 위함이다. Simple View 이므로 힌트를 빼면 무조건 인라인뷰가 해체되어 버그가 재생된다.

 

또 다른 해결책을 보자.

 


SELECT /*+ INDEX(SALES SALES_TIME_CHANNEL_IX) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE    (TIME_ID = TO_DATE('19980214') AND CHANNEL_ID BETWEEN 2 AND 4 )

       OR (TIME_ID = TO_DATE('19980212') AND CHANNEL_ID =4 )         

 GROUP BY TIME_ID; 

 

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 


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

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

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

|   0 | SELECT STATEMENT         |                       |      1 |      2 |       5 |

|   1 |  HASH GROUP BY           |                       |      1 |      2 |       5 |

|   2 |   CONCATENATION          |                       |      1 |    441 |       5 |

|   3 |    PARTITION RANGE SINGLE|                       |      1 |    391 |       3 |

|*  4 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    391 |       3 |

|   5 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|*  6 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

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


 

SQL WHERE절을 수정하여 INLIST ITERATOR가 아닌 CONCATENATION으로 유도 하였다. 위의 실행계획 또한 Group By가 가장 마지막이 실행되므로 버그가 발생되지 않는다. SQL을 수정하기 싫고 힌트만으로 해결하려면 아래의 SQL을 보라. 

 


SELECT /*+ USE_CONCAT(1) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

 GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 

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

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

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

|   0 | SELECT STATEMENT         |                       |      1 |      2 |       9 |

|   1 |  HASH GROUP BY           |                       |      1 |      2 |       9 |

|   2 |   CONCATENATION          |                       |      1 |    441 |       9 |

|   3 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|*  4 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

|   5 |    PARTITION RANGE SINGLE|                       |      1 |    287 |       3 |

|*  6 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |    287 |       3 |

|   7 |    PARTITION RANGE SINGLE|                       |      1 |     54 |       2 |

|*  8 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     54 |       2 |

|   9 |    PARTITION RANGE SINGLE|                       |      1 |     50 |       2 |

|* 10 |     INDEX RANGE SCAN     | SALES_TIME_CHANNEL_IX |      1 |     50 |       2 |

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


위의 SQL 또한 정상적으로 2건이 출력된다.

버그를 유발하는 원인을 제거하라
마지막으로 볼 해결책은 파티션과 INLIST ITERATOR의 불편한 만남(?)을 제거하는 것이다.


SELECT /*+ INDEX_COMBINE(a) */

       TIME_ID, COUNT(TIME_ID)

  FROM SALES a

 WHERE (TIME_ID, CHANNEL_ID) IN (  (TO_DATE('19980212'), 4),

                                   (TO_DATE('19980214'), 2),

                                   (TO_DATE('19980214'), 3),

                                   (TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

 

TIME_ID  COUNT(TIME_ID)

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

98/02/12             50

98/02/14            391

 

2 rows selected.

 

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

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

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

|   0 | SELECT STATEMENT                    |                   |      1 |     2 |      61 |

|   1 |  PARTITION RANGE INLIST             |                   |      1 |     2 |      61 |

|   2 |   HASH GROUP BY                     |                   |      1 |     2 |      61 |

|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |      1 |   441 |      61 |

|   4 |     BITMAP CONVERSION TO ROWIDS     |                   |      1 |   441 |      17 |

|   5 |      BITMAP OR                      |                   |      1 |     1 |      17 |

|   6 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|*  7 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|*  8 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

|   9 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|* 10 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 11 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

|  12 |       BITMAP AND                    |                   |      1 |     1 |       5 |

|* 13 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 14 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     2 |       3 |

|  15 |       BITMAP AND                    |                   |      1 |     1 |       4 |

|* 16 |        BITMAP INDEX SINGLE VALUE    | SALES_TIME_BIX    |      1 |     1 |       2 |

|* 17 |        BITMAP INDEX SINGLE VALUE    | SALES_CHANNEL_BIX |      1 |     1 |       2 |

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

 

Bitmap Operation으로 인하여 INLIST ITERATOR가 사라졌으므로 버그는 보이지 않는다.

 

결론

이 버그는 Oracle 10g 에서 발견되었지만 11gR2 버전까지 해결되지 않고 있다. 파티션과 Local Index를 사용하고 Pair IN 조건이 INLIST ITERATOR로 풀리면 발생된다. 해결방법은 SQL을 수정하여 Group By를 마지막에 수행하거나 INLIST ITERATOR를 제거하면 된다. 버그패치가 없다고 해서 잘못된 결과를 보여줄 수는 없다. 패치가 나올 때 까지는 다른 해결책을 강구해야 한다. 잘 생각해보면 위의 경우처럼 원리만 안다면 상식 선에서 해결할 수 있는 문제가 매우 많다.

 

버그, 오라클에 알려야 해
대부분의 경우 버그는 사용자가 오라클에 보고해야 패치를 만들게 된다. 그러므로 버그가 발견되면 반드시 SR을 진행하여 오라클에 알려야 한다. 버그를 발견한 사람은 SQL을 수정하여 버그를 피할 수 있지만 동료나 다른 사이트에 있는 모든 사람들은 해결책을 모를 수 있고 심지어 버그가 있는지 조차 모를 수 있다. 하지만 많은 수의 사람들은 버그를 피해가는 방법만 발견된다면 내 문제는 해결 되었으므로 오라클에 버그를 통보하지 않고 조용히 넘어갈 것이다. 혹시 위의 버그가 몇년간 남아 있는 이유도 조용한(?) 사람들 때문이 아닐까?

한가지 방법은 오라클사에서 버그리포팅을 하는 사람에게 작은 선물을 주는 것이다. 제품의 품질도 높이고 고객의 참여를 유도하는 Win Win 전략이 될 수 있다.


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
,

지난 2009년 10월달에 Oracle Data Access Pattern을 정복하라 라는 글을 통하여 데이터의 접근방법에 대하여 알아보았다. 오늘은 파티션 데이터의 접근방법에 대하여 알아보자. 필자가 이글을 올리는 이유는 실행계획에 Partition Access Pattern 이 나오지만 해석을 못하는 사람이 많이 있기 때문이다. 오늘 이글을 읽고 이해한다면 파티션에 어떻게 접근하는지 접근하는 방법은 어떤것인지 모두 알수 있다.

기본적인 Partition의 종류는 3가지이다.

1.RANGE
2.LIST
3.HASH

하지만 위의 3가지를 엑세스 패턴으로 나누고자 한다면 매우 종류가 많아진다.


Partition
RANGE Access Pattern  

1.PARTITION RANGE SINGLE
2.2.PARTITION RANGE ITERATOR
3.3.PARTITION RANGE INLIST
4.4.PARTITION RANGE ALL
5.5.PARTITION RANGE EMPTY
6.6.PARTITION RANGE OR
7.7.PARTITION RANGE SUBQUERY
8.8.PARTITION RANGE JOIN-FILTER
9.9.PARTITION RANGE MULTI-COLUMN


Partition
LIST Access Pattern
1.
1.PARTITION LIST SINGLE
2.2.PARTITION LIST ITERATOR
3.3.PARTITION LIST INLIST
4.4.PARTITION LIST ALL
5.5.PARTITION LIST EMPTY
6.6.PARTITION LIST OR
7.7.PARTITION LIST SUBQUERY
8.8.PARTITION LIST JOIN-FILTER


Partition
HASH Access Pattern
1.
1.PARTITION HASH SINGLE
2.2.PARTITION HASH ITERATOR
3.3.PARTITION HASH INLIST
4.4.PARTITION HASH ALL
5.5.PARTITION HASH SUBQUERY
6.6.PARTITION HASH JOIN-FILTER

총 23가지이다.
이모든 것을 언제 다 배운단 말인가?
이럴때 필자가 정리한 파일이 도움이 될것이다.
반드시 Partition Pruning 과 Access Pattern 을 정복하기 바란다.

사용자 삽입 이미지
사용자 삽입 이미지

invalid-file

Partition Access Pattern





PS :
첨부된 문서는 Oracle 11.1까지의 Partition Pruning 과 Access Pattern을 정리한 것이다.
배포시 출처를 밝혀주기 바란다.

참조서적:
1.Trouble Shooting Oracle Performance (Christian Antognini)
2.오라클 메뉴얼
Posted by extremedb
,

아래는 약 3년전에 고객사 DBA 와 필자의 대화내용 이다.
물론 내용은 오라클 파티션 가이드 이다.

파티션 가이드? 구글에 있잖아!

DBA : Oracle 10g 기준으로 파티션 가이드를 하나 만들어 주세요.

필자 : 그런것은 구글에서 "파티션 가이드" 혹은 "Partiton 가이드" 로 검색을 하면 주루룩 하고 나올겁니다.

DBA : 제가 검색을 다 해보았지만 그런 것은 없었습니다.

필자 : 그럴리가요?

DBA : 파티션관련 자료는 많이 있지만 문제는 그 문서들이 파티션의 종류와 특성만을 설명하고 있다는 것입니다.
         파티션의 종류와 특징, 파티션 키의 선정, 인덱스 문제(Local, Global, Prefix ..등등 ) 은 왠만한
         개발자들도 알고 있습니다. 따라서 이런 문서는 개발팀에게 배포할 성격의 것은 아닙니다.

필자 : 그렇다면 하나의 테이블이 있을때 그 테이블을 파티션을 적용할것인지 아닌지 판단하는 로직을 원하시는 건가요?

DBA : 그렇습니다. 파티션을 적용할것인지 말것인지의 로직과 적용한다면 어떤 파티션을 적용할것인지 에 대한
         명쾌한 로드맵 같은것이 있으면 좋겠습니다.

이상하네? 가이드가 없네?
그 DBA 의 말이 일리가 있었다. 3년전 구글에서 조회 해보니 파티션 종류별 특성과 설명 뿐이었다. 이것은 놀라운 결과이다. 무수한 프로젝트에서 파티션을 적용해왔을 터
그런데 가이드가 없다니...

우리가 원하는 것은  "테이블을 어떠한 기준으로 무슨 종류의 파티션을 적용하느냐" 이다.
문제는 "각테이블을 어떤 기준으로 파티션을 적용하며 파티션의 종류는 무엇으로 할것인가?" 이다.  그런데 최근에 다시 파티션 가이드에 대해 구글에서 조회해본 결과 이런 성격의 자료가  전혀 없다. 물론 영문검색을 하면 오라클 메뉴얼이 나오긴 한다. 오라클 메뉴얼에도 명확한 기준은 없다. 또한 고객들이 원하는 것은 한글로된 문서이다.

가이드를 작성하는 방법
일반적으로 가이드라고 하는것은 아래와 같은 조건을 충족 해야한다.

1. 구체적인 표현으로 작성해야 한다. 모호한 표현은 가이드가 될수 없다. 더욱 모호해질 뿐이기 때문이다.
2. 판단 기준이 명확해야 한다. 이것은 분기로직이 있으면 더욱 명확해진다.
3. "10 중 8, 9" 라는 말이 있다. 이말은 예외적인 경우가 아니라면 가이드 대로 하는것이 항상 옳아야 한다는 것이다.
4. 분량이 작아야 한다. 가이드란 도로의 로드맵(이정표)라고 할수 있다.  가이드가 100~200 페이지 정도 된다면 이정표라고 할수 없을 것이다. 그것은 메뉴얼에 가깝다.

위의 방법대로 Oracle 10g 파티션 가이드를 작성해보면 다음과 같이 한장으로 요약 할수 있다.

사용자 삽입 이미지

위의 로드맵을 한글로 표현하면 다음과 같다.

사용자 삽입 이미지

이상으로 2장짜리 Oracle 10g 파티션 가이드를 만들어 보았다. 한글로된 가이드를 필자가 작성하였고 그것을 보고 한 지인이 즉석에서 로드맵을 작성한 것 인데 필자가 그 로드맵의 일부분을 알기쉽게 수정한것이다.
아래는 이 가이드를 정리한 pdf 문서이다.
이 문서에는 추가적으로 파티션 적용시의 주의사항 까지 덤으로 있으니 반드시 다운 받아서 읽어 보기 바란다.
물론 문서를 배포시 출처를 밝히는 것은 기본이고 매너의 문제이다.

invalid-file

Oracle 10g Partition 적용가이드





편집후기 :
가이드를 작성 하고 보니 한가지가 위에서 언급한 가이드를 작성하는 방법1 에 위배된다.  대용량 혹은 소용량 테이블이 그것인데 이 용어는 필자로 서도 어쩔수 없다. 테이블의 용량이 어디서 부터 대용량이고 어디서 부터 소용량인지 구분하는 기준은 존재하지 않기 때문이다.

한가지 화두가 있는데 물리 모델링시에 모델러가 파티셔닝을 해야할까? 아니면 이것을 모델링의 개념이 아닌 튜닝의 개념으로 보아서 튜너 혹은 DBA 가 해야할까? 여러분은 어떻게 생각 하는가?

'Oracle > Partition' 카테고리의 다른 글

오라클 Upgrade 후에 파티션을 만들 수 없어요  (0) 2011.03.15
Posted by extremedb
,