Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

SQL Trace and Oracle Portal

29 November 2011 2 Comments Written by Christian Antognini

Recently I was involved in a project where I had to trace the database calls of an application based on Oracle Portal 10.1.4. The basic requirements were the following:

  • Tracing takes place in the production environment
  • Tracing has to be enable for a single user only
  • Instrumentation code cannot be added to the application

Given that Oracle Portal uses a pool of connections and that for each HTTP call it can use several database sessions, statically enable SQL trace for specific sessions was not an option.

Knowing nothing about Oracle Portal I started RTFM and, gladly, I discovered that there is a simple way to inject a piece of code before and after a requested procedure is called. This is done by setting, via the administration GUI, the parameters PlsqlBeforeProcedure and PlsqlAfterProcedure.

Since Oracle Portal provides a function (WWCTX_API.GET_USER) to get the current user, I decided to create the following procedures to set/clear the client identifier before/after every call. Note that I added the call to SUBSTR and the exception handler to make sure that the procedures do not raise exceptions (hey, it’s a production system and I do not want to impact everyone!).

CREATE PROCEDURE tvd_set_client_identifier AS
BEGIN
  dbms_session.set_identifier(substr(portal.wwctx_api.get_user,1,64));
EXCEPTION
  WHEN others THEN NULL;
END;
CREATE PROCEDURE tvd_clear_client_identifier AS
BEGIN
  dbms_session.clear_identifier;
EXCEPTION
  WHEN others THEN NULL;
END;

To “enable” these procedures we did the following:

  • Set PlsqlBeforeProcedure=portal.tvd_set_client_identifier
  • Set PlsqlAfterProcedure=portal.tvd_clear_client_identifier
  • Restarted the application server

With this configuration in place enabling SQL trace for a single user was easily done by calling the DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE procedure by specifying the user to be traced as value for the CLIENT_ID parameter.

10gR1, 10gR2, 11gR1, 11gR2, SQL Trace
Debugging PL/SQL and Java Stored Procedures with JPDA
Ad: Trivadis TechEvent 2011

No Comments Yet

2 Pings/Trackbacks

  1. Log Buffer #248, A Carnival of the Vanities for DBAs | The Pythian Blog on 2 December 2011 at 07:01
  2. Log Buffer #248, A Carnival of the Vanities for DBAs on 1 March 2013 at 21:24

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.