Optimizing Oracle Optimizer (2009년)
목록
Non Semi And Anti Join 0 0 91,683

by 구루비스터디 Transformation Non Semi Anti Join [2018.07.14]

Not Semi/Anti Join



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이 선택되었다.


Aggregate Subquery Elimination

  • 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 변화까지 관찰해야만 완벽한 해석이 가능하다.
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3874

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입