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;