如何使用SQL_TRACE和10046事件 - 数据库编程

- ----------- ------------------------------

diagnostic_dest string /u01/app/oracle

SQL>
? ? ? 在测试数据库中，trace文件的具体路径为：/u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/。

?
查看trace文件
? ? ? tracefile 命名规则：_ora_.trc，其中pid为相应session所对应的OS PID。根据生成时间在/u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace目录下找到了trace文件HOEGH_ora_16427.trc。我们通过more命令查看文件内容，由于内容较多，只截取了开始部分内容如下。

[oracle@hoegh trace]$ more HOEGH_ora_16427.trc
?
Trace file /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_16427.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: hoegh.example.com

Release: 2.6.18-164.el5PAE

Version: #1 SMP Thu Sep 3 02:28:20 EDT 2009

Machine: i686

VM name: VMWare Version: 6

Instance name: HOEGH

Redo thread mounted by this instance: 1

Oracle process number: 23

*** 2015-05-27 09:56:36.240

*** SESSION ID:(142.1705) 2015-05-27 09:56:36.240

*** CLIENT ID:() 2015-05-27 09:56:36.240

*** SERVICE NAME:(SYS$USERS) 2015-05-27 09:56:36.240

*** ACTION NAME:() 2015-05-27 09:56:36.240

? ? ? 由于trace文件的可读性差，我们使用tkprof对trace文件进行格式化，以下是格式化后的信息内容，在这儿也只截取了开始部门，完整的内容贴到文档的最后供大家参考。
?
格式化trace文件

[oracle@hoegh trace]$ tkprof HOEGH_ora_16427.trc 10046.txt
?

TKPROF: Release 11.2.0.3.0 - Development on Wed May 27 11:20:36 2015

[oracle@hoegh trace]$

?
查看格式化后的trace报告

[oracle@hoegh trace]$ more 10046.txt
?

TKPROF: Release 11.2.0.3.0 - Development on Wed May 27 11:20:36 2015

[oracle@ hoegh trace]$

TKPROF: Release 11.2.0.3.0 - Development on Wed May 27 10:06:10 2015

Trace file: HOEGH_ora_16427.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

SQL ID: 4tk6t8tfsfqbf Plan Hash: 0

alter session set sql_trace=true

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 1 0.00 0.00 0 0 0 0
? ? ? 以上内容实现了一个应用sql_trace的简单案例，trace文件的信息量较大，我将会在下一篇文章中和大家分享。?
?
大师论点
? ? Eygle在课堂上提到他在面试时经常问的一个问题，“在什么技术上你做过深入思考得到了自己的观点”，绝大多数人的回答都是NO。
? ? Eygle提倡由点及面、由浅入深的学习方法，确实，不深入是不可能有收获的。
? ?
? ? ? 期待下一次eygle在微信讲堂的精彩分享。

hoegh

15.05.22

-- The End --

?
附完整trace报告? ? ? ? ? ?

TKPROF: Release 11.2.0.3.0 - Development on Wed May 27 11:20:36 2015
?

[oracle@ hoegh trace]$

TKPROF: Release 11.2.0.3.0 - Development on Wed May 27 10:06:10 2015

Trace file: HOEGH_ora_164

如何使用SQL_TRACE和10046事件(二)