문서유형ㅣ장애해결
분야ㅣ튜닝
적용제품버전ㅣ6FS06, 6FS07, 6FS07PS, 7FS01, 7FS02, 7FS02PS
문서번호ㅣTTUTS007
현상
다중행 서브쿼리에 or 연산자를 2개 이상 사용하는 쿼리를 수행하여 성능 저하가 발생하였습니다.
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)
원인
_TRANS_UNNEST_SUBQUERY_NONE_EQUAL_EXPN파라미터에 의해 = 이외의 조건으로 서브 쿼리 사용 시, unnest 되지 않아 CACHE 아래 부분이 Row수 만큼 실행되어 성능 저하가 발생하였습니다.해결
_TRANS_UNNEST_SUBQUERY_NONE_EQUAL_EXPN 파라미터 값을 Y로 변경하여 해결합니다.(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)참고
회피책으로서, unnest 힌트를 사용하여 join문으로 쿼리 변환을 유도하여 성능 저하 문제를 해결할 수 있습니다.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)