Oracle数据库连接共享/专用模式

专用服务器模式

Oracle专用服务器模式（Dedicated Server Mode）是Oracle一种特殊的进程结构。在专用服务器模式中，用户进程运行在客户端的机器，专用服务器进程运行在数据库服务器端的机器上。用户进行和专有服务器进程是两种不同类型的进程。当客户端发生连接数据库请求的时候，监听器把客户端（用户进程）连接到专有服务器进程中。即使这个服务器进程是很空闲，这个专用服务器进程也只为这个用户进程服务。用户进程和专用服务器进程是一一对应的关系。

 

共享服务器模式（Shared Server Mode）

共享服务器用到的参数：

shared_servers=3

max_shared_servers=39

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)(list=vm)(pool=on)(disp=5)(con=33)(sess=224)'

max_dispatchers=19

 1.查看是否启用共享服务器模式

SQL> show parameter shared ;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer     
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 30198988
shared_pool_size                     big integer 0
shared_server_sessions               integer     
shared_servers                       integer     1

显示调度配置情况

SQL> show parameter disp;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_plan_display            string      AUTO
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=orclXDB)
max_dispatchers                      integer  

2.查看共享服务器进程

SQL> select * from V$shared_Server ;
NAME PADDR            STATUS             MESSAGES      BYTES     BREAKS CIRCUIT                IDLE       BUSY     IN_NET    OUT_NET   REQUESTS
---- ---------------- ---------------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ----------
S000 000007FF5C7A99D0 WAIT(COMMON)              0          0          0 00                 16924434          0          0          0          0

 3.查看调度调度进程

SQL> select * from V$dispatcher ;
NAME NETWORK                                                                          PADDR            STATUS           ACCEPT   MESSAGES      BYTES     BREAKS      OWNED    CREATED       IDLE       BUSY   LISTENER  CONF_INDX
---- -------------------------------------------------------------------------------- ---------------- ---------------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=iZ25j95ckhbZ)(PORT=49602))                          000007FF5C7A8960 WAIT             YES             0          0          0          0          0   16933200          0          0          0

 4.增加共享服务器进程数量

alter system set shared_servers = 8 scope=both;

5.关闭指定的调度进程（先查询到name，完了执行关闭）

SQL> select * from v$dispatcher;
NAME NETWORK                                                                          PADDR            STATUS           ACCEPT   MESSAGES      BYTES     BREAKS      OWNED    CREATED       IDLE       BUSY   LISTENER  CONF_INDX
---- -------------------------------------------------------------------------------- ---------------- ---------------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=iZ25j95ckhbZ)(PORT=49602))   
SQL> alter system shutdown immediate 'D000';

 6.客户端如果需要以专用模式连接

修改配置文件tnsnames.ora，添加SERVER = DEDICATED

 

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 7.查询当前登陆客户连接模式

v$mystat是指自己session的stat

SQL> select server from V$session where sid =(select distinct sid from v$mystat );
SERVER
---------
DEDICATED