当前位置: 首页 > SQL > 正文

sql_trace开启对sql语法的trace

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

欢迎大家加入QQ群:ORACLE数据库超级群讨论

 

1DBOS版本

www.htz.pw > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

 

[oracle@www.htz.pw admin]$lsb_release -a

LSB Version:    :core-3.0-amd64:core-3.0-ia32:core-3.0-noarch:graphics-3.0-amd64:graphics-3.0-ia32:graphics-3.0-noarch

Distributor ID: RedHatEnterpriseAS

Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 8)

Release:        4

Codename:       NahantUpdate8

2,对指定SQL生成10046信息

这里使用了trace_10046_sqlid.sql脚本,脚本内容如下:

www.htz.pw > !cat trace_10046_sqlid.sql

set echo off

set lines 2000 pages 5000 verify off heading on

undefine system_or_session;

undefine level;

undefine sqllist;

#如果是多条sql,在sql之间使用,来隔开

alter &&system_or_session set events

    ‘sql_trace[SQL: &&sqllist ] level &level’;

oradebug setmypid

oradebug eventdump &&system_or_session;

 

pause Press return to stop tracing

 

alter &&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/oracle

Connected.

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

 

  COUNT(*)

———-

        14

 

www.htz.pw > @find_sql.sql

Enter value for sql_text: htz

Enter value for sql_id:

 

SQL_ID         CHILD HASH_VALUE  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

—————————————————————————————–

1twzndftjst38      0 3004982376 2937609675          1           .00           .00 SCOTT

select /*+ htz*/count(*) from scott.emp

 

www.htz.pw > @trace_10046_sqlid.sql

Enter value for system_or_session: system

Enter value for sqllist: 1twzndftjst38

Enter value for level: 12

 

System altered.

 

Statement processed.

sql_trace[SQL: 1twzndftjst38 ] level 12

10949 trace name context forever

28401 trace name context forever, level 1

Press 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.emp

END OF STMT

EXEC #182936901240:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim=1409812013408342

WAIT #182936901240: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812013408407

FETCH #182936901240:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim=1409812013408466

STAT #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=1409812013408672

WAIT #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.sql

Enter value for system_or_session: system

Enter value for sqllist: 1twzndftjst38

Enter value for level: 12

 

System altered.

 

Statement processed.

sql_trace[SQL: 1twzndftjst38 ] level 12

10949 trace name context forever

28401 trace name context forever, level 1

Press return to stop tracing

这是之前的显示的内容

按回车,取消trace

 

System altered.

 

10949 trace name context forever

28401 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.emp

END OF STMT

EXEC #182936887640:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim=1409812149842909

WAIT #182936887640: nam=’SQL*Net message to client’ ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812149842978

FETCH #182936887640:c=999,e=81,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim=1409812149843079

STAT #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=1409812149843273

WAIT #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

再次执行tracesql语句

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

 

  COUNT(*)

———-

        14

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

回到session 3窗口中

www.htz.pw > /

 

  COUNT(*)

———-

        14

 

PARSING 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.emp

END OF STMT

EXEC #182936910800:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim=1409812294019895

WAIT #182936910800: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812294019964

FETCH #182936910800:c=0,e=35,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2937609675,tim=1409812294020018

STAT #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=1409812294020203

WAIT #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(*)

———-

       225

 

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

 

  COUNT(*)

———-

        14

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

 

*** 2014-09-04 14:33:02.697

WAIT #182936910800: nam=’SQL*Net message from client’ ela= 88676734 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1409812382696974

CLOSE #182936910800:c=0,e=12,dep=0,type=1,tim=1409812382697082

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

本文固定链接: http://www.htz.pw/2014/09/07/sql_trace%e5%bc%80%e5%90%af%e5%af%b9sql%e8%af%ad%e6%b3%95%e7%9a%84trace.html | 认真就输

该日志由 huangtingzhong 于2014年09月07日发表在 SQL 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: sql_trace开启对sql语法的trace | 认真就输
关键字: , , ,