Introduction

SQL 수행 시 실제 일량 측정 및 튜닝하는데 유용한 정보들을 많이 포함하는 도구

SQL> select * from scott.emp where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 81/12/03        950                    30


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7900)


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

AutoTrace 옵션

아래와 같은 옵션에 따라 필요한 부분만 출력해 볼 수 있음
(1)set autotrace on

• SQL을 실행하고 그결과와 함께 실행 계획 및 실행통계를 출력

(2) set autotrace on explain

• SQL을 실행하고 그결과와 함께 실행 계획을 출력

(3) set autotrace on statistics

• SQL을 실행하고 그결과와 함께 실행통계를 출력

(4) set autotrace traceonly

• SQL을 실행하지만 그 결과는 출력하지 않고, 실행계획과 실행통계만을 출력

(5) set autotrace traceonly explain

• SQL을 실행하지않고 실행계획만을 출력

(6) set autotrace traceonly statistics

• SQL을 실행하지만 그 결과는 출력하지 않고, 실행통계만을 출력

(1)~(3)수행 결과를 출력 해야 하므로 쿼리를 실제 수행
(4),(6)실행 통계를 보여줘야 하므로 쿼리를 실제 수행
(5) 번은 실행 계획만 출력하면 되므로 실제 수행하지 않음

AutoTrace 필요 권한

(1)Autotrace 기능을 실행계획 확인 용도로 사용한다면 Plan_Table만 생성 되어 있으면 가능

(2)실행통계 까지 확인 하려면 v_$sesstat, v_$statname, v_$mystat 뷰에 대한 읽기 권한이 필요

(3)dba, select_catalog_role 등의 롤을 부여받지 않은 사용자의 경우 별도의 권한 설정이 필요

(4)plustrace 롤을 생성하고 롤을 부여하는 것이 편리

SQL> @?/sqlplus/admin/plustrace.sql
SQL> grant plustrace to scott;

AutoTrace 수행 방식

(1)statistics 모드로 AutoTrace를 활성화 시키면 새로운 세션이 하나 열리면서 현재 세션의 통계정보를 대시 쿼리해서 보여주는 방식

• 쿼리 실행전 현재 세션의 수행통계 정볼르 저장했다가 쿼리 실행 후 수행통계와의 델타 값을 계산해서 보여주는 방식
• 만약 같은 세션에서 수행한다면 세션 통계를 쿼리 할때 수행통계까지 뒤썩이기 때문에 별도의 세션을 사용하는 것임

SQL> @session
USERNAME   PROGRAM     STATUS
--------- ------------ -------
SCOTT     sqlplus.exe  ACTIVE

(2)현재 위처럼 한개 세션이 존재 하는 상황에서 statistics 옵션을 활성하 하면 새로운 세션이 추가 되었음을 확인 할 수 있음

SQL> @session
USERNAME   PROGRAM     STATUS
--------- ------------ -------
SCOTT     sqlplus.exe  ACTIVE
SCOTT     sqlplus.exe  ACTIVE

(3)explain 모드로 변경 했을 경우 새롭게 열렸던 세센이 사라짐

SQL> set autotrace on explain
SQL> @session
USERNAME   PROGRAM     STATUS
--------- ------------ -------
SCOTT     sqlplus.exe  ACTIVE

참조문서

서적(오라클 성능 고도화 원리와해법 I) : http://book.daum.net/detail/book.do?bookid=KOR9788996246015