Oracle Processes - Expert Oracle Database Architecture - page 176

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:
EODA@ORA12CR1> select username, sid, serial#, server, paddr, status
2 from v$session
3 where username = USER
4 /
USERNAME SID SERIAL# SERVER PADDR STATUS
--------------- ---------- ---------- --------- ---------------- --------
EODA 10 15 DEDICATED 00000000727FE9B0 ACTIVE
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:
EODA@ORA12CR1> set autotrace on statistics
EODA@ORA12CR1> select username, sid, serial#, server, paddr, status
2 from v$session
3 where username = USER
4 /
USERNAME SID SERIAL# SERVER PADDR STATUS
--------------- ---------- ---------- --------- ---------------- --------
EODA 10 15 DEDICATED 00000000727FE9B0 ACTIVE
EODA 21 721 DEDICATED 00000000727FE9B0 INACTIVE
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1004 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
EODA@ORA12CR1> 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.