I am writing this blog to  discuss my experience  with Trace Analyzer.  I frequently use this tool due to nature of my job.  Sometimes , trace analyzer  is a silver bullet and sometimes it is your DBA experience or instinct.  This tool is basically an enhanced version of tkprof that not only formats Oracle trace files generated by  setting of  10046 event. It also  provides  diagnostic information like top SQL, Hot IO blocks ,  IO wait summary and  SQL  Genealogy to list  few of them. The output generated is both in text and HTML format.

Installation.

Download  trca.zip from metalink. Refer to  metalink doc ID  [ID 224270.1] for detailed instructions. Once you unzip trca.zip , you will find the following folders along with ” instructions.txt”.

1. dict
2. doc
3. install
4. instructions.txt
5. run
6. utl

All you need to do is “cd install” and execute “tacreate.sql”. The script will create user called “trcanlzr“. You  will be prompted for password, defaulted tablespace and  temporary tablespace.  The tool can be uninstalled  by executing “tadrop.sql” i.e.. user “trcanlzr” is dropped. More detailed instructions can be found in “instructions.txt”

One key advantage of  “trcanlzr” schema is that it stores  diagnostic/detailed information of the trace files in its own schema and therefore you can write your own queries to mine whatever data you want. The trcanlzr schema also captures data dictionary information and stores it within the schema. The downside of storing all that data in the schema is that  running diagnostics on a trace file takes longer as data volume increases. Tell me about tuning the  tool itself.  I have resolved this slowness by backing up existing data , uninstall and then install the tool once again.

How to generate formatted output?

Logon to database as trcanlzr and execute “start trcanlzr tracfile_name” from the run directory of trace analyzer.  The tool will create formatted  text and HTML output in the database udump directory.  A zip file with formated text, HTML, error and tkprof  will also get created in the run folder.

About using the tool itself, I generally start with Top SQL, look at each of the queries, their execution plan , top wait events for the query, bind variables used, any segment contention etc.

.
Sample output

Top SQL

Tagged: 10046, 10046 event, tkprof, Trace Analyzer, Trace Analyzer oracle, trcanlzr