Oracle 文件分析工具race怎么用

Oracle 文件分析工具race怎么用，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

介紹一款非常好用的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 文件所需要的對象！

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。