It surprises many people to discover that a connection is not synonymous with a session. In most people’s eyes, they are the same, but the reality is they do not have to be. A connection may have zero, one, or more sessions established on it.
Each session is separate and independent, even though they all share the same physical connection to the database. A commit in one session does not affect any other session on that connection. In fact, each session using that connection could use different user identities!
In Oracle, a connection is simply a physical circuit between your client process and the database instance—a network connection, most commonly. The connection may be to a dedicated server process or to a dispatcher.
As previously stated, a connection may have zero or more sessions, meaning that a connection may exist with no corresponding sessions. Additionally, a session may or may not have a connection. Using advanced Oracle Net features such as connection pooling, a physical connection may be dropped by a client, leaving the session intact (but idle).
When the client wants to perform some operation in that session, it would reestablish the physical connection. Let’s define these terms in more detail:
•\ Connection: A connection is a physical path from a client to an Oracle instance. A connection is established either over a network or over an IPC mechanism. A connection is typically between a client process and either a dedicated server or a dispatcher. However, using Oracle’s Connection Manager (CMAN), a connection may be between a client and CMAN, and CMAN and the database. Coverage of CMAN is beyond the scope of this book, but Oracle Database Net Services Administrator’s Guide (freely available from http://otn.oracle.com) covers it in some detail.
•\ Session: A session is a logical entity that exists in the instance. It is your session state, or a collection of data structures in memory that represents your unique session. It is what would come first to most people’s minds when thinking of a database connection. It is your session on the server, where you execute SQL, commit transactions, and run stored procedures.
We can use SQL*Plus to see connections and sessions in action and also to recognize that it could be a very common thing indeed for a connection to have more than one session.
We’ll simply use the AUTOTRACE command and discover that we have two sessions! Over a single connection, using a single process, we’ll establish two sessions. Here is the first:
$ sqlplus eoda/foo@PDB1
SQL> select username, sid, serial#, server, paddr, status from v$session
Now, that shows right now that we have one session: a single dedicated server–connected session. The PADDR column is the address of our sole dedicated server process. Next, we turn on AUTOTRACE to see the statistics of statements we execute in SQL*Plus:
SQL> set autotrace on statistics
SQL> select username, sid, serial#, server, paddr, status from v$session
SQL> set autotrace off
In doing so, we now have two sessions, but both are using the same single dedicated server process, as evidenced by them both having the same PADDR value. We can confirm in the operating system that no new processes were created and that we are using a single process—a single connection—for both sessions.
Note that one of the sessions (the original session) has a status of ACTIVE. That makes sense: it is running the query to show this information, so of course it is active. But that INACTIVE session—what is that one for? That is the AUTOTRACE session. Its job is to watch our real session and report on what it does.
When we enable AUTOTRACE in SQLPlus, SQLPlus will perform the following actions when we execute DML operations (INSERT, UPDATE, DELETE, SELECT, and MERGE):
\ 1.\ It will create a new session using the current connection, if the secondary session does not already exist.
\ 2.\ It will ask this new session to query the V$SESSTAT view to remember the initial statistics values for the session in which we will run the DML.
\ 3.\ It will run the DML operation in the original session.
\ 4.\ Upon completion of that DML statement, SQL*Plus will request the other session to query V$SESSTAT again and produce the report displayed previously showing the difference in the statistics for the session that executed the DML.
If you turn off AUTOTRACE, SQLPlus will terminate this additional session, and you will no longer see it in V$SESSION. Why does SQLPlus do this trick?
The answer is fairly straightforward. SQLPlus does it for the same reason that we used a second SQLPlus session in Chapter 4 to monitor memory and temporary space usage: if we had used a single session to monitor memory usage, we would have been using memory to do the monitoring.
By observing the statistics in a single session, we necessarily would change those statistics. If SQL*Plus used a single session to report on the number of I/ Os performed, how many bytes were transferred over the network, and how many sorts happened, then the queries used to find these details would be adding to the statistics themselves.
They could be sorting, performing I/O, transferring data over the network (one would assume they would), and so on. Hence, we need to use another session to measure correctly.
So far, we’ve seen a connection with one or two sessions. Now we’d like to use SQLPlus to see a connection with no session. That one is pretty easy. In the same SQLPlus window used in the previous example, simply type the misleadingly named command, DISCONNECT:
SQL> disconnect
Technically, that command should be called DESTROY_ALL_SESSIONS instead of DISCONNECT, since we haven’t really disconnected physically.
Note The true disconnect in SQL*Plus is “exit,” as you would have to exit to completely destroy the connection.