[20130117]Analyzing a SQL Trace File with SQL Statements.txt

[20130117]Analyzing a SQL Trace File with SQL Statements.txt

參考連結：


    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<=1;

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 <= 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;

/

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 < :id;

EXECUTE :id := 990;

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

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

EXECUTE :id := 20;

SELECT count(pad) FROM t WHERE id < :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 < :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'

          ));