The Art and Science of Tracing

Arup Nanda
http://arup.blogspot.com
@ArupNanda


What is tracing?

• Debugging information by oracle
• Execution plan tracing
• 10053 - Cost Based Optimizer Trace
• alter session set sql_trace =  TRUE;
• alter session set tracefile_idenitfier = something
• something will be appended to any tracefile
• Analyze tracefile with TKPROF
• tkprof tracefilename output_file
• tkprof tracefile output_file explain=sh/sh 
• generates explain plans for sql 
• NOT execution plan used
• tkprof tracefile output_file sys = YES
• all recurse SQL used in session
• tkprof tracefile output_file insert = file.sql
• output all insert statements
• tkprof tracefile output_file record = file.sql
• output ALL sql statements in session trace
• also various SORT options

Types of Tracing?

• Extended Tracing
• actiivty logging 10046 trace
• alter session set events '10046 trace name context forever, level 8';
• Levels
• 2 = regular trace
• 4 = put in bind vars
• 8 = puts the wait information
• 12 = binds and waits
• 0 = Turns off tracing
• Parse Cursor
• R = response time
• TIM = time from epoch
• FETCH
• R= number of rows
• Analyzing Extended Traces
• Trace Analyzer (FREE from Oracle) 
• installs into database
• HOTSOS Profile (paid)
• TVD$XTAT (FREE)
• http://antognini.ch/downloads/tvdxtat_40beta9.zip)
• 
  

tracing in the remote session

• Options 1
• dbms_system.set_sql_trace_in_session(SID, SERIAL#, sql_trace=>true)
• Options 2
• dbms_system.set_ev()
• Option 3
• dbms_support.start_trace_in_session
• need to install 
• more options
• ORADEBUG
• SQLPLUS -prelim 
• allows you to connect to completely hung session
• Arup Blog has examples
• DBMS_MONITOR.session_trace_enable()

tracing with RAC

• trcsess 
• oracle utility to combine trace files 
• able to specify which services to combine. 
• able to limit tracing information across RAC nodes using: 
• service name
• client ID
• Module
• action

Tools to analyze tracefiles
tracing for future tracing!!!!