oracle共享服务器模式配置

1、required initialization parameters

    -- DISPATCHERS

    DISPATCHERS configures dispatcher processes in the shared server architecture.

    -- SHARED_SERVERS

   SHARED_SERVERS specifies the number of server processes that you want to create when an instance is started.

2、optional initialization parameters

  -- MAX_DISPATCHERS

  MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed to be running simultaneously.

  -- MAX_SHARED_SERVERS

  MAX_SHARED_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously.

  -- CIRCUITS

  CIRCUITS specifies the total number of virtual circuits that are available for inbound and outbound network sessions

  -- SHARED_SERVER_SESSIONS

  SHARED_SERVER_SESSIONS specifies the total number of shared server sessions to allow. Setting this parameter enables you to reserve user sessions for dedicated servers.

3、查询以下语句获取session对应的server type（DEDICATED、SHARED）

select t.sid,t.SERVER from v$session t
where t.USERNAME is not null;

4、配置shared server mode

在配置shared server mode之前，先了解几个参数：

PROCESSES

specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value ofPROCESSES, you should evaluate whether to adjust the values of those derived parameters.

说白了，就是oracle最大允许的连接数

MAX_DISPATCHERS

MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed to be running simultaneously.

If MAX_DISPATCHERS is specified, then it should be greater than or equal to the number of dispatchers specified by theDISPATCHERS parameter and less than the number of processes specified by thePROCESSES parameter.

最多可以有多少个DISPATCHERS进程同时运行

DISPATCHERS

DISPATCHERS configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent, case-insensitive manner. For example:

DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)'

设置当前的DISPATCHERS进程数

MAX_SHARED_SERVERS

MAX_SHARED_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.

最多可以有多少个SHARED SERVERS同时运行

SHARED_SERVERS

SHARED_SERVERS specifies the number of server processes that you want to create when an instance is started. If system load decreases, then this minimum number of servers is maintained.

Therefore, you should take care not to set SHARED_SERVERS too high at system startup.

该参数设置初始的shared servers, 同时当数据库负荷降低时, oracle会自动调整shared servers为该参数值。

---下面开始配置---

SQL> select * from v$version;  --数据库版本

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150  --最大进程数

SQL> alter system set max_dispatchers=20;  --设置最大的DISPATCHER进程数

System altered.

SQL> show parameter max_dispatchers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dispatchers                      integer     20

SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=10)';  --设置当前dispatcher进程数

System altered.

SQL> show parameter dispatchers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (protocol=tcp)(dispatchers=10)
max_dispatchers                      integer     20

SQL> alter system set max_shared_servers=40;  --设置最大的shared server数目

System altered.

SQL> show parameter max_shared_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     40

SQL> alter system set shared_servers=20;  --设置当前shared server的数目

System altered.

SQL> show parameter shared_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer     40
shared_servers                       integer     20

下面通过v$process视图查看oracle分配的进程情况(dispatcher和shared server process)。

select * from v$process t
where t.BACKGROUND is null
order by t.PNAME asc;

5、验证设置

(1) Verify that the dispatcher has registered with the listener when the instance was started  by issuing:

shell> lsnrctl services

(2) Verify that you are connected using shared servers by making a single connection then query V$CIRCUIT view to show one entry per shared server connection

6、配置客户端的服务器类型

编辑$ORACLE_HOME/network/admin/tnsnames.ora文件。

（1）配置客户端使用dedicated server

DEDICATED_DEMODB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod.cuug)
      (SERVER = DEDICATED)
    )
  )

注意：a. If the database is configured for shared server and a particular client requires a dedicated server, you can configure the client to use a dedicated server in the following way:

DEDICATED_DEMODB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod.cuug)
      (SERVER = DEDICATED)
    )
  )

（2）配置客户端使用shared server

SHARED_DEMODB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod.cuug)
      (SERVER = SHARED)
    )
  )

注意：a. If shared server is configured and a client connection request arrives when no dispatchers are registered, the request is processed by a dedicated server process.

          b. If you want a particular client always to use a dispatcher, configure (SERVER=SHARED) in the CONNECT_DATA section of the connect descriptor. For example:

  SHARED_DEMODB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod.cuug)
      (SERVER = SHARED)
    )
  )

          c. If a dispatcher is not available, the client connection request is rejected.

7、When to use dedicated server

     a. Submit batch jobs (it is expected that there will be little or no idle time)

     b. Connecting as sysdba to startup, shutdown, or perform recovery

     c. In 3-tier system, dedicated server is better.