TRCA – Trace Analyzer

Trace Analyzer TRCANLZR (TRCA)

1. Overview
2. How to get Trace Analyzer
3. Unzip
4. Install TRCA
5. How to trace session
6. How to find trace file
7. How to Run Trace Analyzer
8. Transfer trca_*zip to local machine
9. Uninstall TRCA


1. Overview

Trace Analyzer (TRCA) is a tool that inputs an EVENT 10046 SQL Trace file, connects to the database, and outputs a comprehensive report for process performance analysis and tuning.

Free download and use

9i, 10g and 11g databases

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.

It is about another, lesser known, utility called Trace Analyzer, that is everything TKPROF is and more. 

Why is Trace Analyzer better than TKPROF?

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 it should be noted that unlike TKPROF, Trace Analyzer requires objects in the database and that means more configuration work on each database that needs the utility installed.

On top of this, TKPROF executes more quickly than Trace Analyzer, due to Trace Analyzer conducting more analysis than TKPROF.


2. How to get Trace Analyzer

TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)


3. Unzip

[oracle@rac2 ~]$ unzip trca.zip
Archive:  trca.zip
   creating: trca/
   creating: trca/run/
  inflating: trca/run/trcanlzr.sql
  inflating: trca/run/trcasplit.sql
   creating: trca/dict/
  inflating: trca/dict/trcadictexp.sql
  inflating: trca/dict/trcadictpurge.sql
  inflating: trca/dict/trcadictimp.sql
  inflating: trca/trca_instructions.html
   creating: trca/doc/
  inflating: trca/doc/trca_changes.html
   creating: trca/install/
  inflating: trca/install/tacpkgg.pkb
  inflating: trca/install/setup.sql
  inflating: trca/install/tasqdirset.sql
  inflating: trca/install/tacpkgx.pkb
  inflating: trca/install/tacpkge.pkb
  inflating: trca/install/install.sql
  inflating: trca/install/tacpkgr.pkb
  inflating: trca/install/tacpkgi.pkb
  inflating: trca/install/tadobj.sql
  inflating: trca/install/tacpkgt.pkb
  inflating: trca/install/tacpkgx.pks
  inflating: trca/install/tacpkgt.pks
  inflating: trca/install/tacreate.sql
  inflating: trca/install/tacpkgp.pkb
  inflating: trca/install/tacpkg.sql
  inflating: trca/install/tacpkge.pks
  inflating: trca/install/tacpkgp.pks
  inflating: trca/install/tacvw.sql
  inflating: trca/install/tacpkgr.pks
  inflating: trca/install/tacommon1.sql
  inflating: trca/install/uninstall.sql
  inflating: trca/install/tadrop.sql
  inflating: trca/install/tadusr.sql
  inflating: trca/install/tacusr.sql
  inflating: trca/install/tacpkgi.pks
  inflating: trca/install/tautltest.sql
  inflating: trca/install/tacpkgg.pks
  inflating: trca/install/tacobj.sql
   creating: trca/utl/
  inflating: trca/utl/tacdirs.sql
  inflating: trca/utl/trcapurge.sql
  inflating: trca/utl/tacdiri1.sql
  inflating: trca/utl/tacdiri2.sql
[oracle@rac2 ~]$

[oracle@rac2 ~]$ ls -ld trca
drwxr-xr-x 7 oracle oinstall 4096 Mar  2  2013 trca
[oracle@rac2 ~]$


4. Install TRCA

[oracle@rac2 ~]$ cd trca/install/
[oracle@rac2 install]$ ls -ltr tacreate.sql
-rw-r--r-- 1 oracle oinstall 3635 Apr  5  2013 tacreate.sql
[oracle@rac2 install]$

Connect as SYS
[oracle@rac2 install]$ sqlplus / as sysdba
SQL> @tacreate.sql

Output

[oracle@rac2 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 2 22:23:01 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @tacreate.sql
        zip warning: name not matched: *_ta*.log

zip error: Nothing to do! (TRCA_installation_logs_archive.zip)
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users TRCANLZR and TRCADMIN. Press RETURN to continue.
------------------- PRESS ENTER HERE ------------------
SQL>
SQL> REM If DROP USER command fails then a session is currently connected.
SQL> PRO

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER TRCANLZR CASCADE
SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER TRCADMIN CASCADE
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE TRCA_USER_ROLE
DROP ROLE TRCA_USER_ROLE
          *
ERROR at line 1:
ORA-01919: role 'TRCA_USER_ROLE' does not exist


SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.
  adding: 161102222307_01_tacreate.log (deflated 90%)


Define the TRCANLZR user password (hidden and case sensitive).

Specify TRCANLZR password:    <------
Re-enter password:

User TRCANLZR created
User TRCANLZR altered
User TRCADMIN created
User TRCADMIN altered

Set up TRCANLZR temporary and default tablespaces

Below are the list of online tablespaces in this database.
Decide which tablespace you wish to create the TRCANLZR tables
and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.

Wait...


TABLESPACE                     FREE_SPACE_MB
------------------------------ -------------
CAT                                       99
USERS                                    301

Above is the list of online tablespaces in this database.
Decide which tablespace you wish to create the TRCANLZR tables
and indexes.  This will also be the TRCANLZR user default tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for tools data is not supported.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS  <-----

Choose the TRCANLZR user temporary tablespace.

Specifying the SYSTEM tablespace will result in the installation
FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Wait...

TABLESPACE_NAME
------------------------------
TEMP
TEST_TEMP

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP  <------

The main application user of TRCA is the schema
owner that generated the SQL Trace to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more TRCA users after this installation
is completed simply grant them the TRCA_USER_ROLE
role.

Main application user of TRCA: SCOTT <------

Type of TRCA repository

Create TRCA repository as Temporary or Permanent objects?
Enter T for Temporary or P for Permanent.
T is recommended and default value. <-------

Type of TRCA repository [T]: <------


TACUSR completed.
No errors.
  adding: 161102222327_02_tacusr.log (deflated 80%)

TAUTLTEST completed.
  adding: 161102222427_09_tautltest.log (deflated 60%)




TACOBJ completed.
  adding: 161102222427_03_tacobj.log (deflated 85%)

SQL> PRO Dropping Libraries for TRCA
Dropping Libraries for TRCA
SQL> SET TERM OFF;
tool_repository_schema: "TRCANLZR"
tool_administer_schema: "TRCADMIN"
role_name: "TRCA_USER_ROLE"
Creating Procedures
Creating Package Specs TRCA$G
No errors.
Creating Package Specs TRCA$P
No errors.
Creating Package Specs TRCA$T
No errors.
Creating Package Specs TRCA$I
No errors.
Creating Package Specs TRCA$E
No errors.
Creating Package Specs TRCA$R
No errors.
Creating Package Specs TRCA$X
No errors.
Creating Views
Creating Package Body TRCA$G
No errors.
Creating Package Body TRCA$P
No errors.
Creating Package Body TRCA$T
No errors.
Creating Package Body TRCA$I
No errors.
Creating Package Body TRCA$E
No errors.
Creating Package Body TRCA$R
No errors.
Creating Package Body TRCA$X
No errors.
Creating Grants on Libraries

Tool Version
----------------
11.4.5.8 <----

Install Date
----------------
20161102

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID)      /u01/app/oracle/diag/rdbms/cat/cat/trace
TRCA$INPUT2(VALID)      /u01/app/oracle/diag/rdbms/cat/cat/trace
TRCA$STAGE(VALID)       /u01/app/oracle/diag/rdbms/cat/cat/trace
user_dump_dest          /u01/app/oracle/diag/rdbms/cat/cat/trace
background_dump_dest    /u01/app/oracle/diag/rdbms/cat/cat/trace

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.5.1 2012/11/27 carlos.sierra $ */
VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.5.8 2013/05/10 carlos.sierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.5.7 2013/04/05 carlos.sierra $ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
TACPKG completed.
  adding: 161102222432_04_tacpkg.log (deflated 80%)

TAUTLTEST completed.
  adding: 161102222443_09_tautltest.log (deflated 59%)


TRCA users must be granted TRCA_USER_ROLE before using this tool.

TACREATE completed. Installation completed successfully.
SQL>


5. How to trace session

For more information on trace. Please click here http://www.br8dba.com/trace/

[oracle@rac2 ~]$ sqlplus scott/tiger;

SQL> set timing on timi on
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_TRACE';
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- Execute the queries or operations to be traced here --
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

-- OR --

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL> set timing on timi on
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_TRACE';
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>12, nm=>' ');
-- Execute the queries or operations to be traced here --
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');

Output

[oracle@rac2 ~]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 2 23:29:48 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set timing on timi on
SQL> ALTER SESSION SET TIMED_STATISTICS = TRUE;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MY_TRACE';

Session altered.

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.00
SQL> select count(*) from BIG_TABLE;

  COUNT(*)
----------
       100

Elapsed: 00:00:00.00
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL>


6. How to find trace file

For more information. Please click here http://www.br8dba.com/trace/#7

SQL> show parameter user_dump_dest

NAME             TYPE        VALUE
---------------- ----------- ------------------------------
user_dump_dest   string      /u01/app/oracle/diag/rdbms/cat
                             /cat/trace
SQL>

[oracle@rac2 ~]$ cd /u01/app/oracle/diag/rdbms/cat/cat/trace
[oracle@rac2 trace]$ ls -ltr *MY_TRACE*
-rw-r----- 1 oracle oinstall   93 Nov  2 23:34 cat_ora_7583_MY_TRACE.trm
-rw-r----- 1 oracle oinstall 3083 Nov  2 23:34 cat_ora_7583_MY_TRACE.trc
[oracle@rac2 trace]$


7. How to Run Trace Analyzer

connect as application user
cd trca/run
sqlplus [apps user]/[apps pwd]
SQL> start trcanlzr.sql [name of your trace file]
SQL> start trcanlzr.sql largesql.trc <== your trace file 
SQL> start trcanlzr.sql trace_file.txt <== your text file

Output

[oracle@rac2 run]$ ls -ltr /home/oracle/trca/run/trcanlzr.sql
-rw-r--r-- 1 oracle oinstall 8587 Nov 23  2012 /home/oracle/trca/run/trcanlzr.sql
[oracle@rac2 run]$

cd /u01/app/oracle/diag/rdbms/cat/cat/trace

[oracle@rac2 trace]$ sqlplus scott/tiger;

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 2 23:46:04 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/home/oracle/trca/run/trcanlzr.sql cat_ora_7583_MY_TRACE.trc

PL/SQL procedure successfully completed.


Parameter 1:
Trace Filename or control_file.txt (required)


Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: cat_ora_7583_MY_TRACE.trc

Analyzing cat_ora_7583_MY_TRACE.trc

DEFINE _SQLPLUS_RELEASE = "1102000300" (CHAR)
***
*** NOTE:
*** If you get one of these errors it means TRCANLZR is not installed:
***   PLS-00201: identifier 'TRCADMIN.TRCA$P' must be declared
***   ORA-00904: "TRCADMIN"."TRCA$G"."VALIDATE_USER": invalid identifier
*** In such case look for errors in NN_*.log files created during install.
***
... please wait ...
To monitor progress, login into another session and execute:
SQL> SELECT * FROM TRCADMIN.trca$_log_v;

... analyzing trace(s) ...



Trace Analyzer completed.
Review first trcanlzr_error.log file for possible fatal errors.
Review next trca_e53968.log for parsing messages and totals.

Copying now generated files into local directory


TKPROF: Release 11.2.0.3.0 - Development on Wed Nov 2 23:46:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.



TKPROF: Release 11.2.0.3.0 - Development on Wed Nov 2 23:46:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


  adding: trca_e53968.html (deflated 87%)
  adding: trca_e53968.log (deflated 85%)
  adding: trca_e53968_nosort.tkprof (deflated 78%)
  adding: trca_e53968_sort.tkprof (deflated 78%)
  adding: trca_e53968.txt (deflated 83%)
  adding: trcanlzr_error.log (deflated 82%)
test of trca_e53968.zip OK
deleting: trcanlzr_error.log
Archive:  trca_e53968.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
    72567  11-02-2016 23:46   trca_e53968.html
    18765  11-02-2016 23:46   trca_e53968.log
     5486  11-02-2016 23:46   trca_e53968_nosort.tkprof
     5518  11-02-2016 23:46   trca_e53968_sort.tkprof
    36026  11-02-2016 23:46   trca_e53968.txt
---------                     -------
   138362                     5 files

File trca_e53968.zip has been created  

TRCANLZR completed.
SQL>


8. Transfer trca_*zip to local machine

Transfer local machine using WINSCP or FTP or Other

unzip trca_e53968.zip

trca_e53968.html
trca_e53968.log
trca_e53968_nosort.tkprof
trca_e53968_sort.tkprofUninstalling TRCA
trca_e53968.txt

For sample zip file. Please click here trca_e53968


9. Uninstall TRCA

Execute tadrop.sql connected as SYS
TRCANLZR schema objects and user are dropped
No parameter values are requested
No spool files are created during uninstall

Connect as SYS
Execute trca/install/tadrop.sql

Example:
cd trca/install
sqlplus /nolog
SQL> connect / as sysdba
SQL> start tadrop.sql

Output

[oracle@rac2 ~]$ cd trca/install
[oracle@rac2 install]$ ls -ltr tadrop.sql
-rw-r--r-- 1 oracle oinstall 1308 Nov 22  2012 tadrop.sql
[oracle@rac2 install]$

[oracle@rac2 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 3 00:01:31 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @tadrop.sql <----
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.
About to DROP users TRCANLZR and TRCADMIN. Press RETURN to continue.

------------------- PRESS ENTER HERE ------------------

SQL>
SQL> REM If DROP USER command fails then a session is currently connected.
SQL> PRO

SQL> DROP USER &&tool_repository_schema. CASCADE;
old   1: DROP USER &&tool_repository_schema. CASCADE
new   1: DROP USER TRCANLZR CASCADE

User dropped.

SQL> DROP USER &&tool_administer_schema. CASCADE;
old   1: DROP USER &&tool_administer_schema. CASCADE
new   1: DROP USER TRCADMIN CASCADE

User dropped.

SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> DROP ROLE &&role_name.;
old   1: DROP ROLE &&role_name.
new   1: DROP ROLE TRCA_USER_ROLE

Role dropped.

SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.
SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

Note 224270.1 Trace Analyzer
Note 215187.1 SQLTXPLAIN
Note 39817.1 Interpreting Raw SQL_Trace

Thank you,
Rajasekhar Amudala