ORACLE參數max_shared_servers空值與零的差別

ORACLE資料庫中的參數max_shared_servers，這是一個DBA很熟悉的參數，但是這個參數max_shared_servers為空值與為0有差別嗎？這個細節可能很多人都沒有注意過。如下所示：

檢查一下參數shared_servers， 這個參數不能為0，否則下面測試，連接配接資料庫就會報“ORA-12520：TNS： 監聽程式無法為請求的伺服器類型找到可以用的處理程式”錯誤。如果shared_servers為0，将其設定為1（也請留意參數shared_server_sessions影響測試結果）

此時檢查shared server process的數量，你會發現隻有一個shared server 程序，如下所示

我們使用SQL Developer或PL/SQL Developer使用共享伺服器模式連接配接到資料庫，開啟多個會話，每個會話執行一個類似下面的SQL語句，邊開啟新的會話，邊檢查shared server process的數量，如下所示，你會看到shared server process的數量會一直增加。

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

<a href="http://images2015.cnblogs.com/blog/73542/201608/73542-20160831221714558-54563498.png"></a>

我們将參數max_shared_servers設定為0，然後對比上面的測試來看看

<a href="http://images2015.cnblogs.com/blog/73542/201608/73542-20160831221715574-2112633459.png"></a>

此時開啟多個共享伺服器連接配接模式的會話執行一些UPDATE語句，你會發現shared server process的數量不會變化。如下所示。

如果你每個會話都執行同一個UPDATE語句， 還會遇到下面錯誤，當然max_shared_servers為空值的情況是不會遇到這種情況的。

<a href="http://images2015.cnblogs.com/blog/73542/201608/73542-20160831221716590-1054394527.png"></a>

可以使用下面SQL語句将max_shared_servers參數重置為空值。不過需要重新開機執行個體

關于這個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.