How To See What Shared Server Attend Which Session

Sometimes we have to monitor or see what are doing a user or process that is easy or relatively easy
in the database, but when we see what the process serving that session in the operating system can be complicated if you are using MTS (Multi Thread Server), since a Shared Server (the process that does the job) can server many sessions (running only one session at any given time).

To identify shared server that is running the job of a user or process can execute the statement below.
The query;

 
COLUMN "NAME SERVER" FORMAT A11

SELECT 
    SS.NAME AS "NAME SERVER", 
    S.USERNAME, 
    S.SQL_ADDRESS, 
    S.PROGRAM
FROM  
    V$SHARED_SERVER SS, 
    V$CIRCUIT C,          -- We need this view to link Shared_server to session
    V$SESSION S
WHERE 
    S.SADDR    = C.SADDR      AND
    SS.CIRCUIT = C.CIRCUIT
/

The output;

 
NAME SERVER USERNAME        SQL_ADDRESS      PROGRAM
___________ _______________ ________________ __________________
S001        CRMSMS          000007FCBCC48948 JDBC Thin Client
S002        ANTONION        000007FC20FB0090 sqlplusw.exe

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s