Optimizing Oracle Optimizer (2011년)
Non-Mergeable views
- Oracle 은 가능한 View Merging 을 시도한다.
- 하지만 항상 성공하는 것은 아니다.
- Oracle Manual 에서는 다음과 같은 View 들을 Non-Mergeable Views, 즉 View Merging 이 불가능한 View 로 정의하고 있다.
- Set operators (UNION, UNION ALL, INTERSECT, MINUS)
- A CONNECT BY clause
- A ROWNUM pseudo column
- Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list
- 하지만 실제로 확인을 해보면 네 번째 경우, 즉 Aggregate Function 이 사용된 경우에는 Merging 에 성공할 때도 있고 실패할 때도 있다.
- 그 외에도 Cursor Expression 이 Main Query 에 사용된 경우에도 View Merging 이 이루어지지 않는다.
- 또한 Analytic Function 이 View 에 서 사용된 경우에도 View Merging 이 이루어 지지 않는다.
- 간단한 예제를 통해 View Merging 이 이루어지지 않는 경우들을 확인해 보자.
- View 내에 Set Operation 이 있는 경우에는 View Merging 이 이루어지지 않는다.
-- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
-- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2
3 FROM
4 t1,
5 (SELECT c1, c2, c3 FROM t2
6 UNION ALL
7 SELECT c1, c2, c3 FROM t3) v
8 WHERE
9 t1.c1 = v.c1 AND
10 v.c3 = 1
11 ;
1 dummy
1 dummy
101 dummy
201 dummy
301 dummy
401 dummy
501 dummy
601 dummy
701 dummy
801 dummy
901 dummy
11 개의 행이 선택되었습니다.
- 아래 실행 계획을 보면 2번 단계에서 View Operation 이 사용된 것을 알 수 있다.
- View Merging 이 이루어지지 않은 것이다.
- 하지만 생각보다는 실행 계획이 훨씬 양호하다는 것을 알게 될 것이다.
- View Merging 이 이루어지지 않았다면 Table t2 와 Table t3 에 대한 Table Full Scan 이 발생했을 것이다.
- 하지만 실제로는 성공적으로 Table t3 에 대해서는 Index Range Scan 이 성공적으로 수행된다.
- Oracle 은 View Merging 이 실패하는 경우에는 Predicate Pushing 을 시도한다.
- 그 덕분에 v.c3 = 1 조건이 Inline View 안으로 삽입되며, 아래 결과에서는 Index Range Scan 으로 나타난다.
- 4번 단계와 5번 단계의 Predicate 를 유의해서 관찰하기 바란다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID akgr7bafd1kvp, child number 0
-------------------------------------
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
-- Oracle 11g
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| 11 |00:00:00.02 | 35 | | | |
|* 1 | HASH JOIN | | 1 | 11 | 12 (9)| 11 |00:00:00.02 | 35 | 870K| 870K| 1082K (0)|
| 2 | VIEW | | 1 | 11 | 4 (0)| 11 |00:00:00.01 | 9 | | | |
| 3 | UNION-ALL | | 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.01 | 26 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
4 - filter("C3"=1)
6 - access("C3"=1)
-- Oracle 10g
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 11 | 10 (10)| 11 |00:00:00.01 | 33 | 870K| 870K| 1015K (0)|
| 2 | VIEW | | 1 | 11 | 3 (0)| 11 |00:00:00.01 | 8 | | | |
| 3 | UNION-ALL PARTITION | | 1 | | | 11 |00:00:00.01 | 8 | | | |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 6 | | | |
| 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 | 6 (0)| 10000 |00:00:00.01 | 25 | | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
4 - filter("C3"=1)
6 - access("C3"=1)
-- 1 교재에서는 NESTED LOOPS 사용
-- 7 교재에서는 INDEX RANGE SCAN 사용
- 아래 예와 같이 복잡한 Aggregate Function 이 사용된 Inline View 는 View Merging 이 이루어지지 않는다.
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2
3 FROM
4 t1,
5 (SELECT c1, MAX(c2) AS c2, MAX(c3) AS c3
6 FROM t2
7 GROUP BY c1) v
8 WHERE
9 t1.c1 = v.c1 AND
10 v.c3 = 1
11 ;
1 dummy
101 dummy
201 dummy
301 dummy
401 dummy
501 dummy
601 dummy
701 dummy
801 dummy
901 dummy
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID g7b17adzrfpjf, child number 0
-------------------------------------
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
-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| 10 |00:00:00.02 | 33 | | | |
|* 1 | HASH JOIN | | 1 | 10 | 12 (17)| 10 |00:00:00.02 | 33 | 870K| 870K| 1076K (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 | 766K| 766K| 1253K (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.01 | 26 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
3 - filter(MAX("C3")=1)
-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 10 | 11 (19)| 10 |00:00:00.01 | 31 | 870K| 870K| 1023K (0)|
| 2 | VIEW | | 1 | 10 | 4 (25)| 10 |00:00:00.01 | 6 | | | |
|* 3 | FILTER | | 1 | | | 10 |00:00:00.01 | 6 | | | |
| 4 | HASH GROUP BY | | 1 | 10 | 4 (25)| 1000 |00:00:00.01 | 6 | | | |
| 5 | TABLE ACCESS FULL| T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 6 | | | |
| 6 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 6 (0)| 10000 |00:00:00.01 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
3 - filter(MAX("C3")=1)
-- 1 교재에서는 NESTED LOOPS 사용
-- 6 교재에서는 INDEX RANGE SCAN 사용
- 위의 Query 에서 비록 View Merging 은 이루어지지 않았지만 v.c3 = 1 조건이 View 안으로 Pushing 되어(3번 단계의 Predicate 참조), 비효율성이 상당히 감소된 것을 알 수 있다.
- View Merging 과 Predicate Pushing 을 항상 같이 관찰해야 실행 계획을 정확하게 해석할 수 있다.
- ROWNUM Operation 이 사용된 View 또한 Merging 이 이루어지지 않는다.
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2
3 FROM
4 t1,
5 (SELECT ROWNUM AS r, c1, c2, c3
6 FROM t2) v
7 WHERE
8 t1.c1 = v.c1 AND
9 v.c3 = 1
10 ;
1 dummy
101 dummy
201 dummy
301 dummy
401 dummy
501 dummy
601 dummy
701 dummy
801 dummy
901 dummy
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID 8h6g72wb5rgyy, child number 0
-------------------------------------
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
-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| 10 |00:00:00.02 | 33 | | | |
|* 1 | HASH JOIN | | 1 | 1000 | 11 (10)| 10 |00:00:00.02 | 33 | 870K| 870K| 1082K (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.01 | 26 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
2 - filter("V"."C3"=1)
-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1000 | 10 (10)| 10 |00:00:00.01 | 31 | 870K| 870K| 1017K (0)|
|* 2 | VIEW | | 1 | 1000 | 3 (0)| 10 |00:00:00.01 | 6 | | | |
| 3 | COUNT | | 1 | | | 1000 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 6 | | | |
| 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 6 (0)| 10000 |00:00:00.01 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
2 - filter("V"."C3"=1)
- 위의 실행 계획을 보면 Aggregate Function 에 의해 View Merging 이 실패한 경우와는 또다른 형태를 보인다는 것을 알 수 있다.
- v.c3 = 1 조건이 View 안으로 Pushing 되지 못하고 View 의 바깥에서 동작한다.
- ROWNUM 연산자에 의해 View Merging 뿐만 아니라 Predicate Pushing 또한 실패하기 때문이다.
- ROWNUM 을 Subquery 나 View 안에서 함부로 사용해서는 안 되는 이유이다.
- 재미있는 것은 ROWNUM 의 이런 속성을 이용해 일부러 불필요한 ROWNUM 을 사용하기도 한다는 것이다.
- ROWNUM 의 속성을 잘 이해할 때 사용할 수 있는 Trick 이라고 할 수 있다.
- Cursor Expression 은 Multi Row 를 Return 하는 Subquery 를 Select List 에서 사용할 수 있도록 해주는 강력한 기능이다.
- 하지만 Fetch 회수를 늘린다는 성능상의 단점이 존재한다.
- 또 하나의 문제점은 아래 예제와 같이 View Merging 을 지원하지 않는다는 것이다.
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2,
3 CURSOR(SELECT * FROM t3 WHERE t3.c1 = t1.c1) AS cs
4 FROM
5 t1,
6 (SELECT c1, c2, c3
7 FROM t2) v
8 WHERE
9 t1.c1 = v.c1 AND
10 v.c3 = 1
11 ;
1 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
1 dummy 1
101 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
201 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
301 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
401 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
501 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
601 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
701 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
801 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
901 dummy CURSOR STATEMENT : 3
CURSOR STATEMENT : 3
선택된 레코드가 없습니다.
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID 76mxg72avxqyw, child number 0
-------------------------------------
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
-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| 10 |00:00:00.02 | 37 | | | |
| 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.02 | 37 | 870K| 870K| 1055K (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.01 | 30 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."C1"=:B1)
3 - access("T1"."C1"="V"."C1")
5 - filter("C3"=1)
-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------------
| 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 | 10 (10)| 10 |00:00:00.01 | 35 | 870K| 870K| 1017K (0)|
| 4 | VIEW | | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 6 | | | |
|* 5 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 6 | | | |
| 6 | INDEX FAST FULL SCAN | T1_N1 | 1 | 10000 | 6 (0)| 10000 |00:00:00.01 | 29 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T3"."C1"=:B1)
3 - access("T1"."C1"="V"."C1")
5 - filter("C3"=1)
-- 3 교재에서는 NESTED LOOPS 사용
-- 6 교재에서는 INDEX RANGE SCAN 사용
- 위의 결과를 보면 Main Query 에서 Cursor Expression 을 사용한 경우에는 매우 간단한 형태의 View 라고 해도 Merging 이 이루어지지 않는다는 것을 알 수 있다.
- 하지만 다행스럽게도 Predicate Pushing 은 성공적으로 이루어진다.
- Analytic Function 이 사용된 경우에도 View Merging 에 실패한다.
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.*
3 FROM
4 t1,
5 (SELECT ROW_NUMBER() OVER (ORDER BY c1) AS rn,
6 c1, c2, c3
7 FROM t2) v
8 WHERE
9 t1.c1 = v.c1 AND
10 v.c3 = 1
11 ;
1 1 1 dummy 1
101 101 101 dummy 1
201 201 201 dummy 1
301 301 301 dummy 1
401 401 401 dummy 1
501 501 501 dummy 1
601 601 601 dummy 1
701 701 701 dummy 1
801 801 801 dummy 1
901 901 901 dummy 1
10 개의 행이 선택되었습니다.
- 아래 결과를 보면 View Merging 에 실패했음(2번 단계)을 확인할 수 있다.
- 더 안타까운 것은 Predicate Pushing 에도 실패한다는 것이다.
- v.c3 = 1 조건이 View 안으로 Pushing 되지 못하고 2번 단계에서 적용된다.
- 즉, Table t2 에 대해 Analytic Function 이 다 적용된 후에 비로소 c3 = 1 조건이 적용된다는 것이다.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID 2tmvfh1zhn3p8, child number 0
-------------------------------------
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
Plan hash value: 784523758
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| 10 |00:00:00.02 | 33 | | | |
|* 1 | HASH JOIN | | 1 | 1000 | 12 (17)| 10 |00:00:00.02 | 33 | 816K| 816K| 1073K (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 | 38912 | 38912 |34816 (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.01 | 26 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
2 - filter("V"."C3"=1)
- Oracle Manual 은 Aggregate Function 이 사용된 경우에는 View Merging 이 실패한다고 기술하고 있다.
- 하지만 Aggregate Function 이 사용딘 View 가 항상 Merging 에 실패하는 것은 아니다.
- 아래와 같은 Query 는 View Merging 이 수행되지 않는다.
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2
3 FROM
4 t1,
5 (SELECT c1, c3, COUNT(*) AS c2
6 FROM t2
7 GROUP BY c1, c3) v
8 WHERE
9 t1.c1 = v.c1 AND
10 v.c3 = 1
11 ;
1 1
101 1
201 1
301 1
401 1
501 1
601 1
701 1
801 1
901 1
10 개의 행이 선택되었습니다.
-- Need Check --
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID 1j3wzw9a0nz11, child number 0
-------------------------------------
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
-- Oracle 11g
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| 10 |00:00:00.02 | 33 | | | |
|* 1 | HASH JOIN | | 1 | 8 | 12 (17)| 10 |00:00:00.02 | 33 | 968K| 968K| 1082K (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 | 848K| 848K| 1011K (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.01 | 26 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
4 - filter("C3"=1)
-- Oracle 10g
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 10 | 11 (19)| 10 |00:00:00.01 | 31 | 968K| 968K| 1016K (0)|
| 2 | VIEW | | 1 | 10 | 4 (25)| 10 |00:00:00.01 | 6 | | | |
| 3 | HASH GROUP BY | | 1 | 10 | 4 (25)| 10 |00:00:00.01 | 6 | | | |
|* 4 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 6 | | | |
| 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 6 (0)| 10000 |00:00:00.01 | 25 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="V"."C1")
4 - filter("C3"=1)
-- 1 교재에서는 NESTED LOOPS 사용
-- 5 교재에서는 INDEX RANGE SCAN 사용
- 하지만 거의 비슷한 형태의 아래와 같은 Query 는 View Merging 이 성공적으로 이루어진다.
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2
3 FROM
4 t1,
5 (SELECT c1, c3, COUNT(*) AS c2
6 FROM t2
7 GROUP BY c1, c3) v
8 WHERE
9 t1.c1 = v.c1 AND
10 t1.c3 = 1 -- v.c3 = 1
11 ;
401 1
201 1
301 1
1 1
801 1
901 1
701 1
101 1
501 1
601 1
10 개의 행이 선택되었습니다.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID g465uavtbp9kc, child number 0
-------------------------------------
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 -- v.c3 = 1
-- Oracle 11g
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 16 (100)| 10 |00:00:00.01 | 45 | | | |
| 1 | HASH GROUP BY | | 1 | 100 | 16 (13)| 10 |00:00:00.01 | 45 | 781K| 781K| 1002K (0)|
|* 2 | HASH JOIN | | 1 | 100 | 15 (7)| 10 |00:00:00.01 | 45 | 870K| 870K| 1207K (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 조건이 사용된다는 것이다.
- 왜 이런 차이가 발생하는가? 10053 Event 를 이용하면 유용한 정보를 얻을 수 있다.
- 아래 내용은 View Merging 이 실패한 Query 에 대해 10053 Event 에 의해 생성도니 Trace File 의 일부를 발췌한 것이다.
- Heuristics 에 의해, 더 정확하게 표현하면 CBO 의 Source Code 에서 사용하는 일종의 규칠(Rule. Heuristics)에 의해 View Merging 이 비효율적일 것으로 판단했기 때문에 View Merging 을 수행하지 않겠다는 것이다.
Be Careful
*************************************
Predicate Move-Around (PM)
*************************************
CVM: Not merging SEL$2 (#0) into SEL$1 (#0) due to heuristics.
- 왜 Optimizer 가 이런 판단을 했는지는 정확하게 알 수 없다.
- Optimizer 의 Transformation과 관련된 Code 에 존재하는 일부 사전적인 확인 절차에 의해 발생했을 것으로 추측해 볼 뿐이다.
- 여기서 역을 추론해 볼 수 있는 것은 View Merging 이 원천적으로 불가능한 것이 아니라 Optimizer 의 어떤 판단에 의해 View Merging 이 이루어지지 않았다는 것이다.
- 따라서 다음과 같이 MERGE Hint 를 강제로 부여하면 성공적으로 View Merging 이 이루어진다.
-- Need Check --
SQL> SELECT /*+ gather_plan_statistics */
2 t1.c1, v.c2
3 FROM
4 t1,
5 (SELECT /*+ merge */ c1, c3, COUNT(*) AS c2
6 FROM t2
7 GROUP BY c1, c3) v
8 WHERE
9 t1.c1 = v.c1 AND
10 v.c3 = 1
11 ;
401 1
201 1
301 1
1 1
801 1
901 1
701 1
101 1
501 1
601 1
10 개의 행이 선택되었습니다.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID dxz4b1bbuym82, child number 0
-------------------------------------
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
-- Oracle 11g
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 (100)| 10 |00:00:00.02 | 32 | | | |
| 1 | HASH GROUP BY | | 1 | 10 | 12 (17)| 10 |00:00:00.02 | 32 | 781K| 781K| 1002K (0)|
|* 2 | HASH JOIN | | 1 | 10 | 11 (10)| 10 |00:00:00.02 | 32 | 968K| 968K| 1080K (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.01 | 25 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="C1")
3 - filter("C3"=1)
-- Oracle 10g
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 10 | 11 (19)| 10 |00:00:00.01 | 30 | | | |
|* 2 | HASH JOIN | | 1 | 10 | 10 (10)| 10 |00:00:00.01 | 30 | 968K| 968K| 1017K (0)|
|* 3 | TABLE ACCESS FULL | T2 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 6 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 6 (0)| 10000 |00:00:00.01 | 24 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="C1")
3 - filter("C3"=1)
-- 2 교재에서는 NESTED LOOPS 사용
-- 4 교재에서는 INDEX RANGE SCAN 사용
- MERGE Hint 를 이용해 강제로 View Merging 을 수행한 경우 Cost 는 13 에서 15로 증가하지만 실제 일량은 28에서 19로 감소하는 것을 알 수 있다.
- Optimizer 의 판단이 항상 옳은 것은 아니라는 것을 단적으로 알 수 있는 좋은 예제이다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3926
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.