Title of this article is from the Oracle Metalink note (Doc ID 444822.1) that describes how to collect performance related data when opening a new SR for Oracle Retail Performance issue.

Inside that note you can find the following:

For additional help in enabling tracing for Oracle Retail Java-based applications, please see Document 568004.1 – How to Trace and Debug Retail ‘Java’ Applications.

The next logical step is to open document 568004.1 that describes how to debug Oracle Retail Java applications.

 

From the name of the document it is clear that the title is wrong. Here the term Oracle Retail Java applications reference all Retail applications that are using connection pool to access database resources.

Up to the version 16 of Oracle Retail, the only application that is not Java based is the central one: RMS (Retail Merchandising System). There are RTS (Retail Trade Management) and ReSA (Sales Audit) besides, but the RMS is most important and that is Oracle Forms based application.

With Oracle Forms, it is very simple to track RMS users, as in v$session you can see the end user name exposed.

Java connection pool is much efficient than Oracle Forms, but it is difficult to track end users, and application itself.

The document 568004.1 should provide solution how to trace, RPM (Retail Price Management) or ReIM for example.

This is what you’ll find as suggested solution from Oracle Support for tracing “Java Apps”:


CREATE OR REPLACE TRIGGER SQLTRACE_TRIGGER AFTER LOGON ON DATABASE
DECLARE
...

   CURSOR c_session_info IS
      SELECT 'x'
        FROM sys.v_$session
       WHERE sid = (SELECT sid FROM sys.v_$mystat WHERE ROWNUM=1)
           AND program LIKE 'JDBC%'
             AND client_info = L_client_info_set;
...
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 8''';
	-- execute immediate 'alter TRIGGER SQLTRACE_TRIGGER disable';

...
   EXCEPTION

     WHEN OTHERS THEN
RAISE;
...

It’s really hard to believe that someone has written that script.

 

You should first ask yourself, should I follow it, as it is official Oracle document, in which case I’ll be compliant with Oracle?

 

I believe it is always the best to think in rational and logical manner with your own head, and NOT to follow that document, as there are much better solution that doesn’t require creation of the AFTER LOGON trigger, which is one of the most dangerous commands in the entire Oracle database.

 

Solution is based on dbms_monitor package, which doesn’t require to create a trigger at all, and it is at your disposal from 10g version of Oracle database (meaning 10+ years).

 

Now when you (hopefully) understand why it is dangerous to track Java apps in general, not only Oracle Retail apps, by using AFTER LOGON trigger, look at the next section of code:


AND program LIKE 'JDBC%'

It is clear that you can’t track some particular user with such code.

Problem is that you can’t even track the whole application.

For example, If you have RPM and ReIM installed, sessions from both of those two apps will be returned with such WHERE clause filter condition.

Let’s move on EXECUTE IMMEDIATE part.


EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 8''';

Again, why use that form when you have much more powerful way to do the same by using dbms_monitor package.

The last point is exception handler.


...
   EXCEPTION

     WHEN OTHERS THEN
RAISE;
...

In 11g version of the database if you set compiler warning


ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'

you’ll get the following comilation error:


PL/SQL Compiler warning "PLW-06009: procedure “string” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR"

 

Fortunately, there is a RAISE command, but that part of the code is still poorly written.

In Oracle Support there are really excellent documents, and as long-term user I can testify there are lot of improvements in MOS portal, but this particular document that I’ve described is not one of that kind.

Hope someone from Oracle will read this, and correct the document.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

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