현상
WHERE 절에 ROWNUM 조건을 사용하는 경우 PARALLEL 힌트가 적용되지 않는 현상이 발생했습니다.
이러한 상황에서 ROWNUM 조건을 병렬로 처리하기 위해 필요한 패치나 설정할 수 있는 파라미터가 있는지 확인해야 합니다.
WHERE 절에 ROWNUM 조건을 포함한 PARALLEL 힌트 적용 예시
SQL> create table test (a1 number);
/
BEGIN
FOR i IN 1..30 LOOP
INSERT INTO TEST (a1) VALUES (i);
END LOOP;
COMMIT;
END;
/
SQL> set autot trace exp plans stat
SQL> select /*+ PARALLEL(4) */ rownum rn, 2*rownum, a1, 2*a1 from TEST where rownum <10;
SQL ID: 9r091hcj7cfv0
Child number: 76
Plan hash value: 785533538
Execution Plan
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 10) (Cost:12, %%CPU:0, Rows:9)
2 TABLE ACCESS (FULL): TEST (Cost:12, %%CPU:0, Rows:4765)
NAME VALUE
------------------------------ ----------
db block gets 1
consistent gets 14
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 9
Execution Stat
--------------------------------------------------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 10) (Time:0. ms, Rows:0, Starts:0)
2 TABLE ACCESS (FULL): TEST (Time:0. ms, Rows:0, Starts:0)
SQL
복사
WHERE 절에 ROWNUM 조건 없이 PARALLEL 힌트 적용 예시
SQL> select /*+ PARALLEL(8) */ rownum rn, a1 from TEST where a1 <4;
SQL ID: 9372pjkb4kwnw
Child number: 87
Plan hash value: 610155294
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:12, %%CPU:0, Rows:2)
2 PE MANAGER (Cost:0, %%CPU:0, Rows:2)
3 PE SEND QC (RANDOM) (Cost:0, %%CPU:0, Rows:2)
4 FILTER (Cost:12, %%CPU:0, Rows:2)
5 PE BLOCK ITERATOR (Cost:12, %%CPU:0, Rows:30)
6 TABLE ACCESS (FULL): TEST (Cost:12, %%CPU:0, Rows:30)
Predicate Information
--------------------------------------------------------------------------------
4 - filter: ("TEST"."A1" < 4) (0.094)
Note
--------------------------------------------------------------------------------
6 - dynamic sampling used for this table (13 blocks)
NAME VALUE
------------------------------ ----------
db block gets 147
consistent gets 31
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 1
rows processed 3
Execution Stat
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Time:0. ms, Rows:0, Starts:0)
2 PE MANAGER (Time:0. ms, Rows:0, Starts:0)
3 PE SEND QC (RANDOM) (Time:0. ms, Rows:0, Starts:0)
4 FILTER (Time:0. ms, Rows:0, Starts:0)
5 PE BLOCK ITERATOR (Time:0. ms, Rows:0, Starts:0)
6 TABLE ACCESS (FULL): TEST (Time:0. ms, Rows:0, Starts:0)
SQL
복사
원인
현재 티베로에서는 ROWNUM 조건이 포함된 쿼리에 대해 병렬 처리를 수행하려면, _USE_PARALLEL_EXECUTION_BELOW_ROWNUM 파라미터를 Y로 설정해야 합니다.
해결
아래 명령어를 통해 파라미터를 설정합니다.
alter [session|system] set _USE_PARALLEL_EXECUTION_BELOW_ROWNUM = y;
SQL
복사
tip 파일에 반영한 후 데이터베이스를 재기동합니다.
SQL> alter session set _USE_PARALLEL_EXECUTION_BELOW_ROWNUM=y;
SQL> set autot trace exp plans stat
SQL> select /*+ PARALLEL(4) */ rownum rn, 2*rownum, a1, 2*a1 from TEST where rownum <10;
M 2*ROWNUM A1 2*A1
---------- ---------- ---------- ----------
1 2 1 2
2 4 2 4
3 6 3 6
4 8 4 8
5 10 5 10
6 12 6 12
7 14 7 14
8 16 8 16
9 18 9 18
SQL ID: f5k89jj7qxx5s
Child number: 135
Plan hash value: 2388666527
Execution Plan
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Cost:12, %%CPU:0, Rows:9)
2 COUNT (STOP NODE) (STOP LIMIT 10) (Cost:12, %%CPU:0, Rows:9)
3 PE MANAGER (Cost:0, %%CPU:0, Rows:4765)
4 PE SEND QC (RANDOM) (Cost:0, %%CPU:0, Rows:4765)
5 PE BLOCK ITERATOR (Cost:12, %%CPU:0, Rows:4765)
6 TABLE ACCESS (FULL): TEST (Cost:12, %%CPU:0, Rows:4765)
NAME VALUE
------------------------------ ----------
db block gets 1
consistent gets 15
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 9
Execution Stat
--------------------------------------------------------------------------------
1 COLUMN PROJECTION (Time:0. ms, Rows:0, Starts:0)
2 COUNT (STOP NODE) (STOP LIMIT 10) (Time:0. ms, Rows:0, Starts:0)
3 PE MANAGER (Time:0. ms, Rows:0, Starts:0)
4 PE SEND QC (RANDOM) (Time:0. ms, Rows:0, Starts:0)
5 PE BLOCK ITERATOR (Time:0. ms, Rows:0, Starts:0)
6 TABLE ACCESS (FULL): TEST (Time:0. ms, Rows:0, Starts:0)
SQL
복사