[Oracle] SQL 실행 계획 확인 방법 1편 (Autotrace)

1. SQL*Plus Autotrace 기능 활성화 

1-1 EXPLAIN PLAN 이란? 

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

1-2 PLUSTRACE 권한 생성

SQL> conn / as sysdba
Connected.
SQL> @?/sqlplus/admin/plustrce.sql
SQL> 
SQL> drop role plustrace;
Role dropped.
SQL> create role plustrace;
Role created.
SQL> 
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> 
SQL> set echo off

1-3 해당 사용자에게 PLUSTRACE 권한 부여

SQL> grant PLUSTRACE to scott;
Grant succeeded.

1-4 해당 사용자로 로그인 후 PLAN Table 생성

SQL> conn scott/oracle
Connected.
SQL> @?/rdbms/admin/utlxplan.sql
Table created.

2. SQL*Plus Autotrace 사용 방법

2-1 Autotrace Mode 설정
SQL> show user;
USER is "SCOTT"
SQL> set autotrace on
SQL> set autotrace traceonly
SQL> set autotrace traceonly explain
SQL> set autotrace traceonly statistics

2-2 EXPLAIN PLAN SQL 실행

SQL> conn scott/oracle
Connected.
SQL> select dname from dept;
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    40 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    40 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         68  recursive calls
          0  db block gets
         22  consistent gets
          8  physical reads
          0  redo size
        488  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

2-3 Autotrace Mode 해제

SQL> set autotrace off;

SQL 튜닝을 하게 되면 기본적을 가장 많이 사용 하는 방법 입니다. 다음은 SQL Trace의 기능에 대해서 설명 하도록 하겠습니다.