블록 그래뉼과 파티션 그래뉼

12.1 버전에서 변경된 블록 그래뉼과 파티션 그래뉼의 동작 방식을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (c1 NUMBER) PARTITION BY HASH (c1) PARTITIONS 64;
CREATE TABLE t2 (c1 NUMBER) PARTITION BY HASH (c1) PARTITIONS 128;

t1 테이블을 병렬도 8로 조회하면 블록 그래뉼로 동작하는 것을 확인할 수 있다.

-- 2
SELECT /*+ FULL(T1) PARALLEL(T1 8) */* FROM t1;

--------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     1 |    64 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T1       |     1 |    64 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------

병렬도를 4로 낮추면 파티션 그래뉼로 동작한다.

-- 3
SELECT /*+ FULL(T1) PARALLEL(T1 4) */* FROM t1;

-----------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Pstart| Pstop | TQ     |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION HASH ALL|          |     1 |    64 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL   | T1       |     1 |    64 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------

t2 테이블은 병렬도를 8로 지정해도 파티션 그래뉼로 동작한다.

-- 4
SELECT /*+ FULL(T2) PARALLEL(T2 8) */* FROM t2;

-----------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Pstart| Pstop | TQ     |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION HASH ALL|          |     1 |   128 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL   | T1       |     1 |   128 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------

병렬도를 16으로 높이면 블록 그래뉼로 동작한다.

-- 5
SELECT /*+ FULL(T2) PARALLEL(T2 16) */* FROM t2;

--------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     1 |   128 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T1       |     1 |   128 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------

아래는 이전 테스트 내용을 정리한 결과다. 12.1 버전부터 병렬도를 파티션 개수로 나눈 값이 8보다 같거나 작을 때만 블록 그래뉼로 동작하는 것을 확인할 수 있다. 다시 정리하면 하나의 병렬 서버가 8개 이상의 파티션을 읽어야 할 경우 파티션 그래뉼로 동작하는 기능이 추가된 것으로 보인다.

---------------------------------------------
| SQL | PARTITION | DOP | P/D | GRANULE     |
---------------------------------------------
|   2 |        64 |   8 |   8 | Block RANGE |
|   3 |        64 |   4 |  16 | Partition   |
|   4 |       128 |   8 |  16 | Partition   |
|   5 |       128 |  16 |   8 | Block Range |
---------------------------------------------

해당 기능에 의해 파티션 개수가 많고 데이터 분포가 고르지 못한 파티션 테이블을 낮은 병렬도로 조회하면 급격한 성능 저하가 발생할 수 있다. 미래 시점의 파티션을 생성해둔 파티션 테이블도 문제가 될 수 있다. _px_partition_scan_enabled 파라미터를 false로 설정하면 해당 기능이 동작하지 않는다.

-- 6
SELECT /*+ OPT_PARAM('_px_partition_scan_enabled', 'false') FULL(T2) PARALLEL(T2 8) */ * FROM t2;

--------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |     1 |   128 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| T1       |     1 |   128 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------

_px_partition_scan_threshold 파라미터는 제대로 동작하지 않는 것으로 보인다.

-- 7
SELECT /*+ OPT_PARAM('_px_partition_scan_threshold', '128') FULL(T2) PARALLEL(T2 8) */ * FROM t2;

---------------------------------------------------
| Id  | Operation          | Name | Pstart| Pstop |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  PARTITION HASH ALL|      |     1 |   128 |
|   2 |   TABLE ACCESS FULL| T1   |     1 |   128 |
---------------------------------------------------