오라클 plan 보기

1. 해당 쿼리를 monitor 힌트를 이용해서 수행

SELECT /* monitor */

ENAME, DEPTNO

FROM EMP

WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT' ) 

AND ENAME != 'SCOTT';

2. 수행한 쿼리의 sql_id 확인

SELECT *

FROM gv$sql_monitor

where SQL_TEXT LIKE '%monitor%';

--SQL_ID : 4nwj4w9ujvv5x

3. sqlplus 직접 로그인

> sqlplus test/test

SQL*Plus: Release 11.2.0.1.0 Production on 토 1월 24 13:28:08 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

4. sqlplus 레이아웃 조정 및 플랜쿼리 수행

SQL> set long 99999

SQL> set longc 200

SQL> set lines 200

SQL> set pages 9999

SQL> select dbms_sqltune.report_sql_monitor(sql_id => '4nwj4w9ujvv5x', report_level=>'ALL',

  2  type=>'TEXT') as text from dual;

5. 결과 확인

SQL> select dbms_sqltune.report_sql_monitor(sql_id => '4nwj4w9ujvv5x', report_level=>'ALL',

  2  type=>'TEXT') as text from dual;

TEXT

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

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

SQL Monitoring Report

SQL Text

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

SELECT /*+ monitor */ ENAME, DEPTNO FROM EMP WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT' ) AND ENAME != 'SCOTT'

Global Information

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

 Status              :  DONE (ALL ROWS)

 Instance ID         :  1

 Session             :  SCOTT (72:617)

 SQL ID              :  4nwj4w9ujvv5x

 SQL Execution ID    :  16777216

 Execution Started   :  01/24/2015 13:26:15

 First Refresh Time  :  01/24/2015 13:26:15

 Last Refresh Time   :  01/24/2015 13:26:15

 Duration            :  .00046s

 Module/Action       :  Orange for ORACLE DBA/4.0.1 (Build:43)

 Service             :  whisler11g

 Program             :  OrangeMain.exe

 Fetch Calls         :  2

Global Stats

==================================================

| Elapsed |    IO    |  Other   | Fetch | Buffer |

| Time(s) | Waits(s) | Waits(s) | Calls |  Gets  |

==================================================

|    0.00 |     0.00 |     0.00 |     2 |     15 |

==================================================

SQL Plan Monitoring Details (Plan Hash Value=587534197)

==========================================================================================================================

| Id |      Operation       | Name |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail |

|    |                      |      | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |

==========================================================================================================================

|  0 | SELECT STATEMENT     |      |         |      |         1 |     +0 |     1 |        4 |          |                 |

|  1 |   TABLE ACCESS FULL  | EMP  |       4 |    3 |         1 |     +0 |     1 |        4 |          |                 |

|  2 |    TABLE ACCESS FULL | EMP  |       1 |    3 |         1 |     +0 |     1 |        1 |          |                 |

==========================================================================================================================

6. 결과 분석 및 쿼리 튜닝