Oracle Trace Analyzer is TKPROF on Steroids

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.

Let’s learn about Trace Analyzer by answering these questions:

• What is Trace Analyzer?
• Where can I get Trace Analyzer?
• How do I install Trace Analyzer?
• How do I use Trace Analyzer?
• Why is Trace Analyzer better than TKPROF?
• Where can I get more information?

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 - Produ
ction

C:/oraclexe/app/oracle/product/10.2.0/server/trca>
           

And here is the Trace Analyzer HTML report.

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.

Where can I get more information?

Here is a list of links to additional resources:

• The Trace Analyzer by Dave Moore
• Oracle Trace Analyzer on PSOUG
• Oracle Statspack Survival Guide
• Using Application Tracing Tools
• JS Analyzer by Dan Hotka

Related articles:

• How do you use tkprof to solve a performance problem?
• Bye Bye 2006, Welcome 2007
• New Oracle PL/SQL Error Management Framework Released

from:http://awads.net/wp/2006/07/18/oracle-trace-analyzer-is-tkprof-on-steroids/