Database Reference
In-Depth Information
Let's log in using a shared server and in that session query:
EODA@ORA12CR1> select a.username, a.sid, a.serial#, a.server,
2 a.paddr, a.status, b.program
3 from v$session a left join v$process b
4 on (a.paddr = b.addr)
5 where a.username = 'EODA'
6 /
USERNAME SID SERIAL# SERVER PADDR STATUS PROGRAM
--------- ------ -------- --------- ---------------- -------- ---------------------
EODA 485 1105 SHARED 0000000071C4BE68 ACTIVE oracle@cs-xvm2 (S004)
Our shared server connection is associated with a process—the
PADDR
is there and we can join to
V$PROCESS
to
pick up the name of this process. In this case, we see it is a shared server, as identified by the text
S004
.
However, if we use another SQL*Plus window to query this same bit of information, while leaving our shared
server session idle, we see something like this:
$ sqlplus / as sysdba
SYS@ORA12CR1> select a.username, a.sid, a.serial#, a.server,
2 a.paddr, a.status, b.program
3 from v$session a left join v$process b
4 on (a.paddr = b.addr)
5 where a.username = 'EODA'
6 /
USERNAME SID SERIAL# SERVER PADDR STATUS PROGRAM
--------- ------ -------- --------- ---------------- -------- ---------------------
EODA 485 1105 NONE 0000000071C46788 INACTIVE oracle@cs-xvm2 (D000)
Notice that our
PADDR
is different and the name of the process we are associated with has also changed. Our idle
shared server connection is now associated with a dispatcher,
D000
. Hence we have yet another method for observing
multiple sessions pointing to a single process. A dispatcher could have hundreds, or even thousands, of sessions
pointing to it.
An interesting attribute of shared server connections is that the shared server process we use can change from
call to call. If I were the only one using this system (as I am for these tests), running that query over and over as
EODA
would tend to produce the same
PADDR
of
0000000071C4BE68
over and over. However, if I were to open more shared
server connections and start to use those shared server connections in other sessions, then I might notice that the
shared server I use varies.
Consider this example. I'll query my current session information, showing the shared server I'm using. Then in
another shared server session, I'll perform a long-running operation (i.e., I'll monopolize that shared server). When I
ask the database what shared server I'm using again, I'll (in my current session) most likely see a different one
(if the original one is off servicing the other session). In the following example, the code in bold represents a second
SQL*Plus session that was connected via a shared server:
EODA@ORA12CR1> select a.username, a.sid, a.serial#, a.server,
2 a.paddr, a.status, b.program
3 from v$session a left join v$process b
4 on (a.paddr = b.addr)
5 where a.username = 'EODA'
6 /