TraceMiner – An Oracle Utility to Mine 10046 Trace Files

   Send article as PDF   

Have you ever needed to trawl through an Oracle Trace file to extract the SQL statements executed and found a whole load of bind variables have been used, so you need to find the BINDS section, extract the values, and virtually paste them into the parsed SQL statement?

No? This utility isn’t for you then.

Update to version 0.16 and you too can compile and run this useful utility on Windows. See the Readme for details.

Trace Miner

Note: Updated 2nd December 2016 for version 0.19.

TraceMiner, as it is known, is available for download as source code, from my Git Hub repository. Click on the Download Zip button to get hold of it, then simply unzip it, cd to the created folder, edit the config.h file to suit your system, and then execute make to build the TraceMiner utility.

The README files (either markdown or HTML) have all the details.

So, given a trace file – which must have binds (10046, level 4 or 12 etc), the output will look something like this:

TraceMiner: Version 0.12
Processing: Trace file /full/path/to/nfpdpr_ora_16153.trc

---------------------------------------------------------------------------------------------------------------------------------------
EXEC Line :        Cursor ID : PARSE Line : SQL Text with binds replaced                           
---------------------------------------------------------------------------------------------------------------------------------------
     7555 :                  :            : COMMIT

     7556 :  #140136345356328:       7477 : INSERT INTO "U_NFP"."NFP_LW_MEASURED" ("SPECIES_RUN_ID","LENGTH","WEIGHT","SCALE_PACKET","CHARACTERISTIC_ID","AGE_BAND_ID","PRE_FIRST_SPAWN_ID","POST_FIRST_SPAWN_ID","TOTAL_SEA_AGE_ID","NUMBER_MARKS","NALL_AGE","TRAP_NUMBER","LW_MEASURED_COMMENT") VALUES (664499,186,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
     7581 :                  :            : COMMIT

     7582 :  #140136345356328:       7568 : select 00016EF5.0015.0006 from dual
     7619 :                  :            : COMMIT
...

So far, it has handled all the trace files I’ve thrown at it, but if yours breaks or doesn’t produce the correct results, give me a shout. My email is in the README.

There’s an option to run TraceMiner in --verbose (or just -v) mode. Don’t! You have been warned. However, if you do (and you really shouldn’t!) make sure to redirect stderr to a file which will get very very very big.

2 thoughts on “TraceMiner – An Oracle Utility to Mine 10046 Trace Files”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.