Custom Search . . .

Tuesday, October 9, 2007

Trace Analyzer

When you generate a raw Oracle SQL Trace file you are faced with the arduous task of analyzing this trace file. Of course, TKPROF has always been there to help you with this analysis. This post is not about TKPROF, it is about another, lesser known, utility called Trace Analyzer, that is everything TKPROF is and more.

What is Trace Analyzer?

Trace Analyzer is a utility that reads a raw SQL Trace generated by standard SQL Trace or by EVENT 10046 (Level 4, 8 or 12), and generates a comprehensive HTML report with performance related details. The HTML report contains information about the Oracle instance (i.e. Statspack type of information) as well as all the details found on TKPROF including a very nice explain plan. The HTML report page is hyper-linked so you can easily jump to different sections of the report.

Where can I get Trace Analyzer?

Trace Analyzer is available as a MetaLink download, note # 224270.1. After you download the zip file, you need to perform a few installation steps before you can use this utility.

How do I install Trace Analyzer?

Let’s go through the installation steps on Oracle XE (Windows XP with Oracle home = C:\oraclexe\app\oracle\product\10.2.0\server):

Unzip the content of the zip file into in your Oracle home folder. Your Oracle home should now contain a folder called “trca”.

In your DOS command window, go to the trca folder in your Oracle home directory

C:\>cd oraclexe\app\oracle\product\10.2.0\server\trca

Run SQL*Plus and connect as SYS

C:\oraclexe\app\oracle\product\10.2.0\server\trca>sqlplus sys as sysdba
SQL*Plus: Release - Production on Sun Jul 16 14:22:42 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Express Edition Release - Production

Run the Trace Analyzer installation script

SQL> @tacreate.sql
... Creating TRCA$ UDUMP Directory
... Creating TRCANLZR user ...
Choose the TRCANLZR user's password.
Not specifying a password will result in the installation FAILING
Specify TRCANLZR password

Enter a password for the new user TRCANLZR

Enter value for trcanlzr_password: tracemeifucan
... Getting Host String ...
Specify Host String (TNS Alias) including @ symbol (if not applicable just hit e
nter) Just hit enter here
Specify Host String including @ prefix (i.e.: @CERBDEV)
Enter value for host_string:
Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the Trace Analyzer tables
and indexes.  This will also be the TRCANLZR user's default tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.
------------------------------ ---------
SYSAUX                         PERMANENT
TEMP                           TEMPORARY
UNDO                           UNDO
USERS                          PERMANENT
Specify TRCANLZR user's default tablespace

You have to enter the default tablespace here, otherwise the installation will fail. You may wish to create a new tablespace before installation to assign the TRCANLZR objects to it.

Enter value for default_tablespace: USERS
Using USERS for the default tablespace
Choose the TRCANLZR user's temporary tablespace.
Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.
Specify TRCANLZR user's temporary tablespace.

You have to enter the temporary tablespace here, otherwise the installation will fail.

Enter value for temporary_tablespace: TEMP
Using TEMP for the temporary tablespace
TACPKG complete. Please check tacpkg.lis for any errors.

That’s it. Trace Analyzer is intalled. You can now execute Trace Analyzer from any schema.

How do I use Trace Analyzer?

Use from SQL*Plus connected as the user that originated the SQL Trace:

SQL> connect hr
Enter password:

To provide maximum trace data, start a Level 12 trace:


Let Oracle collect trace data during the session

SQL> select count(*) from employees;

Take note of the trace file that was generated in the UDUMP directory on the server. Now, execute the trcanlzr.sql script passing the trace file name as the parameter. This generates the Trace Analyzer HTML report into your local client directory using SPOOL

SQL> @trcanlzr.sql xe_ora_832.trc
...generating report
Trace Analyzer Report "trcanlzr_832_1.html" has been created in C:\ORACLEXE\APP\
...copying report "trcanlzr_832_1.html" has been copied into local directory
...exiting now (hit enter key)
Disconnected from Oracle Database 10g Express Edition Release - Production
connect sys/ora@dbname as sysdba
exec trac$i.trace_analyzer(‘urtrace_ora_sid.trc’)

Why is Trace Analyzer better than TKPROF?

Trace Analyzer is better than TKPROF because It:

  • Provides the actual values of the bind variables in SQL.
  • Provides the hottest blocks, optimizer statistics for indexes and tables and other information not available through TKPROF.
  • Separates user recursive and internal recursive calls, unlike TKPROF.
  • Provides more detailed wait event information.
  • Generates an HTML report that is more readable and extensive than the text output generated by TKPROF.

However, unlike TKPROF, Trace Analyzer requires objects in the database and that means more configuration work on each database that needs the utility installed. Moreover, TKPROF executes more quickly than Trace Analyzer, but that’s because Trace Analyzer does more analysis than TKPROF.


Mohammed Taj said...

That is great post babu,
good work!!!

today i know one more new thing about Oracle Database.

Thank you ...

MOhammed Taj

Anonymous said...

Good day !.
You re, I guess , probably curious to know how one can make real money .
There is no initial capital needed You may start to get income with as small sum of money as 20-100 dollars.

AimTrust is what you thought of all the time
AimTrust represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

Its head office is in Panama with affiliates around the world.
Do you want to become a happy investor?
That`s your chance That`s what you really need!

I`m happy and lucky, I started to get real money with the help of this company,
and I invite you to do the same. If it gets down to choose a proper companion who uses your savings in a right way - that`s the AimTrust!.
I make 2G daily, and what I started with was a funny sum of 500 bucks!
It`s easy to get involved , just click this link
and go! Let`s take this option together to become rich