Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

DBMS_TRACE Package


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The dbms_trace package provides an API to allow the actions of PL/SQL programs to be traced, where the scope and volume of the tracing is user configurable. This package is loaded by default, but the schema tables to hold the collected data is created by running the $ORACLE_HOME/rdbms/admin/tracetab.sql as the SYS user, shown in the trace_setup.sql script.

trace_setup.sql

CONNECT sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/tracetab.sql

CREATE PUBLIC SYNONYM plsql_trace_runs FOR plsql_trace_runs;
CREATE PUBLIC SYNONYM plsql_trace_events FOR plsql_trace_events;
CREATE PUBLIC SYNONYM plsql_trace_runnumber FOR plsql_trace_runnumber;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON plsql_trace_events TO PUBLIC;
GRANT SELECT ON plsql_trace_runnumber TO PUBLIC;

The public grants and synonyms mean that users in the database can profile their PL/SQL.  To restrict its usage simply alter these grant statements.

Now that the trace tables are installed, some PL/SQL is needed to trace.  The trace_test.sql script creates a procedure that performs a simple query and some number conversions. 

trace_test.sql

CREATE OR REPLACE PROCEDURE trace_test (p_loops  IN  NUMBER) AS
  l_dummy  NUMBER := 0;
BEGIN
  FOR i IN 1 .. p_loops LOOP
    SELECT l_dummy + 1
    INTO   l_dummy
    FROM   dual;   

    l_dummy := TO_NUMBER(TO_CHAR(l_dummy -1));
  END LOOP;
END;
/
SHOW ERRORS

The content of the trace records depend on the trace level being used.  The dbms_trace package contains the following constants that define the levels.

trace_all_calls          constant INTEGER := 1;
trace_enabled_calls      constant INTEGER := 2;
trace_all_exceptions     constant INTEGER := 4;
trace_enabled_exceptions constant INTEGER := 8;
trace_all_sql            constant INTEGER := 32;
trace_enabled_sql        constant INTEGER := 64;
trace_all_lines          constant INTEGER := 128;
trace_enabled_lines      constant INTEGER := 256;
trace_stop               constant INTEGER := 16384;
trace_pause              constant INTEGER := 4096;
trace_resume             constant INTEGER := 8192;
trace_limit              constant INTEGER := 16;

The run_trace.sql script initiates tracing, calls the test procedure and stops tracing three times, each with a different trace level.

run_trace.sql

DECLARE
  l_result  BINARY_INTEGER;
BEGIN
  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_calls);
  trace_test(p_loops => 100);
  DBMS_TRACE.clear_plsql_trace;

  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_sql);
  trace_test(p_loops => 100);
  DBMS_TRACE.clear_plsql_trace;

  DBMS_TRACE.set_plsql_trace (DBMS_TRACE.trace_all_lines);
  trace_test(p_loops => 100);
  DBMS_TRACE.clear_plsql_trace;
END;
/

With the tracing complete, the trace_runs.sql script can be used to identify the available trace runids.

trace_runs.sql

COLUMN run_date FORMAT A20

SELECT r.runid,
       TO_CHAR(r.run_date, 'DD-MON-YYYY HH24:MI:SS') AS run_date,
       r.run_owner
FROM   plsql_trace_runs r
ORDER BY r.runid;

The output from this script is listed below.

SQL> @trace_runs.sql

 RUNID RUN_DATE             RUN_OWNER
------ -------------------- -------------------------------
     1 05-APR-2005 13:58:46 TEST
     2 05-APR-2005 13:58:46 TEST
     3 05-APR-2005 13:58:46 TEST

3 rows selected.

The trace_run_events.sql script can be executed using the appropriate runid to display the trace data.

trace_run_events.sql

SET LINESIZE 200
SET TRIMOUT ON

COLUMN runid FORMAT 99999
COLUMN event_seq FORMAT 99999
COLUMN event_unit_owner FORMAT A20
COLUMN event_unit FORMAT A20
COLUMN event_unit_kind FORMAT A20
COLUMN event_comment FORMAT A30

SELECT e.runid,
       e.event_seq,
       TO_CHAR(e.event_time, 'DD-MON-YYYY HH24:MI:SS') AS event_time,
       e.event_unit_owner,
       e.event_unit,
       e.event_unit_kind,
       e.proc_line,
       e.event_comment
FROM   plsql_trace_events e
WHERE  e.runid = &1
ORDER BY e.runid, e.event_seq;

The output for the first trace run, trace_all_calls, is displayed below.

SQL> @trace_run_events.sql 1

RUNID EVENT_SEQ EVENT_TIME           EVENT_UNIT_OWNER     EVENT_UNIT           EVENT_UNIT_KIND       PROC_LINE EVENT_COMMENT
---------------------------------------------------------------------------------------------------------------     1         1 05-APR-2005 13:58:46                                                                           PL/SQL Trace Tool started
     1         2 05-APR-2005 13:58:46                                                                          
Trace flags changed
     1         3 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 66
Return from procedure call
     1         4 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 72
Return from procedure call
     1         5 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                  5
Return from procedure call
     1         6 05-APR-2005 13:58:46                      <anonymous>          ANONYMOUS BLOCK               1
Procedure Call
     1         7 05-APR-2005 13:58:46 TEST                 TRACE_TEST           PROCEDURE                     6 Return from procedure call
     1         8 05-APR-2005 13:58:46                      <anonymous>          ANONYMOUS BLOCK              74
Procedure Call
     1         9 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 60
Procedure Call
     1        10 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 55
Procedure Call
     1        11 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 12
Procedure Call 

RUNID EVENT_SEQ EVENT_TIME           EVENT_UNIT_OWNER     EVENT_UNIT           EVENT_UNIT_KIND       PROC_LINE EVENT_COMMENT
------------------------------------------------------------------------------------------------------------------     1        12 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 57 Return from procedure call
     1        13 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 63
Return from procedure call
     1        14 05-APR-2005 13:58:46 SYS                  DBMS_TRACE           PACKAGE BODY                 21
Procedure Call
     1        15 05-APR-2005 13:58:46                                                                          
PL/SQL trace stopped

15 rows selected. 

SQL>

Tracing large applications can produce massive volumes of data, making the tracing mechanism unusable.  To simplify matters, tracing can be switched on for individual programs using one of the following methods.

ALTER SESSION SET PLSQL_DEBUG=TRUE;
CREATE OR REPLACE [PROCEDURE | FUNCTION | PACKAGE BODY] ...

or:

ALTER [PROCEDURE | FUNCTION | PACKAGE]  <libunit-name> COMPILE DEBUG [BODY];

The dbms_trace output is focused on the order of events during a processing run, allowing the identification of code that is not following the expected pattern of execution.  This combined with the dbms_profiler output can allow quick identification of performance bottlenecks.

The next section presents various methods for tracing the SQL statements being issued by a session.

SQL trace, trcsess and tkprof

The quickest way to capture SQL being processed by a session is to switch on SQL trace or set the 10046 event for a representative period of time.  The resulting trace files can be read in their raw state or translated using the tkprof utility.  Explaining the contents of the trace file is beyond the scope of this book; however, the following sections explain how trace files can be created and processed.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational