select count(*)
from
(
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select rownum from t2)
)
;
@stat
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 31 | | | |
|* 2 | HASH JOIN | | 1 | 1000 | 12 (17)| 1000 |00:00:00.02 | 31 | 1517K| 1517K| 1201K (0)|
| 3 | VIEW | VW_NSO_1 | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | | | |
| 4 | HASH UNIQUE | | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | 1453K| 1453K| 1226K (0)|
| 5 | COUNT | | 1 | | | 1000 |00:00:00.01 | 7 | | | |
| 6 | TABLE ACCESS FULL| T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 7 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.03 | 24 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="$nso_col_1")
ROWNUM이 포함된 Subquery는 Main Query안으로 바로 Unnesting 하는 것이 불가능하다.
이경우 Oracle은 Subquery를 View로 변환하고 가능한 Filter Operation을 회피하게끔 실행계획을 수립한다.
-- Filter Operation
select count(*)
from
(
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select /*+ no_unnest */ rownum from t2)
)
;
@stat
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:07.92 | 68883 |
|* 2 | FILTER | | 1 | | | 1000 |00:00:00.62 | 68883 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.04 | 38 |
|* 4 | FILTER | | 10000 | | | 1000 |00:00:07.85 | 68845 |
| 5 | COUNT | | 10000 | | | 9500K|00:01:35.29 | 68845 |
| 6 | TABLE ACCESS FULL| T2 | 10000 | 1000 | 3 (0)| 9500K|00:00:38.50 | 68845 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NOT NULL)
4 - filter(ROWNUM=:B1)
동일한 결과를 얻기 위해 많은 Logical Reads가 발생한다.
UNION ALL 과 같은 Operation에서도 동일한 현상이 발생한다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select c1 from t2
union all
select c1 from t3)
;
@stat
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1101K| 23 (35)| 1000 |00:00:00.04 | 119 | 6 | 1517K| 1517K| 1469K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 1100 | 4 (0)| 1000 |00:00:00.04 | 14 | 6 | | | |
| 3 | HASH UNIQUE | | 1 | | | 1000 |00:00:00.03 | 14 | 6 | 1453K| 1453K| 1229K (0)|
| 4 | UNION-ALL | | 1 | | | 1100 |00:00:00.02 | 14 | 6 | | | |
| 5 | TABLE ACCESS FULL| T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | 0 | | | |
| 6 | TABLE ACCESS FULL| T3 | 1 | 100 | 3 (0)| 100 |00:00:00.02 | 7 | 6 | | | |
| 7 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.03 | 105 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="$nso_col_1")
Subquery가 View로 전환되고, View에 대한 Hash Join이 수행되는 방식의 Unnesting 이루어 진다.
Unnesting이 발생하지 않는 Subquery를 View로 전환하는 것은 매우 효과적인 접근방법이다.
View에 대해 적용 가능한 다른 최적화 기법을 추가적으로 사용할 수 있기 때문이다.
select /*+ gather_plan_statistics */
t1.c1, t1.c2
from
t1
where
t1.c1 in (select c1 from t2
union all
select c1 from t3)
and t1.c1 between 1 and 100
;
@stat
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 6 (17)| 100 |00:00:00.04 | 20 | 1 | 1517K| 1517K| 1197K (0)|
| 2 | VIEW | VW_NSO_1 | 1 | 3 | 2 (0)| 100 |00:00:00.04 | 3 | 1 | | | |
| 3 | HASH UNIQUE | | 1 | | | 100 |00:00:00.04 | 3 | 1 | 1518K| 1518K| 1229K (0)|
| 4 | UNION-ALL PARTITION | | 1 | | | 200 |00:00:00.01 | 3 | 1 | | | |
|* 5 | INDEX RANGE SCAN | T2_N1 | 1 | 100 | 2 (0)| 100 |00:00:00.01 | 2 | 0 | | | |
|* 6 | INDEX RANGE SCAN | T3_N1 | 1 | 100 | 1 (0)| 100 |00:00:00.03 | 1 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 3 (0)| 100 |00:00:00.01 | 17 | 0 | | | |
|* 8 | INDEX RANGE SCAN | T1_N1 | 1 | 100 | 2 (0)| 100 |00:00:00.01 | 9 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="$nso_col_1")
5 - access("C1">=1 AND "C1"<=100)
6 - access("C1">=1 AND "C1"<=100)
8 - access("T1"."C1">=1 AND "T1"."C1"<=100)
UNION ALL과 함께 Subquery의 바깥에서 t1.c1 between 1 and 100 조건이 부여되었다. 이 조건(Predicate)은 View 안으로 Push 될 수 있다.
Predicate Pushing 이라는 이름의 Transformation 기법니다.
5, 6번 단계에서 조건이 view안으로 Pushing 되었다는 것을 알 수 있다.
덕분에 Table Full Scan 대신 Index Range Scan이 선택되었다.
Oracle 10g부터 Aggregate Subquery Elimination이라는 이름의 새로운 Transformation 기법을 소개했다.
Aggregate Function을 포함한 Subquery를 (가능한)없애겠다는 것이다. 즉 Main Query 안으로 넣어 버리겠다는 것이다.
_REMOVE_AGGR_SUBQUERY Parameter로 제어하며 기본값은 True이다.
select /*+ gather_plan_statistics
opt_param('_remove_aggr_subquery','false') */
t1.c1, t2.c2
from
t1, t2
where
t1.c1 = t2.c1 and
t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1)
;
@stat
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1000 | 15 (14)| 1000 |00:00:00.04 | 105 | 1000K| 1000K| 1226K (0)|
| 2 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
|* 3 | HASH JOIN | | 1 | 1000 | 12 (17)| 1000 |00:00:00.03 | 98 | 1095K| 1095K| 1210K (0)|
| 4 | VIEW | VW_SQ_1 | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | | | |
| 5 | HASH GROUP BY | | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | 1049K| 1049K| 1189K (0)|
| 6 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 7 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.04 | 91 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1" AND "T2"."C2"="VW_COL_1")
3 - access("C1"="T1"."C1")
Table t2를 두 번 Access하는 것을 알 수 있다.(Subquery에서 한번, Join에서 한번)
select /*+ gather_plan_statistics
opt_param('_remove_aggr_subquery','true') */
t1.c1, t2.c2
from
t1, t2
where
t1.c1 = t2.c1 and
t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1)
;
@stat
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | VIEW | VW_WIF_1 | 1 | 1000 | 37000 | 12 (17)| 00:00:01 | 1000 |00:00:00.02 | 31 | | | |
| 2 | WINDOW SORT | | 1 | 1000 | 21000 | 12 (17)| 00:00:01 | 1000 |00:00:00.01 | 31 | 59392 | 59392 |53248 (0)|
|* 3 | HASH JOIN | | 1 | 1000 | 21000 | 11 (10)| 00:00:01 | 1000 |00:00:00.02 | 31 | 955K| 955K| 1177K (0)|
| 4 | TABLE ACCESS FULL | T2 | 1 | 1000 | 17000 | 3 (0)| 00:00:01 | 1000 |00:00:00.01 | 7 | | | |
| 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 40000 | 7 (0)| 00:00:01 | 10000 |00:00:00.04 | 24 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_3" IS NOT NULL)
3 - access("T1"."C1"="T2"."C1")
Table t2에 대한 Access가 한번으로 줄어들고 Cost, Logical Reads도 줄어든다.
한가지 이상한 변화는 WINDOW SORT Operation이 추가적으로 관찰된다는 것이다.
(WINDOW SORT Operation 은 Analytic Function을 사용한 경우에 주로 나타난다.)
DBMS_XPLAN.DISPLAY_CURSOR Function이 호출시 ALL Option을 지정하면 Column Projection정보가 추가로 출력된다.
Column Projection이란 실행 계획의 각 단계에서 어떤 Column 값을 사용하는지를 의미 한다.
select * from
table(dbms_xplan.display_cursor(null, null, 'all allstats cost last'));
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[NUMBER,22], "C2"[CHARACTER,10], "VW_COL_3"[ROWID,10]
2 - (#keys=1) "T2"."C1"[NUMBER,22], "T2"."C2"[CHARACTER,10], "T1"."C1"[NUMBER,22], ROWID[ROWID,10], MAX("C2") OVER ( PARTITION BY
"S"."C1")[10]
3 - (#keys=1) "T2"."C1"[NUMBER,22], "T1"."C1"[NUMBER,22], ROWID[ROWID,10], "T2"."C2"[CHARACTER,10]
4 - ROWID[ROWID,10], "T2"."C1"[NUMBER,22], "T2"."C2"[CHARACTER,10]
5 - "T1"."C1"[NUMBER,22]
2번 단께에서 원래 Query 에는 없던 MAX(C2) OVER(PARTITION BY S.C1) Operation이 추가된 것을 확인할 수 있다.
Subquery에 있던 MAX(C2) Column이 Subquery Unnesting과 함께 Main Query안으로 들어간 것으로 해석할 수 있다(Query Tansformation).
Oracle의 Optimizer가 점점 개선됨에 따라 새로운 유형의 Transformation 기법이 계속해서 추가된다.
이런 변화를 감지하고 대응하는 유일한 방법은 DBMS_XPLAN Package를 이용해서 실행 계획 상의 모든 변화를 잘 관찰한는 것이다.
Row Source Operation의 변화, Predicate Information과 Column Projection Information, Outline 변화까지 관찰해야만 완벽한 해석이 가능하다.