Shulou(Shulou.com)06/01 Report--

Oracle file analysis tool race how to use, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

This paper introduces a very useful 10046 analysis tool-trca (Trace Analyzer). In the past, tkprof was used to analyze 10046 trace files, but trca is much simpler than tkprof and the analysis results are more comprehensive:

You can download this tool from ML's article: Trace Analyzer TRCANLZR-Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046 [ID 224270.1]!

Trca.zip mainly includes the following files

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 is used to analyze the usage of trace files generated by 10046:

Trcanlzr.sql controlfile | tracking file name

The installation and use of trca are described below.

Enter the / trca/install directory and log in to the database as 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:-enter the password of the user 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-- (must be uppercase, lowercase creation will fail)

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 purge_trca$_dict_gtt

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.