'Partition Access Pattern'에 해당되는 글 2건

  1. 2010.03.16 Range 파티션에서 maxvalue의 진정한 의미 (6)
  2. 2009.12.24 Partition Access Pattern (13)

"멀티 컬럼으로 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  댓글주소  수정/삭제  댓글쓰기

    감사합니다.