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 10.2.0.1.0 - 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 10.2.0.1.0 - 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

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.

TABLESPACE_NAME                CONTENTS

------------------------------ ---------

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:

Connected.

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

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Let Oracle collect trace data during the session

SQL> select count(*) from employees;

  COUNT(*)

----------

       107

SQL>  ALTER SESSION SET sql_trace = FALSE;

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\

ORACLE\ADMIN\XE\UDUMP

...copying report

...report "trcanlzr_832_1.html" has been copied into local directory

...exiting now (hit enter key)

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Or

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.