Document Type | Troubleshooting
Category | Tuning
Applicable Product Versions | 6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS
Document Number | TTUTS007
Issue
Performance degradation occurred when executing a query using two or more OR operators in a multi-row subquery.
create table t1(col1 number,col2 varchar(20),col3 varchar(14));
insert into t1 select level,level||'test',to_char(sysdate,'yyyymmddhh24miss') from dual connect by level<100000;
commit;
create table t2 as select * from t1;
set autot on;
SQL> select * from t1 where col1 in ( select col1 from t2 where col1 = 38311) or col1 in (select col1 from t2 where col1 = 38322 );
COL1 COL2 COL3
38311 38311test 20250103112201
38322 38322test 20250103112201
2 rows selected.
Total elapsed time 00:15:44.181014
SQL ID: 1dzysntrpsvwf
Child number: 121
Plan hash value: 4291167329
Execution Plan
1 FILTER (Cost:27646914, %%CPU:2, Rows:139632)
2 TABLE ACCESS (FULL): T1 (Cost:407, %%CPU:0, Rows:139632)
3 CACHE (Cost:215, %%CPU:2, Rows:0)
4 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:215, %%CPU:2, Rows:1)
5 TABLE ACCESS (FULL): T2 (Cost:215, %%CPU:2, Rows:1)
Predicate Information
1 - filter: EXISTS ( SELECT COL1 FROM T2 WHERE COL1 = 38311) (1.000)
4 - filter: (ROWNUM = 1) (0.010)
5 - filter: (((:0 = "T2"."COL1") AND ("T2"."COL1" = 38322)) OR ((:0 = "T2"."COL1") AND ("T2"."COL1" = 38311))) (0.000)
Cause
_TRANS_UNNEST_SUBQUERY_NONE_EQUAL_EXPN parameter causes performance degradation because when subqueries use conditions other than =, they are not unnested, resulting in the part under CACHE being executed as many times as the number of rows.Solutions
_TRANS_UNNEST_SUBQUERY_NONE_EQUAL_EXPN parameter value can be changed to Y to resolve the issue. (default: N)alter session set _TRANS_UNNEST_SUBQUERY_NONE_EQUAL_EXPN=Y;
SQL> select * from t1 where col1 in ( select col1 from t2 where col1 = 38311) or col1 in (select col1 from t2 where col1 = 38322 );
COL1 COL2 COL3
38311 38311test 20250103112201
38322 38322test 20250103112201
2 rows selected.
Total elapsed time 00:00:00.092840
SQL ID: 1dzysntrpsvwf
Child number: 119
Plan hash value: 2803296130
Execution Plan
1 NESTED LOOPS (SEMI) (Cost:29980247, %%CPU:1, Rows:1)
2 TABLE ACCESS (FULL): T1 (Cost:407, %%CPU:0, Rows:139632)
3 TABLE ACCESS (FULL): T2 (Cost:214, %%CPU:1, Rows:2)
Predicate Information
1 - access: ((("T1"."COL1" = "T2"."COL1") AND ("T2"."COL1" = 38322)) OR (("T1"."COL1" = "T2"."COL1") AND ("T2"."COL1" = 38311))) (0.000)
3 - filter: (("T2"."COL1" = 38322) OR ("T2"."COL1" = 38311)) (0.000)Note
As a workaround, the performance degradation issue can be resolved by using the unnest hint to induce query transformation into a join statement.SQL> select * from t1 where col1 in ( select /*+ unnest */ col1 from t2 where col1 = 38311) or col1 in (select /*+ unnest */ col1 from t2 where col1 = 38322 ); COL1 COL2 COL3 38311 38311test 20250103112201 38322 38322test 20250103112201 2 rows selected. Total elapsed time 00:00:00.025586 SQL ID: bh61u053fprmj Child number: 216 Plan hash value: 2803296130 Execution Plan 1 NESTED LOOPS (SEMI) (Cost:29980247, %%CPU:1, Rows:1) 2 TABLE ACCESS (FULL): T1 (Cost:407, %%CPU:0, Rows:139632) 3 TABLE ACCESS (FULL): T2 (Cost:214, %%CPU:1, Rows:2) Predicate Information 1 - access: ((("T1"."COL1" = "T2"."COL1") AND ("T2"."COL1" = 38322)) OR (("T1"."COL1" = "T2"."COL1") AND ("T2"."COL1" = 38311))) (0.000) 3 - filter: (("T2"."COL1" = 38322) OR ("T2"."COL1" = 38311)) (0.000)