【实践sql_trace】dbms_system包SET_SQL_TRACE_IN_SESSION开启sql跟踪

 

理论

通过查看Oracle中的trace文件，往往有助于我们去分析数据库dump原因，执行计划的内部过程，绑定变量的使用情况，以及产生死锁的原因等等。我们可以通过如下3种方式手工生产trace文件
开启trace跟踪
会话级 

 

SQL> alter session set sql_trace=true;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> exec sys.dbms_system.set_sql_trace_in_session(10 , 39196 , true); 
实例级
SQL> alter system set sql_trace=true; 
关闭trace跟踪 
会话级 
SQL> alter session set sql_trace=false;
SQL> alter session set events '10046 trace name context off';
SQL> exec sys.dbms_system.set_sql_trace_in_session(10 , 39196 , false); 
实例级
SQL> alter system set sql_trace=false; 

命令格式为：
tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
参数说明：
tracefile：要分析的trace文件 
outputfile：格式化后的文件
explain=user/password@connectstring 
table=schema.tablename 
    上述两个参数是一起使用的，explain指示tkprof要为在跟踪文件中找到的每个SQL语句提供一个执行计划。
    这是通过执行SQL语句EXPLAIN PLAN通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中。
    指定的table名将提供给EXPLAIN PLAN语句。
print=n：只列出最初N个sql执行语句，默认是无限制的，只有在和参数sort一起使用的时候才有意义
insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中
sys=no：sys用户运行的SQL语句(例如，解析操作阶段对数据字典的递归查询)不输出到输出文件中。
record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去 
waits=yes|no：是否统计任何等待事件，默认是yes 
aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes 
sort= option：设置排序选项，可以用逗号分隔多个选项。默认是跟踪文件中发现的SQL顺序。具体选项可以查看tkprof的命令帮助输出得到。

 

 

 

实践

Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 2月 28 09:01:24 2019

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> grant execute on DBMS_SYSTEM to test;

Grant succeeded.

SQL> execute dbms_system.SET_SQL_TRACE_IN_SESSION(143,3770,true);

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\Administrator>tkprof E:\oracle\product\10.2.0\admin\or
cl\udump\orcl_ora_2608.trc c:\1.txt

TKPROF: Release 10.2.0.3.0 - Production on 星期四 2月 28 09:14:54 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

C:\Documents and Settings\Administrator>start \1.txt

 

C:\Documents and Settings\Administrator>tkprof E:\oracle\product\10.2.0\admin\or
cl\udump\orcl_ora_2608.trc c:\2.txt table=test.t1 explain=test/test@ltsi210

TKPROF: Release 10.2.0.3.0 - Production on 星期四 2月 28 09:32:09 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

C:\Documents and Settings\Administrator>tkprof E:\oracle\product\10.2.0\admin\or
cl\udump\orcl_ora_2608.trc c:\2.txt sys=no insert=\3.sql record=\4.sql waits=no

TKPROF: Release 10.2.0.3.0 - Production on 星期四 2月 28 09:35:09 2019

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

C:\Documents and Settings\Administrator>

日志内容参见下载文件中，1.txt,2.txt,3.sql,4.sql