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
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.
TRCANLZR (TRCA): SQL_TRACE/Event 10046 Trace File Analyzer - Tool for Interpreting Raw SQL Traces (Doc ID 224270.1)
[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 ~]$
[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>
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>
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]$
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
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