ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle跟踪文件的格式说明

Oracle跟踪文件的格式说明

原创 Linux操作系统 作者:dppass2 时间:2011-07-20 17:06:40 0 删除 编辑
 APPNAME mod='%s' mh=%lu act='%s' ah=%lu

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

  APPNAME mod='%s' mh=%lu act='%s' ah=%lu

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

  APPNAME:Application name setting。在Oracle 7.2和以上版本中出现。这个名称可以由DBMS_APPLICATION_INFO包来设定。

  mod:Module name

  mh:Module hash value

  act:Action

  ah:Action hash value

  比如:APPNAME mod='SQL*Plus' mh= act='' ah=

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

  PARSING IN CURSOR # len=X dep=X uid=X ct=X lid=X tim=X hv=X ad='X'

  statement....

  END OF STMT

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

  CURSOR:Cursor number

  len :Length of SQL statement,SQL语句的长度

  dep :Recursive depth of the cursorwww.zgjbc.com,当前SQL语句的递规深度,假如为0则表示是用户提交的SQL,为1则是由于用户SQL而导致Oracle后台自己执行的SQL,为2则是由1级SQL继续诱发的下一级SQL。

  uid :Schema user id of parsing user

  oct :Oracle command type.

  lid :Privilege user id.

  tim :Timestamp。在Oracle9i之前单位是1/100秒,9i则是1/1,000,000秒。利用这个值可以计算一个SQL执行了到底多长时间。这个值就是当前行被写进trace文件时数据库V$TIMER视图的值。

  hv :Hash id.

  ad :SQLTEXT address,SQLTEXT的地址,跟V$SQLAREA和V$SQLTEXT视图中的ADDRESS字段值相等。

  statement :The actual SQL statement being parsed.

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

  PARSE ERROR #%d:len=%ld dep=%d uid=%ld ct=%d lid=%ld tim=%lu err=%d

  statement....

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

  PARSE ERROR :在Oracle 7.2以上版本中解析的错误会写进trace文件中。

  len :Length of SQL statement.

  dep :Recursive depth of the statement

  uid :User id.

  oct :Oracle command type (if known).

  lid :Privilege user id.

  tim :Timestamp.

  err :Oracle error code (e.g. ORA-XXXXX) reported

  statement :The SQL statement that errored.

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

  PARSE #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0

  EXEC #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0

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

  UNMAP #:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0

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

  PARSE :Parse a statement. 解析一个SQL

  EXEC :Execute a pre-parsed statement. 执行已经解析完毕的SQL

  FETCH :Fetch rows from a cursor. 从游标中得到数据,通常指select返回记录

  UNMAP :假如游标使用了临时表(temporary table), 当游标封闭的时候将会看到UNMAP

  c :CPU time (100th's of a second in Oracle7 ,8 and 9).

  e :Elapsed time (100th's of a second Oracle7, 8. Microseconds in Oracle 9 onwards).

  p :Number of physical reads.

  cr :Number of buffers retrieved for CR reads.

  cu :Number of buffers retrieved in current mode.

  mis :Cursor missed in the cache.

  r :Number of rows processed.

  dep :Recursive call depth (0 = user SQL, >0 = recursive).

  og :Optimizer goal: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose

  tim :Timestamp (large number in 100ths of a second).

  比如:FETCH #2:c=0,e=106,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,ti m=

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

  ERROR #%d:err=%d tim=%lu

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

  执行或者fetch之后出现的SQL Error

  err :Oracle error code (e.g. ORA-XXXXX) at the top of the stack.

  tim :Timestamp.

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

  STAT # id=N cnt=0 [pid=0 pos=0 bj=0 p='SORT AGGREGATE ']

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

  CURSOR的执行计划.

  CURSOR :Cursor which the statistics apply to.

  id :Line of the explain plan which the row count applies to (从1开始).

  cnt :Number of rows for this row source.

  pid :Parent id of this row source.

  pos :Position in explain plan.

  obj :Object id of row source (if this is a base object).

  op : The row source access operation.

  比如:

  STAT #2 id=2 cnt=0 pid=1 pos=1 bj=510 p='TABLE ACCESS BY INDEX ROWID OBJECT_USAGE (cr=2 r=0 w=0 time=83 us)'

  STAT #2 id=3 cnt=1 pid=2 pos=1 bj=511 p='INDEX RANGE SCAN I_STATS_OBJ# (cr=1 r=0 w=0 time=43 us)'

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

  XCTEND rlbk=%d rd_only=%d

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

  XCTEND是事务结束的标志.

  rlbk :1 if a rollback was performed, 0 if no rollback (commit).

  rd_only :1 if transaction was read only, 0 if changes occurred.

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

  BINDS #%d:

  bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oac***=03 oacfl2=0 size=24 ffset=0

  bfp=02fedb44 bln=22 avl=00 ***=05

  value=10

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

  BIND :Variables bound to a cursor.

  bind N :The bind position being bound.

  dty :Data type.

  mxl :Maximum length of the bind variable (private max len in paren).

  mal :Array length.

  scl :Scale.

  pre :Precision.

  oac*** :Special flag indicating bind options

  oac***2 :Continuation of oac***

  size :Amount of memory to be allocated for this chunk

  offset :Offset into this chunk for this bind buffer

  bfp :Bind address.

  bln :Bind buffer length.

  avl :Actual value length (array length too).

  *** :Special flag indicating bind status

  value :The actual value of the bind variable.

  比如:

  BINDS #4:

  bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oac***=08 oacfl2=1 size=24 ffset=0

  bfp=ffffffff7ce64ee0 bln=22 avl=01 ***=05

  value=0

  bind 1: dty=1 mxl=32(11) mal=00 scl=00 pre=00 oac***=18 oacfl2=1 size=32 ffset=0

  bfp=ffffffff7ce6b128 bln=32 avl=11 ***=05

  value="TABCOMPART$"

  bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oac***=08 oacfl2=1 size=24 ffset=0

  bfp=ffffffff7ce6bae8 bln=24 avl=02 ***=05

  value=1

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

  WAIT #: nam="" ela=0 p1=0 p2=0 p3=0

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

  WAIT :An event that we waited for.

  nam :What was being waited for.

  ela :Elapsed time for the operation.

  p1 :P1 for the given wait event.

  p2 :P2 for the given wait event.

  p3 :P3 for the given wait event.

  比如 (Full Table Scan):

  WAIT #1: nam="db file scattered read" ela= 5 p1=4 p2=1435 p3=25

  在游标1上经历了"db file scattered read"等待事件www.hcw163.com,一共等了0.05秒,在读取File 4,从1435 block开始,读了25个block

  比如 (Index Scan):

  WAIT #1: nam="db file sequential read" ela= 4 p1=4 p2=1224 p3=1

  在游标1上经历了"db file sequential read"等待事件,一共等了0.04秒,在读取file 4,block 1224,读取了这一个block

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16572356/viewspace-702672/,如需转载,请注明出处,否则将追究法律责任。

上一篇: sqlplus中行预取
请登录后发表评论 登录
全部评论

注册时间:2008-11-26

  • 博文量
    39
  • 访问量
    74552