224270.1 Trace Analyzer TRCANLZR (TRCA) 11.4.5.8 May 10, 2013
Analyzing SQL Traces with Binds and/or Waits generated by EVENT 10046

TRCA Overview

Trace Analyzer, also known as TRCANLZR or TRCA, is a tool provided by Oracle Software Support Center of Expertise CoE. TRCA inputs one or several SQL trace(s) generated by Event 10046 and outputs a diagnostics report in two formats (html and text). These reports are commonly used to diagnose processes performing poorly.

TRCA identifies expensive SQL out of the provided trace(s), then it connects to the database and collects their explain plans, Cost-based Optimizer CBO statistics, metadata, configuration parameters, and similar elements that influence the performance of the process being analyzed.

For best output results TRCA must be installed and used in the same system and database where the trace is generated. But this is not a mandatory requirement. Understanding that installing TRCA in a production system is not always possible, TRCA is capable of analyzing a trace using a different system.

To use TRCA, simply install the tool first and proceed then to use it next.

Security Model

During the installation of this TRCA tool, 2 users and one role are created. These users and role have fixed names.

The TRCA repository is owned by user TRCANLZR. User TRCANLZR is granted the following system privileges:

The TRCA set of PL/SQL packages and Views is owned by user TRCADMIN. This TRCADMIN user is locked and identified by a random password. TRCADMIN is granted the following system privileges:

All TRCA users must be granted the TRCA_USER_ROLE before they can use TRCA. This TRCA_USER_ROLE role is not granted any system privileges

Installing TRCA

TRCA installs under its own schemas TRCANLZR and TRCADMIN. It does not install any objects into the application schema(s). You can install this version of TRCA in Oracle databases 9.2, 10.1, 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:

  1. Execute trca/install/tacreate.sql connected as SYS.
    2. # cd trca/install
# sqlplus / as sysdba
SQL> START tacreate.sql

During the installation you will be asked these questions:

  1. TRCANLZR password.

    2. Case sensitive in some systems.

  2. TRCANLZR Default Tablespace.

    3. Select from a list of available permanent tablespaces which one should be used by TRCANLZR for the TRCA repository. It must have more than 50MB of free space.

  3. TRCANLZR Temporary Tablespace.

    4. Select from a list of available temporary tablespaces which one should be used by TRCANLZR for volatile operations and objects.

  4. Optional Application User.

    5. This is the user that will execute TRCA. Normally it is the same user that generated the Trace to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional TRCA users after the tool is installed, by granting them role TRCA_USER_ROLE.

  5. Type of large objects in TRCA repository.

    6. Choose between global temporary or permanent tables for large objects in the TRCA repository. Enter values "T" or "P". Temporary objects is the default and recommended value.

Uninstalling TRCA

Uninstalling TRCA removes the TRCA repository and all TRCANLZR/TRCADMIN schema objects. TRCANLZR and TRCADMIN users also gets dropped. To uninstall TRCA simply execute trca/install/tadrop.sql connected as SYS.

# cd trca/install
# sqlplus / as sysdba
SQL> START tadrop.sql

Using TRCA

Before attempting using TRCA, be sure TRCA has been installed and that your TRCA user has been granted the TRCA_USER_ROLE.

With TRCA you can analyze one or more traces in the same system where they were generated. You can also analyze them on a different system. Several related parallel execution PX traces could be analyzed as one entity, or each trace could be analyzed by itself.

Particular steps to analyze one or several traces, within the same or different system follow.

I. Analyzing one trace on the same system where it was generated.

  1. Navigate to trca/run and connect as the application user that generated the trace to be analyzed.
  2. Execute trca/run/trcanlzr.sql passing the name of the trace to be analyzed (do not include the path). The trace file must exist in the INPUT1 directory. To locate INPUT1: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$INPUT1'; INPUT1 is usually USER_DUMP_DEST.
    3. # cd trca/run
# sqlplus qtune/qtune
SQL> START trcanlzr.sql v1024_ora_32523.trc

II. Analyzing a set of related traces (PX) on the same system where they were generated.

  1. Create a text "control file" with the names of the traces to be analyzed (do not include the path). One trace filename per line within the text file.
  2. Place your control_file.txt into INPUT1 directory. To locate INPUT1: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$INPUT1'; INPUT1 is usually USER_DUMP_DEST.
  3. Navigate to trca/run and connect as the application user that generated the (PX) traces to be analyzed.
  4. Execute trca/run/trcanlzr.sql passing the name of the control file which contains the names of the traces to be analyzed (do not include the path). Actual traces can be located in INPUT1 or INPUT2 directories. The latter is usually BACKGROUND_DUMP_DEST.
    5. # cd trca/run
# sqlplus qtune/qtune
SQL> START trcanlzr.sql control_file.txt

III. Analyzing one trace on a different system (target) to the one where it was generated (source).

  1. On source system connect as SYS and execute trca/dict/trcadictexp.sql This script generates a TRCA_DICT.zip file which contains a small subset of the source data dictionary. There is no need to install TRCA on source.
    2. # sqlplus / as sysdba
SQL> START trcadictexp.sql
  2. Copy into target INPUT1 directory the trace file generated in the source system. To locate INPUT1: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$INPUT1'; INPUT1 is usually USER_DUMP_DEST.
  3. Copy into target STAGE directory the TRCA_DICT.zip file generated in the source system. To locate STAGE: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$STAGE'; STAGE is usually USER_DUMP_DEST.
  4. Unzip TRCA_DICT.zip file within target STAGE directory. There would be then 11 TRCA_*.txt text files inside the target STAGE directory.
    5. # cd [STAGE directory]
# unzip TRCA_DICT.zip
  5. In target, navigate to trca and connect as TRCANLZR.
  6. Execute trca/dict/trcadictimp.sql to import the TRCA repository generated in source. No parameters are passed.
  7. Execute trca/run/trcanlzr.sql passing the name of the trace to be analyzed (do not include the path). TRCA will look for the trace inside the INPUT1 directory.
    8. # cd trca
# sqlplus trcanlzr/trcanlzr
SQL> START dict/trcadictimp.sql
SQL> START run/trcanlzr.sql V1117_ora_29896.trc

IV. Analyzing a set of related traces (PX) on a different system (target) to the one where they were generated (source).

  1. On source system connect as SYS and execute trca/dict/trcadictexp.sql This script generates a TRCA_DICT.zip file which contains a small subset of the source data dictionary. There is no need to install TRCA on source.
    2. # sqlplus / as sysdba
SQL> START trcadictexp.sql
  2. Copy into target INPUT1 directory the trace files generated in the source system. To locate INPUT1: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$INPUT1'; INPUT1 is usually USER_DUMP_DEST.
  3. Copy into target STAGE directory the TRCA_DICT.zip file generated in the source system. To locate STAGE: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$STAGE'; STAGE is usually USER_DUMP_DEST.
  4. Unzip TRCA_DICT.zip file within target STAGE directory. There would be then 11 TRCA_*.txt text files inside the target STAGE directory.
    5. # cd [STAGE directory]
# unzip TRCA_DICT.zip
  5. Create a text "control file" with the names of the traces to be analyzed (do not include the path). One trace filename per line within the text file.
  6. Place your control_file.txt into your target INPUT1 directory.
  7. In target, navigate to trca and connect as TRCANLZR.
  8. Execute trca/dict/trcadictimp.sql to import the TRCA repository generated in source. No parameters are passed.
  9. Execute trca/run/trcanlzr.sql passing the name of the control file which contains the names of the traces to be analyzed (do not include the path).
    10. # cd trca
# sqlplus trcanlzr/trcanlzr
SQL> START dict/trcadictimp.sql
SQL> START run/trcanlzr.sql control_file.txt

Frequently Asked Questions

  1. What is new in this TRCA release?

    2. A summary of recent TRCA changes can be found in file trca/doc/trca_changes.html.

  2. Can I change the TRCA staging directory?

    3. Yes, use trca/utl/tacdirs.sql passing the full directory path. This path cannot contain the "?" or "*" symbols and it is case sensitive in most systems. This directory must exist in the server and ORACLE must have read/write access to it.

  3. Can I change the TRCA input directories?

    4. Yes, but this is not recommended. You can use trca/utl/tacdiri1.sql or trca/utl/tacdiri2.sql passing the full directory path. This path cannot contain the "?" or "*" symbols and it is case sensitive in most systems. This directory must exist in the server and ORACLE must have read access to it.

  4. How can I expedite "Taking a snapshot of some Data Dictionary objects"?

    5. A snapshot of some Data Dictionary objects takes between a couple of minutes and up to several hours, depending on the size of the data dictionary. This is the last step of the installation. If killed, it will be automatically executed when the tool is first used.

    To expedite the snapshot time, and with some loss of functionality, you can disable the snaphot of extents by executing these commands connecting as TRCANLZR:
    SET SERVEROUT ON;
    EXEC trca$g.set_param('capture_extents', 'N');
    EXEC trca$t.refresh_trca$_dict_from_this;

    If still "Taking a snapshot of some Data Dictionary objects" takes longer than several hours, it can be disabled altogether with significant loss of functionality, by executing these commands connecting as TRCANLZR:
    SET SERVEROUT ON;
    EXEC trca$g.set_param('refresh_dict_repository', 'N');
    EXEC trca$t.refresh_trca$_dict_from_this;

  5. How do I provide feedback on this TRCA tool?

    6. If you have an open ticket with Oracle Support where TRCA was requested, use that ticket, else you may want to email its author (Carlos Sierra).