테이블을 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
+---------------------------------------+-------+--------+------+
관련 링크