Oracle Trace Analyzer, TRCANLZR or TRCA, is a SQL trace profiling tool for analyzing your trace files. Here are the major sections of a TRCA report that provide you a lot of useful diagnostic information than tkprof.
· Response Time Summary
· Overall Time and Totals
· Non-Recursive Time and Totals
· Recursive Time and Totals
· Top SQL
· Non-Recursive SQL
· SQL Genealogy
· Individual SQL
· Overall Segment I/O Wait Summary
· Hot I/O Blocks
· Gaps in Trace
· ORA errors in Trace
· Transactions Summary
· Non-default Initialization Params
· Trace Header
· Tool Data Dictionary
· Tool Execution Environment
· Tool Configuration Parameters
You need to download the tool from Oracle Support. Once you’ve downloaded it, the setup is very easy by executing the /trca/install/trcreate.sql.
SETUP:
cd /software/tools/trca/install
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @tacreate.sql
Uninstalling TRCA, please wait
TADOBJ completed.
SQL>
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM If this DROP USER command fails that means a session is connected with this user.
SQL> DROP USER trcanlzr CASCADE;
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.
Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key
Optional Connect Identifier (ie: @PROD): @LABPOC1
Define the TRCANLZR user password (hidden and case sensitive).
Specify TRCANLZR password:
Re-enter password:
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_NAME FREE_SPACE_MB
------------------------------ -------------
TOOLS 55
TOOLS_OLD 98
LAB_I 830
USERS 1130
LAB_D 65632
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]: TOOLS_OLD
DEFAULT_TABLESPACE
------------------------------
TOOLS_OLD
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
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]: TEMP
TEMPORARY_TABLESPACE
------------------------------
TEMP
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.
SQLT$STAGE: write test file tasqdirset.txt
sqlt$_trca$_dir_set.open_write_close: ORA-29283: invalid file
operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file
operation
SQLT$STAGE: created
SQLT$STAGE: READ,WRITE access granted to SQLTXPLAIN
SQLT$STAGE: READ,WRITE access granted to TRCANLZR
SQLT$STAGE: write test file tasqdirset.txt
SQLT$STAGE: read test file tasqdirset.txt
…..
TRCA$INPUT2: read test file tasqdirset.txt
TRCA$INPUT2: get attributes for file tasqdirset.txt
TRCA$INPUT2: /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
Connected.
TAUTLTEST completed.
no rows selected
TACOBJ completed.
tool_owner: "TRCANLZR"
role_name: "PUBLIC"
Creating Package Specs TRCA$G
No errors.
Creating Grants on Packages
Tool Version
----------------
11.4.4.2
Install Date
----------------
20120320
Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID) /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
TRCA$INPUT2(VALID) /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
TRCA$STAGE(VALID) /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
user_dump_dest /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
background_dump_dest /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace
Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.3.1 2011/06/17 csierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.4.2 2012/02/02 csierra $ */
VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.4.1 2012/01/02 csierra $ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.2.7 2011/04/08 csierra $ */
TACPKG completed.
Taking a snapshot of some Data Dictionary objects, please wait...
17:48:23 => refresh_trca$_dict_from_this
17:48:23 -> purge_trca$_dict
17:48:23 dict_state_before_purge
17:48:23 -----------------------
17:48:23 -> print_dict_state
17:48:23 dict_refresh_days :
17:48:23 dict_refresh_date :
17:48:23 dict_database_id :
…..
17:48:45 <- print_dict_state
17:48:45 <= refresh_trca$_dict_from_this
PL/SQL procedure successfully completed.
Snapshot of some Data Dictionary objects completed.
TAUTLTEST completed.
TACREATE completed. Installation completed successfully.
SQL>
TRACING:
alter session set events ‘10046 trace name context forever, level 12’;
alter system set tracefile_identifier=’mytrace’;
<Your SQL statement here…>
alter session set events ‘10046 trace name context off’;
SQL> START trcanlzr.sql /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc
Parameter 1:
Trace Filename or control_file.txt (required)
Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc
Analyzing /apps/oracle/diag/rdbms/labpoc/LABPOC1/trace/LABPOC1_ora_23740_mytrace.trc
To monitor progress, login as TRCANLZR into another session and execute:
SQL> SELECT * FROM trca$_log_v;
... analyzing trace(s) ...
Trace Analyzer completed.
Review first trcanlzr_error.log file for possible fatal errors.
Review next trca_e43354.log for parsing messages and totals.
Copying now generated files into local directory
TKPROF: Release 11.2.0.3.0 - Development on Tue Mar 20 17:59:31 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
adding: trca_e43354.html (deflated 89%)
adding: trca_e43354.log (deflated 84%)
adding: trca_e43354.tkprof (deflated 82%)
adding: trca_e43354.txt (deflated 84%)
adding: trcanlzr_error.log (deflated 81%)
test of trca_e43354.zip OK
deleting: trcanlzr_error.log
Archive: trca_e43354.zip
Length Date Time Name
-------- ---- ---- ----
107234 03-20-12 17:59 trca_e43354.html
15891 03-20-12 17:59 trca_e43354.log
8367 03-20-12 17:59 trca_e43354.tkprof
52391 03-20-12 17:59 trca_e43354.txt
-------- -------
183883 4 files
File trca_e43354.zip has been created
TRCANLZR completed.
No comments:
Post a Comment