【偵聽】V$session.server = none when using Shared Server/MTS Configuration

***
This article is being delivered in Draft form. and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
***

PURPOSE
-------

V$session view showing 'NONE' in server column is not a bug in MTS/Shared Server configuration.

 
SCOPE & APPLICATION
-------------------

This article can be useful for DBA's, Support Engineers to conclude 'NONE' in server column of v$session view
is not a bug in MTS/Shared Server configuration.



Analysis
-----------------------------
 
In MTS/Shared Server configuration when you see value 'NONE' (as shown below), it means there is no task being 
processed by shared server for that session. The server column will infact show status of 'SHARED' if there is 
some task being processed at that particular time by the shared server process for that session.

SQL> select username,server from v$session where program like '%sql%';

USERNAME                       SERVER
------------------------------ ---------
SYS                            DEDICATED
SYSTEM                         SHARED
SCOTT                          NONE

對以上的理解

經過測試發現:當session的status 為active  SERVER為  shared

              當session的status 為active  SERVER為  NONE

測試過程如下:

視窗1

SQL> select username,server from v$session group by username,server;

USERNAME                       SERVER
------------------------------ ---------
                               DEDICATED
SYS                            DEDICATED
SYSTEM                         SHARED

視窗2:

SQL> create table t1 (id number);

Table created.

SQL> 
SQL> 
SQL> select * from t1;

no rows selected

SQL> insert into t1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> begin
  2  for a in 1..100000 loop 
  3  insert into t1 values(2);
  4  commit;
  5  end loop;
  6  end;
  7  
  8  /

同時查詢視窗1

SQL> select username,server from v$session group by username,server;

USERNAME                       SERVER
------------------------------ ---------
                               DEDICATED
SYS                            DEDICATED
SYSTEM                         SHARED

指令碼執行完成後:

SQL> select username,server from v$session group by username,server;

USERNAME                       SERVER
------------------------------ ---------
                               DEDICATED
SYS                            DEDICATED
SYSTEM                         NONE