10046事件是一个很好的排查sql语句执行缓慢的内部事件,具体设置方式如下:
根据10046事件跟踪SQL语句
1、 alter session set events '10046 trace name context forever,level 12';
2、执行SQL语句
3、关闭10046事件
alter session set events '10046 trace name context off';
4、查找生成的TARCE文件
select value from v$diag_info where name='Default Trace File';
这边可以利用一个小工具tkprof来格式化tarce文件,方便阅读
用法: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] …
tracefile:你要分析的trace文件
outputfile:格式化后的文件
explain=user/password@connectstring
table=schema.tablename
注1:这两个参数是一起使用的,通过连接数据库对在trace文件中出现的每条sql语句查看执行计划,并将之输出到outputfile中
注2:该table必须是数据库中不存在的,如果存在会报错
print=n :只列出最初N个sql执行语句
insert=filename :会产生一个sql文件,运行此文件可将收集到的数据insert到数据库表中
sys=no :过滤掉由sys执行的语句
record=filename :可将非嵌套执行的sql语句过滤到指定的文件中去
waits=yes|no :是否统计任何等待事件
aggregate=yes|no :是否将相同sql语句的执行信息合计起来,默认为yes
sort= option :设置排序选项,选项如下
举例:
1.列出前2条sql语句的执行情况:
[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt print=2
2.将数据保存到数据库:
[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt insert=insert.sql
执行后会在当前目录产生insert.sql文件,执行该文件即可将数据保存到数据库
3.提取sql执行语句:
[oracle@silent trace]$ tkprof orcl_ora_4601_10046.trc out.txt record=sqlstr.sql
[oracle@silent trace]$ cat sqlstr.sql
4.产生执行计划:
[oracle@silent trace]$ tkprof orcl_ora_123981.trc out.txt explain=test/oracle@PDB table=test.a,test.b
在产生的out.txt文件中会体现其执行计划