UNUSABLE 인덱스 파티션에 의한 Join Factorization 쿼리 변환으로 인해 쿼리의 성능이 저하된 사례를 살펴보겠습니다. 참고로 이 글은 PPWJ 성능 저하 사례와 관련이 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. t1은 비파티션 테이블, t2는 파티션 테이블입니다. t1 테이블에는 t2 테이블의 p1, p2 파티션에 해당하는 데이터가 저장되어 있습니다.
-- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; CREATE TABLE t1 (c1, c2) AS SELECT CEIL ((ROWNUM + 600000) / 100000), ROWNUM + 600000 FROM XMLTABLE ('1 to 200000'); CREATE TABLE t2 (c1, c2, c3) PARTITION BY LIST (c1) ( PARTITION p1 VALUES (1) , PARTITION p2 VALUES (2) , PARTITION p3 VALUES (3) , PARTITION p4 VALUES (4) , PARTITION p5 VALUES (5) , PARTITION p6 VALUES (6) , PARTITION p7 VALUES (7) , PARTITION p8 VALUES (8) ) AS SELECT CEIL (ROWNUM / 100000), ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 800000'); CREATE INDEX t2_x1 ON t2 (c1, c2) LOCAL;
아래 예제는 t1, t2 테이블을 해시 조인합니다. ADAPTIVE PART JOIN FILTER로 인해 t2 테이블의 p1, p2 파티션(25MB)만 조회됩니다.
-- 2 SELECT /*+ PARALLEL(2) LEADING(A) USE_HASH(B) PQ_DISTRIBUTE(B HASH HASH) CARDINALITY(A 1E7) */ COUNT (b.c3) FROM t1 a , t2 b WHERE b.c1 = a.c1 AND b.c2 = a.c2; ========================================================================================= | Id | Operation | Name | Execs | Rows | Read | Read | | | | | | (Actual) | Reqs | Bytes | ========================================================================================= | 0 | SELECT STATEMENT | | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | | 2 | PX COORDINATOR | | 5 | 2 | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 2 | 2 | | | | 4 | SORT AGGREGATE | | 2 | 2 | | | | 5 | HASH JOIN | | 2 | 200K | | | | 6 | PART JOIN FILTER CREATE | :BF0000 | 2 | 200K | | | -- ! | 7 | PX RECEIVE | | 2 | 200K | | | | 8 | PX SEND HASH | :TQ10000 | 2 | 200K | | | | 9 | PX BLOCK ITERATOR | | 2 | 200K | | | | 10 | TABLE ACCESS FULL | T1 | 26 | 200K | | | | 11 | PX RECEIVE | | 2 | 200K | | | | 12 | PX SEND HASH | :TQ10001 | 2 | 200K | | | | 13 | PX BLOCK ITERATOR ADAPTIVE | | 2 | 200K | | | | 14 | TABLE ACCESS FULL | T2 | 26 | 200K | 28 | 25MB | -- ! =========================================================================================
아래와 같이 t2_x1 인덱스의 p8 파티션을 UNUSABLE로 변경하고 쿼리를 다시 수행하면 TE(Table Expansion) 쿼리 변환 후 JF(Join Factorization) 쿼리 변환이 발생합니다. ADAPTIVE PART JOIN FILTER가 동작하지 않아 t2 테이블의 전체 파티션(99MB)이 조회되고, 이로 인해 쿼리의 성능이 저하될 수 있습니다.
-- 3-1 ALTER INDEX t2_x1 MODIFY PARTITION p8 UNUSABLE; -- 3-2 SELECT /*+ PARALLEL(2) LEADING(A) USE_HASH(B) PQ_DISTRIBUTE(B HASH HASH) CARDINALITY(A 1E7) */ COUNT (b.c3) FROM t1 a , t2 b WHERE b.c1 = a.c1 AND b.c2 = a.c2; ============================================================================================= | Id | Operation | Name | Execs | Rows | Read | Read | | | | | | (Actual) | Reqs | Bytes | ============================================================================================= | 0 | SELECT STATEMENT | | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | | 2 | PX COORDINATOR | | 5 | 2 | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 2 | 2 | | | | 4 | SORT AGGREGATE | | 2 | 2 | | | | 5 | HASH JOIN | | 2 | 200K | | | | 6 | PX RECEIVE | | 2 | 200K | | | | 7 | PX SEND HASH | :TQ10000 | 2 | 200K | | | | 8 | PX BLOCK ITERATOR | | 2 | 200K | | | | 9 | TABLE ACCESS FULL | T1 | 26 | 200K | | | | 10 | PX RECEIVE | | 2 | 800K | | | | 11 | PX SEND HASH | :TQ10001 | 2 | 800K | | | | 12 | VIEW | VW_JF_SET$54A8F361 | 2 | 800K | | | | 13 | UNION-ALL | | 2 | 800K | | | | 14 | PX BLOCK ITERATOR | | 2 | 100K | | | | 15 | TABLE ACCESS FULL | T2 | 26 | 100K | 27 | 12MB | -- p1, p2 | 16 | PX BLOCK ITERATOR | | 2 | 700K | | | | 17 | TABLE ACCESS FULL | T2 | 28 | 700K | 112 | 87MB | -- p3 ~ p8 ============================================================================================= Outline Data ------------- EXPAND_TABLE(@"SEL$1" "B"@"SEL$1") FACTORIZE_JOIN(@"SET$DBD0C655"("A"@"SET$DBD0C655_2" "A"@"SET$DBD0C655_1"))
앞선 예제에서 ADAPTIVE PART JOIN FILTER가 동작하지 않은 원인은 HASH 분배 방식과 관련된 것으로 보입니다. 아래 예제처럼 PQ_DISTRIBUTE 힌트로 t1 테이블을 BROADCAST하면 ADAPTIVE PART JOIN FILTER가 동작하는 것을 확인할 수 있습니다.
-- 4 SELECT /*+ PARALLEL(2) LEADING(A) USE_HASH(B) CARDINALITY(A 1E7) PQ_DISTRIBUTE(@"SEL$0B478C0A" "VW_JF_SET$54A8F361"@"SEL$FDECE45D" BROADCAST NONE) */ COUNT (b.c3) FROM t1 a , t2 b WHERE b.c1 = a.c1 AND b.c2 = a.c2; =================================================================================================== | Id | Operation | Name | Execs | Rows | Read | Read | | | | | | (Actual) | Reqs | Bytes | =================================================================================================== | 0 | SELECT STATEMENT | | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | | 2 | PX COORDINATOR | | 3 | 2 | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 2 | | | | 4 | SORT AGGREGATE | | 2 | 2 | | | | 5 | HASH JOIN | | 2 | 200K | | | | 6 | PART JOIN FILTER CREATE | :BF0000 | 2 | 400K | | | -- ! | 7 | TABLE ACCESS FULL | T1 | 2 | 400K | | | | 8 | VIEW | VW_JF_SET$54A8F361 | 2 | 200K | | | | 9 | UNION-ALL | | 2 | 200K | | | | 10 | PX BLOCK ITERATOR ADAPTIVE | | 2 | 100K | | | | 11 | TABLE ACCESS FULL | T2 | 26 | 100K | 27 | 12MB | -- ! | 12 | PX BLOCK ITERATOR ADAPTIVE | | 2 | 100K | | | | 13 | TABLE ACCESS FULL | T2 | 26 | 100K | 27 | 12MB | -- ! ===================================================================================================
아래 예제처럼 EXPAND_TABLE 힌트와 OPT_PARAM 힌트를 추가한 후 쿼리를 수행하면 TE 쿼리 변환만 발생합니다. t1 테이블을 2번 읽지만 ADAPTIVE PART JOIN FILTER가 동작하는 것을 확인할 수 있습니다.
-- 5 SELECT /*+ PARALLEL(2) LEADING(A) USE_HASH(B) PQ_DISTRIBUTE(B HASH HASH) CARDINALITY(A 1E7) EXPAND_TABLE(B) OPT_PARAM('_optimizer_join_factorization' 'false') */ COUNT (b.c3) FROM t1 a , t2 b WHERE b.c1 = a.c1 AND b.c2 = a.c2; =========================================================================================== | Id | Operation | Name | Execs | Rows | Read | Read | | | | | | (Actual) | Reqs | Bytes | =========================================================================================== | 0 | SELECT STATEMENT | | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | | 2 | PX COORDINATOR | | 5 | 2 | | | | 3 | PX SEND QC (RANDOM) | :TQ10004 | 2 | 2 | | | | 4 | SORT AGGREGATE | | 2 | 2 | | | | 5 | VIEW | VW_TE_1 | 2 | 200K | | | | 6 | UNION-ALL | | 2 | 200K | | | | 7 | HASH JOIN | | 2 | 100K | | | | 8 | PART JOIN FILTER CREATE | :BF0000 | 2 | 200K | | | -- ! | 9 | PX RECEIVE | | 2 | 200K | | | | 10 | PX SEND HASH | :TQ10000 | 2 | 200K | | | | 11 | PX BLOCK ITERATOR | | 2 | 200K | | | | 12 | TABLE ACCESS FULL | T1 | 26 | 200K | | | | 13 | PX RECEIVE | | 2 | 100K | | | | 14 | PX SEND HASH | :TQ10001 | 2 | 100K | | | | 15 | PX BLOCK ITERATOR ADAPTIVE | | 2 | 100K | | | | 16 | TABLE ACCESS FULL | T2 | 26 | 100K | 27 | 12MB | -- ! | 17 | HASH JOIN | | 2 | 100K | | | | 18 | PART JOIN FILTER CREATE | :BF0001 | 2 | 100K | | | -- ! | 19 | PX RECEIVE | | 2 | 100K | | | | 20 | PX SEND HASH | :TQ10002 | 2 | 100K | | | | 21 | PX BLOCK ITERATOR | | 2 | 100K | | | | 22 | TABLE ACCESS FULL | T1 | 26 | 100K | | | | 23 | PX RECEIVE | | 2 | 100K | | | | 24 | PX SEND HASH | :TQ10003 | 2 | 100K | | | | 25 | PX BLOCK ITERATOR ADAPTIVE | | 2 | 100K | | | | 26 | TABLE ACCESS FULL | T2 | 26 | 100K | 27 | 12MB | -- ! =========================================================================================== Outline Data ------------- EXPAND_TABLE(@"SEL$1" "B"@"SEL$1")
성능 저하을 개선하려면 NO_EXPAND_TABLE 힌트를 사용하거나 UNUSABLE 인덱스 파티션를 REBUILD해야 합니다.
-- 6-1 SELECT /*+ PARALLEL(2) LEADING(A) USE_HASH(B) PQ_DISTRIBUTE(B HASH HASH) CARDINALITY(A 1E7) NO_EXPAND_TABLE(B) */ COUNT (b.c3) FROM t1 a , t2 b WHERE b.c1 = a.c1 AND b.c2 = a.c2; ========================================================================================= | Id | Operation | Name | Execs | Rows | Read | Read | | | | | | (Actual) | Reqs | Bytes | ========================================================================================= | 0 | SELECT STATEMENT | | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | | 2 | PX COORDINATOR | | 5 | 2 | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | 2 | 2 | | | | 4 | SORT AGGREGATE | | 2 | 2 | | | | 5 | HASH JOIN | | 2 | 200K | | | | 6 | PART JOIN FILTER CREATE | :BF0000 | 2 | 200K | | | -- ! | 7 | PX RECEIVE | | 2 | 200K | | | | 8 | PX SEND HASH | :TQ10000 | 2 | 200K | | | | 9 | PX BLOCK ITERATOR | | 2 | 200K | | | | 10 | TABLE ACCESS FULL | T1 | 26 | 200K | | | | 11 | PX RECEIVE | | 2 | 200K | | | | 12 | PX SEND HASH | :TQ10001 | 2 | 200K | | | | 13 | PX BLOCK ITERATOR ADAPTIVE | | 2 | 200K | | | | 14 | TABLE ACCESS FULL | T2 | 26 | 200K | 28 | 25MB | -- ! ========================================================================================= -- 6-2 ALTER INDEX t2_x1 REBUILD PARTITION p8 NOLOGGING;