Connection: A physical path between the client process and the server process
Session: A logical entity of a database, built by client processes and server processes, as a channel for communication between 2 processes
One, dedicated server connection
1. On Linux, you can run the client and server processes on the same machine, and you can clearly see this parent/child process relationship
System@prod> Select A.spid dedicated_server,b.process clientpid
2 from v$process a,v$session b
3 Where A.addr=b.paddr and
4 b.sid= (select Sid from V$mystat where rownum=1);
Dedicated_se clientPID
------------ ------------
4606 4557
System@prod>!/bin/ps-ef | grep 4557
Oracle 4557 8686 0 15:25 PTS/3 00:00:00 sqlplus Client process (parent process)
Oracle 4606 4557 0 15:25? 00:00:00 Oracleprod (description= (Local=yes)
(address= (PROTOCOL=BEQ)) Server-side processes (child processes)
That is, the sqlplus process is the parent process->oracleprod is a subprocess, and can clearly see this parent/child process relationship. Who initiates who is the parent process
Description= (Local=yes) indicates that the client process is local
Description= (Local=no) indicates that the client process is remote
2. You can use Sqlplus to look at the actual connection and what the conversation looks like. The Autotrace command is used here and two sessions are found
, we created 2 sessions in a single connection. Different sessions on a single connection can use different user identities
That is, you can create 0, one, multiple sessions on a single connection, and each session is independent.
A session can have a connection or no connection (session will be idle)
[ORACLE@SECDB1 ~]$ sqlplus system/oracle Use System user to enter Sqlplus
System@prod> Select Username,sid,serial#,server,paddr,status from V$session
2 where Username=user;
USERNAME SID serial# SERVER paddr STATUS
------------------------------ ---------- ---------- --------- -------- --------
SYSTEM dedicated329db9dcactive
Just open autotrace to see the statistics of the statements executed in Sqlplus
System@prod> set Autotrace on statistics to open the statistics in the execution plan
System@prod> Select Username,sid,serial#,server,paddr,status from V$session
2 where Username=user;
USERNAME SID serial# SERVER paddr STATUS
------------------------------ ---------- ---------- --------- -------- --------
More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/
SYSTEM 109 183 Dedicated329db9dcinactive Monitoring session
SYSTEM dedicated329db9dcactive Actual session
This 109 110 session uses a 329DB9DC process address, which is a connection that creates 2 sessions
Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
0 consistent gets
0 physical Reads
0 Redo Size
774 Bytes sent via sql*net to client
381 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
2 rows processed
System@prod> set Autotrace off execution plan
When Autotrace is enabled in Sqlplus, if we perform a DML operation (insert update Delete merge)
(1) If a "monitoring session" does not exist, it creates a new session using the current connection (monitoring session 109)
(2) Request this new session query V$sesstat view to remember the initial statistics of the actual conversation.
(3) running DML operations in the original session
(4) After the DML statement is executed, Sqlplus requests the monitoring session to query the V$sesstat view again and generates the previous report, showing that the actual session statistics are only poor.
3. Now use Sqlplus to view a connection without any sessions, type a disconnect command in the Sqlplus window
System@prod> Disconnect closes all sessions
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options
@> Show User
User is "" has no logged-in users
@> Select Username,sid,serial#,server,paddr,status from V$session
2 where Username=user;
Sp2-0640:not connected just shows no session, but the connection is still
Note: Technically, this command should be called distroy_all_session (breaking all sessions) more appropriate than disconnect because we are not really disconnected from the physical connection. If you want to disconnect the physical connection, you should run exit
@> exit
[ORACLE@SECDB1 ~]$
[ORACLE@SECDB1 ~]$ sqlplus/as SYSDBA uses the SYS user to query the session of the System user
Sys@prod> SELECT * from v$session where username= ' SYSTEM '; As you can see, there is no session under this system user
No rows selected
But the PADDR329DB9DC process still exists and corresponds to a physical connection.
Sys@prod> Select Username,program from v$process where addr= ' 329DB9DC ';
USERNAME Program
--------------- ------------------------------------------------
Oracle ORACLE@SECDB1 (TNS v1-v3)
4. Now use the Sqlplus Connect command to create a new session, but still use the original PADDR329DB9DC process connection
@> Conn System/oracle
Connected.
System@prod>
System@prod> Select Username,sid,serial#,server,paddr,status from V$session
2 where Username=user;
USERNAME SID serial# SERVER paddr STATUS
SYSTEM dedicated329db9dcactive
You can note that PADDR329DB9DC still uses the original process connection, but the SID is the new session ID and may either be assigned the original SID or the new SID, depending on whether someone is logged in when we log off, and if our original SID is available.
Note: The most common is a connection that corresponds to a session, which is what most people see every day
1 Dedicated Server mode UGA in the PGA space allocation
2 in the shared server mode, UGA allocates space in the SGA
Author: 51cto Blog Oracle Little Bastard