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.

sql_trace、10046、10053、tkprof

相關文章