[Oracle] 실행 계획 - EXPLAIN PLAN, SET AUTOTRACE

오라클 DBMS에서 제공하는 두 가지 명령어

1. EXPLAIN PLAN
2. SET AUTOTRACE

 

1. EXPLAIN PLAN

> 1  EXPLAIN PLAN

  2  SET STATEMENT_ID = 'TEST1' INTO PLAN_TABLE

  3  FOR

  4  SELECT /*+ USE_NL(E D) */ E.ENAME, E.DEPTNO, D.DNAME

  5  FROM EMP E, DEPT D

  6* WHERE E.DEPTNO = D.DEPTNO

 

Explained.

 

-- PLAN_TABLE에 저장된 결과 확인

> 1  SELECT *

  2  FROM TABLE(DBMS_XPLAN.DISPLAY

  3* ('PLAN_TABLE','TEST1','ALL'))

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 4192419542

 

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

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

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

|  0 | SELECT STATEMENT  |      |    14 |  588 |    10  (0)| 00:00:01 |

|  1 |  NESTED LOOPS      |      |    14 |  588 |    10  (0)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| DEPT |    4 |    88 |    3  (0)| 00:00:01 |

|*  3 |  TABLE ACCESS FULL| EMP  |    4 |    80 |    2  (0)| 00:00:01 |

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

 

---

2. SET AUTOTRACE

> 1 SELECT /*+ USE_NL(E D) */

  2  E.DEPTNO, D.DNAME

  3  FROM SCOTT.EMP E, SCOTT.DEPT D

  4  WHERE E.DEPTNO=D.DEPTNO;

 

    DEPTNO DNAME

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

        10 ACCOUNTING

...

14 rows selected.

 

 

Execution Plan

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

Plan hash value: 4192419542

 

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

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

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

|  0 | SELECT STATEMENT  |      |    14 |  224 |    10  (0)| 00:00:01 |

|  1 |  NESTED LOOPS      |      |    14 |  224 |    10  (0)| 00:00:01 |

|  2 |  TABLE ACCESS FULL| DEPT |    4 |    52 |    3  (0)| 00:00:01 |

|*  3 |  TABLE ACCESS FULL| EMP  |    4 |    12 |    2  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

  3 - filter("E"."DEPTNO"="D"."DEPTNO")

 

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

Total hints for statement: 1 (U - Unused (1))

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

 

  2 -  SEL$1 / D@SEL$1

        U -  USE_NL(E D)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

        32  consistent gets

          0  physical reads

          0  redo size

        810  bytes sent via SQL*Net to client

        460  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        14  rows processed