How to Tie OS-level PIDs to Oracle Database Sessions

About once a week, our team gets a request from the server admins to provide some information on why a database server is running slowly. This typically begins a painstaking process of finding the main processes on the OS side and tracing them back to database sessions (and possibly even to Oracle Apps Concurrent Requests).

As we all know, writing SQL to do this tie-back-when-the-issue-is-happening is not a good practice — there is always the chance to mis-type something or to forget a join and have the data come back as unusable for good decision-making. Not to mention that there is usually a time constraint involved when diagnosing high server load, etc.

We started fairly small with a solution to our problem and simply joined the v$process view to the v$session to get the OS process ID tied out to a database session. Once you have that information from v$session, you can start to going in other views such as v$sort_usage, v$sql, v$session_wait, v$transaction, and so on. Some of these additional views may have relevant information for your specific issue.

We had an additional issue in our case — the databases that sit on our servers run Oracle Applications, so simply tying to a database session provides only half of the picture. You also need to be able to pull information from the fnd_concurrent_requests table to be able to see if the OS process may be generated from a Concurrent Request within Oracle Apps.

Attached to this post (see bottom for link) is a general diagram that the team came up with for relating our tables together, taking RAC into consideration. Based on these relationships, we began to piece together a single SQL statement that typically runs in a few seconds that will give us a snapshot view of all database sessions and how they can be related back to the OS and also to Oracle Applications:

select distinct
        s.inst_id,
        s.status,
        s.username,
        s.sid,
        s.serial#,
        s.schemaname,
        s.osuser,
        f.user_name,
        p.spid,
        f.request_id,
        s.process,
        s.module,
        s.action,
        s.machine,
        s.program,
        u.value || '/' || lower(n.value) || '_ora_' || p.spid || '.trc' Trace_File,
        t.sql_text
        --
from    gv$session s,
        gv$process p,
        gv$sql t,
        (select u.user_name, r.* from apps.fnd_concurrent_requests r, apps.fnd_user u where r.requested_by = u.user_id and phase_code = 'R') f,
        (select inst_id, value from gv$parameter where name = 'user_dump_dest') u,
        (select inst_id, value from gv$parameter where name = 'instance_name') n
        --
where   s.paddr       =  p.addr
and     s.inst_id     =  p.inst_id
and     s.sql_address =  t.address(+)
and     s.inst_id     =  t.inst_id(+)
and     p.spid        =  f.oracle_process_id(+)
and     s.inst_id     =  u.inst_id
and     s.inst_id     =  n.inst_id
;

This query shown above is now our “base-case” from which we begin to build additional queries to get into the nuts and bolts. This provides just enough information to give us a good peek at the database and dive into the sessions in more detail using additional views.

Note in the from clause that we put a sub-select on the fnd_concurrent_requests table to pull information about the user and the concurrent requests for any jobs that are marked as ‘Running’ in the Concurrent Managers. This sub-select is then outer-joined to the gv$process to get all database sessions and include information about the Concurrent Request if it is available.

We put additional items into the query based on how frequently the users or server admins might ask for that information. For example, we have built in the location of a trace file if one exists. Users ask for this information quite frequently, so it helps to have the location at the tips of your fingers (literally) so that you can copy it, tkprof it, etc. It is not necessary to the basis of our SQL query, but the information is very useful and takes so little time to retrieve now.

I hope you can find this information as useful as we have. Enjoy!

os_db_app_proc_joins1.txt