We have, however, closed all of our sessions. If we open another session using some other user account and query (replacing EODA with your account name, of course):
$ sqlplus / as sysdba
SQL> select * from v$session where username = ‘EODA’; no rows selected
We can see that we have no sessions—but we still have a process, a physical connection (using the previous ADDR value):
SQL> select username, program from v$process where addr = hextoraw( ‘000000007850D488’ );
So, here we have a connection with no sessions associated with it. We can use the also misnamed SQLPlus CONNECT command to create a new session in this existing process (the CONNECT command might be better named CREATE_SESSION). Using the SQLPlus instance we disconnected in, we’ll execute the following:
SQL> connect eoda/foo@PDB1
SQL> select username, sid, serial#, server, paddr, status from v$session
Notice that we have the same PADDR as before, so we are using the same physical connection, but we have (potentially) a different SID. I say potentially because we could get assigned the same SID—it just depends on whether other people logged in while we were logged out and whether the original SID we had was available.
Note On Windows or other thread-based operating systems, you might see different results—the process address may change since you are connected to a threaded process, not just a single-purpose process as you would on UNIX/Linux.
So far, these tests were performed using a dedicated server connection, so the PADDR was the process address of our dedicated server process. What happens if we use a shared server? I’ll configure my database to use a shared server as follows:
SQL> alter system set shared_servers=4;
SQL> alter system set max_dispatchers=8 scope=spfile;
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 S003.
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
SQL> select a.username, a.sid, a.serial#, a.server,a.paddr, a.status, b.program
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, D001. 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 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). The following example represents a second SQL*Plus session that was connected via a shared server:
SQL> select a.username, a.sid, a.serial#, a.server,a.paddr, a.status, b.program
From another terminal session, connect to the database as the user SCOTT (the shared text in the following connection string maps to an entry in the tnsnames.ora file that instructs SQL*Plus to connect via a shared server connection). For reference, here’s the shared entry in my tnsnames.ora file:
Note You need to use an account that has execute privileges on the DBMS_LOCK package. I granted my demo account SCOTT execute privileges on the DBMS_LOCK package to accomplish this: SQL> grant execute on dbms_lock to scott;
Notice that the first time I queried, I was using S003 as the shared server. Then, in another session (as SCOTT), I executed a long-running statement that monopolized the shared server, which just happened to be S003 this time.
The first nonbusy shared server is the one that gets assigned to do the work, and in this case no one else was asking to use the S003 shared server, so the DBMS_LOCK command took it. When I queried again in the first SQL*Plus session, I got assigned to another shared server process, S000, since the S003 shared server was busy.
It is interesting to note that the parse of a query (returns no rows yet) could be processed by shared server S000, the fetch of the first row by S001, the fetch of the second row by S002, and the closing of the cursor by S003. That is, an individual statement might be processed bit by bit by many shared servers.
So, what we have seen in this section is that a connection—a physical pathway from a client to a database instance—may have zero, one, or more sessions established on it. We have seen one use case of that when using SQL*Plus’s AUTOTRACE facility.
Many other tools employ this ability as well. For example, Oracle Forms uses multiple sessions on a single connection to implement its debugging facilities. The n-tier proxy authentication feature of Oracle, used to provide end-to-end identification of users from the browser to the database, makes heavy use of the concept of a single connection with multiple sessions, but each session would use a potentially different user account.
We have seen that sessions can use many processes over time, especially in a shared server environment. Also, if we are using connection pooling with Oracle Net, then our session might not be associated with any process at all; the client would drop the connection after an idle time and reestablish it transparently upon detecting activity.
In short, there is a many-to-many relationship between connections and sessions. However, the most common case, the one most of us see day to day, is a one-to-one relationship between a dedicated server and a single session.