Nigel Noble's Oracle Blog

trace_by_hash_readme

Link to original blog item:

My 10046 SQL Trace File Formatter

Notes:

  • The awk script is run as a shell script, so script needs execute permissions
  • This version runs on 10g and 11g (seperate 9i version with slight changes available)
  • I used this script mostly on 10g and just made a couple of minor tweaks to also support 11g (does not convert cursor to sql_id yet)
  • Cutting out smaller parts of a raw trace file with sqlhash_for_cursor=1 (ie cursor numbers replaced by hash_value) seems to cause tkprof to crash. I am sure it worked in past but may be wrong. Should still be able to profile small parts of trace file if with debug=1 and sqlhash_for_cursor=0)

“configure options” parameters (in the top of the script)

  • debug
    • Set to 0 will switch off the extra PARSING IN CURSOR text on every EXEC
    • Set to 1 will switch on an extra PARSING IN CURSOR section on every EXEC
  • sqlhash_for_cursor
    • Set to 0 switches off replacement of #cursor numbers with sql_hash_value
    • Set to 1 switches on replacement of #cursor numbers with sql_hash_value
  • indent_level
    • Set to 0 will not indent the sql depth (dep=)
    • Set to any other value will indent output by n chars for each depth
  • clock_time_on
    • Set to 0 switches off the approimate clock and delta time
    • Set to 1 switches on the approimate clock and delta time
  • generate_hash_file
    • Set to 0 switches off generation of file containing sql statement dependency
    • Set to 1 switches on generation of file (hash.txt)
    • Note: requires a LOT of messing with the file, never fully implemented

Link to the script:

 trace_by_hash.awk

Advertisements

1 Comment »

  1. […] to “readme” file which also includes link to the script: trace_by_hash_readme I hope people find the script useful for profiling SQL trace files and also a good source for […]

    Pingback by My 10046 SQL Trace Formatter « Nigel Noble's Oracle Blog — 17/06/2010 @ 10:52 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: