Search Results for 'Optimizing Oracle Optimizer'

2 POSTS

  1. 2012|09 Oracle - DBMS_XPLAN - 실행 계획 예측 정보
  2. 2012|09 Oracle - CBO 기본 흐름

# DBMS_XPLAN

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

- 기본적인 사용법
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 호출의 조합으로 실행 계획을 보여준다.
가장 기본적인 방법은 다음과 같다.
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 가 어떻게 표현되는지 잘 알 수 있다.
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 정보가 출력된다.
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 한다.
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 값을 지정해 주어야 한다.
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 이다.
실제로 사용할 경우는 없을 것이다.
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 정보를 보여준다는 사실이 중요하다.
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 할 때 유용한 정보가 된다.
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 의 실행 계획을 해석할 때 매우 유용한 기능이다.
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 가 사용된 것을 알 수 있다.
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 을 합친 것과 같다.
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




출처 : Optimizing Oracle Optimizer - 조동욱


※ 위 내용은, 여러 자료를 참고하거나 제가 주관적으로 정리한 것입니다.
   잘못된 정보나 보완이 필요한 부분을, 댓글 또는 메일로 보내주시면 많은 도움이 되겠습니다.
09 9, 2012 15:52 09 9, 2012 15:52


Trackback URL : http://develop.sunshiny.co.kr/trackback/827

Leave a comment


Recent Posts

  1. HDFS - Python Encoding 오류 처리
  2. HP - Vertica ROS Container 관련 오류...
  3. HDFS - Hive 실행시 System Time 오류
  4. HP - Vertica 사용자 쿼리 이력 테이블...
  5. Client에서 HDFS 환경의 데이터 처리시...

Recent Comments

  1. Generally I do not read post on bl... 레기읏룸 셔츠룸 차이. 레깅스룸 부엉이 01 24,
  2. Wonderful site. A lot of useful in... /427 01 23,
  3. 안녕하세요^^ 배그핵
  4. 안녕하세요^^ 도움이 되셨다니, 저... sunshiny
  5. 정말 큰 도움이 되었습니다.. 감사합... 사랑은

Recent Trackbacks

  1. cabo packages cabo packages %M
  2. airbnb host insurance airbnb host insurance %M
  3. beaches in cabo beaches in cabo %M
  4. joe’s dj service joe’s dj service %M
  5. short term rental property insurance short term rental property insurance %M

Calendar

«   01 2020   »
      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  

Bookmarks

  1. 위키피디아
  2. MysqlKorea
  3. 오라클 클럽
  4. API - Java
  5. Apache Hadoop API
  6. Apache Software Foundation
  7. HDFS 생태계 솔루션
  8. DNSBL - Spam Database Lookup
  9. Ready System
  10. Solaris Freeware
  11. Linux-Site
  12. 윈디하나의 솔라나라

Site Stats

TOTAL 2824810 HIT
TODAY 429 HIT
YESTERDAY 443 HIT