您当前的位置: 首页 > 数据库教程 > Oracle教程 > OracleSQLTrace几种不同方法示例

OracleSQLTrace几种不同方法示例

作者:不详 来源:网络 发布时间: 2014-08-23 20:45 点击:
示例相关:SQL tname.sql select value from v$diag_info where name = 'Default Trace File'; sinfo.sql select sid,serial# from v$session where sid=sid; spinfo.sql select s.sid,s.serial# from v$process p,v$session s where p.addr=s.paddr and p.spid=pid; s

OracleSQLTrace几种不同方法示例

  示例相关:SQL

  tname.sql

  select value from v$diag_info

  where name = 'Default Trace File';

  sinfo.sql

  select sid,serial# from v$session where sid=&sid;

  spinfo.sql

  select s.sid,s.serial# from v$process p,v$session s

  where p.addr=s.paddr and p.spid=&pid;

  sid.sql

  select sid from v$mystat where rownum<2 ;

  已知session ID 对其进行跟踪

  dbms_monitor

  用法:

  execute dbms_monitor.session_trace_enable(session_id=>&sid, serial_num=>&serial,

  waits=>true,binds=>false);

  关闭:

  execute dbms_monitor.session_trace_disable(session_id=>&sid,serial_num=>&serial);

  DBMS_MONITOR.SESSION_TRACE_ENABLE(

  session_id IN BINARY_INTEGER DEFAULT NULL,

  serial_num IN BINARY_INTEGER DEFAULT NULL,

  waits IN BOOLEAN DEFAULT TRUE,

  binds IN BOOLEAN DEFAULT FALSE,

  plan_stat IN VARCHAR2 DEFAULT NULL);

  示例:

  ?

  

  

  

  

  

  

  

  


  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

  13

  14

  15

  16

  17

  18

  19

  20

  21

  22

  23

  24

  25

  26

  27

  28

  29

  30

  31

  32

  33

  34

  35

  


  dexter@DEX11g> @sid

   

         SID

  ----------

          36

   

  sys@DEX11g> @sinfo

  Enter value for sid: 36

  old  1: select sid,serial#from v$session where sid=&sid

  new  1: select sid,serial#from v$session where sid=36

   

         SID   SERIAL#

  ---------- ----------

          36       415

   

  sys@DEX11g> executedbms_monitor.session_trace_enable(session_id=>&sid,serial_num=>&serial,waits=

  >true,binds=>false);

  Enter value for sid: 36

  Enter value for serial: 415

   

  PL/SQL procedure successfully completed.

   

  dexter@DEX11g> select count(*) from t ;

   

    COUNT(*)

  ----------

       72523

   

  关闭对session的跟踪

   

  sys@DEX11g> executedbms_monitor.session_trace_disable(session_id=>&sid,serial_num=

  >&serial);

  Enter value for sid: 36

  Enter value for serial: 415

   

  PL/SQL procedure successfully completed.

  


  dbms_system

  用法:

  exec dbms_system.set_ev(&sid,&serial,&event,&level,'&name');

  若要关闭,只需要将level设置为0即可

  dbms_syste.set_ev(&sid,&serial,&event,0, '&name') ;

  这个方法比较通用

  PROCEDURE SET_EV

  Argument Name Type In/Out Default?

  ----------------------------------------------------- ------ --------

  session_id BINARY_INTEGER IN

  seriv# BINARY_INTEGER IN

  event BINARY_INTEGER IN

  level BINARY_INTEGER IN

  name VARCHAR2 IN

  示例

  ?

  

  

  

  

  

  

  

  


  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

  13

  14

  15

  16

  17

  18

  19

  20

  21

  22

  23

  


  dexter@DEX11g> @sid

   

         SID

  ----------

          36

   

  sys@DEX11g> @sinfo 36

  Enter value for sid: 36

  old  1: select sid,serial#from v$session where sid=&sid

  new  1: select sid,serial#from v$session where sid=36

   

         SID   SERIAL#

  ---------- ----------

          36       421

   

  sys@DEX11g> execdbms_system.set_ev(&sid,&serial,&event,&level,'&name') ;

  Enter value for sid: 36

  Enter value for serial: 421

  Enter value for event: 10046

  Enter value for level: 12

  Enter value for name:

   

  PL/SQL procedure successfully completed.

  


  找到相应的trace文件。注意

  select value from v$diag_info

  where name = 'Default Trace File';

  得到的trace文件的path只是针对本session的。

  比如这个例子中,使用sys用户对sid为36的session进行跟踪,那么trace文件的位置可以在sid为36的

  session也就是dexter用户执行

  select value from v$diag_info

  where name = 'Default Trace File';

  才可以得到相应的trace文件。

  dexter@DEX11g> select count(*) from t ;

  COUNT(*)

  ----------

  72523

  关闭(设置level=0即可):

  sys@DEX11g> execdbms_system.set_ev(&sid,&serial,&event,&level,'&name') ;

  Enter value for sid: 36

  Enter value for serial: 421

  Enter value for event: 10046

  Enter value for level: 0

  Enter value for name:

  PL/SQL procedure successfully completed.

  已知os pid 对其进行跟踪

  oradebug

  用法

  oradebug event 10046 trace context forever , level 12 ;

  oradebug event 10046 trace context off ;

  示例

  oradebug setospid &pid

  oradebug event 10046 trace context forever , level 12 ;

  oradebug event 10046 trace context off ;

  ?

  

  

  

  

  

  

  

  


  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

  13

  14

  15

  16

  17

  18

  19

  20

  21

  22

  23

  24

  25

  26

  27

  28

  29

  30

  31

  32

  33

  34

  35

  36

  37

  38

  39

  40

  41

  42

  43

  44

  45

  46

  47

  48

  49

  50

  51

  52

  53

  54

  55

  56

  57

  58

  59

  


  [oracle@dex ~]$ ps-aef | grep oracledex

  oracle  5687 5681 0 Apr05 ?       00:00:00 oracledex(DESCRIPTION=(LOCAL=YES)(ADDRESS

  =(PROTOCOL=beq)))

  oracle   6750   1 0 Apr05 ?       00:00:01 oracledex (LOCAL=NO)

  oracle   7785   1 0 Apr05 ?       00:00:00 oracledex (LOCAL=NO)

  oracle  20421 9560 0 09:35 ?       00:00:00 oracledex(DESCRIPTION=(LOCAL=YES)(ADDRESS

  =(PROTOCOL=beq)))

  oracle  20482    1 009:37 ?       00:00:00 oracledex(LOCAL=NO)

  oracle  20518 9234 0 09:40 pts/8   00:00:00 grep oracledex

   

  sys@DEX11g> oradebug setospid 5687

  Oracle pid: 25, Unix process pid: 5687, image: oracle@dex (TNSV1-V3)

  sys@DEX11g> oradebug event 10046 trace name context forever ,level 12 ;

  Statement processed.

   

  scott@DEX11g> select * from tab ;

   

  TNAME                        TABTYPE CLUSTERID

  ------------------------------ ------- ----------

  BONUS                        TABLE

  DEPT                          TABLE

  EMP                          TABLE

  SALGRADE                     TABLE

   

  就会在trace文件中看到相应的trace

  select count(*) from emp

  END OF STMT

  PARSE#47316048750480:c=30996,e=154507,p=3,cr=34,cu=0,mis=1,r=0,dep=0,og=1,plh=2937609675

  ,tim=1365212576667998

  EXEC#47316048750480:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2937609675,tim

  =1365212576668227

  WAIT #47316048750480: nam='SQL*Net message to client' ela= 11 driverid=1650815232 #bytes

  =1 p3=0 obj#=40 tim=1365212576668455

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

  =

  1365212576689528

  STAT #47316048750480 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE(cr=1 pr=0 pw=

  0 time=20996 us)'

  STAT #47316048750480 id=2 cnt=15 pid=1 pos=1 obj=75336 op='INDEXFULL SCAN PK_EMP (cr=1 pr=0 pw=0 time=20960 us cost=1 size=0 card=15)'

  WAIT #47316048750480: nam='SQL*Net message from client' ela= 560driver id=1650815232

  #bytes=1 p3=0 obj#=40 tim=1365212576690779

  FETCH #47316048750480:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2937609675,tim

  =1365212576690879

  WAIT #47316048750480: nam='SQL*Net message to client' ela= 6 driverid=1650815232

  #bytes=1 p3=0 obj#=40 tim=1365212576690934

  WAIT #47316048750480: nam='SQL*Net message from client' ela= 834driver id=16508

  15232 #bytes=1 p3=0 obj#=40 tim=1365212576691804

  PARSE#47316048799960:c=0,e=75,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=

  1365212576691987

  BINDS #47316048799960:

  Bind#0

    oacdty=123 mxl=4000(4000)mxlc=00 mal=00 scl=00 pre=00

    oacflg=00 fl2=1000000 frm=00csi=00 siz=4000 off=0

  toid ptr value=87B716C0 length=16

  AD26DE2F1F4C7C06E0431E4EE50AB7B3

    kxsbbbfp=2b08a050c4d0 bln=4000 avl=00 flg=15

  Bind#1

    oacdty=02 mxl=22(22) mxlc=00mal=00 scl=00 pre=00

    oacflg=01 fl2=1000000 frm=00csi=00 siz=24 off=0

    kxsbbbfp=2b08a054bb00 bln=22 avl=22 flg=05

    value=###

    An invalid number has beenseen.Memory contents are :

  Dump of memory from 0x00002B08A054BB00 to 0x00002B08A054BB16

  2B08A054BB00 000010C1 00000000 00000000 00000000 [................]

  2B08A054BB10 00000000 00000000                   [........]     

  WAIT #47316048799960: nam='SQL*Net message to client' ela= 9 driverid=1650815232 #bytes=1

  p3=0 obj#=40 tim=1365212576693088

  EXEC#47316048799960:c=999,e=1002,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=13652

  12576693170

   

  关闭

  sys@DEX11g> oradebug event 10046 trace name context off ;

  Statement processed.

  


  sql_trace

  用法

  alter system set events ‘sql_trace {process:&pid} level 12’;

  alter system set events ‘sql_trace {process:&pid} off’;

  (关闭的时候有延迟)

  示例

  ?

  

  

  

  

  

  

  

  


  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

  13

  14

  15

  16

  17

  18

  19

  20

  21

  22

  23

  24

  25

  


  [oracle@dex trace]$ ps -aef | grep oracledex

  oracle   6750    1 0 Apr05 ?       00:00:01 oracledex (LOCAL=NO)

  oracle   7785    1 0 Apr05 ?       00:00:00 oracledex (LOCAL=NO)

  oracle  20482    1 0 09:37 ?       00:00:00 oracledex (LOCAL=NO)

  oracle  20730 20727 0 09:53 ?       00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

  oracle  20752 20746 0 09:54 ?       00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

  oracle  20757 9610 0 09:54 pts/3   00:00:00 grep oracledex

  sys@DEX11g> alter system set events 'sql_trace {process:20752} level 12';

  System altered.

  dexter@DEX11g> select * from tab ;

  TNAME                         TABTYPE CLUSTERID

  ------------------------------ ------- ----------

  RUN_STATS                     TABLE

  STATS                         VIEW

  T                             TABLE

  TS                            TABLE

  sys@DEX11g> alter system set events 'sql_trace {process:20752} off';

  System altered.

  


  已知sql_id对其进行跟踪

  sql_trace

  用法

  alter system set events 'sql_Trace[sql:&sql_id] level 12' ;

  alter system set events 'sql_Trace[sql:&sql_id] off' ;

  示例

  ?

  

  

  

  

  

  

  

  


  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

  13

  14

  15

  16

  17

  18

  19

  20

  21

  22

  23

  24

  25

  26

  27

  28

  29

  30

  31

  32

  33

  34

  


  sys@DEX11g> select sql_text , sql_id from v$sql where sql_textlike '%from t ';

   

  SQL_TEXT                                                    SQL_ID

  -------------------------------------------------------------------------

  selectmin(object_id) from t                                gp2gyxwx140jx

  select count(*) from t                                      45vdc2q5hs1f3

  select count(*) from t                                     45vdc2q5hs1f3

  select avg(object_id) from t                                1xbrzw9w1m9rf

  select avg(object_id) from t                                1xbrzw9w1m9rf

  select sum(object_id) from t                                3jpp2an783paa

  select sum(object_id) from t                                3jpp2an783paa

   

  7 rows selected.

   

  sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]level 12' ;

  Enter value for sql_id: gp2gyxwx140jx

  old  1: alter system setevents 'sql_Trace[sql:&sql_id] level 12'

  new  1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] level 12'

   

  System altered.

   

  dexter@DEX11g> select min(object_id) from t ;

   

  MIN(OBJECT_ID)

  --------------

               2

   

   

  sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]off' ;

  Enter value for sql_id: gp2gyxwx140jx

  old  1: alter system setevents 'sql_Trace[sql:&sql_id] off'

  new  1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] off'

   

  System altered.

  


  注:

  因为trace文件是和session关联的,所以不同session执行sql_id为gp2gyxwx140jx的语句的时候,会生成多个trace文件。

  当关闭sql_id的trace的时候,对已经连接的session来说,不受影响(还是会生成trace信息),对于新建立连接的session生效。
分享到:
本文"OracleSQLTrace几种不同方法示例"由远航站长收集整理而来,仅供大家学习与参考使用。更多网站制作教程尽在远航站长站。
顶一下
(0)
0%
踩一下
(0)
0%
[点击 次] [返回上一页] [打印]
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 密码: 验证码:
关于本站 - 联系我们 - 广告合作 - 网站声明 - 友情连接- 网站地图 - 站点地图 - 返回顶部
Copyright © 2007-2013 www.yhzhan.com(远航站长). All Rights Reserved .
远航站长:为中小站长提供最佳的学习与交流平台,提供网页制作与网站编程等各类网站制作教程.
官方QQ:445490277 网站群:26680406 网站备案号:豫ICP备07500620号-4