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。