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.
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”.
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.