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 /
 
Search WWH ::




Custom Search