Saturday, October 9, 2010

TraceAnalyzer Setup and Execution Example

Bettter to have seperate table spaces for this tool.
create bigfile tablespace tp_traceanalyzer1 datafile '/home/oracle/db/dbs/tp_traceanalyzer1'
size 200m reuse
autoextend on next 100m maxsize unlimited
extent management local uniform size 100k
segment space management auto
nologging;

create temporary tablespace tmp_traceanalyzer tempfile '/home/oracle/db/dbs/tmp_traceanalyzer'
size 1920 k reuse;

START tacreate.sql

SQL> SET ECHO OFF;
TADUSR completed.
TADROP completed.

Creating TRCA$ INPUT/BDUMP/STAGE Server Directories

Notes:
1. Directories cannot contain "?", "*" or "$" symbols.
2. Specify existing server directories that ORACLE can access.
3. To accept a directory within [brackets] hit the "Enter" key.


Enter INPUT1 directory where traces are located (UDUMP normally).
Hit "Enter" key if you agree with recommended value below:
/home/oracle/db/rdbms/log

INPUT1 directory: /home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_1

INPUT1_DIRECTORY
--------------------------------------------------------------------------------
/home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_1

Enter INPUT2 directory where PX traces are located (BDUMP normally).
Hit "Enter" key if you agree with recommended value below:
/home/oracle/db/rdbms/log

INPUT2 directory: /home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_2

INPUT2_DIRECTORY
--------------------------------------------------------------------------------
/home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_2

Enter STAGE directory used as TRCA workarea (defaults to UDUMP).
Hit "Enter" key if you agree with recommended value below:
/home/oracle/db/rdbms/log

STAGE directory: /home/oracle/ORA_TRACE_ANAL_HOME/STAGE

STAGE_DIRECTORY
--------------------------------------------------------------------------------
/home/oracle/ORA_TRACE_ANAL_HOME/STAGE

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):


Define the TRCANLZR user password (hidden).

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


TABLESPACE_NAME                FREE_SPACE_MB
------------------------------ -------------
SYSAUX                                    11
UNDOTBS1                                  22
SOE                                      128
SOEINDEX                                 166
TP_TRACEANALYZER1                        200

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.


Default tablespace [UNKNOWN]: tp_traceanalyzer1

DEFAULT_TABLESPACE
------------------------------
TP_TRACEANALYZER1

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
TMP_TRACEANALYZER

Temporary tablespace [UNKNOWN]: tmp_traceanalyzer

TEMPORARY_TABLESPACE
------------------------------
TMP_TRACEANALYZER

Type of TRCA repository

Create TRCA repository 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.
Connected.

TAUTLTEST completed.

no rows selected

TACOBJ completed.
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$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
tool_owner: "TRCANLZR"
No errors.
Creating Package Body TRCA$R
No errors.
Creating Package Body TRCA$X
No errors.
Creating Grants on Packages

Tool Version
----------------
11.4.1.6

Install Date
----------------
20101009

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID)      /home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_1
TRCA$INPUT2(VALID)      /home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_2
TRCA$STAGE(VALID)       /home/oracle/ORA_TRACE_ANAL_HOME/STAGE
user_dump_dest          /home/oracle/db/rdbms/log
background_dump_dest    /home/oracle/db/rdbms/log

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.1.4 2010/07/12 csierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.1.5 2010/08/04 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.1.0 2010/05/25 csierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.1.4 2010/07/12 csierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.1.0 2010/05/25 csierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 11.4.1.5 2010/08/04 csierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 11.4.1.5 2010/08/04 csierra $ */
VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.1.5 2010/08/04 csierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.1.5 2010/08/04 csierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 11.4.1.6 2010/08/20 csierra $ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.1.5 2010/08/04 csierra $ */
TACPKG completed.

Taking a snapshot of some Data Dictionary objects, please wait...

00:41:16 => refresh_trca$_dict_from_this
00:41:16 -> purge_trca$_dict
00:41:16 dict_state_before_purge
00:41:16 -----------------------
00:41:16 -> print_dict_state
00:41:16 dict_refresh_days :
00:41:16 dict_refresh_date :
00:41:16 dict_database_id  :
00:41:16 dict_database_name:
00:41:16 dict_instance_id  :
00:41:16 dict_instance_name:
00:41:16 dict_host_name    :
00:41:16 dict_platform     :
00:41:16 dict_rdbms_version:
00:41:16 dict_db_files     :
00:41:16 <- print_dict_state
00:41:16 -> purge_trca$_dict_gtt
00:41:16 <- purge_trca$_dict_gtt
00:41:16 dict_state_after_purge
00:41:16 ----------------------
00:41:16 -> print_dict_state
00:41:16 dict_refresh_days :
00:41:16 dict_refresh_date :
00:41:16 dict_database_id  :
00:41:16 dict_database_name:
00:41:16 dict_instance_id  :
00:41:16 dict_instance_name:
00:41:16 dict_host_name    :
00:41:16 dict_platform     :
00:41:16 dict_rdbms_version:
00:41:16 dict_db_files     :
00:41:16 <- print_dict_state
00:41:16 <- purge_trca$_dict
00:41:16 -> trca$_file$


Snapshot of some Data Dictionary objects completed.


TAUTLTEST completed.

TACREATE completed. Installation completed successfully.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Install Complete
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =


Use:
1. default user is:TRCANLZR I choose "oracle" as password
2. The trace file must exist in the INPUT1 directory. To locate INPUT1: SELECT directory_path FROM dba_directories WHERE directory_name = 'TRCA$INPUT1'; INPUT1 is usually USER_DUMP_DEST.But I created a separate directory. 


[oracle@dctxvm55 log]$ mv testdb_logs.tar /home/oracle/ORA_TRACE_ANAL_HOME/INPUT_SQL_TRACES_1

[oracle@dctxvm55 run]$ pwd
/home/oracle/ORA_TRACE_ANAL_HOME/HOME/trca/run
[oracle@dctxvm55 run]$ ls
trcanlzr.sql
[oracle@dctxvm55 run]$
SQL> conn TRCANLZR/oracle
Connected.
SQL> START trcanlzr.sql db1_ora_29116.trc


Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: db1_ora_29116.trc

Analyzing db1_ora_29116.trc

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@dctxvm55 run]$

No comments:

Post a Comment