Optimizing Oracle Optimizer (2011년)
목록
Simple View Merging 0 0 2,416

by 구루비스터디 Transformation View Merging [2018.07.14]

Simple View Merging

  • Simple View 는 말 그대로 Group By 등의 복잡한 Operation 을 포함하지 않는 단순한 View를 의미한다.
  • Simple View 조차도 Merging 의 성공 여부에 따라 실행 계획 상의 큰 차이를 보일 수 있다.
  • 간단한 예제를 통해 Simple View Merging 의 동작 방식을 알아보자.
  • 우선 다음과 같이 필요한 Object 를 생성한다.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL> drop table t1 purge;
테이블이 삭제되었습니다.
SQL> drop table t2 purge;
테이블이 삭제되었습니다.
SQL> drop table t3 purge;
테이블이 삭제되었습니다.
SQL> create table t1(c1 int, c2 char(10), c3 int);
테이블이 생성되었습니다.
SQL> create table t2(c1 int, c2 char(10), c3 int);
테이블이 생성되었습니다.
SQL> create table t3(c1 int, c2 char(10), c3 int);
테이블이 생성되었습니다.
SQL> create index t1_n1 on t1(c1);
인덱스가 생성되었습니다.
SQL> create index t2_n1 on t2(c1);
인덱스가 생성되었습니다.
SQL> create index t3_n1 on t3(c1);
인덱스가 생성되었습니다.
SQL> create index t1_n2 on t1(c3);
인덱스가 생성되었습니다.
SQL> create index t2_n2 on t2(c3);
인덱스가 생성되었습니다.
SQL> create index t3_n2 on t3(c3);
인덱스가 생성되었습니다.

-- 논의를 간단하게 하기 위해 다음과 같이 Cost Based Query Transformation 을 비활성화 한다.
SQL> ALTER SESSION SET "_optimizer_cost_based_transformation" = off;
세션이 변경되었습니다.
SQL> ALTER SESSION SET "_optimizer_push_pred_cost_based" = false;
세션이 변경되었습니다.


  • Column c1 은 Unique 값을 가지며, Column c2 는 하나의 Distinct Count 만, Column c3 는 0~99 의 100개의 Distinct Count 를 갖도록 Data 를 생성한다.
  • Table t1 은 10,000 건, Table t2 는 1,000 건, Table t3 는 100 건의 Data 를 갖는다.




SQL> insert into t1
  2  select level, 'dummy', mod(level, 100) from dual
  3  connect by level <= 10000
  4  ;
10000 개의 행이 만들어졌습니다.

SQL> insert into t2
  2  select level, 'dummy', mod(level, 100)  from dual
  3  connect by level <= 1000
  4  ;
1000 개의 행이 만들어졌습니다.

SQL> insert into t3
  2  select level, 'dummy', mod(level, 100) from dual
  3  connect by level <= 100
  4  ;
100 개의 행이 만들어졌습니다.

SQL> commit;

커밋이 완료되었습니다.

SQL> EXEC dbms_stats.gather_table_stats(user, 'T1');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXEC dbms_stats.gather_table_stats(user, 'T2');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> EXEC dbms_stats.gather_table_stats(user, 'T3');
PL/SQL 처리가 정상적으로 완료되었습니다.


  • Simple View 를 사용하는 Query 에 대해 NO_MERGE Hint 를 사용해 View Merging 을 강제로 비활성화한 경우에는 다음과 같은 실행 계획을 보인다.




SQL> SELECT count(*)
  2  FROM (
  3  SELECT /*+ gather_plan_statistics */
  4    t1.c1, v.c2
  5  FROM
  6    t1,
  7    (SELECT /*+ no_merge */ c1, c2
  8      FROM t2
  9      WHERE c1 BETWEEN 1 AND 1000) v
 10  WHERE
 11    t1.c1 = v.c1
 12  )
 13  ;
      1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  gp8tnk7qwgcj8, child number 0
-------------------------------------
SELECT count(*) FROM ( SELECT /*+ gather_plan_statistics */   t1.c1,
v.c2 FROM   t1,   (SELECT /*+ no_merge */ c1, c2     FROM t2     WHERE
c1 BETWEEN 1 AND 1000) v WHERE   t1.c1 = v.c1 )

Plan hash value: 3590297760

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem|
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       |      1 |        |    10 (100)|      1 |00:00:00.02 |      33 |       |       ||
|   1 |  SORT AGGREGATE         |       |      1 |      1 |            |      1 |00:00:00.02 |      33 |       |       ||
|*  2 |   HASH JOIN             |       |      1 |   1000 |    10  (10)|   1000 |00:00:00.02 |      33 |  1066K|  1066K| 1192K (0)|
|   3 |    VIEW                 |       |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       ||
|*  4 |     INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       ||
|   5 |    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"="V"."C1")
   4 - filter(("C1">=1 AND "C1"<=1000)) -- 교재에서는 access 처리 : INDEX RANGE SCAN 사용


실행 계혹의 3번 단계의 VIEW Operation이 View Merging이 실패 했다는 것을 잘 보여준다.
  • View Merging 이 실패한 경우 Oracle 은 View 에 대한 Optimization 작업과 전체 Query 에 대한 Optimization 작업을 별개로 처리하게 된다.
  • Oracle 의 Optimization 작업은 Query Block 단위로 이루어지기 때문이다.
  • 위의 결과를 보면 Inline View 는 Index Range Scan 을 수행하게끔 Optimization 이 이루어지고,
  • Main Query는 View 의 결과를 Index t1_n1에 대한 Index Fast Full Scan과 Hash Join을 수행하게끔 Optimization 이 이루어진다.
  • View Merging 에 의해 Inline View 가 Main Query 안으로 흡수되지 못하는 상황에서는 선택 가능한 최적의 실행 계획인 셈이다.


  • View Merging 이 성공적으로 이루어진 경우에는 전혀 다른 실행 계획을 보인다.


SQL> SELECT count(*)
  2  FROM (
  3  SELECT /*+ gather_plan_statistics */
  4    t1.c1, v.c2
  5  FROM
  6    t1,
  7    (SELECT c1, c2
  8      FROM t2
  9      WHERE c1 BETWEEN 1 AND 1000) v
 10  WHERE
 11    t1.c1 = v.c1
 12  )
 13  ;
      1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  gc3pxkdxw3gvr, child number 0
-------------------------------------
SELECT count(*) FROM ( SELECT /*+ gather_plan_statistics */   t1.c1,
v.c2 FROM   t1,   (SELECT c1, c2     FROM t2     WHERE c1 BETWEEN 1 AND
1000) v WHERE   t1.c1 = v.c1 )

Plan hash value: 856841256

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |     6 (100)|      1 |00:00:00.01 |      11 |       |       |          |
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.01 |      11 |       |       |          |
|*  2 |   HASH JOIN            |       |      1 |    999 |     6  (17)|   1000 |00:00:00.01 |      11 |  1066K|  1066K| 1241K (0)|
|*  3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       8 |       |       |          |
|*  4 |    INDEX RANGE SCAN    | T1_N1 |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C1"="C1")
   3 - filter(("C1">=1 AND "C1"<=1000)) -- 교재에서는 access 처리 : INDEX RANGE SCAN 사용
   4 - access("T1"."C1">=1 AND "T1"."C1"<=1000)


우선 가장 큰 변화는 실행 계획에서 View Operation 이 없어졌다는 것이다.
  • View Merging으로 인해 View 가 없어졌다는 것을 의미한다.
  • 또 하나의 큰 변화는 Table t1, t2 에 대해 모두 Index Range Scan 을 사용하게 되었다는 것이다.(11g 테스트에서는 INDEX FAST FULL SCAN 사용)
  • 그로 인해 Cost 뿐만 아니라 실제 일량도 크게 개선되었다.
  • 왜 이런 변화가 생긴 것인가? Predicate Information 에 해답이 있다.


4번 단계에서 사용된 Predicate 를 보면 놀라운 사실을 알 수 있다.
  • 이 Predicate 는 우리가 사용한 적이 없다.
  • Oracle 에 의해 추가적으로 생성된 Predicate 인 셈이다.
  • 이 Predicate 의 존재로 인해 Table t2 뿐만 아니라 Table t1 에 대해서도 Index Range Scan 을 사용할 수 있게 되었다.
  • 이를 역으로 추론해 보면 Simple View Merging 에 의해 원래 Query 가 다음과 같이 변형되었다는 것을 알 수 있다.




SELECT 
	t1.c1, t2.c2
FROM
	t1, t2
WHERE 
	t1.c1 = t2.c1 AND
	t1.c1 BETWEEN 1 AND 1000 AND
	t2.c1 BETWEEN 1 AND 1000
;

       998 dummy
       999 dummy
      1000 dummy

1000 개의 행이 선택되었습니다.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  ad7gwucmhnq9z, child number 0
-------------------------------------
SELECT  t1.c1, t2.c2 FROM  t1, t2 WHERE  t1.c1 = t2.c1 AND  t1.c1
BETWEEN 1 AND 1000 AND  t2.c1 BETWEEN 1 AND 1000

Plan hash value: 1733838511

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |     7 (100)|   1000 |00:00:00.01 |      77 |    |          |          |
|*  1 |  HASH JOIN         |       |      1 |    999 |     7  (15)|   1000 |00:00:00.01 |      77 |  1066K|  1066K| 1225K (0)|
|*  2 |   INDEX RANGE SCAN | T1_N1 |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |       3 |    |          |          |
|*  3 |   TABLE ACCESS FULL| T2    |      1 |   1000 |     3   (0)|   1000 |00:00:00.01 |      74 |    |          |          |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="T2"."C1")
   2 - access("T1"."C1">=1 AND "T1"."C1"<=1000)
   3 - filter(("T2"."C1">=1 AND "T2"."C1"<=1000))
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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