Chinaunix首页 | 论坛 | 博客
  • 博客访问: 6815060
  • 博文数量: 1662
  • 博客积分: 16831
  • 博客等级: 上将
  • 技术积分: 12554
  • 用 户 组: 普通用户
  • 注册时间: 2011-02-25 07:23
个人简介

柔中带刚,刚中带柔,淫荡中富含柔和,刚猛中荡漾风骚,无坚不摧,无孔不入!

文章分类

全部博文(1662)

文章存档

2021年(8)

2020年(24)

2019年(4)

2018年(19)

2017年(66)

2016年(60)

2015年(49)

2014年(201)

2013年(221)

2012年(638)

2011年(372)

分类: Oracle

2012-02-16 09:41:22

Oracle TRCA 工具 说明
分类: Oracle Performance 392人阅读 评论(1) 举报

 

一.TRCA 说明

分析10046的trace 文件,之前介绍过一个工具,tkprof,但是tkprof的功能有限。 相比之下,TRCA工具则能提供更加详细的报告。

 

关于tkprof 工具说明,参考之前的Blog:

使用Tkprof 分析ORACLE 跟踪文件

http://blog.csdn.net/tianlesoftware/article/details/5632003

 

Oracle SQLTrace 和 10046事件

http://blog.csdn.net/tianlesoftware/article/details/5857023

 

Trace Analyzer,also known as TRCANLZR or , is a toolprovided by Oracle Server Technologies Center of Expertise - ST CoE. TRCAinputs one or several SQL trace(s) generated by Event 10046 and outputs adiagnostics report in two formats (html and text). These reports are commonly usedto diagnose processes performing poorly.

       --Trace Analyzer,也称TRCANLZR 或者 TRCA,其由STCoE 提供。 TRCA 输入一个或者多个10046 的trace 文件,然后生成诊断报告,报告有2种格式:html和text。                                                                                              

 

TRCA identifie***pensive SQL out of the provided trace(s), then it connects to the databaseand collects their explain plans, Cost-based Optimizer CBO statistics,metadata, configuration parameters, and similar elements that influence theperformance of the process being analyzed.

--TRCA 可以通过trace 分析消耗资源多的SQL时,会连接数据库,收集其执行计划,CBO 的统计信息,metadata,配置参数。 同样的道理,影响性能的进程也会被分析。

 

For best outputresults TRCA must be installed and used in the same system and database wherethe trace is generated. Understanding that installing TRCA in a production systemis not always possible, TRCA is capable of analyzing a trace using a differentsystem. Refer to the included instructions for details.

--为了达到最好的分析结果,TRCA 必须安装在产生trace 文件相同的系统和数据库上,不过一般生产环境不允许安装TRCA。 TRCA 可以分析跨系统分析trace 文件。

 

TRCA 工具可以从MOS上下载:

Trace Analyzer TRCANLZR - Interpreting RawSQL Traces with Binds and/or Waits generated by EVENT 10046 [ID 224270.1]

 

也可以从我的CSDN 下载:

 

 

二.使用示例 2.1 安装TRCA

TRCA installsunder its own schema TRCANLZR. It does not install any objects into theapplication schema(s). You can install this version of TRCA in Oracle databases9.2, 10.1, 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

--TRCA 会安装到自己的用户:TRCANLZR,不会安装对象到其他用户。

 

安装方法:

--先解压缩trca,然后使用sys用户运行tacreate.sql

 

C:\Users\Administrator.DavidDai>cdE:\Software\OracleSoftware\Tools\trca\install

C:\Users\Administrator.DavidDai>E:

E:\Software\OracleSoftware\Tools\trca\install>sqlplus/ as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 9 16:13:20 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand 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 failsthat 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 perOracle Net) Include "@" symbol, ie. @PROD,If not applicable, enter nothingand hit the "Enter" key

 

Optional Connect Identifier (ie: @PROD):

 

--提示二:指定TRCANLZR用户的密码(隐藏,大小写敏感)

Define the TRCANLZR user password (hiddenand case sensitive).

Specify TRCANLZR password:

Re-enter password:

 

 

Set up TRCANLZR temporary and defaulttablespaces

 

Below are the list of online tablespaces inthis database.

Decide which tablespace you wish to createthe TRCANLZR tables

and indexes.  This will also be the TRCANLZR user defaulttablespace.

 

Specifying the SYSTEM tablespace willresult in the installation

FAILING, as using SYSTEM for tools data isnot supported.

 

Wait...

 

 

Above is the list of online tablespaces inthis database.

Decide which tablespace you wish to createthe TRCANLZR tables

and indexes.  This will also be the TRCANLZR user defaulttablespace.

 

Specifying the SYSTEM tablespace willresult in the installation

FAILING, as using SYSTEM for tools data isnot supported.

 

Tablespace name is case sensitive.

 

--提示三:指定TRCANLZR用户默认表空间

Default tablespace [USERS]:

 

DEFAULT_TABLESPACE

------------------------------

USERS

 

Choose the TRCANLZR user temporarytablespace.

 

Specifying the SYSTEM tablespace willresult in the installation

FAILING, as using SYSTEM for the temporarytablespace is not recommended.

 

Wait...

 

TABLESPACE_NAME

------------------------------

TEMP

 

Tablespace name is case sensitive.

 

--提示四:指定TRCANLZR用户默认临时表空间

Temporary tablespace [TEMP]:

 

TEMPORARY_TABLESPACE

------------------------------

TEMP

 

Type of TRCA repository

 

Create TRCA repository as Temporary orPermanent objects?

Enter T for Temporary or P for Permanent.

T is recommended and default value.

 

--提示五:选择保存类型:T,临时,P 永久

Type of TRCA repository [T]:

 

 

TACUSR completed.

No errors.

SQLT$STAGE: created

SQLT$STAGE: READ,WRITE access granted toTRCANLZR

SQLT$STAGE: write test file tasqdirset.txt

SQLT$STAGE: read test file tasqdirset.txt

SQLT$STAGE: get attributes for filetasqdirset.txt

SQLT$STAGE:d:\app\administrator\diag\rdbms\dave\dave\trace

TRCA$STAGE: created

TRCA$STAGE: READ,WRITE access granted toTRCANLZR

TRCA$STAGE: write test file tasqdirset.txt

TRCA$STAGE: read test file tasqdirset.txt

TRCA$STAGE: get attributes for filetasqdirset.txt

TRCA$STAGE:d:\app\administrator\diag\rdbms\dave\dave\trace

SQLT$UDUMP: created

SQLT$UDUMP: READ access granted to TRCANLZR

SQLT$UDUMP: read test file tasqdirset.txt

SQLT$UDUMP: get attributes for filetasqdirset.txt

SQLT$UDUMP:d:\app\administrator\diag\rdbms\dave\dave\trace

SQLT$BDUMP: created

SQLT$BDUMP: READ access granted to TRCANLZR

SQLT$BDUMP: read test file tasqdirset.txt

SQLT$BDUMP: get attributes for filetasqdirset.txt

SQLT$BDUMP:d:\app\administrator\diag\rdbms\dave\dave\trace

TRCA$INPUT1: created

TRCA$INPUT1: READ access granted toTRCANLZR

TRCA$INPUT1: read test file tasqdirset.txt

TRCA$INPUT1: get attributes for filetasqdirset.txt

TRCA$INPUT1:d:\app\administrator\diag\rdbms\dave\dave\trace

TRCA$INPUT2: created

TRCA$INPUT2: READ access granted toTRCANLZR

TRCA$INPUT2: read test file tasqdirset.txt

TRCA$INPUT2: get attributes for filetasqdirset.txt

TRCA$INPUT2:d:\app\administrator\diag\rdbms\dave\dave\trace

Connected.

 

TAUTLTEST completed.

 

no rows selected

 

TACOBJ completed.

tool_owner: "TRCANLZR"

role_name: "PUBLIC"

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.4.2

 

Install Date

----------------

20120209

 

Directories

------------------------------------------------------------------------------------------------

TRCA$INPUT1(VALID)     d:\app\administrator\diag\rdbms\dave\dave\trace

TRCA$INPUT2(VALID)     d:\app\administrator\diag\rdbms\dave\dave\trace

TRCA$STAGE(VALID)      d:\app\administrator\diag\rdbms\dave\dave\trace

user_dump_dest          d:\app\administrator\diag\rdbms\dave\dave\trace

background_dump_dest   d:\app\administrator\diag\rdbms\dave\dave\trace

 

Libraries

------------------------------------------------------------------------------------------------

VALID PACKAGE TRCA$I /* $Header: 224270.1tacpkgi.pks 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE TRCA$E /* $Header: 224270.1tacpkge.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE TRCA$G /* $Header: 224270.1tacpkgg.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE TRCA$P /* $Header: 224270.1tacpkgp.pks 11.4.4.1 2012/01/02 csierra $ */

VALID PACKAGE TRCA$R /* $Header: 224270.1tacpkgr.pks 11.4.2.7 2011/04/08 csierra $ */

VALID PACKAGE TRCA$T /* $Header: 224270.1tacpkgt.pks 11.4.3.1 2011/06/17 csierra $ */

VALID PACKAGE TRCA$X /* $Header: 224270.1tacpkgx.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 Dictionaryobjects, please wait...

 

16:17:25 => refresh_trca$_dict_from_this

16:17:25 -> purge_trca$_dict

16:17:25 dict_state_before_purge

16:17:25 -----------------------

16:17:25 -> print_dict_state

16:17:25 dict_refresh_days :

16:17:25 dict_refresh_date :

16:17:25 dict_database_id  :

16:17:25 dict_database_name:

16:17:25 dict_instance_id  :

16:17:25 dict_instance_name:

16:17:25 dict_host_name    :

16:17:25 dict_platform     :

16:17:25 dict_rdbms_version:

16:17:25 dict_db_files     :

16:17:25 <- print_dict_state

16:17:25 -> purge_trca$_dict_gtt

16:17:25 <- purge_trca$_dict_gtt

16:17:25 dict_state_after_purge

16:17:25 ----------------------

16:17:25 -> print_dict_state

16:17:25 dict_refresh_days :

16:17:25 dict_refresh_date :

16:17:25 dict_database_id  :

16:17:25 dict_database_name:

16:17:25 dict_instance_id  :

16:17:25 dict_instance_name:

16:17:25 dict_host_name    :

16:17:25 dict_platform     :

16:17:25 dict_rdbms_version:

16:17:25 dict_db_files     :

16:17:25 <- print_dict_state

16:17:25 <- purge_trca$_dict

16:17:25 -> trca$_file$

16:17:25 <- trca$_file$ (5 rows)

16:17:25 using serial execution

16:17:25 -> trca$_segments

16:17:25 <- trca$_segments (5391 rows)

16:17:25 -> trca$_extents_dm

16:17:25 <- trca$_extents_dm (0 rows)

16:17:25 -> trca$_extents_lm

16:17:33 <- trca$_extents_lm (8310 rows)

16:17:33 -> trca$_users

16:17:34 <- trca$_users (16 rows)

16:17:34 -> trca$_extents

16:17:35 <- trca$_extents (8105 rows)

16:17:35 -> purge_trca$_dict_gtt

16:17:35 <- purge_trca$_dict_gtt

16:17:35 -> trca$_tables$

16:17:36 <- trca$_tables$ (2806 rows)

16:17:36 -> trca$_indexes$

16:17:37 <- trca$_indexes$ (4726 rows)

16:17:37 -> trca$_ind_columns$

16:17:39 <- trca$_ind_columns$ (7534rows)

16:17:39 -> trca$_tab_cols$

16:17:43 <- trca$_tab_cols$ (6746 rows)

16:17:43 -> trca$_objects$

16:17:44 <- trca$_objects$ (6944 rows)

16:17:44 -> trca$_parameter2$

16:17:44 <- trca$_parameter2$ (17 rows)

16:17:44 dict_state_after_refresh

16:17:44 ------------------------

16:17:44 -> print_dict_state

16:17:44 dict_refresh_days : 1

16:17:44 dict_refresh_date : 20120209

16:17:44 dict_database_id  : 837618292

16:17:44 dict_database_name: DAVE

16:17:44 dict_instance_id  : 1

16:17:44 dict_instance_name: dave

16:17:44 dict_host_name    : DAVIDDAI

16:17:44 dict_platform     : 64-bit Windows

16:17:44 dict_rdbms_version: 11.2.0.1.0

16:17:44 dict_db_files     : 200

16:17:44 <- print_dict_state

16:17:44 <= refresh_trca$_dict_from_this

 

PL/SQL procedure successfully completed.

 

Snapshot of some Data Dictionary objectscompleted.

 

TAUTLTEST completed.

 

TACREATE completed. Installation completedsuccessfully.

SQL>

 

2.2 卸载TRCA

UninstallingTRCA removes the TRCA repository and all TRCANLZR schema objects.The TRCANLZR user also gets dropped. To uninstall TRCA simplyexecute trca/install/ connected as SYS.

--卸载时会移除TRCA 的数据和TRCANLZR用户的所有对象,最后也会drop 这个用户,卸载用sys执行脚本:tadrop.sql

 

# cd trca/install

# sqlplus / as sysdba

SQL> START tadrop.sql

 

 

2.3 示例

 

2.3.1 分析同系统的一个trace文件

 

--生成10046trace

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL> select * from all_users;

SQL> oradebug event 10046 trace name context off;

Statement processed.

SQL> oradebug tracefile_name

d:\app\administrator\diag\rdbms\dave\dave\trace\dave_ora_4588.trc

SQL>

 

--进入trca/run目录

E:\Software\OracleSoftware\Tools\trca\install>cd../run

E:\Software\OracleSoftware\Tools\trca\run>

 

--使用生成trace的用户连接sqlplus,

E:\Software\OracleSoftware\Tools\trca\run>sqlplus/ as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 9 16:43:18 2012

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing option

SQL>

--这里直接用sys用户

 

注意:

       这里强调是生成trace 的用户,因为TRCA在分析性能查的SQL时会连接到数据库去收集相关的信息,所以这里必须是生成trace 的用户。

 

 

--使用生成trace的用户执行trcanlzr.sql 脚本分析trace

--这里要注意一个trace文件的位置问题,默认情况下这个目录是USER_DUMP_DEST.

SQL> SELECT directory_path FROMdba_directories WHERE directory_name = 'TRCA$INPUT1';

 

DIRECTORY_PATH

--------------------------------------------------------------------------------

d:\app\administrator\diag\rdbms\dave\dave\trace

 

SQL> show parameter USER_DUMP_DEST

 

NAME           TYPE        VALUE

----------------------------------------------- ------------------------------

user_dump_dest    string     d:\app\administrator\diag\rdbms\dave\dave\trace

 

这个TRCA$INPUT1 的默认位置可以修改,具体见后面常见问题部分。

 

--开始分析

SQL> @trcanlzr.sql dave_ora_4588.trc

--这里注意2点:

(1)trace名称不包含路径,使用TRCA$INPUT1的目录

(2)sqlplus 连接的是trace 生成的用户

 

Parameter 1:

Trace Filename or control_file.txt(required)

 

 

Value passed to trcanlzr.sql:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TRACE_FILENAME: dave_ora_4588.trc

 

Analyzing dave_ora_4588.trc

 

To monitor progress, login as TRCANLZR intoanother session and execute:

SQL> SELECT * FROM trca$_log_v;

 

... analyzing trace(s) ...

 

 

 

Trace Analyzer completed.

Review first trcanlzr_error.log file forpossible fatal errors.

Review next trca_e15491.log for parsingmessages and totals.

 

Copying now generated files into localdirectory

 

 

TKPROF: Release 11.2.0.1.0 - Development on星期四 2月 9 16:47:53 2012

 

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

 

 

 adding: trca_e15491.html (224 bytes security) (deflated 88%)

 adding: trca_e15491.log (224 bytes security) (deflated 83%)

 adding: trca_e15491.tkprof (224 bytes security) (deflated 79%)

 adding: trca_e15491.txt (224 bytes security) (deflated 84%)

 adding: trcanlzr_error.log (224 bytes security) (deflated 81%)

test of trca_e15491.zip OK

deleting: trcanlzr_error.log

Archive:  trca_e15491.zip

 Length      Date    Time   Name

--------- ---------- -----   ----

   98344  2012/02/09 16:47   trca_e15491.html

   16010  2012/02/09 16:47   trca_e15491.log

    7368  2012/02/09 16:47   trca_e15491.tkprof

   47565  2012/02/09 16:47   trca_e15491.txt

---------                     -------

  169287                     4 files

 

File trca_e15491.zip hasbeen created

--分析结束生成了一个zip 的包,里面包含以上4个文件。该包存在sqlplus 的当前目录下,即开始连接sqlplus 的用户,这里是trca/run目录

 

TRCANLZR completed.

SQL>

 

Html 如下:

 


2.3.2 分析同系统的多个trace文件

--创建controlfile

将多个trace 文件名写入control file,只要名称,不包含路径,每行写一个trace名。

 

--将controlfile 拷贝到TRCA$INPUT1目录下,这个可以目录可以使用如下SQL 查询:

SQL>SELECT directory_path FROMdba_directories WHERE directory_name = 'TRCA$INPUT1'; 

 

--使用trace 的生成用户连接,分析trace


SQL>oradebug setmypid

Statement processed.

SQL>oradebug event 10046 trace name context forever,level 12;

Statement processed.

SQL>select * from dba_data_files;

SQL>oradebug event 10046 trace name context off;

Statement processed.

SQL>oradebug tracefile_name

d:\app\administrator\diag\rdbms\dave\dave\trace\dave_ora_6596.trc

 

--control_file.txt 内容:

dave_ora_6596.trc

dave_ora_4588.trc

 

--开始分析

SQL>@trcanlzr.sql control_file.txt

 

Parameter 1:

Trace Filename or control_file.txt(required)

 

 

Value passed to trcanlzr.sql:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TRACE_FILENAME: control_file.txt

 

Analyzing control_file.txt

 

To monitor progress, login as TRCANLZR intoanother session and execute:

SQL> SELECT * FROM trca$_log_v;

 

... analyzing trace(s) ...

 

 

 

Trace Analyzer completed.

Review first trcanlzr_error.log file forpossible fatal errors.

Review next trca_e15492.log for parsingmessages and totals.

 

Copying now generated files into localdirectory

 

 

TKPROF: Release 11.2.0.1.0 - Development on星期四 2月 9 17:05:21 2012

 

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

 

 

 adding: trca_e15492.html (224 bytes security) (deflated 90%)

 adding: trca_e15492.log (224 bytes security) (deflated 85%)

 adding: trca_e15492.tkprof (224 bytes security) (deflated 81%)

 adding: trca_e15492.txt (224 bytes security) (deflated 87%)

 adding: trcanlzr_error.log (224 bytes security) (deflated 82%)

test of trca_e15492.zip OK

deleting: trcanlzr_error.log

Archive: trca_e15492.zip

 Length      Date    Time   Name

--------- ---------- -----   ----

  198791  2012/02/09 17:05   trca_e15492.html

   19080  2012/02/09 17:05   trca_e15492.log

   10162  2012/02/09 17:05   trca_e15492.tkprof

   96165  2012/02/09 17:05   trca_e15492.txt

---------                     -------

  324198                     4 files

 

File trca_e15492.zip has been created

 

TRCANLZR completed.

SQL>

 

 

在报告的顶部会列出分析的trace 文件名。

 

2.3.3 在Target系统上分析Source系统上的一个trace 文件

 

(1)在Source数据库上操作,使用SYS 连接,执行/trca/dict/trcadictexp.sql 脚本,生成一个TRCA_DICT.ZIP 文件,其内容是Source data dictionary的一个子集。 执行这部不需要在源库上安装TRCA.

 

[oracle@localhost ~]$ cd /u01/trca/ dict/

[oracle@localhost dict]$ ls

trcadictexp.sql  trcadictimp.sql  trcadictpurge.sql

[oracle@localhost dict]$ sqlplus / assysdba

 

SQL*Plus: Release 10.2.0.4.0 - Productionon 星期四 2月 9 18:21:49 2012

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Production

With the Partitioning, Data Mining and RealApplication Testing options

 

SQL> @trcadictexp.sql

 

exporting data dictionary...

 

01 of 11: control file

02 of 11: sys.file$

03 of 11: sys.uet$ dictionary managedextents (subset)

04 of 11: sys.x$ktfbue locally managedextents (subset)

 

05 of 11: dba_segments (subset)

06 of 11: dba_tables

07 of 11: dba_indexes

08 of 11: dba_tab_cols (only indexedcolumns)

09 of 11: dba_ind_columns

10 of 11: dba_objects (subset)

11 of 11: v$parameter2 (subset)

generating zip file...

 adding: TRCA_CONTROL.txt (deflated 8%)

 adding: TRCA_EXTENTS_DM.txt (stored 0%)

 adding: TRCA_EXTENTS_LM.txt (deflated 79%)

 adding: TRCA_FILE.txt (deflated 41%)

 adding: TRCA_IND_COLUMNS.txt (deflated 87%)

 adding: TRCA_INDEXES.txt (deflated 87%)

 adding: TRCA_OBJECTS.txt (deflated 82%)

 adding: TRCA_PARAMETER2.txt (deflated 47%)

 adding: TRCA_SEGMENTS.txt (deflated 83%)

 adding: TRCA_TAB_COLS.txt (deflated 84%)

 adding: TRCA_TABLES.txt (deflated 83%)

 adding: trcadictexp.log (deflated 52%)

test of TRCA_DICT.zip OK

 

TRCA_DICT.zip has been generated

 

trcadictexp.sql completed.

 

--生成的文件在sqlplus的当前目录下。

 

 

(2)将生成的TRCA_DICT.zip文件在拷贝到TRCA$STAGE目录下,查看目录使用如下脚本,一般是USER_DUMP_DEST.

SQL>SELECT directory_path FROMdba_directories WHERE directory_name = 'TRCA$STAGE'; 

 

 

(3)将源库生成的trace 文件拷贝到Target 库的TRCA$INPUT1目录下

SQL>SELECT directory_path FROMdba_directories WHERE directory_name = 'TRCA$INPUT1';

这里的文件名是:xezf_ora_24571.trc

 

(4)在TRCA$STAGE下解压缩TRCA_DICT.ZIP 文件, 其会生成一个TRCA_*.txt的文件,注意不能有文件夹,生成的文件必须直接放在TRCA$STAGE目录下。

 

# cd [STAGE directory]

# unzip TRCA_DICT.zip

 

(5)在Target 库,切换trca 目录,使用TRCANLZR 连接sqlplu,执行trca/dict/脚本导入源库的TRCA repository。

 

E:\Software\OracleSoftware\Tools\trca\dict>sqlplustrcanlzr/oracle

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 2月 9 18:39:38 2012

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

 

SQL> @trcadictimp.sql

Trace Analyzer data dictionary repositoryhas been imported.

 

trcadictimp.sql completed.

 

(6)使用trcanlzr用户运行trca/run/ 脚本分析之前拷贝过来的trace 文件。

 

SQL> @trcanlzr.sql xezf_ora_24571.trc

 

Parameter 1:

Trace Filename or control_file.txt(required)

 

 

Value passed to trcanlzr.sql:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TRACE_FILENAME: xezf_ora_24571.trc

 

Analyzing xezf_ora_24571.trc

 

To monitor progress, login as TRCANLZR intoanother session and execute:

SQL> SELECT * FROM trca$_log_v;

 

... analyzing trace(s) ...

 

 

Trace Analyzer completed.

Review first trcanlzr_error.log file forpossible fatal errors.

Review next trca_e15493.log for parsingmessages and totals.

 

Copying now generated files into localdirectory

 

TKPROF: Release 11.2.0.1.0 - Development on星期四 2月 9 18:43:19 2012

 

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

 

 

 adding: trca_e15493.html (224 bytes security) (deflated 89%)

 adding: trca_e15493.log (224 bytes security) (deflated 84%)

 adding: trca_e15493.tkprof (224 bytes security) (deflated 78%)

 adding: trca_e15493.txt (224 bytes security) (deflated 83%)

 adding: trcanlzr_error.log (224 bytes security) (deflated 81%)

test of trca_e15493.zip OK

deleting: trcanlzr_error.log

Archive: trca_e15493.zip

 Length      Date    Time   Name

--------- ---------- -----   ----

   97228  2012/02/09 18:43   trca_e15493.html

   15881  2012/02/09 18:43   trca_e15493.log

    8669  2012/02/09 18:43   trca_e15493.tkprof

   44707  2012/02/09 18:43   trca_e15493.txt

---------                     -------

  166485                     4 files

 

File trca_e15493.zip has been created

 

TRCANLZR completed.

 

 

小结:

1.我这里的测试环境:

Source : linux + Oracle10gR2

Target : win7 + Oracle 11gR2

 

2. 不同系统与同系统的区别在于,当系统不同时,需要先使用trca 脚本,把Source 库的data dictionary 导出在导入到Target 系统上,然后使用trcanlzr用户来进行操作。

       如果是同系统,那么直接使用生成trace 的用户来连接,因为在分析时,其要收集相关的信息。

 

 

2.3.4 在Target系统上分析Source系统上的多个trace 文件

 

这里的流程和前面的就差不多了。

 

(1)在Source 库用SYS用户执行trca/dict/脚本,导出TRCA_DICT.ZIP。

# sqlplus / as sysdba

SQL> START trcadictexp.sql

 

(2)将生成的TRCA_DICT.ZIP 文件拷贝到TARGET系统的TRCA$STAGE目录下,通常是USER_DUMP_DEST。然后解压缩,将文件直接放在TRCA$STAGE目录下。

 

(3)将要分析的trace 文件都拷贝到Target 系统的TRCA$INPUT1目录下。

 

(4)创建控制文件,control_file.txt,将要分析的trace 文件名写入control_file.txt,每行一个文件名,不包含路径,然后将控制文件放到TRCA$INPUT1下。

 

(5)在Target系统上使用TRCANLZR用户连接sqlplus,然后执行trca/dict/导入Source 库的数据字典,在执行trca/run/ 脚本分析trace 文件。

 

 

三.常见问题

3.1 What is new in this TRCA release?

A summary ofrecent TRCA changes can be found in file trca/doc/.

 

3.2 Can I change the TRCA staging directory?

Yes,use trca/utl/ passing the full directory path. This pathcannot contain the "?" or "*" symbols and it is casesensitive in most systems. This directory must exist in the serverand ORACLE must have read/write access to it.

 

3.3 Can I change the TRCAinput directories?

Yes, but this isnot recommended. You can use trca/utl/ or trca/utl/ passing the full directory path. This pathcannot contain the "?" or "*" symbols and it is casesensitive in most systems. This directory must exist in the serverandORACLE must have read access to it.

 

3.4 How can I expedite"Taking a snapshot of some Data Dictionary objects"?

A snapshot ofsome Data Dictionary objects takes between a couple of minutes and up toseveral hours, depending on the size of the data dictionary. This is the laststep of the installation. If killed, it will be automatically executed when thetool is first used.

To expedite thesnapshot time, and with some loss of functionality, you can disable the snaphotof extents by executing these commands connecting as TRCANLZR: 


SET SERVEROUT ON;
EXEC trca$g.set_param('capture_extents', 'N');
EXEC trca$t.refresh_trca$_dict_from_this;

 

If still"Taking a snapshot of some Data Dictionary objects" takes longer thanseveral hours, it can be disabled altogether with significant loss of functionality,by executing these commands connecting as TRCANLZR: 
SET SERVEROUT ON;
EXEC trca$g.set_param('refresh_dict_repository', 'N');
EXEC trca$t.refresh_trca$_dict_from_this;

 

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

Email:  

Skype: tianlesoftware

Blog:    

Weibo:

Twitter:

Facebook:

 

阅读(840) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~