[오라클] [Explain Plan] SQL 실행하지 않고 Trace만 보는 방법

explain plan, set autotrace traceonly explain 사용한다. 

 

SYS@orcl 22/01/03> explain plan for
  2  select * from emp e, dept d
  3  where e.deptno = d.deptno and
  4  d.deptno = 10;

Explained.

 

- SQL*Plus의 SET AUTOTRACE 이용

  - PLUSTRACE 권한 생성 및 부여

SYS@orcl 22/01/03> conn / as sysdba
Connected.
SYS@orcl 22/01/03> @?/sqlplus/admin/plustrce.sql
SYS@orcl 22/01/03>
SYS@orcl 22/01/03> drop role plustrace;

Role dropped.

SYS@orcl 22/01/03> create role plustrace;

Role created.

SYS@orcl 22/01/03>
SYS@orcl 22/01/03> grant select on v_$sesstat to plustrace;

Grant succeeded.

SYS@orcl 22/01/03> grant select on v_$statname to plustrace;

Grant succeeded.

SYS@orcl 22/01/03> grant select on v_$mystat to plustrace;

Grant succeeded.

SYS@orcl 22/01/03> grant plustrace to dba with admin option;

Grant succeeded.

SYS@orcl 22/01/03>
SYS@orcl 22/01/03> set echo off
SYS@orcl 22/01/03> grant PLUSTRACE to scott;

Grant succeeded.

 

- 사용 방법

SYS@orcl 22/01/03> set autotrace on
SYS@orcl 22/01/03> set autot off
SYS@orcl 22/01/03> set autotrace traceonly
SYS@orcl 22/01/03> set autotrace traceonly explain
SYS@orcl 22/01/03> set autotrace traceonly statistics

 

- SQL*Plus AUTOTRACE 예

SYS@orcl 22/01/03> select * from dept where deptno = 10;


Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
         33  consistent gets
          0  physical reads
          0  redo size
        576  bytes sent via SQL*Net to client
        387  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed