autotrace 설정

EXPLAIN PLAN 이란?

  사용자들이 SQL 문의 액세스 경로를 확인하고 튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후 실행 계획을 테이블(plan_table)에 저장하도록 해주는 명령 이다.

1. PLAN TABLE의 생성

  EXPLAIN PLAN을 sql 에 포함해서 수행하면 옵티마이저가 실행 계획까지만 수립하여 PLAN_TABLE에 저장 한다

 
-- PLAN을 사용하고자 하는 USER로 SQLPLUS LOGIN 하다 
-- $ORACLE_HOME/rdbms/admin/utlxplan.sql을 수행한다.

C:\> SQLPLUS scott/tiger 
SQL> @C:\oracle\rdbms\admin\utlxplan.sql;
    

2. PLUSTRACE ROLE의 생성

  SYSDBA 권한으로 접속하여 PLUSTRACE ROLE을 생성 한다.

  $ORACLE_HOME/sqlplus/admin/plustrce.sql을 수행하여 PLUSTRACE ROLE을 생성 한다

 
SQL> CONN / AS SYSDBA
연결되었습니다.

SQL> @C:\oracle\sqlplus\admin\plustrce.sql;

SQL> CREATE ROLE plustrace;
롤이 생성되었습니다.
 
SQL> GRANT select ON v_$sesstat TO plustrace;
 권한이 부여되었습니다.
 
SQL> GRANT select ON v_$statname TO plustrace;
권한이 부여되었습니다.
 
SQL> GRANT select ON v_$session TO plustrace;
 권한이 부여되었습니다.

SQL> GRANT plustrace TO dba with admin option;
권한이 부여되었습니다.
    

3. PLUSTRACE ROLE 부여

  PLUSTRACE ROLE을 plan을 사용하고자 하는 유저에게 부여 한다.

 
SQL> GRANT plustrace TO scott;
권한이 부여되었습니다. 
    

4. AUTOTRACE 실행

  
SQL>conn scott/tiger
접속되었습니다.
 
-- autotrace 상태를 on으로 변경한다.
SQL> SET AUTOTRACE ON  ;
 
-- SQL문을 실행해 보자 
SQL> SELECT a.ename, a.sal, b.dname
     FROM emp a, dept b
     WHERE a.deptno = b.deptno;

SMITH           800     RESEARCH
ALLEN           1600    SALES
WARD            1250    SALES
JONES           2975    RESEARCH
MARTIN          1250    SALES
BLAKE           2850    SALES
...

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
 
Statistics
----------------------------------------------------------
        460     recursive calls
          6     db block gets
        111     consistent gets
          9     physical reads
          0     redo size
       1259  bytes sent via SQL*Net to client
        429     bytes received via SQL*Net from client
          2     SQL*Net roundtrips to/from client
          8     sorts (memory)
          0     sorts (disk)
         14     rows processed
    

출처 - http://www.oracleclub.com/lecture/1159