[20130117]Analyzing a SQL Trace File with SQL Statements.txt
参考链接：
http://antognini.ch/2010/04/analyzing-a-sql-trace-file-with-sql-statements/
As of Oracle Database 11g the DBMS_SQLTUNE package provides the SELECT_SQL_TRACE function. Its purpose is to load the
content of a SQL trace file into a SQL tuning set. But, as it often happens, a feature can be (mis)used for another purpose.
The aim of this post is to show how to take advantage of this function to display through SQL statements the content of a
SQL trace file.
重复作者的测试，我的测试环境：
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
参考链接：
http://antognini.ch/2010/04/analyzing-a-sql-trace-file-with-sql-statements/
As of Oracle Database 11g the DBMS_SQLTUNE package provides the SELECT_SQL_TRACE function. Its purpose is to load the
content of a SQL trace file into a SQL tuning set. But, as it often happens, a feature can be (mis)used for another purpose.
The aim of this post is to show how to take advantage of this function to display through SQL statements the content of a
SQL trace file.
重复作者的测试，我的测试环境：
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
CREATE TABLE t
AS
SELECT rownum AS id, rpad('*',1000,'*') AS pad
FROM dual
CONNECT BY level
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;
/
execute dbms_monitor.session_trace_enable(binds => TRUE, plan_stat => 'ALL_EXECUTIONS');
variable id number;
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id
SELECT count(pad) FROM t WHERE id
EXECUTE :id := 20;
SELECT count(pad) FROM t WHERE id
SELECT sum(id) FROM t;
execute dbms_monitor.session_trace_disable;
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------------
/u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_8506.trc
CREATE DIRECTORY trace AS '/u01/app/oracle11g/diag/rdbms/test/test/trace/';
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 => 'test_ora_8506.trc',
select_mode => 2 -- all executions
)) t
WHERE parsing_schema_name = user
GROUP BY sql_id
ORDER BY elapsed_time DESC;
SQL_ID ELAPSED_TIME EXECUTIONS ELAPSED_TIME_PER_EXECUTION
------------- ------------ ---------- --------------------------
asth1mx10aygn 24808 4 6202
6tgnxwpymddqc 1800 1 1800
SELECT sql_text
FROM table(dbms_sqltune.select_sql_trace(
directory => 'TRACE',
file_name => 'test_ora_8506.trc',
select_mode => 1 -- only first execution
)) t
WHERE sql_id = 'asth1mx10aygn';
SQL_TEXT
--------------------------------------------------------
SELECT count(pad) FROM t WHERE id
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 => 'test_ora_8506.trc',
select_mode => 2 -- all executions
)) t
WHERE sql_id = 'asth1mx10aygn'
ORDER BY elapsed_time DESC;
PLAN_HASH_VALUE EXECUTIONS FETCHES ELAPSED_TIME CPU_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
--------------- ---------- ---------- ------------ ---------- ---------- ----------- --------------
4294967295 1 2 13677 5999 0 1434 1
4294967295 1 2 8223 3999 0 992 1
4294967295 1 2 1604 1000 0 21 1
4294967295 1 2 1304 2000 0 11 1
column value format 9999999999999
SELECT elapsed_time,
value(b).gettypename() AS type,
value(b).accessnumber() AS value
FROM table(dbms_sqltune.select_sql_trace(
directory => 'TRACE',
file_name => 'test_ora_8506.trc',
select_mode => 2 -- all executions
)) t,
table(bind_list) b
WHERE sql_id = 'asth1mx10aygn'
ORDER BY elapsed_time DESC;
ELAPSED_TIME TYPE VALUE
------------ ---------------------------------------- --------------
13677 SYS.NUMBER 990
8223 SYS.NUMBER 990
1604 SYS.NUMBER 20
1304 SYS.NUMBER 10
===========
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 => 'test_ora_8506.trc',
select_mode => 2 -- all executions
)) t;
dbms_sqltune.load_sqlset('TEST', c);
CLOSE c;
END;
/
SELECT *
FROM table(dbms_xplan.display_sqlset(
sqlset_name => 'TEST',
sql_id => 'asth1mx10aygn'
));
