SQL trace, 10046 and tkprof 介绍

捕获正在处理的SQL会话信息的最快方法是打开SQL跟踪或在一段代表性的时间内设置10046事件。生成的跟踪文件可以以原始状态读取，也可以使用tkprof实用程序进行转换。下面针对具体的案例进行简单的demo, 大家也可以在测试环境进行演练。

生成SQL跟踪文件

oracle数据库中有多种方法对数据库的会话进行跟踪，下面用常规的方法进行演示。以下方法可用于数据库10g以上的所有版本。

-- 适用于所有版本.SQL> ALTER SESSION SET sql_trace=TRUE;SQL> ALTER SESSION SET sql_trace=FALSE;SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';SQL> ALTER SESSION SET EVENTS '10046 trace name context off';SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>'');SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>'');-- 自8i起一直支持，需要从SQL plus执行。.SQL> CONN sys/password AS SYSDBA;  -- User must have SYSDBA.SQL> ORADEBUG SETMYPID;            -- Debug current session.SQL> ORADEBUG SETOSPID 1234;       -- Debug session with the specified OS process.SQL> ORADEBUG SETORAPID 123456;    -- Debug session with the specified Oracle process ID.SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;SQL> ORADEBUG TRACEFILE_NAME;      -- Display the current trace file.SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF; -- 所有版本都支持, 需要加载DBMS_SUPPORT包。SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);SQL> EXEC DBMS_SUPPORT.stop_trace;SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE);SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234);

默认情况下，DBMS_SUPPORT包不存在，但可以通过执行“dbmsupp.sql”脚本作为SYS用户加载。

@$ORACLE_HOME/rdbms/admin/dbmssupp.sql

对于需要跟踪级别的方法，以下是有效值。

0-无痕迹。比如关闭sql_trace。

2-等价于常规sql_trace。

4-与2相同，但添加了绑定变量值。

8-与2相同，但添加了等待事件。

12-与2相同，但同时具有绑定变量值和等待事件。

跟踪单个SQL语句

通过将所需的SQL_ID替换为以下语句，可以为单个SQL语句启动SQL跟踪。

SQL> -- SQL Trace (10046)SQL> ALTER SYSTEM SET EVENTS 'sql_trace [sql:&&sql_id] bind=true, wait=true';SQL> -- 10053SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';

对于最近的SQL，可以在V$SQL或V$SQLSTAT视图中找到语句的SQL_ID，对于历史语句，可以从AWR存储库的DBA_HIST_SQLSTAT查看。

识别跟踪文件

Oracle允许在会话级别设置TRACEFILE_IDENTIFIER参数，允许您在跟踪文件名中包含一些可识别的文本。

ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION";

也可以使用以下SQL查询的方式，找到具体的trace 文件：

SET LINESIZE 100COLUMN trace_file FORMAT A60SELECT s.sid,       s.serial#,       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||           '_ora_' || p.spid || '.trc' AS trace_fileFROM   v$session s,       v$process p,       v$parameter paWHERE  pa.name = 'user_dump_dest'AND    s.paddr = p.addrAND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

如果使用的是windows环境，则需要将连接字符串中的“/”更改为“\”。

由于引入了诊断视图，Oracle 11g中的跟踪文件识别更简单。identity_trace_file.sql的Oracle 11g版本如下所示。

SET LINESIZE 100COLUMN value FORMAT A60SELECT valueFROM   v$diag_infoWHERE  name = 'Default Trace File';

您可以使用V$SSESSION和V$PROCESS视图标识特定会话的跟踪文件。

SELECT p.tracefileFROM   v$session s       JOIN v$process p ON s.paddr = p.addrWHERE  s.sid = 635;TRACEFILE------------------------------------------------------------------/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trcSQL>

在Oracle 12.2中，V$DIAG_TRACE_FILE和V$DIAG_TRACE_FILE_CONTENTS视图使其更加简单。V$DIAG_TRACE_FILE视图标识实例ADR home下当前的所有跟踪文件。

SELECT trace_filenameFROM   v$diag_trace_fileWHERE  con_id = 3;

V$DIAG_TRACE_FILE_CONTENTS允许您查询跟踪文件的内容。

SELECT payloadFROM   v$diag_trace_file_contentsWHERE  trace_filename = 'cdb1_j002_16830.trc'ORDER BY line_number;

tkprof

前面讨论的方法生成的SQL跟踪文件可以以原始形式读取，也可以通过tkprof实用程序将其转换为更易于阅读的形式。

$ tkprofUsage: tkprof tracefile outputfile [explain= ] [table= ]              [print= ] [insert= ] [sys= ] [sort= ]  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.  print=integer    List only the first 'integer' SQL statements.  aggregate=yes|no  insert=filename  List SQL statements and data inside INSERT statements.  sys=no           TKPROF does not list SQL statements run as user SYS.  record=filename  Record non-recursive statements found in the trace file.  waits=yes|no     Record summary for any wait events found in the trace file.  sort=option      Set of zero or more of the following sort options:    prscnt  number of times parse was called    prscpu  cpu time parsing    prsela  elapsed time parsing    prsdsk  number of disk reads during parse    prsqry  number of buffers for consistent read during parse    prscu   number of buffers for current read during parse    prsmis  number of misses in library cache during parse    execnt  number of execute was called    execpu  cpu time spent executing    exeela  elapsed time executing    exedsk  number of disk reads during execute    exeqry  number of buffers for consistent read during execute    execu   number of buffers for current read during execute    exerow  number of rows processed during execute    exemis  number of library cache misses during execute    fchcnt  number of times fetch was called    fchcpu  cpu time spent fetching    fchela  elapsed time fetching    fchdsk  number of disk reads during fetch    fchqry  number of buffers for consistent read during fetch    fchcu   number of buffers for current read during fetch    fchrow  number of rows fetched    userid  userid of user that parsed the cursor    ......

waits参数仅在Oracle9i中添加，因此在此版本之前，必须从原始跟踪文件中读取wait信息。绑定变量的值必须从原始文件中读取，因为它们不会显示在tkprof输出中。

会话跟踪演示

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';Session altered.SQL>     INSERT INTO sql_trace_test (id, description)    VALUES (1, 'Description for ' );SQL>    commit;SQL> ALTER SESSION SET EVENTS '10046 trace name context off';Session altered.SQL>

查询Trace文件名

SELECT p.tracefileFROM   v$session s       JOIN v$process p ON s.paddr = p.addrWHERE  s.sid = 635;TRACEFILE------------------------------------------------------------------/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trcSQL>

以下命令使用tkprof实用程序转换跟踪文件，将转换后的输出放在translate.txt文件中。explain和table参数已设置为允许显示执行计划，sys参数阻止显示递归SQL。

$ cd /u01/app/oracle/admin/DEV/udump/$ tkprof dev1_ora_367660.trc translated.txt explain=test/test table=sys.plan_table sys=no waits=yes

文件的内容如下所示：

针对此会话执行的每个语句，输出的文件内容包含该文件包含解析、执行和获取统计信息的记录、必要时的执行计划以及会话等待列表。