ORACLE数据库会话跟踪

练习二：使用alter session跟踪会话 1. 使用test表的owner用户连接数据库，执行如下脚本 alter session set events='10046 trace name context forever, level 12'; select * from test where id<100; ALTER SESSION SET EVENTS '10046 trace name context off'; 2. 到udump下找到生成的trc文件并格式化， tkprof <filename.trc> test02_output.txt sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10 3. 仔细阅读test02_output.txt文件，获得体会
Level 1 2 Action Print statistics and computations Print computations only
会话跟踪的作用
能够对会话进行连续跟踪，跟踪的过程为会话的每一个操作都会进行流水记录，每 一个记录均有时间戳标记。 从日志中，能够得到如下信息： 1，了解会话运行的SQL程序，包括执行计划、执行每一个操作消耗的时间、每一个 3200 SQL的资源消耗准确值； 2，了解会话的等待情况； 3，了解系统对会话的影响； 。。。。。
Level 1 4 8 12 Action Print SQL statements, execution plans and execution statistics As level 1 plus bind variables As level 1 plus wait statistics As level 1 plus bind variables and wait statistics
会话跟踪的方法
序号 1 使用工具 使用 dbms_system 使用alter session 命令 execute 3200 dbms_system.set_sql_trace_in_session(sid, serial#,True/false); alter session set events='10046 trace name context forever, level 12'; 使用场景 对正在运行的会话进行跟踪
Event 10046 - Enable SQL Statement Trace This event can be used to dump SQL statements executed by a session with execution plans and statistics. Bind variable and wait statistics can optionally be included. Level 12 is the most detailed. For example ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; Levels are
3200
下列命令会生成一个按照运行时间的长短进行排序，并且结果只含有运行时间最长的10 个查询语句的跟踪文件。
特别需要注意的是，tkprof获得时间统计，需要数据库级别的TIMED_STATISTICS=true
TKPROF工具使用方法
TKPROF Options ~~~~~~~~~~~~~~ print=integer List only the first 'integer' SQL statements. 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 statements found in the trace file. sort=option Set of zero or more of the following sort options: prscnt number of times parse was called 3200 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
oradebug setospid <OS proccess id> oradebug event 10046 trace name context off
对数据库主机的异常进程进行 跟踪
TKPROF工具使用方法
对事件进行跟踪后，会在数据库的udump目录下生成一个类似<SID>_ora_<PID>.trc 名称的trc文件，这个文件原始是几乎丌可读的，需要进行格式化。 TKPROF就是一个ORACLE日志的格式化工具。
3200
5.
6.
到udump下找到生成的trc文件并格式化， tkprof <filename.trc> test03_output.txt sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10 仔细阅读test03_output.txt文件，获得体会
2
程序调试时，加载到代码里
ALTER SESSION SET EVENTS '10046 trace name context off'; 3 使用oradebug oradebug setospid <OS proccess id> oradebug unlimit oradebug event 10046 trace name context forever, level 12
Event 10053 - Dump Optimizer Decisions This event can be used to dump the decisions made by the optimizer when parsing a statement. Level 1 is the most detailed For example ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; Levels are
练习
练习三：使用oradebug跟踪会话 1. 使用test表的owner用户连接数据库，查询到这个会话在操作系统的pid; 2. 使用‘sysdba’权限登录数据库，并执行 oradebug setospid <OS proccess id> oradebug unlimit oradebug event 10046 trace name context forever, level 12 3. 4. 在之前的用户会话中执行 select count(*) from test where id<30000; 在sysdba窗口执行oradebug setos pid <OS proccess id> oradebug event 10046 trace name context off
LOGO
ORACLE数据库会话跟踪方法
四川成享软件—刘德鹏
2013.06
目
录
3200
Event 10046与10053 会话跟踪的作用 会话跟踪的方法
TKPROF工具使用方法 练习
Event 10046不10053
Event即事件，对Event的关注即是对指定ORACLE事件进行关注。 ORA-10046: enable SQL statement timing SQL运行的时间片上事件状态，代表SQL的运行过程 ORA-10053: CBO Enable optimizer trace 3200 CBO优化器选择的过程