반응형

# DBMS_XPLAN

[실행 계획 예측]
DBMS_XPLAN Package 는 Oracle 9i 에서 소개되었으며 Version Up 에 따라 기능이 점점 확장되고 있다.
Package 이름이 암시하는 것처럼, 이 Package 의 기본 목적은 실행 계획의 예측을 보여주는 것이다.

- 기본적인 사용법

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SCOTT@ora10g>drop table t1 purge;
 
 
Table dropped.
 
SCOTT@ora10g>create table t1(c1 int, c2 char(10));
 
Table created.
 
SCOTT@ora10g>insert into t1
  2  select level, 'dummy'
  3  from dual
  4  connect by level commit;
 
Commit complete.
 
SCOTT@ora10g>create index t1_n1 on t1(c1);
 
Index created.


EXPLAIN PLAN 명령과 DBMS_XPLAN.DISPLAY 호출의 조합으로 실행 계획을 보여준다.
가장 기본적인 방법은 다음과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SCOTT@ora10g>explain plan for
  2  select *
  3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Note
-----
   - dynamic sampling used for this statement
 
18 rows selected.


# DBMS_XPLAN.DISPLAY Function 은 기본적을 다음과 같은 세 가지 종류의 값을 보여준다.

    * Row Source Operation 을 보여준다.
       위의 예에서는 Index t1_n1 을 INDEX RANGE SCAN 으로 Access 하는 실행 계획이 수립될 것임을 보여준다.

    * Predicate 정보를 보여준다.
      2번 Operation, 즉 Index t1_n1 에 대한 Range Scan 단계에서는 access("C1"=1) Predicate 가 사용되었다.
      Index Access 를 통해서 걸러진 Data 는 1번 단계, 즉 TABLE ACCESS BY INDEX ROWID Operation 에서 filter("C2"='dummy') Predicate 를 이용해 다시 Filtering 된다.
      Predicate 정보는 이를 이해하지 못하면 Execution Plan 의 절반 밖에 이해하지 못한다고 할 정도로 중요한 정보이다.

    * Note 정보를 통해 부가적으로 필요한 정보를 제공한다.
       이 예제에서는 Dynamic Sampling 이 사용되었음을 알려 준다.
       Oracle 10g 에서는 통계 정보가 없는 Table 에 대해서 Dynamic Sampling 을 수행한다.


# Access Predicate 와 Filter Predicate 의 차이는 다음과 같다.

    * Access Predicate 는 Access Type을 결정하는데 사용되는 Predicate(조건)를 의미한다.
       더 정확하게 말하면 실제 Block 을 읽기 전에 어떤 방법으로 Block 을 읽을 것인가를 결정한다는 의미이다.
       따라서 Index Lookup 이나 Join 등은 Access Predicate 로 표현된다.

    * Filter Predicate 는 실제 Block 을 읽은 후 Data 를 걸러 내기 위해 사용되는 Predicate(조건)을 의미한다.

# Access Predicate 와 Filter Predicate 가 표현되는 방식에 대한 정확한 이해는 실행 계획 해석에 있어 필수적인 지식이다.
   다음 예제를 보면 Access Predicate 와 Filter Predicate 가 어떻게 표현되는지 잘 알 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SCOTT@ora10g>create table t1(c1 int, c2 int);
 
Table created.
 
SCOTT@ora10g>create table t2(c1 int, c2 int);
 
Table created.
 
SCOTT@ora10g>create index t1_n1 on t1(c1);
 
Index created.
 
SCOTT@ora10g>create index t1_n2 on t2(c1);
 
Index created.
 
SCOTT@ora10g>insert into t1 select level, level
  2  from dual
  3  connect by level insert into t2 select level, level
  2  from dual
  3  connect by level explain plan for
  2  select /*+ use_nl(t1 t2) */
  3  *
  4  from t1, t2
  5  where t1.c1 = t2.c1
  6  and   t1.c2 = 1
  7  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    52 |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    26 |     2   (0)|
|   2 |   NESTED LOOPS              |       |     1 |    52 |     5   (0)|
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |    26 |     3   (0)|
|*  4 |    INDEX RANGE SCAN         | T1_N2 |     1 |       |     1   (0)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("T1"."C2"=1)
   4 - access("T1"."C1"="T2"."C1")


반면 Hash Join 에서는 Join 단계(1번)에서 Access Predicate 정보가 출력된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SCOTT@ora10g>explain plan for
  2  select /*+ use_hash(t1 t2) */
  3  *
  4  from t1, t2
  5  where t1.c1 = t2.c1
  6  and   t1.c2 = 1
  7  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    52 |     7  (15)|
|*  1 |  HASH JOIN         |      |     1 |    52 |     7  (15)|
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
|   3 |   TABLE ACCESS FULL| T2   |  1000 | 26000 |     3   (0)|
----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."C1"="T2"."C1")
   2 - filter("T1"."C2"=1)


왜 Nested Loops Join 과 Hash Join 에서 Access Predicate 가 표현된느 방식의 차이가 발생하는가?
두 Join 의 동작 방식의 차이에서 비롯된다.
Nested Loops Join 은 선행 Table 에서 Key 값을 하나씩 읽으면서 후행 Table 에서 Key 값에 해당하는 값을 읽어 들이는 방식이다.
따라서 실제 Join 은 후행 Table 에 대한 Access 에서 발생한다.
따라서 후행 Table 을 읽는 단계가 Access Predicate 가 된다.

반면에 Hash Join 은 선행 Table 을 먼저 Build 한 후, 후행 Table 과 한번에 Join 하는 방식이다.
따라서 실제 Join 이 발생하는 Hash Join 단계가 Access Predicate 로 표현된다.

DBMS_XPLAN.DISPLAY Function 이 제공하는 Parameter 들을 잘 이용하면 더 많은 종류의 정보를 추출할 수 있다.
예를 들어 다음 2개의 문장은 동일한 결과를 Return 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display);
 
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1 AND "C2"=TO_NUMBER('dummy'))
 
SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table', null, 'typical',null));
 
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1 AND "C2"=TO_NUMBER('dummy'))


EXPLAIN PLAN 수행 시 Statement ID 를 부여한 경우에는 다음과 같이 ID 값을 지정해 주어야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SCOTT@ora10g>explain plan
set statement_id = 'test' for
  2    3  select *
  4  from t1
  5  where c1 = 1 and c2 = 'dummy'
  6  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table', 'test', 'typical',null));
 
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |     3   (0)|
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C1"=1 AND "C2"=TO_NUMBER('dummy'))




# 다양한 출력 Format
DBMS_XPLAN.DISPLAY Function 은 Basic, Typical, Serial, All 네 가지의 출력 Format 을 제공하며 기본값은 Typical 이다.
더불어 Outline, Advanced 라는 문서화되지 않은 Format 도 제공한다.
각 Format 의 출력 결과와 의미는 다음과 같다.

Basic Format 은 실행 계획의 단계별 Operation 과 Object 이름만을 보여주는 말 그대로 매우 기본적인 Format 이다.
실제로 사용할 경우는 없을 것이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'basic'));
 
----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T1   |
----------------------------------



Typical Format 이 가장 일반적인 용도로 고안되었다.
단계별 Operation 과 Object 명, 예측 Row 수와 Result Set 의 크기(Bytes), Cost 와 예측 실행 시간등의 정보를 보여준다.
무엇보다 Predicate 정보를 보여준다는 사실이 중요하다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SCOTT@ora10g>explain plan for
select *
  2    3  from t1
  4  where c1 = 1 and c2 = 'dummy'
  5  ;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'typical'));
 
--------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    26 |     5   (0)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    26 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)|
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)


Predicate 정보가 왜 그렇게 중요한가? 
실행 계획이 복잡해지면 Where 절의 특정 조건이 실행 계획의 각 단계에서 정확하게 어떻게 사용되었는지가 매우 중요해진다.
Query Transformation 이라는 특별한 과정 때문에 Predicate 의 변형이 발생할 때는 이 정보가 특히 중요해진다.


All Format 은 실행 계획을 분석하는데 있어서 없어서는 안될 중요한 두 가지 정보를 추가적으로 제공한다.

첫째, Query Block 정보를 제공한다.
Oracle 은 우리가 수행 요청한 SQL 문장을 Query Block 이라는 단위로 나눈다.
Query Block 은 Transformation 및 Optimization 의 기본 단위가 된다.
아래 예제에서는 SWL$1 이라는 암호화 같은 Query Block 명이 사용된 것을 확인할 수 있다.
Query Block 명은 Inline View 와 Subquery 가 많이 사용되는 복잡한 Query 를 해석할 때 특히 유용하다.

둘째, Column Projection 정보를 제공한다.
Column Projection 이란 실행 계획의 특정 단계에서 어떤 Column 을 추출하는가를 의미한다.
아래 예제에서는 2번 단게에서는 Index t1_n1 으로부터 ROWID 와 Column c1 을 추출하며, 1번 단계에서는 Column c1, c2 를 추출한다는 것을 알 수 있다.
Column Projection 정보 또한 특별한 유형의 Query Transformation 을 Troubleshooting 할 때 유용한 정보가 된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SCOTT@ora10g>create table t1(c1 int, c2 char(10));
 
Table created.
 
SCOTT@ora10g>insert into t1
select level, 'dummy'
from dual
connect by level <= 10000
;
 
10000 rows created.
 
SCOTT@ora10g>commit;
 
Commit complete.
 
SCOTT@ora10g>create index t1_n1 on t1(c1);
 
Index created.
 
SCOTT@ora10g>explain plan for
select *
from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'all'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C1"[NUMBER,22], "C2"[CHARACTER,10]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement


QB_NAME Hint 를 사용하면 Query Block 명을 직접 조작할 수 있다.
복잡한 Query 의 실행 계획을 해석할 때 매우 유용한 기능이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SCOTT@ora10g>explain plan for
select /*+ qb_name(x) */
*
from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'all'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - X / T1@X
   2 - X / T1@X
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C1"[NUMBER,22], "C2"[CHARACTER,10]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement


Outline Format 은 매우 재미있는 추가적인 정보를 제공한다.
Outline 은 실행 계획을 수립하는데 필요한 Hint 들의 목록을 의미한다.
특정 실행 계획을 재현하기 위해 어떤 Hint 가 필요한지 확인하기 위한 용도로 사용 가능하다.
다음 예제에서는 INDEX Hint 와 ALL_ROWS Hint 가 사용된 것을 알 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SCOTT@ora10g>explain plan for
select *
from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'outline'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Note
-----
   - dynamic sampling used for this statement


Advanced Format 은 All Format 과 Outline Format 을 합친 것과 같다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
SCOTT@ora10g>explain plan for
select *
 from t1
where c1 = 1 and c2 = 'dummy'
;
 
Explained.
 
SCOTT@ora10g>select * from table(dbms_xplan.display('plan_table',null,'advanced'));
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("C2"='dummy')
   2 - access("C1"=1)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "C1"[NUMBER,22], "C2"[CHARACTER,10]
   2 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
 
Note
-----
   - dynamic sampling used for this statement


출처 : http://develop.sunshiny.co.kr/827

원본 :  Optimizing Oracle Optimizer - 조동욱

반응형
저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Merge 구문 1  (0) 2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리  (0) 2015.09.03
특정 컬럼만 Masking  (0) 2015.09.03
[펌]Index Access and Filter  (0) 2015.08.28
DBMS_RANDOM  (0) 2015.08.18

+ Recent posts

티스토리툴바