| General Information |
| Note: Trace Analyzer is a little known tool
downloadable from Oracle that is an improvement and substitute for TKPROF for analyzing trace files. |
| Download Trace Analyzer |
MetaLink Note 224270.1
http://metalink.oracle.com/metalink/plsql/ml2_gui.startup |
| Install Trace Analyzer |
Read the instructions in INSTRUCTIONS.TXT to install the product.
As the instructions are not the clearest the following is what I did to install
TraceAnalyzer so that it would be owned by the SYSTEM schema |
1. Created a directory named INSTALL
2. Unzipped TRCA.zip into the INSTALL directory
3. Created a directory under $ORACLE_HOME named TraceAnalyzer
4. Moved the .sql files from the INSTALL to the TraceAnalyzer directory
5. Logged onto Oracle as SYS
conn / as sysdba
6. Performed the following grants to SYSTEM
GRANT SELECT ON dba_indexes TO <schema_name>;
GRANT SELECT ON dba_ind_columns TO <schema_name>;
GRANT SELECT ON dba_objects TO <schema_name>;
GRANT SELECT ON dba_tables TO <schema_name>;
GRANT SELECT ON dba_temp_files TO <schema_name>;
GRANT SELECT ON dba_users TO <schema_name>;
GRANT SELECT ON v_$instance TO <schema_name>;
GRANT SELECT ON v_$latchname TO <schema_name>;
GRANT SELECT ON v_$parameter TO <schema_name>;
7. Connected to Oracle as SYSTEM
8. Ran the installation script TRCACREA.sql
If any error occur recompile the package TRCA$ and correct errors.
They will most likely be caused by permissions granted through
roles by SYS rather than being granted explicitly as required. |
| |
| Running Trace Analyzer |
| Run TraceAnalyzer |
Assuming the name of the trace file is
orabase_ora_1708.trc and that the trace file is located at /oracle/admin/orabase/udump |
CONN system/<password>
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SELECT COUNT(*)
FROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name;
ALTER SESSION SET sql_trace = FALSE;
Log onto an operating system session and navigate to the TraceAnalyzer directory:
c:> cd oracle\ora92\TraceAnalyzer
Start SQL*Plus
c:\oracle\ora92\TraceAnalzyer> sqlplus system/<pwd>@<service_name>
Run Trace Analysis
@TRCANLZR.sql UDUMP orabase_ora_1708.trc
Exit SQL*Plus
The trace analysis will be located in the TraceAnalyzer directory
with the name TRCANLZR_orabase_ora_1708.LOG.
Run TKPROF on the same trace file for comparison. |