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:
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.
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
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.
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.
Trace Analyzer is better than TKPROF because It:
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.
Here is a list of links to additional resources: