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 이상이어야 한다.
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'
));