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. [sourcecode] call dbms_session.set_identifier(‘VALUEOF(NQ_SESSION.USER)’) [/sourcecode]
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.
