Oracle shared server 配置詳解

官方文件的位置：Administrator Guide --> Managing Processes -->openConfiguring Oracle Database for Shared Server
一、Shared Server配置的相關引數
使用shared server 可以減少對db server 的CPU和記憶體資源的消耗。
和Shared Server相關的引數主要有兩個：shared_server和dispatchers。後一個引數用來設定分發器，可以針對不同的協議設定分發器，比如這樣
DISPATCHERS = “(PRO=TCP)(DIS=3)(PRO=IPC)(DIS=2)”
複雜一點的，帶有連線池的
DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS=1)(POOL=on)(TICK=1)(CONNECTIONS=500)(SESSIONS=1000)"
Shared_server用來定義系統啟動時啟動的Shared server數量，如果是0，就取消了Shared mode。

除了這兩個主要的引數，還有幾個其他引數。

MAX_DISPATCHERS:設定最大的排程器數
SHARED_SERVER_SESSIONS:限制ＰＭＯＮ能夠自動建立的SHARED SERVER的最大session數。注意shared server processes的數量也收 processes引數的限制。
MAX_SHARED_SERVERS：設定最大的SHARED_SERVER數。如果未給此引數附值，那麼SHARED_SERVER數庫無限制。

一般情況下為10個shared server process 建立一個dispatcher，具體情況根據系統來定。

相關檢視：
V$SHARED_SERVER
V$DISPATCHER
V$SHARED_SERVER_MONITOR
V$SESSION

二、如何配置共享伺服器
1)配置共享伺服器引數
alter system set shared_servers=10 scope=both;
alter system set max_dispatchers=7 scope=both;
ALTER SYSTEM SET DISPATCHERS = '(PROT=tcp)(DISP=3)', '(PROT=tcps)(DISP=3)';
注意：這裡max_dispathers 的數量如果比所有型別dispatchers數量總數少，命令也能成功，但是Oracle會在alert中給出警告。

在設定完shared_servers引數後，Oracle會通過PMON自動建立一些shared_server processes ，通過作業系統命令檢視。
ps -ef|grep ora_|grep s00|grep testdb|grep -v

SYS@kyle>!ps -ef|grep ora_|grep s00|grep testdb|grep -v grep
oracle   15515     1  0 18:34 ?        00:00:05 ora_s000_testdb
oracle   15517     1  0 18:34 ?        00:00:00 ora_s001_testdb
oracle   15519     1  0 18:34 ?        00:00:00 ora_s002_testdb
oracle   15521     1  0 18:34 ?        00:00:00 ora_s003_testdb
oracle   15523     1  0 18:34 ?        00:00:00 ora_s004_testdb
oracle   15525     1  0 18:34 ?        00:00:00 ora_s005_testdb
oracle   15527     1  0 18:34 ?        00:00:00 ora_s006_testdb
oracle   15529     1  0 18:34 ?        00:00:00 ora_s007_testdb
oracle   15531     1  0 18:34 ?        00:00:00 ora_s008_testdb
oracle   15533     1  0 18:34 ?        00:00:03 ora_s009_testdb
檢視建立的dispatchers
SYS@kyle>col name for a10
SYS@kyle>col network for a60
SYS@kyle>SELECT NAME, NETWORK FROM V$DISPATCHER;

NAME       NETWORK
---------- ------------------------------------------------------------
D000       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=11733))
D001       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=48313))
D002       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=48142))
D003       (ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=14360))
D004       (ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=54000))
D005       (ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=42408))

也可以通過下面命令單獨關閉某個Dispatcher

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';


2）配置tnsnames.ora檔案
dedicated =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.99)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hong)
      (server = shared)
    )
)
3)使用共享模式連線資料庫
 sqlplus kyle/kyle@testdb_shared
4)檢視shared 會話
set line 120
col server for a20
col program for a30
col username for a15
select server,program,username,status from v$session
where server in ('SHARED','NONE')
order by program;
還可以通過作業系統命令，檢視是哪個dispatcher進行的排程
netstat -anp|grep ora_
可以同過這個命令查詢是哪個dispatcher排程了會話
tcp        0      0 127.0.0.1:13279             127.0.0.1:1522              ESTABLISHED 18894/ora_lreg_db12
tcp        0      0 :::63244                    :::*                        LISTEN      15503/ora_d000_test
tcp        0      0 :::59441                    :::*                        LISTEN      15509/ora_d003_test
tcp        0      0 :::16465                    :::*                        LISTEN      15507/ora_d002_test
tcp        0      0 :::16081                    :::*                        LISTEN      18902/ora_d000_db12
tcp        0      0 :::57621                    :::*                        LISTEN      15511/ora_d004_test
tcp        0      0 :::17090                    :::*                        LISTEN      15505/ora_d001_test
tcp        0      0 :::42663                    :::*                        LISTEN      15513/ora_d005_test
tcp        0      0 ::ffff:127.0.0.1:1521       ::ffff:127.0.0.1:15078      ESTABLISHED 15503/ora_d000_test
tcp        0      0 ::ffff:10.68.32.199:1521    ::ffff:10.68.32.199:59961   ESTABLISHED 15507/ora_d002_test
tcp        0      0 ::ffff:127.0.0.1:1521       ::ffff:127.0.0.1:15072      ESTABLISHED 15505/ora_d001_test
tcp        0      0 ::ffff:127.0.0.1:1521       ::ffff:127.0.0.1:15034      ESTABLISHED 15507/ora_d002_test
tcp        0      0 ::ffff:127.0.0.1:1521       ::ffff:127.0.0.1:15021      ESTABLISHED 15503/ora_d000_test
tcp        0      0 ::1:19698                   ::1:1521                    ESTABLISHED 15471/ora_pmon_test
tcp        0      0 ::ffff:127.0.0.1:1521       ::ffff:127.0.0.1:15161      ESTABLISHED 15505/ora_d001_test
tcp        0      0 ::ffff:127.0.0.1:1521       ::ffff:127.0.0.1:15024      ESTABLISHED 15505/ora_d001_test



三、如何禁用shared server
ALTER SYSTEM SET DISPATCHERS = '';
or
ALTER SYSTEM SET SHARED_SERVERS=0 SCOPE=BOTH;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26723566/viewspace-2137139/，如需轉載，請註明出處，否則將追究法律責任。