Correlating APEX Sessions to Database Sessions

I received the following question via email today:

"Had a question from a client yesterday concerning the subject:  I want to know which database session (APEX_PUBLIC_USER)  is servicing which APEX session. Poking around in the V$ tables, I can see that in v$SQL, the module column will reveal the APEX Application and Page, but not the Session ID.  Even if the session ID was in there, I don’t see an obvious way to join back to V$SESSION." 

It's a bit of a puzzling problem for DBA's and developers - being able to correlate a database session with a specific APEX application and session.  As I wrote about earlier, all database sessions in the database session pool of ORDS are connected as APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL Gateway).  If a user is experiencing slowness in their APEX application, how can a DBA look under the hood, identify the database session associated with the request from that user, and also look at the active SQL statement and possibly any wait events with that session?

This question comes up a lot, and should really be covered in the Oracle Application Express documentation.  But in the meantime, here's the definitive answer:

APEX populates the following information in GV$SESSION for ACTIVE sessions:

client_info: Workspace ID:Authenticated username
module: DB Schema/APEX:APP application id:page id
client_identifier: Authenticated username:APEX Session ID

For example, for a recent request I did on apex.oracle.com, I had the following values in the DB session that executed my request:

client_info: 3574091691765823934:JOEL.KALLMAN@FOOBAR.COM
module: JOEL_DB/APEX:APP 17251:4
client_identifier: JOEL.KALLMAN@FOOBAR.COM:12161645673208

There is no permanent, fixed correlation between an APEX session and a database session.  The assignment of a session in the session pool to service an APEX request is essentially unpredictable and not constant.  That's why this correlation can only be done for active database sessions, which are actively servicing APEX requests.

There's one caveat.  A developer could overwrite these values using the database-provided PL/SQL API, which I've seen customers do occasionally.  Otherwise, for active database sessions, you'll see these three elements populated in GV$SESSION, and module & client_identifer will also be present in the Active Session History.  Carlos Sierra has an excellent blog post about how to query the Active Session History and identify poor performing APEX applications and their associated SQL.