Oracle Corner

Raw trace files give you few additional information over the tkprof-ed file. The rest is the same. The most interesting information you can get from the raw trace file is how much waiting was for each object.

Oracle raw trace file general statistics

• PARSE , EXEC , FETCH
  
• mis>0 for the library cach misses
• e= for the seconds (divide by million for >=9i)
  
• c= for the CPU consumption
• p= for the physical reads
• cr= for the consistent requests
  
• cu= for the current requests
• r= for the number of rows
• og= for th eoptimizer goal (4 is choose, 3 is rule, 2 is first_rows, 1 is all_rows)
  
• WAIT
  
• ela= for the E-06 seconds
• p1= , p2= and p3= , with the same meaning as in the wait views

Waits per object
If you see lots of I/O waits in the raw trace file, tkprof will not divide waits per object. Tkprof gives you waits per SQl statement, but if your SQL statement is complex, you might need to know how much waits was for each of the objects.
To find that, you need to process the raw trace file.

I usually do it like this (not the super efficient way, but fast enough):

• Find your query and cut out the waiting part into the new file, for example
• more justwaits.trc
  
• WAIT #45: nam='db file sequential read' ela= 10987 p1=48 p2=12266 p3=1
• WAIT #45: nam='db file sequential read' ela= 5605 p1=48 p2=55970 p3=1
• WAIT #45: nam='db file sequential read' ela= 17531 p1=51 p2=30972 p3=1
• ... and so on
• Process the justwaits.trc any way you want to get just 2 columns: p1 and p2.
• For example:
• 48 12266
• 48 55970
• 51 30972
• 51 98012
• 51 98179
• 30 60962
• 30 60963
• 51 98184
• 51 98182
• ...
  
• Well, now we have the File ID and Block ID for our objects
• Write a SQL script, for example called object_wait.sql :
• set feedback off
• set head off
• set verify off
• set linesi 58
• select segment_name||'.'||segment_type from dba_extents
• where file_id = &1 and &2 between block_id and block_id + blocks - 1;
• exit
  
• Run a shell command:
• while read p1 p2; do sqlplus -s '/ as sysdba' @object_wait $p1 $p2 >>a.a ; done "<"justwaits.trc (remove the ", I had to put them there because of the HTML tags)
• cat a.a | sort | uniq -c
• And you have the result, for example:
  
• 20 EMS.CNS_OBL_UNQ
• 2 EMS.I_OBJECTS_NAME
• 5 EMS.I_OBJECTS_PK
• 3 EMS.OBJECTS