Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Utilities Tips


The Oracle Trace Utility

Executing Trace Analyzer

First, tracing needs enabled at the appropriate level. For example, to provide maximum trace data, a Level 12 trace can be started for the current session:

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

After the session executes for enough time to gain needed data, the trcanlzr SQL script can be executed. It requires the name of the directory object. This object points to the physical operating system directory for the user_dump_dest. The installation of the utility will automatically create the directory object required (named UDUMP).

SQL>@d:\trcanlzr.sql UDUMP asg920xr_ora_13033.trc

Once executed, the output will be displayed on the screen and a spool file is created.

Better than tkprof?


Traditionally, tkprof has been the best tracing diagnostics tool available. That is, until the introduction of Trace Analyzer which is everything tkprof is and more. However, as of version 9.2, the Trace Analyzer utility is still not shipped with the Oracle DBMS like tkprof.

Given access to both utilities, Trace Analyzer has the following advantages:

1. Trace Analyzer provides the actual values of the bind variables in SQL. No longer are DBAs faced with wondering what the values were at runtime – Trace Analyzer provides them.
For the following SQL statement listed in the output:

DELETE FROM HISTORY where ALERT_TIME <= :b1 AND INSTANCE_NUMBER = :b2

Trace Analyzer would also display:

0:"2/4/2003 15:57:35" 1:1

which equates to the actual SQL statement of:

DELETE FROM HISTORY where ALERT_TIME <= :"2/4/2003 15:57:35" AND INSTANCE_NUMBER = 1

2. Trace Analyzer provides the hottest blocks, optimizer statistics for indexes and tables, and other information not available through tkprof. The output below shows the SQL statement, the execution plan, and statistics for each object in the SQL.

DELETE FROM SCOTT.EMPLOYEE

call count cpu elapsed disk query current rows misses
------- ------- ------- -------- -------- --------- --------- ------------ ---------
Parse 1 0.00 0.00 0 0 0 0 0
Execute 3 0.05 0.52 0 27 224 216 0
------- -------- -------- -------- -------- --------- --------- ----------- --------
total 4 0.05 0.52 0 27 224 216 0

Explain Plan
---------------------------------------------------------------
...3 DELETE STATEMENT
...2 .DELETE OF 'SCOTT.EMPLOYEE
...1 ..TABLE ACCESS (FULL) OF ‘SCOTT.EMPLOYEE'

OWNER.TABLE_NAME
...owner.index_name num rows blocks sample last analyzed date
------------------------------------ ---------- ---------- ---------- -------------------
SCOTT.EMPLOYEE..........................


The output above indicates that the EMPLOYEE table does not have statistics.

3. Trace Analyzer separates user recursive and internal recursive calls, unlike tkprof.

4. Trace Analyzer provides more detailed wait event information, which can be very useful to those DBAs who prefer wait-based tuning methodologies. This data is also very helpful when there is a significant gap between CPU and elapsed times.

Event Times Count Max. Total Blocks
waited on Waited Zero Time Wait Waited Accessed
----------------------------------------- --------- --------- ------- ------- --------
PL/SQL lock timer........................ 15 0 5.01 75.08
log file sync............................ 1 0 0.01 0.01
library cache pin........................ 1 0 0.00 0.00
SQL*Net message from client (idle)....... 2 0 17.22 30.21
SQL*Net message to client (idle)......... 3 0 0.00 0.00
total.................................... 22 0 17.22 105.30 0


One drawback concerning Trace Analyzer is that it requires objects in the database (the tracing repository) and that means more configuration work on each database that needs the utility installed. This is unlike tkprof, which is an executable file always there, ready to serve.

The benefits of the Trace Analyzer far outweigh the negatives since never before has so much useful information been available to the Oracle tuner in one place. Usually, tkprof needs combined with statspack reports and other utilities provide a comprehensive picture. Trace Analyzer takes tuning to a new level.

 


To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference".  You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.


 


  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.