sql_trace的使用方法
SQL> ALTER SESSION SET sql_trace=true;
SQL> SQL语句;
SQL> alter session set sql_trace=false;
SQL> select * from v$diag_info;
备注：Sql_trace参数千万不要在system级设置，否则所有会话都会跟踪，将极大影响性能
11G已经开始使用sql_trace替代掉10046了，sql_trace的内容比10046还多了一些数据字典的查询动作
SQL_TRACE enables or disables the SQL trace facility. Setting this parameter to true provides information on tuning that you can use to improve performance.
ALTER SESSION SET SQL_TRACE等同于DBMS_SESSION.SET_SQL_TRACE (sql_trace boolean)
但是session级别SQL_TRACE只能跟踪自己的trace文件，如果要跟踪其他session的trace文件则使用DBMS_MONITOR.SESSION_TRACE_ENABLE
TRCSESS可以将多个跟踪文件保存到一个文件中

DBMS_SESSION.SET_SQL_TRACE Procedure:This procedure turns tracing on or off.

DBMS_MONITOR.SESSION_TRACE_ENABLE Procedure:This procedure enables a SQL trace for the given Session ID on the local instance
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);

10046的使用方法
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SQL> SQL语句;
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
SQL> select * from v$diag_info;
实践发现level 12也没比level 1多很多内容，其实用level 1也就够了

10053的使用方法
SQL> alter session set events '10053 trace name context forever, level 1';
SQL> SQL语句;
或SQL> explain plan for SQL语句;
SQL> alter session set events '10053 trace name context off';
SQL> select * from v$diag_info;
10053生成的dump文件里面可以看到影响优化器的参数

TKPROF转换文件的命令如下
tkprof XX.trc XX.txt sort=execpu

tkprof只解读(格式化)sql_trace或10046的trace文件，不能解读10053的trace文件

TKPROF其中的三列解读
DISK：Total number of data blocks physically read from the data files on disk for all parse, execute, or fetch calls.
QUERY：Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
CURRENT：Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

ITPub博客

sql_trace、10046、10053、tkprof

最新文章