About DATABASE

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-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;