Document Type | Troubleshooting
Category | Administration
Applicable Product Version | 7FS02
Document Number | TADTS032
Issue
When using a ROWNUM condition in the WHERE clause, the PARALLEL hint is not applied.
In this situation, it is necessary to check if there is a patch or parameter that can be set to enable parallel processing of the ROWNUM condition.
Example of Applying PARALLEL Hint Including ROWNUM Condition in WHERE Clause
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)Example of Applying PARALLEL Hint Without ROWNUM Condition in WHERE Clause
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)
Cause
Currently, in Tibero, to perform parallel processing on queries that include a ROWNUM condition, the parameter _USE_PARALLEL_EXECUTION_BELOW_ROWNUM must be set to Y.
Solutions
Set the parameter using the following command.
alter [session|system] set _USE_PARALLEL_EXECUTION_BELOW_ROWNUM = y;
After applying this in the tip file, restart the database.
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)