介紹一款非常好用的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

19:56:30 -> purge_trca$_dict_gtt

19:56:30

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 :