Optimizing Oracle Optimizer (2009년)
Non-Mergeable Views
- Oracle은 가능한 View Merging을 시도한다. 하지만 항상 성공하는 것은 아니다.
- Oracle Menual에서는 다음과 같은 View들은 Non-Mergeable Views, 즉 View Merging이 불가능한 View로 정의하고 있다.
- Set operatior (UNION, UNION ALL, INTERSECT, MINUS)
- A CONNECT BY clause
- A ROWNUM pseudo column
- Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in ther select list
- 네번째의 경우, 실제로는 Merging이 성공할 때도 있고 실패할 때도 있다.
- 이외에도 Cursor Expression이 Main Query 에 사용된 경우, Analytic Function이 View에서 사용된 경우에도 View Merging이 이루어지지 않는다.
- Set Operation이 있는 경우
select /*+ gather_plan_statistics */
t1.c1, v.c2
from
t1,
(select c1, c2, c3 from t2
union all
select c1, c2, c3 from t3) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 11 | 12 (9)| 11 |00:00:00.01 | 34 | 1095K| 1095K| 1048K (0)|
| 2 | VIEW | | 1 | 11 | 4 (0)| 11 |00:00:00.01 | 9 | | | |
| 3 | UNION-ALL PARTITION | | 1 | | | 11 |00:00:00.01 | 9 | | | |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | T3_N2 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 | | | |
| 7 | INDEX FAST FULL SCAN | T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.04 | 25 | | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
4 - filter("C3"=1)
6 - access("C3"=1)
==> Hash Join으로 풀림(책과 다름)
- Oracle은 View Merging이 실패하는 경우에는 Predicate Pushing을 시도한다.
- v.c3 = 1 조건이 Inline View안으로 삽입되어 t3에 대해서는 Index Range Scan으로 나타난다.
- Aggregate Function이 사용된 Inline View의 경우
select /*+ gather_plan_statistics */
t1.c1, v.c2
from
t1,
(select c1, max(c2) as c2, max(c3) as c3
from t2
group by c1) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 10 | 12 (17)| 10 |00:00:00.01 | 32 | 1095K| 1095K| 1063K (0)|
| 2 | VIEW | | 1 | 10 | 4 (25)| 10 |00:00:00.01 | 7 | | | |
|* 3 | FILTER | | 1 | | | 10 |00:00:00.01 | 7 | | | |
| 4 | HASH GROUP BY | | 1 | 10 | 4 (25)| 1000 |00:00:00.01 | 7 | 873K| 873K| 1230K (0)|
| 5 | TABLE ACCESS FULL| T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 6 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.03 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
3 - filter(MAX("C3")=1)
==> Hash Join 으로 풀림(책과 다름)
- View Merging은 이루어지지 않았지만 v.c3 = 1 조건이 View안으로 Pushing 되어(3번 단계) 비효율성이 상당히 감소된 것을 알 수 있다.
- ROWNUM Operation이 사용된 경우
select /*+ gather_plan_statistics */
t1.c1, v.c2
from
t1,
(select rownum as r, c1, c2, c3
from t2) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1000 | 11 (10)| 10 |00:00:00.01 | 32 | 1095K| 1095K| 1102K (0)|
|* 2 | VIEW | | 1 | 1000 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
| 3 | COUNT | | 1 | | | 1000 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.03 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
2 - filter("V"."C3"=1)
- v.c3 = 1 조건이 View 안으로 Pushing 되지 못하고 View의 바깥에서 동작한다.
- ROWNUM 연산자에 의해 View Merging 뿐만 아니라 Predicate Pushing 또한 실패하기 때문이다.
- ROWNUM을 Subquery나 View안에서 함부로 사용해서는 않되지만 이런 속성을 이용해 일부러 불필요한 ROWNUM을 사용하기도 한다.
- Cursor Expression은 Multi Row를 Return하는 Subquery를 Select List에서 사용할 수 있도록 해주는 강력한 기능이다.
- 하지만 Fetch회수를 늘린다는 성능상의 단점이 존재한다. View Merging을 지원하지 않는다. (상위 버젼으로 가면서 지원하기도 한다. 공식 문서 없음.)
select /*+ gather_plan_statistics */
t1.c1, v.c2,
cursor(select * from t3 where t3.c1 = t1.c1) as csr
from
t1,
(select c1, c2, c3
from t2) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 0 | 1 | 2 (0)| 0 |00:00:00.01 | 0 | | | |
|* 2 | INDEX RANGE SCAN | T3_N1 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 | | | |
|* 3 | HASH JOIN | | 1 | 10 | 11 (10)| 10 |00:00:00.01 | 36 | 1095K| 1095K| 1079K (0)|
| 4 | VIEW | | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
|* 5 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
| 6 | INDEX FAST FULL SCAN | T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.03 | 29 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."C1"=:B1)
3 - access("T1"."C1"="V"."C1")
5 - filter("C3"=1)
==> Hash Join으로 풀림(책과 다름)
- Analytic Function이 사용된 경우
select /*+ gather_plan_statistics */
t1.c1, v.*
from
t1,
(select row_number() over (order by c1) as rn,
c1, c2, c3
from t2) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1000 | 12 (17)| 10 |00:00:00.01 | 32 | 980K| 980K| 1067K (0)|
|* 2 | VIEW | | 1 | 1000 | 4 (25)| 10 |00:00:00.01 | 7 | | | |
| 3 | WINDOW SORT | | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | 43008 | 43008 |38912 (0)|
| 4 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.03 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
2 - filter("V"."C3"=1)
- Aggregate Function이 사용된 경우 View Merging이 일어나는 경우와 실패하는 경우
- 실패
select /*+ gather_plan_statistics */
t1.c1, v.c2
from
t1,
(select c1, c3, count(*) as c2
from t2
group by c1, c3) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 8 | 12 (17)| 10 |00:00:00.01 | 32 | 1306K| 1306K| 1066K (0)|
| 2 | VIEW | | 1 | 8 | 4 (25)| 10 |00:00:00.01 | 7 | | | |
| 3 | HASH GROUP BY | | 1 | 8 | 4 (25)| 10 |00:00:00.01 | 7 | 1049K| 1049K| 959K (0)|
|* 4 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
| 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.03 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
4 - filter("C3"=1)
- 성공
select /*+ gather_plan_statistics */
t1.c1, v.c2
from
t1,
(select c1, c3, count(*) as c2
from t2
group by c1, c3) v
where
t1.c1 = v.c1 and
t1.c3 = 1
;
@stat
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 100 | 16 (13)| 10 |00:00:00.01 | 45 | 904K| 904K| 976K (0)|
|* 2 | HASH JOIN | | 1 | 100 | 15 (7)| 10 |00:00:00.01 | 45 | 1095K| 1095K| 1169K (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 100 | 11 (0)| 100 |00:00:00.01 | 38 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="C1")
3 - filter("T1"."C3"=1)
- 두 Query의 차이는 View Merging이 이루어지지 않는 경우에는 v.c3 = 1조건이, View Merging이 이루어지는 경우는 t1.c3 = 1 조건이 사용된다는 것이다.
- View Merging이 실패한 Query에 대해 10053 Event에 의해 생성된 Trace File을 이용하면 Heuristics 에 의해, 더 정확하게 표현하면 CBO의 Source Code에서 사용하는 일종의 규칙(Rule, Heuristics)에 의해 View Merging이 비효율적일 것으로 판단했기 때문에 View Merging을 수행하지 않겠다는 것이다.
- 왜 Optimizer가 이런 판단을 했느지는 정확하게 알 수 없다. 역으로 추론해 볼 수 있는 것은 View Merging이 원천적으로 불가능한 것이 아니라 Optimizer의 어떤 판단에 의해 View Merging이 이루어지지 않았다는 것이다.
- MERGE Hint를 강제로 부여하면 성공적으로 View Merging이 이루어 진다.
select /*+ gather_plan_statistics */
t1.c1, v.c2
from
t1,
(select /*+ merge */ c1, c3, count(*) as c2
from t2
group by c1, c3) v
where
t1.c1 = v.c1 and
v.c3 = 1
;
@stat
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 10 | 12 (17)| 10 |00:00:00.01 | 31 | 904K| 904K| 976K (0)|
|* 2 | HASH JOIN | | 1 | 10 | 11 (10)| 10 |00:00:00.01 | 31 | 1306K| 1306K| 1046K (0)|
|* 3 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 7 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.04 | 24 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="C1")
3 - filter("C3"=1)
==> Hash Join으로 풀림(책과 다름)
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3879
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.