connection和session的關係

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.

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 in the server, where you execute SQL, commit transactions, and run stored procedures.

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.

SQL> conn seagull/aaaa
Connected.
SQL> select username||' '||sid||' '||serial#||' '||server||' '||paddr||' '||status from v$session where username='SEAGULL';

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL 140 61 DEDICATED 2A220948 ACTIVE

SQL> set autotrace on statistics
SQL> select username||' '||sid||' '||serial#||' '||server||' '||paddr||' '||status from v$session where username='SEAGULL';

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL 140 61 DEDICATED 2A220948 ACTIVE
SEAGULL 142 231 DEDICATED 2A220948 INACTIVE

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL>

從例子可以看到，程式(paddr=2A220948)對應了兩個session(140,142)，即我們透過一個dedicated server擁有了兩個sessoin，其實那個inactive的session(142)就是autotrace session,用來watch我們真正的sessoin的，可以知道autotrace在我們發出insert,delete,update,select,merge時會作出一下動作：

1)用當前的connection新建1個new session,當然，如果建立後就不用再建立了

2)然後new session會讀取original session的v$sessstat檢視並記錄統計資訊

3)接著在original session中執行DML

4)執行完畢後，new session會再次讀取original session的v$sessstat資訊，並且把和之前的差別統計出來，顯示給使用者看.

如果關閉autotrace,那麼new session會消失，如下：

SQL> set autotrace off
SQL> select username||' '||sid||' '||serial#||' '||server||' '||paddr||' '||status from v$session where username='SEAGULL';

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL 140 61 DEDICATED 2A220948 ACTIVE

SQL>

此時在sqlplus中執行disconnect操作：

SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

在另外的sqlplus登入介面裡查詢v$session

SQL> select * from v$session where username='SEAGULL';

now rows return

SQL> select addr,pid,spid,terminal,username from v$process where addr='2A220948';

ADDR PID SPID TERMINAL USERNAME
-------- ---------- ------------ ------------------------------ ---------------
2A220948 21 13132 pts/0 oracle10

發現此時session(sid=140)已經沒有了，但用之前的paddr='2A220948'還能查出資訊，表明connection還在，表明1個connection可以對應0個session.

再次原來的sqlplus裡面執行connect操作:

SQL> connect seagull/aaaa
Connected.

查詢v$session

SQL> select username||' '||sid||' '||serial#||' '||server||' '||paddr||' '||status from v$session where username='SEAGULL';

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL 146 103 DEDICATED 2A220948 ACTIVE

發現1個new session在原來的connection(paddr=2A220948)基礎上又被建立了。