Instrumenting OBIEE for tracing Oracle DB calls

Cary Millsap recently published a paper “Mastering Performance with Extended SQL Trace” describing how to use Oracle trace to assist with troubleshooting the performance of database queries.
As with all of Cary Millsap’s papers it is superbly written, presenting very detailed information in a clear and understandable way. (and yes I do have a DBA crush ;-))
It discusses how you can automate the tracing of specific sessions on the database, and requiring the application to be appropriately instrumented. This reminded me of a post that I made almost exactly 12 months ago here, where I explained how to pass through the username of the OBIEE user to the database. Initially I thought it would be useful simply for being able to pin a rogue query to an end-user, but reading Cary’s paper made me realise there is more potential to it.

Why would you use it in OBIEE?

Essentially, it enables you to precisely identify DB connections coming in from OBIEE. Since you can identify the connections, you can then trace them or collect additional statistics on them.

In Production, this would be useful for helping with troubleshooting. If a query is behaving badly, the responsible user can be easily identified, and through the login ID matched back to Usage Tracking data (you do collect Usage Tracking data, right?). Conversely, if a user is complaining (unlikely, I know 😉 ) of performance issues you can easily spot their queries running on the database and get a head start on identifying the problem.

As well as tracing, you can use these attributes to collect statistics (eg I/O wait time, db time, etc) for specific users or application areas. You use the DBMS_MONITOR CLIENT_ID_STAT_ENABLE procedure and then view the stats in V$CLIENT_STAT. Similar proc & V$ table exist for module-targeted statistics collecting.

Implementation

In essence, all you do is use the OBIEE Connection Scripts setting in the appropriate Connection Pool to call one or more of the PL/SQL packages. The values that you can set on the connection are as follows:

V$SESSION column	Corresponding connection command to set	Max value length
MODULE	dbms_application_info.set_module(module_name=>'[…]’,action_name =>NULL)	47
ACTION	dbms_application_info.set_module(module_name=>'[…]’,action_name=>'[…]’) or dbms_application_info.set_action(action_name=>'[…]’)	31
CLIENT_INFO	dbms_application_info.set_client_info	63
CLIENT_IDENTIFIER	dbms_session.set_identifier	63

(Ref: DBMS_APPLICATION_INFO, DBMS_SESSION)

For example, to pass through the OBIEE username and display name (NQ_SESSION.USER and NQ_SESSION.DISPLAYNAME respectively) you would use the following code:

Connection Pool settings

When you look at V$SESSION for the connection from OBIEE, it would show up something like this:

V$SESSION showing values from OBIEE connection

Instrument individual reports

What would be really cool would be if we could pass through the details of the report being executed.
A rather clunky way of doing this is by setting a custom session variable in the Logical SQL that gets sent to BI Server:

Then add a script to the connection pool to pass this value through in the database connection:

When run this then shows up on V$SESSION as:

col client_identifier for a20
col client_info for a20
SELECT PROGRAM, CLIENT_IDENTIFIER, CLIENT_INFO, MODULE, ACTION FROM V$SESSION WHERE LOWER(PROGRAM) LIKE 'nqsserver%';

You get an error if you’ve not set a value for the variable that is referenced in the connection script (in the above example, “REPORT”). So what you could do is create a dummy session variable called REPORT with a default value (eg “<unspecified report>”), which will then be used if a report doesn’t override it:

Dummy session variable

Dummy init block

Variables

It may be my misunderstanding of the subtleties of the flavours of OBIEE variables, but the behaviours seem inconsistent to me. For example, even though I am issuing a SET VARIABLE in my logical SQL, the value of the variable REPORT doesn’t change from its default (in this example ‘NONE’) when listed in the Session Manager or queried via Narrative view. It isn’t even shown if I don’t create it as a session variable in the RPD.

Unchanging session variable value

Session variables displayed in Answers

Despite this, the modified value of the variable is what gets passed through correctly in the DB connection.

SAW_SRC_PATH

This is a variable (along with QUERY_SRC_CD) that is passed automagically by Presentation Services to BI Server in the Logical SQL it executes:

Logical SQL, as seen in NQQuery.log

If this could be harnessed and manipulated (eg right-most 63 chars) then the report details of any report could be automatically included with the DB connection string. But – try as I have I can’t access the variable through VALUEOF. Anyone know how?

References

• Mastering Performance with Extended SQL Trace – Cary Millsap
• @lex’s variables reference diagram
• Gerard Nico’s excellent wiki has lots of information about OBIEE variables, amongst other things