<div class="note"> Beginning Oracle Programming 18장 "튜닝 도구" </div>

차례#

  1. autotrace[1]
  2. timed_statistics[2]
  3. sql_trace[3]
  4. tkprof[4]
  5. statspack[5]

오라클의 튜닝용 도구및 명령#

  1. EXPLAIN PLAN - 주어진 쿼리를 오라클이 어떻게 실행할것인지, 오라클이 사용할 계획은 무엇인지 설명하도록 하는 SQL명령
  2. AUTOTRACE - 오라클이 쿼리를 어떻게 실행할 것인지, 또는 이미 실행된 쿼리의 경우 어떻게 실행했는지를 보여주기 위한 SQL*Plus 의 기능
  3. TKPROF - 애플리케이션이 어떤 SQL구문을 어떻게 실행했는지 확인하기 위한 명령창 도구

select sql_text, hash_value, optimizer_mode
from v$sql
where lower(sql_textlike 쿼리문

autotrace[#1]#

autotrace 기능을 사용하기 위해서는 관리자 권한이 필요하다. 관리자 권한을 얻기 위해서는 다음과 같은 과정이 필요하다.

Microsoft Windows XP [Version 5.1.2600]
(CCopyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\fromm0>sqlplus "sys/c12345 as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 토 1월 5 20:17:57 2008

Copyright (c19822005, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다


SQL> create role plustrace;

롤이 생성되었습니다.

SQL>
SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL>
SQL> set echo off
SQL> grant plustrace to public;

권한이 부여되었습니다.

SQL>

여기서 실제로 해줄 작업은 @C:\oracle\product\10.2.0\db_1\sqlplus\admin\plustrce.sql 와 grant plustrace to public; 정도이다. 중간과정을 보면 알겠지만 이 작업으로 생성되는 뷰는 다음과 같다.

  1. v_$sesstat - 세션 통계 테이블
  2. v_$statname - 통계번호(ID)와 이름의 매핑 테이블
  3. v_$mystat - v$sesstat 테이블내에서 행을 확인하기 위한 세션 식별자를 검색하는데 사용되는 테이블

autotrace를 사용하는 방법은 다음처럼 set autotrace 명령어를 사용하면 된다.

C:\Documents and Settings\fromm0>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 토 1월 5 20:26:28 2008

Copyright (c19822005, Oracle.  All rights reserved.


다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set autotrace on
SQL> select
  2     empno,
  3     ename,
  4     job,
  5     dname,
  6     loc
  7  from emp, dept
  8  where emp.deptno = dept.deptno;

     EMPNO ENAME      JOB       DNAME          LOC
---------- ---------- --------- -------------- -------------
      7369 SMITH      CLERK     RESEARCH       DALLAS
      7499 ALLEN      SALESMAN  SALES          CHICAGO
      7521 WARD       SALESMAN  SALES          CHICAGO
      7566 JONES      MANAGER   RESEARCH       DALLAS
      7654 MARTIN     SALESMAN  SALES          CHICAGO
      7698 BLAKE      MANAGER   SALES          CHICAGO
      7782 CLARK      MANAGER   ACCOUNTING     NEW YORK
      7788 SCOTT      ANALYST   RESEARCH       DALLAS
      7839 KING       PRESIDENT ACCOUNTING     NEW YORK
      7844 TURNER     SALESMAN  SALES          CHICAGO
      7876 ADAMS      CLERK     RESEARCH       DALLAS

     EMPNO ENAME      JOB       DNAME          LOC
---------- ---------- --------- -------------- -------------
      7900 JAMES      CLERK     SALES          CHICAGO
      7902 FORD       ANALYST   RESEARCH       DALLAS
      7934 MILLER     CLERK     ACCOUNTING     NEW YORK

14 개의 행이 선택되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 351108634

--------------------------------------------------------------------------------
--------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   | SELECT STATEMENT             |         |    14 |   574 |     4   (0)00:00:01 |

|   |  NESTED LOOPS                |         |    14 |   574 |     4   (0)00:00:01 |

|   |   TABLE ACCESS FULL          | EMP     |    14 |   294 |     3   (0)00:00:01 |

|   |   TABLE ACCESS BY INDEX ROWID| DEPT    |     |    20 |     1   (0)00:00:01 |

|*  |    INDEX UNIQUE SCAN         | PK_DEPT |     |       |     0   (0)00:00:01 |

----------------------------------------------------------------------------------------


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

   - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
       1152  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

SQL>

  1. recursive calls - 쿼리를 실행하기 위해 오라클이 수행한 재귀 SQL구문의 수. 재귀 SQL 구문은 쿼리 분석, 테이블 내의 추가공간 확보등을 위해 실행되거나 DML작업에 의해 실행될수 있다. 이 값이 0이라는 것은 쿼리를 해석하는데 필요한 모든 정보가 SGA내의 딕셔너리나 라이브러리 캐시에 있다는 의미. 즉 오라클이 쿼리의 처리를 위한 부가적인 정보를 위해 다른 SQL구문을 실행하지 않았음을 알수 있다.
  2. db block gets - 데이터베이스로부터 current모드로 읽어들인 블록(현재 존재하는 블럭)을 말함. 대개 블럭은 consistent모드로 읽어들이는데, 이는 쿼리가 시작될때 존재하는 블럭을 의미한다. SELECT구문의 경우, 모든 블럭이 current모드로 읽혀지지는 않지만 대부분의 블럭이 current모드로 읽혀진다. 오라클은 변경을 위해 블럭의 현재 상태를 확인해야 하기 때문이다.
  3. consistent gets - consistent 모드로 읽어들인 블럭의 수를 의미한다. 쿼리의 경우 대부분의 블럭은 이러한 consistent모드로 읽혀진다. 데이터를 변경하는 DML작업의 경우 구문의 "쿼리" 부분은 consistent 모드로 읽혀지고, 쿼리의 "쓰기"부분은 current모드내에서 블럭을 변경하게 된다. 24개의 블럭이 consistent 읽기 모드상태에서 읽혀졌음을 알수 있다.
  4. physical reads
  5. redo size
  6. bytes sent via SQL*Net to client
  7. bytes received via SQL*Net from client
  8. SQL*Net roundtrips to/from client
  9. sorts (memory)
  10. sorts (disk)
  11. rows processed

autotrace 를 끌때는 다음처럼 처리해준다.

set autotrace off

앞서 본 예제와 달리 결과물을 제외하고 실행계획과 런타임 통계만을 출력하고자 할때는 다음처럼 처리해준다.

set autotrace traceonly

autotrace 모드

  1. set autotrace on - 구문결과, 실행계획, 세션 통계 정보 모두 출력
  2. set autotrace traceonly - 실행계획, 세션 통계 정보를 출력
  3. set autotrace on explain - 쿼리 결과와 실행 계획을 출력
  4. set autotrace on statistics - 쿼리 결과와 성능 통계를 출력
  5. set autotrace traceonly explain - 실행계획만을 출력. 쿼리를 실행하지 않고 쿼리 계획을 보고자 할때 사용.
  6. set autotrace traceonly statistics - 성능 통계만을 출력

timed_statistics[#2]#

timed_statistics는 오라클이 데이터베이스 내의 여러가지 작업에 대한 소요시간 정보를 수집할것인지를 제어하는데 사용됨. 디폴트값은 true. session이나 system 단위로 값을 변경할수 있다.

alter session set timed_statistics=true;

sql_trace[#3]#

애플리케이션에서 실행하는 모든 SQL에 대한 실행내용, 성능 통계 정보, 쿼리 계획등을 기록하는데 사용

sql_trace 를 활성화시키는 방법

  1. alter session set sql_trace=true | false
  2. sys.dbms_system.set_sql_trace_in_session - 데이터베이스내 모든 세션에 대한 sql_trace사용여부를 제어한다.
  3. alter session set events - alter session set sql_trace=true 구문을 실행하는 이벤트를 만들어 사용하는 방법이다. 이러한 이벤트를 사용하면 sql_trace가 만들어내는 정보뿐만 아니라, SQL에 사용된 여러가지 바인드 변수 정보또한 얻을수 있으므로 유용하다.

tkprof[#4]#

statspack[#5]#

스냅샷의 시작과 종료 시점간의 공유 풀에 저장된 모든 SQL문에 기초하여 4개의 뷰를 제공. 다음 항목 정보를 제공한다.
  1. gets 수치에 의해 정렬된 SQL
  2. reads 수치에 의해 정렬된 SQL
  3. executions 수치에 의해 정렬된 SQL
  4. parse calls 수치에 의해 정렬된 SQL

실행계획생성

explain plan
set statement_id='plan1'
for 
select *
from emp, dept
where emp.deptno=dept.deptno
and empno='7369';

여기서 statement_id 값을 명시적으로 선언해주는 편이 나중에 확인할때 도움이 된다. 실행계획은 다음과 같은 방법으로 조회할수 있다.

plan_table 테이블 직접 조회를 통한 실행계획확인

select id, operation, options, object_name, projection
from plan_table
where statement_id='plan1';

utlxpls.sql, utlxplp.sql 을 통한 실행계획확인

10g를 사용중인데 조금 이상함.. 확인 필요.

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2385808155

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   | SELECT STATEMENT             |         |     |    57 |     2   (0)00:00:01 |

|   |  NESTED LOOPS                |         |     |    57 |     2   (0)00:00:01 |

|   |   TABLE ACCESS BY INDEX ROWID| EMP     |     |    37 |     1   (0)00:00:01 |

|*  |    INDEX UNIQUE SCAN         | PK_EMP  |     |       |     0   (0)00:00:01 |

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

|   |   TABLE ACCESS BY INDEX ROWID| DEPT    |     |    80 |     1   (0)00:00:01 |

|*  |    INDEX UNIQUE SCAN         | PK_DEPT |     |       |     0   (0)00:00:01 |

----------------------------------------------------------------------------------------



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

   - access("EMPNO"=7369)
   - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 개의 행이 선택되었습니다.

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2385808155

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   | SELECT STATEMENT             |         |     |    57 |     2   (0)00:00:01 |

|   |  NESTED LOOPS                |         |     |    57 |     2   (0)00:00:01 |

|   |   TABLE ACCESS BY INDEX ROWID| EMP     |     |    37 |     1   (0)00:00:01 |

|*  |    INDEX UNIQUE SCAN         | PK_EMP  |     |       |     0   (0)00:00:01 |

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

|   |   TABLE ACCESS BY INDEX ROWID| DEPT    |     |    80 |     1   (0)00:00:01 |

|*  |    INDEX UNIQUE SCAN         | PK_DEPT |     |       |     0   (0)00:00:01 |

----------------------------------------------------------------------------------------



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

   - access("EMPNO"=7369)
   - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 개의 행이 선택되었습니다.

SQL>

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-6) was last changed on 06-Jan-2008 13:31 by DongGukLee