Oracle shared server配置

接触Oracle这么些年来，看到Oracle不少，起码也有几百个了吧，但数据库s是shared server配置只有一个。所以也一直没有好好研究它。OCM考试大纲中有Configure the database instance to support shared server connections 一栏，那今天就来配置一下Oracle shared server。



由上图可知，shared server进程主要通过disipatch进程进行派发，此进程起到了类似中间件的作用。采用shared server模式，可以最大程度的减少主机用户进程数（也就意味着shared server模式比dedicated sever模式更加节约操作系统资源）。以下为shared server配置过程（从dedicated sever转变成shared server）。
1、配置dispatch进程数，协议为TCP
SQL> alter system set dispatchers='(PROTOCOL=TCP) (DISPATCHERS=20)';     

System altered.
2、配置最大dispatch进程数，需要注意的是如果dispatchers参数配置中大于30，如dispatchers='(PROTOCOL=TCP) (DISPATCHERS=40)'，将覆盖max_dispatchers=30
SQL> alter system set max_dispatchers=30 ;

System altered.
3、设置Oracle启动时最小的shared server进程数，需要注意的是不要将此参数设置太高。
SQL> alter system set shared_servers=5;

System altered.
4、设置Oracle允许最大shared server session数。需要注意的是当设置此参数之后不能大于参数sessions。如果不设置此参数，Oracle将会根据空闲槽（free slot）创建shared server session数。

SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
sessions                             integer     500
shared_server_sessions               integer
SQL> alter system set shared_server_sessions=400;

System altered.

当配置完成shared server之后，可以看到操作系统中fork出20个dispatch进程
[ora10g@mcprod admin]$ ps -ef|grep ora_d|grep -v grep|grep -v  ora_dbw
ora10g   13915     1  0 18:03 ?        00:00:00 ora_d001_mcstar
ora10g   13917     1  0 18:03 ?        00:00:00 ora_d002_mcstar
ora10g   13919     1  0 18:03 ?        00:00:00 ora_d003_mcstar
ora10g   13921     1  0 18:03 ?        00:00:00 ora_d004_mcstar
ora10g   13923     1  0 18:03 ?        00:00:00 ora_d005_mcstar
ora10g   13925     1  0 18:03 ?        00:00:00 ora_d006_mcstar
ora10g   13927     1  0 18:03 ?        00:00:00 ora_d007_mcstar
ora10g   13929     1  0 18:03 ?        00:00:00 ora_d008_mcstar
ora10g   13931     1  0 18:03 ?        00:00:00 ora_d009_mcstar
ora10g   13933     1  0 18:03 ?        00:00:00 ora_d010_mcstar
ora10g   13935     1  0 18:03 ?        00:00:00 ora_d011_mcstar
ora10g   13937     1  0 18:03 ?        00:00:00 ora_d012_mcstar
ora10g   13939     1  0 18:03 ?        00:00:00 ora_d013_mcstar
ora10g   13941     1  0 18:03 ?        00:00:00 ora_d014_mcstar
ora10g   13943     1  0 18:03 ?        00:00:00 ora_d015_mcstar
ora10g   13945     1  0 18:03 ?        00:00:00 ora_d016_mcstar
ora10g   13947     1  0 18:03 ?        00:00:00 ora_d017_mcstar
ora10g   13968     1  0 18:03 ?        00:00:00 ora_d018_mcstar
ora10g   13982     1  0 18:03 ?        00:00:00 ora_d019_mcstar
ora10g   17123     1  0 Apr03 ?        00:00:00 ora_d000_mcstar
操作系统fork出5个shared server进程
[ora10g@mcprod admin]$  ps -ef|grep ora_s|grep -v ora_smon_mcstar
ora10g    3097     1  0 18:22 ?        00:00:00 ora_s000_mcstar
ora10g    7909     1  0 18:24 ?        00:00:00 ora_s001_mcstar
ora10g    7911     1  0 18:24 ?        00:00:00 ora_s002_mcstar
ora10g    7913     1  0 18:24 ?        00:00:00 ora_s003_mcstar
ora10g    7915     1  0 18:24 ?        00:00:00 ora_s004_mcstar
查看监听service可以看dispatch状态
[ora10g@mcprod admin]$ lsnrctl services

LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 06-APR-2011 18:24:53

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.4.163)(PORT=1521)))
Services Summary...
Service "mcstar" has 1 instance(s).
  Instance "mcstar", status READY, has 21 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:17 refused:0 state:ready
         LOCAL SERVER
      "D018" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: mcprod, pid: 7086>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mcprod)(PORT=30802))
      "D017" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: mcprod, pid: 7084>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mcprod)(PORT=61930))
      "D016" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: mcprod, pid: 7082>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mcprod)(PORT=18529))
      "D015" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: mcprod, pid: 7080>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mcprod)(PORT=61294))
      "D014" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: mcprod, pid: 7078>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mcprod)(PORT=8452))
      "D013" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: mcprod, pid: 7076>
         (ADDRESS=(PROTOCOL=tcp)(HOST=mcprod)(PORT=7950))
。。。
在共享模式下，如果local naming，tnsnames.ora也可以配置成专用模式，需要注意的是如果使用参数DEDICATED，Oracle将单独派生进程进行客户端处理，也就是专用模式。
mcstar =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.163)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (service_name = mcstar2)
    )
  )
如果要使用shared server模式，需要将tnsnames.ora配置成
mcstar =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.163)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = shared)
      (sid = mcstar)
    )
  )
需要注意的是配置成shared server模式之后，我们建议将larger pool加大，因为在shared server模式下，配置larger pool之后，UGA将保存在larger pool下，这样可以分担shared pool部分压力。此外配置成shared pool之后，尤其当数据库出现性能问题时，如下视图将变得十分重要：v$shared_sevrver,v$circuit,v$dispatcher,v$queue。