学习笔记：SQL_trace开启对SQL语法的trace

news/2025/9/28 15:26:54/文章来源:href="https://blog.51cto.com/luoping/14126053" target="_blank"

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！由于博客中有大量代码，通过页面浏览效果更佳。

SQL_trace开启对SQL语法的trace

在11G，sql_trace增加只对某个sql语句生成10046。在一些特殊的环境中我们可能使用到，如：v$sql中sql_fulltext为空，就可以通过配置sql_trace来dump只定的sql语句，而不需要生成更多的其它的trace信息。关于sql_trace对sid,pname,orapid的使用方法，见mos文件。

1，DB与OS版本

www.htz.pw > select * from v$version;BANNER
——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionPL/SQL Release 11.2.0.3.0 – ProductionCORE    11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 – ProductionNLSRTL Version 11.2.0.3.0 – Production[oracle@www.htz.pw admin]$lsb_release -aLSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarchDistributor ID: RedHatEnterpriseASDescription:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)Release:        4Codename:       NahantUpdate8

2，对指定SQL生成10046信息

这里使用了trace_10046_sqlid.sql脚本，脚本内容如下：

www.htz.pw > !cat trace_10046_sqlid.sqlset echo offset lines 2000 pages 5000 verify off heading onundefine system_or_session;undefine level;undefine sqllist;#### 如果是多条sql，在sql之间使用,来隔开alter &&system_or_session set events‘sql_trace[SQL: &&sqllist ] level &level’;oradebug setmypidoradebug eventdump &&system_or_session;pause Press return to stop tracingalter &&system_or_session set events ‘sql_trace[SQL: &&sqllist ] off’;oradebug eventdump &&system_or_session;undefine system_or_session;undefine level;undefine sqllist;www.htz.pw > conn scott/oracleConnected.www.htz.pw > select /*+ htz*/count(*) from scott.emp;COUNT(*)———-14www.htz.pw > @find_sql.sqlEnter value for sql_text: htzEnter value for sql_id:SQL_ID         CHILD HASH_VALUE  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME————- —— ———- ———- ———- ————- ————- ————-SQLTEXT—————————————————————————————–1twzndftjst38      0 3004982376 2937609675          1           .00           .00 SCOTTselect /*+ htz*/count(*) from scott.empwww.htz.pw > @trace_10046_sqlid.sqlEnter value for system_or_session: systemEnter value for sqllist: 1twzndftjst38Enter value for level: 12System altered.Statement processed.sql_trace[SQL: 1twzndftjst38 ] level 1210949 trace name context forever28401 trace name context forever, level 1Press return to stop tracing

sql_trace配置后，只会对新连接的会话有效，原来已经连接的会话不生效的。

打开session2,session3连接的会话执行

www.htz.pw >  select /*+ htz*/count(*) from scott.emp;COUNT(*)———-14

可以看到生成下面的日志

PARSING IN CURSOR #182936901240 len=39 dep=0 uid=83 oct=3 lid=83 tim=1409812013408343 hv=3004982376 ad=’883540b0′ sqlid=’1twzndftjst38′select /*+ htz*/count(*) from scott.empEND OF STMTEXEC #182936901240:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim=1409812013408342WAIT #182936901240: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812013408407FETCH #182936901240:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim=1409812013408466STAT #182936901240 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=0 pw=0 time=35 us)’STAT #182936901240 id=2 cnt=14 pid=1 pos=1 obj=79714 op=’INDEX FULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=34 us cost=1 size=0 card=14)’FETCH #182936901240:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2937609675,tim=1409812013408672WAIT #182936901240: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812013408689

已经trace成功

生成了exec,wait,stat的信息，因为SQL没有绑定变量，所以未见绑定变量信息

3 关闭trace

回来session1

www.htz.pw > @trace_10046_sqlid.sqlEnter value for system_or_session: systemEnter value for sqllist: 1twzndftjst38Enter value for level: 12System altered.Statement processed.sql_trace[SQL: 1twzndftjst38 ] level 1210949 trace name context forever28401 trace name context forever, level 1Press return to stop tracing这是之前的显示的内容按回车，取消trace。System altered.10949 trace name context forever28401 trace name context forever, level 1

4，验证是否关闭trace

回到session 2窗口中

www.htz.pw > /COUNT(*)———-14仍然生成了trace信息PARSING IN CURSOR #182936887640 len=39 dep=0 uid=83 oct=3 lid=83 tim=1409812149842911 hv=3004982376 ad=’883540b0′ sqlid=’1twzndftjst38′select /*+ htz*/count(*) from scott.empEND OF STMTEXEC #182936887640:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim=1409812149842909WAIT #182936887640: nam=’SQL*Net message to client’ ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812149842978FETCH #182936887640:c=999,e=81,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim=1409812149843079STAT #182936887640 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=0 pw=0 time=83 us)’STAT #182936887640 id=2 cnt=14 pid=1 pos=1 obj=79714 op=’INDEX FULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=67 us cost=1 size=0 card=14)’FETCH #182936887640:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2937609675,tim=1409812149843273WAIT #182936887640: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812149843301

执行一条其它的SQL后再回来执行原来的SQL

www.htz.pw > select count(*) from scott.emp;COUNT(*)———-14

再次执行trace的sql语句

www.htz.pw > select /*+ htz*/count(*) from scott.emp;COUNT(*)———-14

在trace中没有看到任何的TRACE了内容了

回到session 3窗口中

www.htz.pw > /COUNT(*)———-14PARSING IN CURSOR #182936910800 len=39 dep=0 uid=83 oct=3 lid=83 tim=1409812294019897 hv=3004982376 ad=’883540b0′ sqlid=’1twzndftjst38′select /*+ htz*/count(*) from scott.empEND OF STMTEXEC #182936910800:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim=1409812294019895WAIT #182936910800: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812294019964FETCH #182936910800:c=0,e=35,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim=1409812294020018STAT #182936910800 id=1 cnt=1 pid=0 pos=1 obj=0 op=’SORT AGGREGATE (cr=1 pr=0 pw=0 time=42 us)’STAT #182936910800 id=2 cnt=14 pid=1 pos=1 obj=79714 op=’INDEX FULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=40 us cost=1 size=0 card=14)’FETCH #182936910800:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2937609675,tim=1409812294020203WAIT #182936910800: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812294020231

还在成trace，所以排除窗口2是由于时间到而停止trace sql内容

再次执行其它SQL

www.htz.pw > select count(*) from SEQ;COUNT(*)———-225www.htz.pw > select /*+ htz*/count(*) from scott.emp;COUNT(*)———-14

同时trace文件中再也没有生成sqlid的trace内容

*** 2014-09-04 14:33:02.697WAIT #182936910800: nam=’SQL*Net message from client’ ela= 88676734 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812382696974CLOSE #182936910800:c=0,e=12,dep=0,type=1,tim=1409812382697082

通过session 2,session 3操作，可以猜测，取消trace后，对会话来说，需要执行一条非trace的SQL语句后才会生效，如果一直执行trace sql，那么会一直生成10046的trace信息的。

------------------作者介绍-----------------------

姓名：黄廷忠现就职：Oracle中国高级服务团队曾就职：OceanBase、云和恩墨、东方龙马等电话、微信、QQ：18081072613

个人博客: (http://www.htz.pw)

CSDN地址: (https://blog.csdn.net/wwwhtzpw)

博客园地址: (https://www.cnblogs.com/www-htz-pw)