04 DBMS_XPLAN 패키지

 

오라클 10g부터는 라이브러리 캐시에 캐싱돼 있는 SQL 커서에 대한 실행계획은 물론 Row Source별 수행통계까지 손쉽게 출력해 볼 수 있도록 기능이 확장되었다.

 

(1) 예상 실행계획 출력

 

@?/rdbms/admin/utlxpls

내부적으로 dbms_xplan 패키지를 호출

 

select plan_table_output

from  table(dbms_xplan.display('plan_table', null, 'serial');

 

첫번째 인자 : 실행계획이 저장된 plan table명

두번째 인자 : statement_id, null일 때는 가장 마지막 explain plan 명령에 사용했던 쿼리의 실행계획

병렬 쿼리에 대한 실행계획을 수집했다면 @?/rdbms/admin/utlxplp 스크립트를 수행

세번째 인자 : 다양한 포맷 옵션을 선택 (Basic, Typical, Serial, All, Outline, Advanced)

 

 

EXPLAIN PLAN SET STATEMENT_ID = 'SQL1' FOR

SELECT *

FROM   ECS.TB_ECS_POLICY_MSTS A,

       TB_ECS_RIDER_MSTS B

WHERE  A.POLICYNO = B.POLICYNO

AND    A.POLICYNO = '1360079414'

;

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'BASIC'))

;

----------------------------------------------------------------

| Id  | Operation                    | Name                    |

----------------------------------------------------------------

|   0 | SELECT STATEMENT             |                         |

|   1 |  NESTED LOOPS                |                         |

|   2 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS      |

|   3 |    INDEX UNIQUE SCAN         | PK_TB_ECS_POLICY_MSTS   |

|   4 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS       |

|   5 |    INDEX RANGE SCAN          | TB_ECS_RIDER_MSTS_IDX03 |

----------------------------------------------------------------

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'TYPICAL'))

;

--------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                         |     5 |  4175 |     8   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |                         |     5 |  4175 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS      |     1 |   514 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_TB_ECS_POLICY_MSTS   |     1 |       |     2   (0)| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS       |     5 |  1605 |     5   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | TB_ECS_RIDER_MSTS_IDX03 |     5 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."POLICYNO"='1360079414')

5 - access("B"."POLICYNO"='1360079414')

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'ALL'))

;

--------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                         |     5 |  4175 |     8   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |                         |     5 |  4175 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS      |     1 |   514 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_TB_ECS_POLICY_MSTS   |     1 |       |     2   (0)| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS       |     5 |  1605 |     5   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | TB_ECS_RIDER_MSTS_IDX03 |     5 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / A@SEL$1

3 - SEL$1 / A@SEL$1

4 - SEL$1 / B@SEL$1

5 - SEL$1 / B@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."POLICYNO"='1360079414')

5 - access("B"."POLICYNO"='1360079414')

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - (#keys=0) "A"."POLICYNO"[VARCHAR2,10], "A"."POL_STS_MAINCODE"[VARCHAR2,10],

"A"."POL_STS_SUBCODE"[VARCHAR2,10], "A"."BEFORE_POL_STS_MAINCODE"[VARCHAR2,10],

"A"."BEFORE_POL_STS_SUBCODE"[VARCHAR2,10], "A"."POL_STS_CHG_DATE"[DATE,7],

"A"."BASE_PLAN_CODE"[VARCHAR2,10], "A"."INSURTYPE_CODE"[VARCHAR2,10],

"A"."COVERAGE_PERIOD_CLASS"[VARCHAR2,10], "A"."COVERAGE_PERIOD_VALUE"[NUMBER,22],

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', ' OUTLINE'))

;

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

USE_NL(@"SEL$1" "B"@"SEL$1")

LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")

INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" ("TB_ECS_RIDER_MSTS"."POLICYNO"

"TB_ECS_RIDER_MSTS"."RIDER_NO" "TB_ECS_RIDER_MSTS"."PLAN_CODE"))

INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("TB_ECS_POLICY_MSTS"."POLICYNO"))

OUTLINE_LEAF(@"SEL$1")

OPT_PARAM('query_rewrite_enabled' 'false')

OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'SQL1', 'ADVANCED'))

 

 

(2) 캐싱된 커서의 실제 실행계획 출력

 

커서 : 하드 파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 그리고 그것을 실행하는

필요한 정보를 담은 SQL Area

 

오라클은 라이브러리 캐시에 캐싱돼 있는 각 커서에 대한 수행통계를 볼 수 있도록 v$sql 뷰를 제공

이 뷰와 함께 sql_id 값으로 조인해서 사용할 수 있도록 제공되는 뷰 중 활용도가 높은 것은 v$sql_plan과 v$sql_plan_statistics 이다. 그리고 이 뷰를 합쳐서 보여주는 것이 v$sql_plan_statistics_all 이다.

 

v$sql_plan 뷰의 활용방법

 

v$sql_plan를 조회하려면 마지막 수행한 SQL의 sql_id와 child_number 값을 알아야 하는데, 아래 쿼리를 이용해 쉽게 찾을 수 있다.

 

SQL> set serveroutput off

SQL> select *

  2  from   emp e, dept d

  3  where  d.deptno = e.deptno

  4  and    e.sal >= 1000;

 

 

12 rows selected.

 

SQL> column prev_sql_id new_value sql_id

SQL> column prev_child_number new_value child_no

SQL> select prev_sql_id, prev_child_number

  2  from   v$session

  3  where  sid = userenv('sid')

  4  and    username is not null

  5  and    prev_hash_value <> 0;

 

PREV_SQL_ID   PREV_CHILD_NUMBER

------------- -----------------

704dj20tgv1b0                0

 

select * from table(dbms_xplan.display_cursor('[sql_id]', [child_no], '[format]'));

 

위 함수는 라이브러리 캐시에 현재 캐싱돼 있는 SQL 커서의 실제 실행계획과, 실행계획을 만들면서 예상했던 Rows, Bytes, Cost, Time 정보를 보여준다.

 

 

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASIC ROWS BYTES COST PREDICATE'));

PLAN_TABLE_OUTPUT

------------------

EXPLAINED SQL STATEMENT:

------------------------

SELECT *   FROM   ECS.TB_ECS_POLICY_MSTS A,         TB_ECS_RIDER_MSTS B  WHERE

A.POLICYNO = B.POLICYNO  AND    A.POLICYNO = '1360079414'

Plan hash value: 3830427808

---------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                         |       |       |     8 (100)|

|   1 |  NESTED LOOPS                |                         |     5 |  4175 |     8   (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS      |     1 |   514 |     3   (0)|

|*  3 |    INDEX UNIQUE SCAN         | PK_TB_ECS_POLICY_MSTS   |     1 |       |     2   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS       |     5 |  1605 |     5   (0)|

|*  5 |    INDEX RANGE SCAN          | TB_ECS_RIDER_MSTS_IDX03 |     5 |       |     2   (0)|

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."POLICYNO"='1360079414')

5 - access("B"."POLICYNO"='1360079414')

                                                                                                                                                                                                                                                       

(3) 캐싱된 커서의 Row Source별 수행 통계 출력

 

SQL문에 gather_plan_staticstics 힌트를 사용하거나, 시스템 또는 세션 레벨에서 statistics_level 파라미터를 all로 설정하면 실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계별로 수행 통계를 수집.

 

조회할 때는 v$sql_plan_statistics 또는 v$sql_plan_statistics_all 뷰를 이용

 

v$sql_plan_statistics 뷰에는 모든 통계항목에 대해 마지막 수행 통계치와 누적 통계치를 조회할 수 있도록 컬럼이 두 개씩 제공.

Output_rows, last_output_rows

Cr_buffer_gets, last_cu_buffer_gets

Disk_reads, last_disk_reads

 

Dbms_xplan.display_cursor 함수는 이러한 정보를 깔끔하게 포맷팅해주는 기능을 제공한다.

 

Dbms_xplan.display 함수에는 없던 iostats, memstats, allstats 옵션을 사용하면 실제 수행 시 Row Source별 수행통계를 보여준다.

 

E-Rows : SQL을 수행하기 전 옵티마이저가 각 Row Source별로 예상했던 로우 수 (v$sql_plan)

A-Rows : 실제 수행 시 읽었던 로우 수 (v$sql_plan_statistics)

 

이처럼 옵티마이저의 예상 로우 수와 수행 시 실제 로우 수를 비교해 보여주므로 옵티마이저의 행동을 관찰할 때 유용하게 사용

 

SELECT /*+ gather_plan_statistics */

       *

FROM   ECS.TB_ECS_POLICY_MSTS A,

       TB_ECS_RIDER_MSTS B

WHERE  A.POLICYNO = B.POLICYNO

AND    A.POLICYNO = '1360079414'

;

 

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));

PLAN_TABLE_OUTPUT

---------------------

SQL_ID  1rngkgr9m1xv3, child number 0

-------------------------------------

SELECT /*+ gather_plan_statistics */        *  FROM   ECS.TB_ECS_POLICY_MSTS A,        TB_ECS_RIDER_MSTS

B WHERE  A.POLICYNO = B.POLICYNO AND    A.POLICYNO = '1360079414'

Plan hash value: 3830427808

------------------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------------

|   1 |  NESTED LOOPS                |                         |      1 |      5 |     11 |00:00:00.01 |      13 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_POLICY_MSTS      |      1 |      1 |      1 |00:00:00.01 |       4 |

|*  3 |    INDEX UNIQUE SCAN         | PK_TB_ECS_POLICY_MSTS   |      1 |      1 |      1 |00:00:00.01 |       3 |

|   4 |   TABLE ACCESS BY INDEX ROWID| TB_ECS_RIDER_MSTS       |      1 |      5 |     11 |00:00:00.01 |       9 |

|*  5 |    INDEX RANGE SCAN          | TB_ECS_RIDER_MSTS_IDX03 |      1 |      5 |     11 |00:00:00.01 |       4 |

------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."POLICYNO"='1360079414')

5 - access("B"."POLICYNO"='1360079414')