트러블슈팅 오라클 퍼포먼스 2판 (2017년)
목록
DBMS_XPLAN 패키지 0 0 20,772

by 구루비스터디 실행계획실 DBMS_XPLAN [2023.09.09]

DBMS_XPLAN 패키지

출력


SQL_ID  dwnnunj9nuztb, child number 0
------------------------------------
SELECT t2.* FROM t1, t2 WHERE t1.n = t2.n AND t1.id > :t1_id AND
t2.id BETWEEN :t2_id_min AND :t2_id_max


다음과 같은 정보를 제공한다.
  • sql_id는 부모 커서를 식별한다.
  • sql_id와 함께 child number는 자식 커서를 식별한다.
  • SQL 구문의 텍스트는 display_cursor 및 display_awr 함수를 사용하여 출력을 생성했을 경우에만 사용할 수 있다.



Plan hash value: 2539808735

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |       |       |    15 (100)|          |
|*  1 |  FILTER                       |       |       |       |            |          |
|*  2 |   HASH JOIN                   |       |    14 |  7756 |    15   (7)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |    14 |  7392 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T2_PK |    14 |       |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | T1    |   876 | 22776 |    23   (0)| 00:00:01 |
--------------------------------------------------------------------------------------






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

   1 - SEL$1
   3 - SEL$1 / T2@SEL$1
   4 - SEL$1 / T2@SEL$1
   5 - SEL$1 / T1@SEL$1





Outline Data ------------

  /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
    DB_VERSION('11.2.0.4')
    ALL_ROWS
    OUTLINE_LEAF(@"SEL$1")
    INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."ID"))
    FULL(@"SEL$1" "T1"@"SEL$1")
    LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
    USE_HASH(@"SEL$1" "T1"@"SEL$1")
    END_OUTLINE_DATA
  */





Peeked Binds (identified by position):
-------------------------------------

   1 - :T1_ID (NUMBER): 6
   2 - :T2_ID_MIN (NUMBER): 6
   3 - :T2_ID_MAX (NUMBER): 19





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

   1 - filter(:T2_ID_MIN<=:T2_ID_MAX)
   2 - access("T1"."N"="T2"."N")
   4 - access("T2"."ID">=:T2_ID_MIN AND "T2"."ID"<=:T2_ID_MAX)
   5 - filter("T1"."ID">:T1_ID)





Column Projection Information (identified by operation id):
----------------------------------------------------------

   1 - "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
   2 - (#keys=1) "T2"."N"[NUMBER,22], "T2"."ID"[NUMBER,22],       "T2"."PAD"[VARCHAR2,1000]
   3 - "T2"."ID"[NUMBER,22], "T2"."N"[NUMBER,22], "T2"."PAD"[VARCHAR2,1000]
   4 - "T2".ROWID[ROWID,10], "T2"."ID"[NUMBER,22]
   5 - "T1"."N"[NUMBER,22]





Note
----
  - dynamic sampling used for this statement (level=2)


DISPLAY 함수



SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

----------------------------------
| Id  | Operation	  | Name |
----------------------------------
|   0 | SELECT STATEMENT  |	 |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

8 행이 선택되었습니다.

SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("EMPNO"=TO_NUMBER(:B1))

13 행이 선택되었습니다.
SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter("EMPNO"=TO_NUMBER(:B1))


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
       "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]

40 행이 선택되었습니다.






SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'basic +predicate'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

----------------------------------
| Id  | Operation	  | Name |
----------------------------------
|   0 | SELECT STATEMENT  |	 |
|*  1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("EMPNO"=TO_NUMBER(:B1))

13 행이 선택되었습니다.

SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'typical -bytes -note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Cost (%CPU)| Time	 |
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     3   (0)| 00:00:01 |
------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("EMPNO"=TO_NUMBER(:B1))

13 행이 선택되었습니다.






SQL> ALTER SESSION SET current_schema = franco;

SQL> EXPLAIN PLAN FOR SELECT * FROM t;

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------

Error: cannot fetch last explain plan from PLAN_TABLE

SQL> EXPLAIN PLAN INTO franco.plan_table FOR SELECT * FROM t;

SQL> SELECT * FROM table(dbms_xplan.display(table_name=>'franco.plan_table'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------

Plan hash value: 3956160932

-------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |    14 |  1218 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------





SQL> SELECT /*+ gather_plan_statistics */ count(*) FROM t;

  COUNT(*)
---------
  1000


SQL> CREATE TABLE my_plan_table
   2  AS
   3  SELECT cast(1 AS VARCHAR2(30)) AS plan_id, p.*
   4  FROM v$sql_plan_statistics_all p
   5  WHERE (sql_id, child_number) = (SELECT prev_sql_id, prev_child_number
   6                                  FROM v$session
   7                                  WHERE sid = sys_context('userenv','sid'));

SQL> SELECT * FROM table(dbms_xplan.display('my_plan_table', NULL, 'iostats'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

Plan hash value: 2966233522

---------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      2 |        |      2 |00:00:00.01 |      10 |      4 |
|   1 |  SORT AGGREGATE    |      |      2 |      1 |      2 |00:00:00.01 |      10 |      4 |
|   2 |   TABLE ACCESS FULL| T    |      2 |   1000 |   2000 |00:00:00.01 |      10 |      4 |
---------------------------------------------------------------------------------------------



DISPLAY_CURSOR 함수


SQL> SELECT /*+ gather_plan_statistics */ count(pad)
  2  FROM (SELECT rownum AS rn, pad FROM t ORDER BY n)
  3  WHERE rn = 1;

COUNT(PAD)
---------
1

SQL> SELECT * FROM table(dbms_xplan.display_cursor('d5v0dt28fp5fh', 0, 'iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

SQL_ID  d5v0dt28fp5fh, child number 0
------------------------------------
SELECT /*+ gather_plan_statistics */ count(pad) FROM (SELECT rownum AS rn, pad FROM t ORDER BY n) WHERE rn = 1

Plan hash value: 2545006537

---------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:00.02 |     147 |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:00.02 |     147 |
|*  2 |   VIEW                |      |      1 |   1000 |      1 |00:00:00.02 |     147 |
|   3 |    SORT ORDER BY      |      |      1 |   1000 |   1000 |00:00:00.02 |     147 |
|   4 |     COUNT             |      |      1 |        |   1000 |00:00:00.01 |     145 |
|   5 |      TABLE ACCESS FULL| T    |      1 |   1000 |   1000 |00:00:00.01 |     145 |
---------------------------------------------------------------------------------------

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

   2 - filter("RN"=1)


DISPLAY_AWR 함수


SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', NULL, NULL, 'basic'));

PLAN_TABLE_OUTPUT
----------------------------------

SQL_ID 48vuyqjwpf9wg
-------------------
SELECT COUNT(N) FROM T

Plan hash value: 2966233522

----------------------------------
| Id  | Operation          | Name |
----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    | ----------------------------------

SQL_ID 48vuyqjwpf9wg
-------------------
SELECT COUNT(N) FROM T

Plan hash value: 3776247601

-------------------------------------
| Id  | Operation             | Name |
-------------------------------------
|   0 | SELECT STATEMENT      |      |
|   1 |  SORT AGGREGATE       |      |
|   2 |   INDEX FAST FULL SCAN| I    |
-------------------------------------

SQL> SELECT * FROM table(dbms_xplan.display_awr('48vuyqjwpf9wg', 2966233522, NULL, 'basic'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

SQL_ID 48vuyqjwpf9wg
-------------------
SELECT COUNT(N) FROM T


Plan hash value: 2966233522

----------------------------------
| Id  | Operation          | Name |
----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
----------------------------------
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition) " 도서를 스터디하면서 정리한 내용 입니다.

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

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

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

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