SQL Trace file의 내용을 SQL 문장으로 분석

Analyzing a SQL Trace File with SQL Statements

많은 경우 그렇듯이 어떤 기능은 제공하는 자의 예상과는 다른 목적으로 사용될 수 있다.

 

오라클 11g부터 지원하는 DBMS_SQLTUNE의 SELECT_SQL_TRACE 함수는 본래 SQL trace 파일의 내용을

SQL tuning set에 로딩하는 것이 목적이었는데, 이 포스트에서는 다소 다른 용도로 활용하려고 한다.

 

사용할 수 있는 오라클 버전은 11.1.0.7 이상이어야 한다. 

 

- SELECT_SQL_TRACE 함수

- SELECT_SQL_TRACE 함수의 리턴값

 

1.테스트 Setup

 

drop table t purge;

 

CREATE TABLE t
AS
SELECT rownum AS id, rpad('*',1000,'*') AS pad
FROM dual
CONNECT BY level <= 10000
ORDER BY dbms_random.value;

 

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

 

BEGIN
  dbms_stats.gather_table_stats(
      ownname          => user,
      tabname          => 't',
      estimate_percent => 100,
      method_opt       => 'for all columns size 1'
    );
END;
/

2.SQL Trace 설정

 

execute dbms_monitor.session_trace_enable(binds => TRUE, plan_stat => 'ALL_EXECUTIONS')

 

3.몇몇 쿼리 수행

 

variable id number

EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;

 

EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;

 

EXECUTE :id := 20;
SELECT count(pad) FROM t WHERE id < :id;

 

SELECT sum(id) FROM t;

 

4.SQL Trace 설정 해제 및 Trace 파일 이름 확인

 

execute dbms_monitor.session_trace_disable

 

set serveroutput on

exec print_table('select * from  v$diag_info')

 

SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File';

 

  VALUE
  -------------------------------------------------------------------
  /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14663.trc

 

5.디렉토리 객체 생성, 뷰 생성 및 내용 확인

 

CREATE DIRECTORY trace AS '/u01/app/oracle/diag/rdbms/orcl/orcl/trace/';

 

CREATE OR REPLACE VIEW V
AS
SELECT SQL_ID, FORCE_MATCHING_SIGNATURE, SQL_TEXT, BIND_DATA, PARSING_SCHEMA_NAME, MODULE,
       ACTION, ELAPSED_TIME, CPU_TIME, BUFFER_GETS, DISK_READS, DIRECT_WRITES, ROWS_PROCESSED,
       FETCHES, EXECUTIONS, END_OF_FETCH_COUNT, OPTIMIZER_COST, OPTIMIZER_ENV, PRIORITY,
       COMMAND_TYPE, FIRST_LOAD_TIME, STAT_PERIOD, ACTIVE_STAT_PERIOD, OTHER, PLAN_HASH_VALUE
FROM table(dbms_sqltune.select_sql_trace(
             directory => 'TRACE',
             file_name => 'orcl_ora_14663.trc',
             select_mode => 2)) t;

 

DESC V;

 

set serveroutput on

exec print_table('select * from v') 

 

FORCE_MATCHING_SIGNATURE      : 0
SQL_TEXT                      : SELECT count(pad) FROM t WHERE id < :id
BIND_DATA                     :
PARSING_SCHEMA_NAME           : HR
MODULE                        :
ACTION                        :
ELAPSED_TIME                  : 853
CPU_TIME                      : 1000
BUFFER_GETS                   : 11
DISK_READS                    : 0
DIRECT_WRITES                 :
ROWS_PROCESSED                : 1
FETCHES                       : 2
EXECUTIONS                    : 1
END_OF_FETCH_COUNT            :
OPTIMIZER_COST                :
OPTIMIZER_ENV                 :
PRIORITY                      :
COMMAND_TYPE                  : 3
FIRST_LOAD_TIME               :
STAT_PERIOD                   :
ACTIVE_STAT_PERIOD            :
OTHER                         :
PLAN_HASH_VALUE               : 4294967295
-----------------

... 생략 ...

 

6.현재 유저에 의해 수행된 SQL, 경과시간, 수행 횟수 집계 

 

SELECT sql_id,
       sum(elapsed_time) AS elapsed_time,
       sum(executions) AS executions,
       round(sum(elapsed_time)/sum(executions)) AS elapsed_time_per_execution
FROM table(dbms_sqltune.select_sql_trace(
             directory => 'TRACE',
             file_name => 'orcl_ora_14663.trc',
             select_mode => 2)) t
WHERE parsing_schema_name = user
GROUP BY sql_id
ORDER BY elapsed_time DESC;

 

7.SQL 문장

 

SELECT sql_text
FROM table(dbms_sqltune.select_sql_trace(
             directory => 'TRACE',
             file_name => 'orcl_ora_14663.trc',
             select_mode => 2)) t
WHERE sql_id = 'asth1mx10aygn';

 

8.다양한 통계 확인

 

SELECT plan_hash_value, executions, fetches, elapsed_time, cpu_time, disk_reads, buffer_gets, rows_processed
FROM table(dbms_sqltune.select_sql_trace(
             directory => 'TRACE',
             file_name => 'orcl_ora_14663.trc',
             select_mode => 2)) t
ORDER BY elapsed_time DESC;

9.Bind 변수의 값 확인

 

(참고.bind 변수는 오라클에서 사용하는 용어이고, 다른 DBMS에서는 host 변수라고 부른다.)

 

col type format a20

SELECT elapsed_time,
       value(b).gettypename() AS type,
       value(b).accessnumber() AS value
FROM table(dbms_sqltune.select_sql_trace(
             directory => 'TRACE',
             file_name => 'orcl_ora_14663.trc',
             select_mode => 2)) t,
     table(bind_list) b
WHERE sql_id = 'asth1mx10aygn'
ORDER BY elapsed_time DESC;

10.실행계획 확인

 

begin
  dbms_sqltune.drop_sqlset('TEST');
end;
/

 

DECLARE
  c sys_refcursor;
BEGIN

  dbms_sqltune.create_sqlset('TEST');
  OPEN c FOR
    SELECT value(t)
    FROM table(dbms_sqltune.select_sql_trace(
               directory => 'TRACE',
               file_name => 'orcl_ora_14663.trc',
               select_mode => 2)) t;
  dbms_sqltune.load_sqlset('TEST', c);
  CLOSE c;
END;
/

 

SELECT *
FROM table(dbms_xplan.display_sqlset(
             sqlset_name => 'TEST',
             sql_id => 'asth1mx10aygn'
          ));