PLUSTRACE
AUTOTRACE는 SQL 문장의 실행경로, 실행시의 비용(Cost), 통계정보를 얻기위해서 사용된다. 인덱스를 설정하기 전과 설정한 후의 실행경로 및 실행비용을 확인해 보기 위해서 AUTOTRACE기능을 이용하게 된다.
--일반이용자로 로그인한다
conn scott/tiger;
--특정 테이블에 대한 통계를 작성하여 오라클에게 제공한다.
exec dbms_stats.gather_table_stats('scott', 'emp');
--실행계획을 저장할 계획테이블을 생성한다 (utlxplan.sql을 실행하면 된다)
@C:\oracle\product\10.2.0\db_2\RDBMS\ADMIN\utlxplan.sql;
--관리자로 로그인한다
conn /as sysdba;
--plustrace role을 생성한다(plustrce.sql을 실행하면 된다)
@C:\oracle\product\10.2.0\db_2\sqlplus\admin\plustrce.sql;
--사용자에게 plustrace role을 부여한다
GRANT plustrace TO scott;
--일반사용자로 로그인한다
conn scott/tiger;
--실행계획과 통계자료를 출력하도록 설정한다
set autotrace on; --혹은 set autotrace traceonly;
--sql를 실행하여 실행계획과 통계자료를 확인한다.
select * from emp;
위의 문장을 실행하면 다음과 같은 결과를 얻을 수 있다.
................................................................................................................................................................................................................
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1428 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)
14 rows processed
................................................................................................................................................................................................................
참고로 set autotrace에는 여러가지 option을 부여해 작업할 수도 있다.
예)
set autotrace on => Explain plan and statistics.
set autotrace on explain => Explain plan only.
set autotrace traceonly => select된 결과는 빼고 trace만 display시킴.
set autotrace on statistics => sql statement execution statistics.
PLUSTRCE.SQL의 전체내용
--
-- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
--
-- NAME
-- plustrce.sql
--
-- DESCRIPTION
-- Creates a role with access to Dynamic Performance Tables
-- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
-- After this script has been run, each user requiring access to
-- the AUTOTRACE feature should be granted the PLUSTRACE role by
-- the DBA.
--
-- USAGE
-- sqlplus "/ as sysdba" @plustrce
--
-- Catalog.sql must have been run before this file is run.
-- This file must be run while connected to a DBA schema.
set echo on
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
set echo off