"멀티 컬럼으로 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에 영향을 끼치며 이것은 성능과 직결된다.
PS
위의 해결책 1,2 를 통해 문제를 해결하지 않으면 해당 select문의 성능저하뿐만 아니라 해당 테이블을 사용하는 DML문의 성능이 저하되는 끔찍한 현상이 발생할 수 있다. 다음시간에 이 문제에 대하여 논의 해보자.
'Oracle > TroubleShooting' 카테고리의 다른 글
| 튜닝 프로젝트 시 이슈 극복 사례 (15) | 2010.05.20 |
|---|---|
| RAC에서 특정 Instance만 성능이 느린 이유 (0) | 2010.03.24 |
| COPY_T를 대신하여 Connect By 문을 사용하는가? (11) | 2010.03.05 |
| 한방 Query를 사용하지 말아야 할 때 (17) | 2010.02.19 |
| 유명한 Function Based Index 의 버그 (9) | 2009.11.19 |
invalid-file
