You get a call from your friendly DBA. He says the production database is up the spout, and it’s “that bee eye thingumy causing it”. What do you do now? All you’ve got to go on is a program name in the Oracle session tables of “nqsserver@MYSERVER (TNS V1-V3)” and the SQL the DBA sent you that if you’re lucky will look as presentable as this:
The username against the SQL is the generic User ID that you had created for connections to the database from OBIEE.
So you turn to Usage Tracking and discover that when that particular SQL ran there were twenty users all running reports. And not only that, but the SQL that’s recorded is the Logical SQL, not the physical SQL.
So how do you identify the originating report that spawned the SQL that broke the database that upset the DBA that phoned you? …
With a large hat-tip to Mark Rittman, here’s one thing you can do to help matters. Within the Connection Pool object in the RPD you can add statements to execute at the beginning of each connection. In this case, we can set the Client ID for the user running the request.
call dbms_session.set_identifier('VALUEOF(NQ_SESSION.USER)')
Now when you look at the queries from OBIEE running on the database you’ll see the Client ID column is populated :
This helps you trace SQL from the database back to the originating user.
My only question about this is with regards to connection pooling. The documentation states that the Execute on Connect is run “…each time a connection is made to the database.” – but if connection pooling is enabled then by definition the connection is re-used so the client ID will only be set for the first user into the connection pool. However this doesn’t seem to be the case as on the database I see different Client IDs against the same session.
rmoff 
Thanks for this useful tip!
Comment by Kevin — January 26, 2010 @ 20:05
When I test this, it appears that the “…each time a connection is made to the database.” statement may be true. I am also seeing different client IDs against the same Session ID, however in my testing logon_time is also getting updated. This leads me to believe that the sessions are actually being recreated with this code in place. Without the code in the connection script, I see sessions being reused by different users (based on SQL being run) where the logon_time doesn’t change. Any thoughts?
Comment by cmartin2 — February 3, 2010 @ 20:49
I’m no further to understanding what should be happening (perhaps worth an SR to ask Oracle engineering for clarification?), but in doing some other work I noticed this:
For c.20 unique OBIEE SQL statements in the Oracle cursor cache, there was also “call dbms_session.set_identifier(‘Administrator’)” with an EXECUTIONS value of 21.
The reason I say c.20 is that this was whilst testing something else so the cache (both DB and OBIEE) wasn’t clean first so the number may be slightly skewed. But – I was using a dev OBIEE box with just me logged in, so by rights would have expected to see set_identifier called once or twice, not 20 times.
This is certainly an interesting point and one which if I get some time will try and pursue to its conclusion. It’s not just an academic one either because if a deployment is relying on connection pooling to provide decent scalability then implementing set_identifier (or any other statement to execute on connection) might impact this.
Comment by rnm1978 — February 5, 2010 @ 12:16
Wonderful artcile for setting up client id in session. It is really good practice to set cliend id for tracing it exactly and identfing correctly. Very good posting buddy.
Comment by Jack Nicholson — April 12, 2010 @ 07:16
[…] 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 […]
Pingback by Instrumenting OBIEE for tracing Oracle DB calls « RNM — February 2, 2011 @ 15:04
[…] Identify your OBIEE users by setting Client ID in Oracle connection […]
Pingback by Have you defined CLIENT_ID in OBIEE yet? « RNM — August 8, 2011 @ 07:34
To ensure proper functionality, why not use the “Execute before query” section as opposed to the “Execute on connect”?
Comment by oraclenerd (@oraclenerd) — August 9, 2011 @ 03:07
[…] on this blog: 1, 2, […]
Pingback by Instrumenting OBIEE – the final chapter « RNM — October 10, 2011 @ 20:19