【Oracle】一款非常好用的trace檔案分析工具之一

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

tablespace name is case sensitive.

default tablespace [unknown]: example  --（必須大寫，小寫會建立失敗）

default_tablespace

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

example

choose the trcanlzr user temporary tablespace.

failing, as using system for the temporary tablespace is not recommended.

tablespace_name

temp

temporary tablespace [unknown]: temp

temporary_tablespace

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

creating package specs trca$p

creating package specs trca$t

creating package specs trca$i

creating package specs trca$e

creating package specs trca$r

creating package specs trca$x

creating views

creating package body trca$g

creating package body trca$p

creating package body trca$t

creating package body trca$i

creating package body trca$e

creating package body trca$r

creating package body trca$x

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

tacreate completed. installation completed successfully.

建立了trcanlzr使用者和分析trace 檔案所需要的對象！