RNM

February 2, 2011

Instrumenting OBIEE for tracing Oracle DB calls

Filed under: log, monitoring, obiee, oracle, performance, usagetracking — rmoff @ 15:04

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

About these ads

6 Comments

  1. And hop a crunch ;-)

    Comment by Nicolas GERARD — February 2, 2011 @ 17:58

  2. Just to say that you can’t see the value of a request variable in the session manager because their scope is only for the logical sql query.

    And as you can’t have a request variable without a session variable, I ask me if you can’t use the standard SAW_SRC_PATH in place of REPORT.

    Cheers
    Nico

    Comment by Nicolas GERARD — April 3, 2011 @ 20:30

  3. [...] Instrumenting OBIEE for tracing Oracle DB calls [...]

    Pingback by Have you defined CLIENT_ID in OBIEE yet? « RNM — August 8, 2011 @ 07:34

  4. Brilliant work on the instrumentation stuff. I’m a firm believer of setting MODULE and ACTION from a DBMS_MONITOR and V$SESSION perspective whenever I write PL/SQL, and it’s interesting how you got this working with OBIEE.

    One recommendation though… perhaps we could use MODULE and ACTION. Set MODULE to “OBIEE Analyses” (or whatever) and ACTION to the actual report name. That way, we can use DBMS_MONITOR to trace all analyses (this is sort of the point of MODULE… and I think I’ve applied to principal correctly):

    SQL> BEGIN
    2 DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
    3 (‘APPS1′,
    4 ‘OBIEE Analyses’,
    5 DBMS_MONITOR.ALL_ACTIONS
    6 );
    7 END;
    8 /

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.02
    SQL>

    Or just an individual analysis:

    SQL> BEGIN
    2 DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
    3 (‘APPS1′,
    4 ‘OBIEE Analyses’,
    5 ‘Revenue by Quarter/Product’
    6 );
    7 END;
    8 /

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.00
    SQL>

    What might be better is to set MODULE equal the Dashboard, or Dashboard + Dashboard Page. I’ll have to think about that.

    Comment by Stewart Bryson (@stewartbryson) — September 22, 2011 @ 12:45

    • Hi Stewart,
      thanks for the comment. Using MODULE & ACTION is what I’ve suggested in the para “Instrument individual reports”, unless I’ve misunderstood you? Getting the report name out automagically is the tricky bit though, and where I’m stuck. Any idea around harnessing the variables (eg SAW_SRC_PATH)?

      Comment by rnm1978 — September 22, 2011 @ 22:05

  5. [...] on this blog: 1, 2, [...]

    Pingback by Instrumenting OBIEE – the final chapter « RNM — October 10, 2011 @ 20:19


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: