'파티션'에 해당되는 글 4건

  1. 2010.08.20 파티션과 IN 조건의 잘못된 만남
  2. 2010.03.16 Range 파티션에서 maxvalue의 진정한 의미 (6)
  3. 2009.12.24 Partition Access Pattern (13)
  4. 2009.08.05 Oracle 10g Partitioning 가이드 (12)

가능한가?

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

댓글을 달아 주세요

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

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

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

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

    • Favicon of https://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를 주긴했지만,
    이런용도로 사용할 수 있는 것이군요..

    잘 읽었습니다.

지난 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 을 정복하기 바란다.

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

Partition Access Pattern.pdf

Partition Access Pattern





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

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

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.12.24 17:37  댓글주소  수정/삭제  댓글쓰기

    이번에도 또 하나의 컨텐츠를 만들어 내셨군요 ^^
    크리스마스 선물로 잘 보겠습니다. 동규님도 즐거운 성탄절 보내시기 바랍니다.

  2. bosingwa 2009.12.27 00:15  댓글주소  수정/삭제  댓글쓰기

    감사합니다. 잘 보겠습니다.
    새해 복 많이 받으시길...

  3. feelie 2009.12.29 17:21  댓글주소  수정/삭제  댓글쓰기

    partition된 테이블의 실행계획을 이해하기기 무척 어려웠는데..
    감사합니다.
    지난내용중에 패러럴에 대한 부분도 여러가지 있는데 아직 패러럴에 대해서도
    모르는 부분이 많습니다.
    하나씩 올려주시는 내용보면서 많이 느끼고 있습니다.

  4. 서은아빠 2009.12.31 18:28  댓글주소  수정/삭제  댓글쓰기

    한해동안 블러그 관리하랴 집필활동 하랴...프로젝트 하랴..

    고생 많으셨습니다.

    내년 한해 건강에 유념하며 원하시는 모든일 이루어지길 바램해봅니다.

    새해 복 마니 받으시기 바랍니다.

  5. patrick 2010.02.13 23:02  댓글주소  수정/삭제  댓글쓰기

    대단히 좋은 글들이 굉장이 많이 있어서 한참 동안 자료를 보다 갑니다. 몇몇 자료는 개인적으로 사용하고자 노트북에 정리를 해두었습니다. 자주 들르도록 하겠습니다. 더불어, 책도 기대하고 있겠습니다.

  6. 공의 2010.12.20 11:31  댓글주소  수정/삭제  댓글쓰기

    올려 주신 자료 잘 보고 있는중인데요
    근데 해당 자료(Partition Access Pattern.pdf)에서

    tx 테이블이 나오는데요
    해당 tx 및 tx의 index생성 스크립트를 받고
    싶은데요

    해당스크립트를 주시면 테스트 하는데 도움이
    되겠습니다.

    감사합니다. 새해복 많이 받으세요 ~~

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.12.20 14:15 신고  댓글주소  수정/삭제

      안녕하세요. 해당 스크립트는 TOP 책의 저자인 Christian Antognini의 책을 정리한 것입니다.
      아래의 스크립트를 받아서 9장을 테스트 해보시면 됩니다.
      http://antognini.ch/downloads/Scripts20100624.zip

      PS 영어가 가능하시다면 책을 구입하시기 바랍니다.
      감사합니다.

  7. 공의 2010.12.20 14:17  댓글주소  수정/삭제  댓글쓰기

    감사합니다.

아래는 약 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 문서이다.
이 문서에는 추가적으로 파티션 적용시의 주의사항 까지 덤으로 있으니 반드시 다운 받아서 읽어 보기 바란다.
물론 문서를 배포시 출처를 밝히는 것은 기본이고 매너의 문제이다.

Oracle_10g_Partition_적용_가이드.pdf

Oracle 10g Partition 적용가이드





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

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

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

오라클 Upgrade 후에 파티션을 만들 수 없어요  (0) 2011.03.15
Oracle 10g Partitioning 가이드  (12) 2009.08.05
Posted by extremedb

댓글을 달아 주세요

  1. 지나가던 나그네 2009.08.06 09:53  댓글주소  수정/삭제  댓글쓰기

    오도사(오(라클|동규) 도사)님! 파티션 가이드가 많지 않았는데 이렇게 좋은 자료를 만들어주셔서 감사합니다.

    넓은 아량으로 배포까지 해주시니 학습에 많은 도움이 될꺼 같습니다.

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

      ㅎㅎ 제 이름이 그렇게 연결되나요?
      도사라니요 당치도 않습니다.
      그저 오라클을 더많이 알기 위해 노력하는 사람 이지요.

  2. 혈기린 2009.08.06 10:49  댓글주소  수정/삭제  댓글쓰기

    전 물리모델링시에 모델러가 파티션 적용을 고려해야 할거 같은데요 ^^
    좋은 가이드라인 감사합니다.

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

      제 생각도 마찬가지 입니다.
      이유는 데이터의 성격을 제일 잘아는 사람들이 모델러 이기 때문입니다. 파티션을 적용하려면 데이터의 성격을 잘알아야 하지요.
      개발 프로젝트시에 모델러들이 다빠져나가고 나면 새로들어온 튜너나 DBA, 개발자 의 경우 데이터의 성격을 잘알지 못하기 때문에 파티션을 적용하기 어렵죠. 따라서 최소한 모델러들이 물리 ERD 에 반영을 해놓거나 아니면 따로 목록이라도 만들어서 DBA 나 튜너에게 인수인계를 한다면 데이터를 재분석하는일 등은 없어질 것 입니다.

  3. 타락천사 2009.09.30 16:36  댓글주소  수정/삭제  댓글쓰기

    좋은글 감사합니다.
    출처는 밝히고, OracleClub 에 남기겠습니다.

  4. 돌칼 2010.01.06 17:28  댓글주소  수정/삭제  댓글쓰기

    음 저도 물리 모델링시에 파티션적용을 고려하는게 맞을것 같아요.
    사실 둘 다 같이 하는게 맞지 않을까 합니다.
    저는 보관주기가 있는 대용량 테이블일 경우만 생각을 하고 적용해왔는데, range + hash파티션도 고려를 해봐야할것 같아요. 가이드 정말 명쾌하게 잘 만드신것 같습니다. 도움이 많이 되었습니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.01.06 19:20 신고  댓글주소  수정/삭제

      반갑습니다.
      말씀 하신것 처럼 튜너와 모델러가 같이 작업을 한다면 최적이죠.
      그리고 가이드가 도움이 되었다니 다행입니다.
      새해 복많이 받으세요.

  5. 금땡이 2010.07.13 15:37  댓글주소  수정/삭제  댓글쓰기

    오~ 좋은 자료 입니다.

    실제 물리 모델 단계에서 파티션 대상 Table 선정시 잘 활용하고 있는데요,

    아래의 2가지 질문 사항이 있습니다.

    1. '6.동일Block 동시 처리'에 대한 판단을 정량적으로 할 수 있는 방법이 있는지 궁금 합니다.

    논리 모델링을 위주로 하는 모델러이다 보니 위의 사항에 대한 가이드시 애매한 부분이 많더라구요.

    결국, '거래로그'와 같이 대량의 Transaction에 대한 DML 경합에 대한 분산으로 생각되는데...

    2. 물리 모델 단계(구현 단계가 아닌 설계 단계)에서 가급적 완성도 높은 파티셔닝 전략을 위해 '6.동일Block 동시 처리'에 대한 판단을 할 수

    있는지도 궁금 하구요.


    고견 부탁 드립니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.07.14 11:12 신고  댓글주소  수정/삭제

      오랜만 입니다.
      답변을 드리겠습니다.

      1.동일블럭 동시처리는 대량 Transaction에 대한 DML 경합도 해당이 되지만 select 도 해당됩니다. 가령 GC bufffer busy 나 buffer busy wait은 dml 과 select문을 가리지 않고 발생합니다.

      문제는 정량적으로 판단하여 Hash 파티션을 할것인지 말것인지 판단하는 것은 매우 어렵다는 것입니다.
      "GC bufffer busy 나 buffer busy wait등이 초당 몇번 발생하면 hash 파티션을 해야하나?"는 기준은 없다는 것입니다.

      하지만 rough 한 기준은 만들수 있습니다. SQL의 ELAPSTIME 시간 기준으로 1/3(33%)의 시간 이상을 GC bufffer busy 나 buffer busy wait EVENT 때문에 대기하고 있다면 문제가 있다고 보아도 무방합니다. 즉 수행시간중 1/3 이 HOT block 문제로 대기한다면 해결해 주어야 합니다. 물론 실시간 동시처리가 많은 테이블에 한 합니다. 이 정보는 AWR 이나 ADDM 에서 확인가능 합니다.

      2. 물리모델 단계에서 예상은 할 수 있지만 결론을 내릴수는 없다고 봅니다. 말씀하신 '거래로그'와 같이 대량의 Transaction은 뻔한것 이므로 Hash 파티션을 무조건 하여도 될것 같지만 문제는 Hash 파티션의 갯수를 몇개로 할것이냐 입니다. 4개로 쪼갤 것인지 아니면 8개로 쪼갤것인지? 아니면 265개? .....정확한 것은 테스트를 하여서 Wait Event가 줄어드는 양을 보고 결정하는 수 밖에 없다고 보여집니다. 아쉽네요.

  6. Favicon of http://www.perfectreplicawatch.co.uk/replica-breitling-c-142.html BlogIcon replica breitling 2011.08.06 16:36  댓글주소  수정/삭제  댓글쓰기

    넓은 아량으로 배포까지 해주시니 학습에 많은 도움이 될꺼 같습니다.

  7. f0081 2014.05.26 22:04  댓글주소  수정/삭제  댓글쓰기

    얼마전에 검색하다가 우연히 이 사이트를 알게되었는데, 많은 공부가 되고 있습니다. 가려운곳을 정확히 짚어주시니 감사할 따름입니다.