CH03.오라클 성능 관리 - 01.Explain plan 02.AutoTrace

01 Explain plan

SQL을 수행하기 전에 실행계획을 확인하고자 할때 explain plan 명령을 사용합니다.
이 명령어를 사용하려면 먼저 plan_table을 생성해야 하는데, 아래 스크립트를 실행하면 됩니다.
sql>@?/rdbms/admin/utlxplan.sql

오라클 10g 부터는 기본적으로 sys.plan_table$이 생성됩니다. 그리고 이를 가리키는 public synonym을 기본적으로 생성해두기 때문에 사용자가 별도로 plan_table을 만드는 수고를 하지 않아도 됩니다.
SQL> select owner,synonym_name,table_owner,table_name
  2  from all_synonyms
  3  where synonym_name='PLAN_TABLE';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
--------------------------------------------------------------------------------
PUBLIC  PLAN_TABLE           SYS             PLAN_TABLE$

 

이제부터 explain plan for 명령을 수행하고 나면 해당 SQL에 대한 실행계획이 plan_table에 저장됩니다. 아래 예시에서 set statement_id= 는 생략가능합니다.

 

SQL> explain plan set statement_id= 'query1' for
  2  select * from scott.emp where empno=7788;

Explained.

 

AutoTrace 또는 TOAD같은 쿼리 툴에서 실행계획을 보여줄 때도 내부적으로 지금 설명한 절차대로 명령을 수행하고 plan_table에서 읽은 데이터를 포맷팅해서 출력해주는 것뿐입니다.

 

 

9i부터는 개별적으로 쿼리를 작성할 필요없이 아래에서 오라클이 제공해주는 utlxpls.sql 또는 utlplp.sql 스크립트를 이용하여 편리하게 볼 수 있습니다.

 

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("EMPNO"=7788)

 

요즘은 SQL을 프로그램 속에 하드코딩하지 않고 XML 포맷으로 별도 Repository에 저장해 둡니다. 모든 SQL에 대해 매일 explain plan 명령을 수행해 그 실행계획을 별도 테이블로 저장해 둔다면 이를 활요해 안정적인 시스템 운영 및 성능관리에 활용할 수 있습니다.

 

02. AutoTrace

SQL튜닝하는데 가장 즐겨사용하는 도구 중 하나입니다.

 

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL> select * from emp where empno=7900;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

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

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

   2 - access("EMPNO"=7900)

 

아래와 같은 옵션 조합에 따라 필요한 부분만 출력할 수 있습니다.

- set autotrace on

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

- set autotrace on explain

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

- set autotrace on statistics

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

- set autotrace traceonly

SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행통계와 실행계획을 출력

- set autotrace traceonly explain

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

- set autotrace traceonly statistics

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

 

 

AutoTrace 기능을 실행계획 확인 용도로만 사용한다면 plan_table만 생성돼 있으면 됩니다. 하지만 실행통계까지 함께 확인하려면 v_$sesstat,v_$statname,v_$mystat 뷰에 대한 읽기 권한이 필요합니다. 따라서 일반사용자들에게는 별도의 권한 설정이 필요합니다. 이들 뷰에 대한 읽기 권한을 일일이 부여해도 되지만 plustrace 롤을 생성하고 필요한 사용자들에게 부여하는 것이 편리합니다.

 

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> grant plustrace to scott;

Grant succeeded.

 

참고로, statistics 모드로 Autotrace를 활성화시키면 새로운 세션이 하나 열리면서 현재 세션의 통계정보를 대신 쿼리해서 보여줍니다. 쿼리를 실행하기 전 현재 세션의 수행 통계 정보를 어딘가에 저장했다가 쿼리 실행 후 수행통계와의 델타 값을 계산해 보여주는 방식입니다. 만약 같은 세션에서 수행한다면 세션 통계를 쿼리할 때의 수행통계까지 뒤섞이기 때문에 별도의 세션을 사용하는 것입니다.