ORACLE参数max_shared_servers空值与零的区别

报文来源：潇湘隐者  的 《ORACLE参数max_shared_servers空值与零的区别》

ORACLE数据库中的参数max_shared_servers，这是一个DBA很熟悉的参数，但是这个参数max_shared_servers为空值与为0有区别吗？这个细节可能很多人都没有注意过。如下所示：

SQL> show parameter max_shared_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers                   integer
SQL>

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 23488102
shared_pool_size                     big integer 0
shared_server_sessions               integer     100
shared_servers                       integer     1
SQL>

检查一下参数shared_servers， 这个参数不能为0，否则下面测试，连接数据库就会报“ORA-12520：TNS： 监听程序无法为请求的服务器类型找到可以用的处理程序”错误。如果shared_servers为0，将其设置为1（也请留意参数shared_server_sessions影响测试结果）

SQL> alter system set shared_servers=1 scope=both;

System altered.

SQL>

此时检查shared server process的数量，你会发现只有一个shared server 进程，如下所示

[oracle@DB-Server trace]$ ps -ef | grep ora_s00 | grep -v grep
oracle    6824     1  0 15:35 ?        00:00:02 ora_s000_gsp
[oracle@DB-Server trace]$

--我们使用SQL Developer或PL/SQL Developer使用共享服务器模式连接到数据库，开启多个会话，每个会话执行一个类似下面的SQL语句，边开启新的会话，边检查shared server process的数量，如下所示，你会看到shared --server process的数量会一直增加。

UPDATE TEST SET NAME='Kerry' WHERE ID= 1001;

我们将参数max_shared_servers设置为0，然后对比上面的测试来看看

SQL> alter system set max_shared_servers=0 scope=both;

System altered.

SQL> !
[oracle@DB-Server ~]$  ps -ef | grep ora_s00 | grep -v grep
oracle    6824     1  0 15:35 ?        00:00:02 ora_s000_gsp
[oracle@DB-Server ~]$

此时开启多个共享服务器连接模式的会话执行一些UPDATE语句，你会发现shared server process的数量不会变化。如下所示。

[oracle@DB-Server ~]$ ps -ef | grep ora_s00 | grep -v grep
oracle    8396     1  0 17:21 ?        00:00:00 ora_s000_gsp
[oracle@DB-Server ~]$ ps -ef | grep ora_s00 | grep -v grep
oracle    8396     1  0 17:21 ?        00:00:00 ora_s000_gsp
[oracle@DB-Server ~]$ ps -ef | grep ora_s00 | grep -v grep
oracle    8396     1  0 17:21 ?        00:00:00 ora_s000_gsp
[oracle@DB-Server ~]$ ps -ef | grep ora_s00 | grep -v grep
oracle    8396     1  0 17:21 ?        00:00:00 ora_s000_gsp
[oracle@DB-Server ~]$

如果你每个会话都执行同一个UPDATE语句， 还会遇到下面错误，当然max_shared_servers为空值的情况是不会遇到这种情况的。

UPDATE TEST SET NAME='Kerry' WHERE ID= 1001;

可以使用下面SQL语句将max_shared_servers参数重置为空值。不过需要重启实例

SQL> alter system reset "max_shared_servers" scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

关于这个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.

When you want to reduce the range of shared servers, you can reduce MAX_SHARED_SERVERS before reducing SHARED_SERVERS. If MAX_SHARED_SERVERS is lower than SHARED_SERVERS, then the number of shared servers will not vary but will remain at the constant level specified by SHARED_SERVERS. If MAX_SHARED_SERVERS is not specified, then a shared server process may be spawned as long as the number of free process slots is greater than 1 / 8 the maximum number of processes, or 2 if PROCESSES is less than 24.



另外其它相关参数的描述如下所示，仅供参考。

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.


MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.


SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.


DISPATCHERS: Configures dispatcher processes in the shared server architecture.


MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.


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