HCC 압축 후 블록 I/O 증가 현상

테이블을 HCC 압축하면 인덱스를 통해 테이블을 액세스하는 쿼리의 블록 I/O가 증가하고 이로 인해 쿼리의 성능이 저하될 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (
    c01, c02, c03, c04, c05, c06, c07, c08, c09, c10
  , c11, c12, c13, c14, c15, c16, c17, c18, c19, c20
  , c21, c22, c23, c24, c25, c26, c27, c28, c29, c30
)
NOLOGGING
PARALLEL 10
PARTITION BY RANGE (c01) (
    PARTITION p01 VALUES LESS THAN ( 2)
  , PARTITION p02 VALUES LESS THAN ( 3)
  , PARTITION p03 VALUES LESS THAN ( 4)
  , PARTITION p04 VALUES LESS THAN ( 5)
  , PARTITION p05 VALUES LESS THAN ( 6)
  , PARTITION p06 VALUES LESS THAN ( 7)
  , PARTITION p07 VALUES LESS THAN ( 8)
  , PARTITION p08 VALUES LESS THAN ( 9)
  , PARTITION p09 VALUES LESS THAN (10)
  , PARTITION p10 VALUES LESS THAN (11)
) 
AS
WITH w1 AS (
SELECT NVL (NULLIF (MOD (ROWNUM, 10000), 0), 10000) AS c02, ORA_HASH (ROWNUM) AS c03
  FROM XMLTABLE ('1 to 1000000'))
SELECT /*+ PQ_CONCURRENT_UNION */
       c01, c02
     , c03 + 1, c03 + 2, c03 + 3, c03 + 4, c03 + 5, c03 + 6, c03 + 7, c03 + 8, c03 + 9, c03 + 10
     , c03 + 1, c03 + 2, c03 + 3, c03 + 4, c03 + 5, c03 + 6, c03 + 7, c03 + 8, c03 + 9, c03 + 10
     , c03 + 1, c03 + 2, c03 + 3, c03 + 4, c03 + 5, c03 + 6, c03 + 7, c03 + 8
  FROM (SELECT  1 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  2 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  3 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  4 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  5 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  6 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  7 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  8 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT  9 AS c01, c02, c03 FROM w1 UNION ALL
        SELECT 10 AS c01, c02, c03 FROM w1);

CREATE INDEX t1_x1 ON t1 (c02) LOCAL NOLOGGING PARALLEL 16;

ALTER TABLE t1 NOPARALLEL;
ALTER INDEX t1_x1 NOPARALLEL;

 

아래 쿼리는 테이블 액세스 단계에서 1,000개의 블록 I/O가 발생합니다.

-- 2
SELECT /*+ INDEX(T1 T1_X1) NO_CLUSTER_BY_ROWID(T1) */
       COUNT (c03), COUNT (c30)
  FROM t1
 WHERE c02 = 1;

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name  | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |       |      1 |       |       |      1 |    1030 |
|   1 |  SORT AGGREGATE                             |       |      1 |       |       |      1 |    1030 |
|   2 |   PARTITION RANGE ALL                       |       |      1 |     1 |    10 |   1000 |    1030 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |     10 |     1 |    10 |   1000 |    1030 | -- 1,000
|*  4 |     INDEX RANGE SCAN                        | T1_X1 |     10 |     1 |    10 |   1000 |      30 |
---------------------------------------------------------------------------------------------------------

 

아래와 같이 테이블을 Query High 옵션으로 HCC 압축하고, 인덱스를 REBUILD하겠습니다.

-- 3-1
ALTER TABLE t1 MOVE PARTITION p01 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p02 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p03 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p04 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p05 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p06 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p07 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p08 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p09 COMPRESS FOR QUERY HIGH PARALLEL 16;
ALTER TABLE t1 MOVE PARTITION p10 COMPRESS FOR QUERY HIGH PARALLEL 16;

-- 3-2
ALTER INDEX t1_x1 REBUILD PARTITION p01 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p02 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p03 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p04 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p05 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p06 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p07 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p08 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p09 PARALLEL 16;
ALTER INDEX t1_x1 REBUILD PARTITION p10 PARALLEL 16;

-- 3-3
ALTER TABLE t1 NOPARALLEL;
ALTER INDEX t1_x1 NOPARALLEL;

 

앞선 쿼리를 다시 수행하면 테이블 액세스 단계에서 11,659개의 블록 I/O가 발생합니다. 압축 전보다 블록 I/O가 11배 증가했습니다.

-- 4
SELECT /*+ INDEX(T1 T1_X1) NO_CLUSTER_BY_ROWID(T1) */
       COUNT (c03), COUNT (c30)
  FROM t1
 WHERE c02 = 1;

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name  | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |       |      1 |       |       |      1 |   11689 |
|   1 |  SORT AGGREGATE                             |       |      1 |       |       |      1 |   11689 |
|   2 |   PARTITION RANGE ALL                       |       |      1 |     1 |    10 |   1000 |   11689 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |     10 |     1 |    10 |   1000 |   11689 | -- 11,659
|*  4 |     INDEX RANGE SCAN                        | T1_X1 |     10 |     1 |    10 |   1000 |      30 |
---------------------------------------------------------------------------------------------------------

 

아래 쿼리는 c03 칼럼만 집계하여 테이블 액세스 단계에서 2,459개의 블록 I/O가 발생합니다. 4번 쿼리와의 블록 I/O 차이는 9,190개(= 11,659 - 2,469)입니다.

-- 5
SELECT /*+ INDEX(T1 T1_X1) NO_CLUSTER_BY_ROWID(T1) */
       COUNT (c03)
  FROM t1
 WHERE c02 = 1;

---------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name  | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |       |      1 |       |       |      1 |    2499 |
|   1 |  SORT AGGREGATE                             |       |      1 |       |       |      1 |    2499 |
|   2 |   PARTITION RANGE ALL                       |       |      1 |     1 |    10 |   1000 |    2499 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |     10 |     1 |    10 |   1000 |    2499 | -- 2,469
|*  4 |     INDEX RANGE SCAN                        | T1_X1 |     10 |     1 |    10 |   1000 |      30 |
---------------------------------------------------------------------------------------------------------

 

4번 쿼리와 5번 쿼리의 세션 통계를 비교하면 HCC scan rdbms CUs row pieces accessed 통계값 차이와 블록 I/O 차이가 동일한 것을 확인할 수 있습니다. CU 내에서 하나의 로우가 약 10개의 블록으로 분할된 것을 유추할 수 있습니다. 아울러 table fetch continued row 통계값이 HCC scan rdbms CUs row pieces accessed 통계값과 동일하므로 CU 내 블록은 Chained Row와 동일하게 처리됨을 알 수 있습니다.

-- 6
+---------------------------------------+-------+--------+------+
|NAME                                   |    # 4|     # 5|CLASS |
+---------------------------------------+-------+--------+------+
|table fetch continued row              |  9.66K| 473.00 |SQL   | -- 9,190
|HCC scan rdbms CUs columns accessed    |  2.00K| 999.00 |Debug |
|HCC scan rdbms CUs row pieces accessed | 10.66K|   1.47K|Debug | -- 9,190
+---------------------------------------+-------+--------+------+

 

관련 링크

• Jonathan Lewis - Compression Units