Abbreviation In Oracle Trace File

References:
http://www.itpub.net/forum.php?mod=viewthread&tid=1137992
1.Activating/deactivating trace http://www.gplivna.eu/papers/otrace.htm
2.Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output, Metalink Note 39817.1
3.Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 Metalink Note 224270.1

This article:
Abbreviation In Oracle Trace File

See Also:
Abbreviation In Oracle Trace File

Here is a sample snippet from a trace file.

PARSING IN CURSOR #5 len=222 dep=1 uid=0 oct=3 lid=0 tim=1230889092568518 hv=3598296092 ad='402c848c' sqlid='1a8n1zgb7m90w'
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,exptime, ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2 from user$ where name=:1
END OF STMT
PARSE #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1457651150,tim=1230889092568518
EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1457651150,tim=1230889092568518
FETCH #5:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1457651150,tim=1230889092568518
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=0 us)'
STAT #5 id=2 cnt=1 pid=1 pos=1 obj=46 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=0 us)'
CLOSE #5:c=0,e=0,dep=1,type=3,tim=1230889092568518

Parsing in cursor:

len =the length of the cursor.
dep =recursive depth of cursor(0-parent,>0-child)
uid =user id
oct =oracle command type no OCI
lid =user id owning the statement e.g. package(libary user id)
tim =the time the parse began
hv =hash valus
ad =sql address of cursor
sqlid=sql id of cursor
END OF STMT=indicate the end of sql STateMenT


c =cpu time
e =elapsed time
p =number of database blocks read (physical)
cr =number of consistent mode blocks read(logical)
cu =number of current mode blocks read(logical)
mis=number of library cache misses
r =number of rows returned
dep=recursive depth of cursor
og =optimizer goal (1=all_rows, 2=first_rows, 3=rule and 4=choose)
plh=plan hash value
tim=


cr =BG(Backgroud) Consistent Read mode
pr =OS Buffer Gets
pw =OS Write Calls

WAIT #5: nam='rdbms ipc reply' ela= 873 from_process=9 timeout=2147483647 p3=0 obj#=64143 tim=1230889212565389
WAIT #5: nam='rdbms ipc reply' ela= 820 from_process=5 timeout=2147483647 p3=0 obj#=64143 tim=1230889212566547
XCTEND rlbk=0, rd_only=1, tim=1230889212602618


nam =event name
ela =elapsed time

next three values after ela are indicate the P1, P2 and P3 variables for each specific event.

XCTEND =Transaction Commit END

rlbk True(1) if and only if the transaction was rolled back.
rd_only True(1) if and only if the transaction changed no data in the database