【Oracle】一款非常好用的trace文件分析工具之一
摘要： 介绍一款非常好用的10046分析工具--trca(Trace Analyzer)，以前分析10046跟踪文件的时候都是使用tkprof 工具，但是trca 比tkprof要简单的多而且分析的结果更全面： 可以从ML的文章：Trace Analyzer TRCANLZR - Interpreting
介绍一款非常好用的10046分析工具--trca(Trace Analyzer)，以前分析10046跟踪文件的时候都是使用tkprof 工具，但是trca 比tkprof要简单的多而且分析的结果更全面：
可以从ML的文章：Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 [ID 224270.1] 来下载此工具！
trca.zip 主要包括一下文件
install：
TRCACREA.sql - creates all objects needed by Trace Analyzer by calling other scripts below.
TRCADROP.sql - drops the schema objects.
TRCAPKGB.sql - creates the package body.
TRCAPKGS.sql - creates the package header (specification).
TRCAREPO.sql - creates the staging repository.
TRCADIRA.sql - creates the directory object pointing to the place where the trace files exist (only if placing traces on a directory other than user_dump_dest).
TRCAGRNT.sql - grants privileges needed to use Trace Analyzer
TRCAREVK.sql - revokes privileges granted by TRCAGRNT.
TRCAPURG.sql - purges old SQL traces from the repository.
TRCATRNC.sql - truncates the staging repository.
TRCANLZR.sql - main Trace ANalyzer script. that generates the report.
TRCACRSR.sql - generates report for one cursor.
TRCAEXEC.sql - generates report for one cursor execution.
run：
trcanlzr.sql 用来分析10046产生的跟踪文件的 用法：
trcanlzr.sql controlfile |跟踪文件名
下面介绍一下trca的安装和使用
进入/trca/install 目录，以sys 用户登录数据库：
sys@RAC> @tacreate.sql
Uninstalling TRCA, please wait
TADOBJ completed.
sys@RAC>
sys@RAC> WHENEVER SQLERROR EXIT SQL.SQLCODE;
sys@RAC> REM If this DROP USER command fails that means a session is connected with this user.
sys@RAC> DROP USER trcanlzr CASCADE;
sys@RAC> WHENEVER SQLERROR CONTINUE;
sys@RAC>
sys@RAC> 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): @rac
Define the TRCANLZR user password (hidden and case sensitive).
Specify TRCANLZR password: --输入用户TRCANLZR的密码
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...
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]: EXAMPLE --（必须大写，小写会创建失败）
DEFAULT_TABLESPACE
------------------------------
EXAMPLE
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 repoitory as Temporary or Permanent objects?
Enter T for Temporary or P for Permanent.
T is recommended and default value.
Type of TRCA repository [T]: T
TACUSR completed.
No errors.
SQLT$STAGE: created
SQLT$STAGE: READ,WRITE access granted to TRCANLZR
SQLT$STAGE: write test file tasqdirset.txt
SQLT$STAGE: read test file tasqdirset.txt
SQLT$STAGE: get attributes for file tasqdirset.txt
SQLT$STAGE: /opt/rac/oracle/diag/rdbms/rac/rac1/trace
TRCA$STAGE: created
TRCA$STAGE: READ,WRITE access granted to TRCANLZR
TRCA$STAGE: write test file tasqdirset.txt
TRCA$STAGE: read test file tasqdirset.txt
TRCA$STAGE: get attributes for file tasqdirset.txt
TRCA$STAGE: /opt/rac/oracle/diag/rdbms/rac/rac1/trace
SQLT$UDUMP: created
SQLT$UDUMP: READ access granted to TRCANLZR
SQLT$UDUMP: read test file tasqdirset.txt
SQLT$UDUMP: get attributes for file tasqdirset.txt
SQLT$UDUMP: /opt/rac/oracle/diag/rdbms/rac/rac1/trace
SQLT$BDUMP: created
SQLT$BDUMP: READ access granted to TRCANLZR
SQLT$BDUMP: read test file tasqdirset.txt
SQLT$BDUMP: get attributes for file tasqdirset.txt
SQLT$BDUMP: /opt/rac/oracle/diag/rdbms/rac/rac1/trace
TRCA$INPUT1: created
TRCA$INPUT1: READ access granted to TRCANLZR
TRCA$INPUT1: read test file tasqdirset.txt
TRCA$INPUT1: get attributes for file tasqdirset.txt
TRCA$INPUT1: /opt/rac/oracle/diag/rdbms/rac/rac1/trace
TRCA$INPUT2: created
TRCA$INPUT2: READ access granted to TRCANLZR
TRCA$INPUT2: read test file tasqdirset.txt
TRCA$INPUT2: get attributes for file tasqdirset.txt
TRCA$INPUT2: /opt/rac/oracle/diag/rdbms/rac/rac1/trace
Connected.
TAUTLTEST completed.
no rows selected
TACOBJ completed.
tool_owner: "TRCANLZR"
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 Packages
Tool Version
----------------
11.4.3.1
Install Date
----------------
20111008
Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID) /opt/rac/oracle/diag/rdbms/rac/rac1/trace
TRCA$INPUT2(VALID) /opt/rac/oracle/diag/rdbms/rac/rac1/trace
TRCA$STAGE(VALID) /opt/rac/oracle/diag/rdbms/rac/rac1/trace
user_dump_dest /opt/rac/oracle/diag/rdbms/rac/rac1/trace
background_dump_dest /opt/rac/oracle/diag/rdbms/rac/rac1/trace
Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.2.7 2011/04/08 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.1.4 2010/07/12 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.2.7 2011/04/08 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.2.7 2011/04/08 csierra $ */
VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.3.1 2011/06/17 csierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.2.7 2011/04/08 csierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.3.1 2011/06/17 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...
19:56:30 => refresh_trca$_dict_from_this
19:56:30 -> purge_trca$_dict
19:56:30 dict_state_before_purge
19:56:30 -----------------------
19:56:30 -> print_dict_state
19:56:30 dict_refresh_days :
19:56:30 dict_refresh_date :
19:56:30 dict_database_id :
19:56:30 dict_database_name:
19:56:30 dict_instance_id :
19:56:30 dict_instance_name:
19:56:30 dict_host_name :
19:56:30 dict_platform. :
19:56:30 dict_rdbms_version:
19:56:30 dict_db_files :
19:56:30 <- print_dict_state
19:56:30 -> purge_trca$_dict_gtt
19:56:30 <- purge_trca$_dict_gtt
19:56:30 dict_state_after_purge
19:56:30 ----------------------
19:56:30 -> print_dict_state
19:56:30 dict_refresh_days :
19:56:30 dict_refresh_date :
19:56:30 dict_database_id :
19:56:30 dict_database_name:
19:56:30 dict_instance_id :
19:56:30 dict_instance_name:
19:56:30 dict_host_name :
19:56:30 dict_platform. :
19:56:30 dict_rdbms_version:
19:56:30 dict_db_files :
19:56:30 <- print_dict_state
19:56:30 <- purge_trca$_dict
19:56:30 -> trca$_file$
19:56:30 <- trca$_file$ (6 rows)
19:56:30 using serial execution
19:56:30 -> trca$_segments
19:56:31 <- trca$_segments (6040 rows)
19:56:31 -> trca$_extents_dm
19:56:31 <- trca$_extents_dm (0 rows)
19:56:31 -> trca$_extents_lm
19:56:46 <- trca$_extents_lm (10462 rows)
19:56:46 -> trca$_users
19:56:46 <- trca$_users (22 rows)
19:56:46 -> trca$_extents
19:56:46 <- trca$_extents (10374 rows)
19:56:46 -> purge_trca$_dict_gtt
19:56:46 <- purge_trca$_dict_gtt
19:56:46 -> trca$_tables$
19:56:47 <- trca$_tables$ (2857 rows)
19:56:47 -> trca$_indexes$
19:56:48 <- trca$_indexes$ (4855 rows)
19:56:48 -> trca$_ind_columns$
19:56:49 <- trca$_ind_columns$ (7669 rows)
19:56:49 -> trca$_tab_cols$
19:56:51 <- trca$_tab_cols$ (6864 rows)
19:56:51 -> trca$_objects$
19:56:51 <- trca$_objects$ (7690 rows)
19:56:51 -> trca$_parameter2$
19:56:51 <- trca$_parameter2$ (28 rows)
19:56:51 dict_state_after_refresh
19:56:51 ------------------------
19:56:51 -> print_dict_state
19:56:51 dict_refresh_days : 1
19:56:51 dict_refresh_date : 20111008
19:56:51 dict_database_id : 2350763456
19:56:51 dict_database_name: RAC
19:56:51 dict_instance_id : 1
19:56:51 dict_instance_name: rac1
19:56:51 dict_host_name : rac1
19:56:51 dict_platform. : Linux
19:56:51 dict_rdbms_version: 11.2.0.1.0
19:56:51 dict_db_files : 200
19:56:51 <- print_dict_state
19:56:51 <= refresh_trca$_dict_from_this
PL/SQL procedure successfully completed.
Snapshot of some Data Dictionary objects completed.
TAUTLTEST completed.
TACREATE completed. Installation completed successfully.
创建了trcanlzr用户和分析trace 文件所需要的对象！