dispatcher & shared server小結

若要開啟此功能，需要將shared_servers引數設定成大於0的整數(可以動態修改)，即使不設定dispatcher引數，預設也會啟動一個dispatcher程式。

資料庫啟動時候，如果只設定了dispatcher，啟動後shared server同樣會被開啟。

一般來說， 平均一個shared server支援10個連線。

當資料庫中shared server數目小於或等於shared_servers引數值時，則PMON不會終止任何shared server程式，避免了資料庫因壓力的波動而自動調整shared server數目。

SHARED_SERVER_SESSIONS

specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers. This in turn ensures that administrative tasks that require dedicated servers, such as backing up or recovering the database, are not preempted by shared server sessions

原理：

當客戶端程式發起連線請求時，listener先判斷其請求型別，若是請求shared server process，則返回壓力最小的dispatcher程式地址，然後客戶端程式直接與其通訊。

Dispatcher接到請求，將其放到large pool裡的request queue，而後由shared server process處理並返回給response queue，response queue返回給dispatcher

優劣

可以減少OS程式的數量

減少PGA的使用，每個dedicated或者shared server都需使用PGA（後者的UGA存在於SGA中），伺服器程式越少則PGA佔有的越少

提高客戶端連線資料庫的併發能力同時發起資料庫連線的數目

當短連線比較高的時候，連線速度會比dedicated server快

當使用dispatcher程式連線時，不能使用管理員許可權進行如下操作：開關資料庫/介質恢復

效能調優

dispatcher contention

To assess dispatcher performance, query the V$DISPATCHER_RATE view and compare the current values with the maximums.

If the current and average rates are significantly less than the maximums, then consider reducing the number of dispatchers. Conversely, if current and average rates are close to the maximums, then you might need to add more dispatchers.

Adding dispatcher processes: limited by the value of the initialization parameter MAX_DISPATCHERS

Enabling connection poolin: configuring the dispatcher to support more users with connection pooling.

Enabling Session Multiplexing: used by a connection manager process to establish and maintain network sessions from multiple users to individual dispatchers

Shared servers contention

Monitor these statistics occasionally while your application is running by issuing the following SQL statement:

SELECT DECODE(TOTALQ, 0, 'No Requests',

   WAIT/TOTALQ || ' HUNDREDTHS OF SECONDS') "AVERAGE WAIT TIME PER REQUESTS"

  FROM V$QUEUE

 WHERE TYPE = 'COMMON';

This query returns the results of a calculation that show the following:

AVERAGE WAIT TIME PER REQUEST

-----------------------------

.090909 HUNDREDTHS OF SECONDS

From the result, you can tell that a request waits an average of 0.09 hundredths of a second in the queue before processing.

You can also determine how many shared servers are currently running by issuing the following query:

SELECT COUNT(*) "Shared Server Processes"

  FROM V$SHARED_SERVER

 WHERE STATUS != 'QUIT';

The result of this query could look like the following:

Shared Server Processes

-----------------------

10

Example:

Typical This is a typical example of setting the DISPATCHERS initialization parameter.

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"

Example: Forcing the IP Address Used for Dispatchers The following hypothetical example will create two dispatchers that will listen on the specified IP address. The address must be a valid IP address for the host that the instance is on. (The host may be configured with multiple IP addresses.)

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"

Example: Forcing the Port Used by Dispatchers To force the dispatchers to use a specific port as the listening endpoint, add the PORT attribute as follows:

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"

關閉shared server & dispatcher

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

You disable shared server by setting SHARED_SERVERS to 0. You can do this dynamically with the ALTER SYSTEM statement. When you disable shared server, no new clients can connect in shared mode.

To terminate dispatchers once all shared server clients disconnect, enter this statement:

ALTER SYSTEM SET DISPATCHERS = '';

Trouble shooting

Dispatchers Are Not Registering With the Listener When LOCAL_LISTENER is Set Correctly [ID 465888.1]

Applies to:

Oracle Net Services - Version: 10.1.0.2.0 to 11.1.0.6.0

This problem can occur on any platform.

Symptoms

The dispatchers are configured and spawning properly but are not registering with the desired listener. This can be seen in the lsnrctl services output. There are no D00x handlers registered against the listener.

Furthermore, all remote connections are being made in DEDICATED mode.

Changes

It is likely that this database has recently gone from dedicated to shared OR some change has been made to the address of the local listener.

Cause

The listener option in the DISPATCHERS configuration will override both the LOCAL_LISTENER and REMOTE_LISTENER settings.

For Example:

Below is the configuration in the pfile or spfile:

dispatchers="(dispatchers=5)(protocol=tcp) (listener=(address=(protocol=tcp)(port=2703)))"

These dispatchers are configured to register against a listener address running on port 2703. This setting would override the following two pfile settings that are pointing to a different listener address.

LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=TEST)(PORT=11106))"

remote_listener="(ADDRESS=(PROTOCOL=TCP)(HOST=REMOTE)(PORT=11107))"

Solution

In order to register the dispatchers with the LOCAL_LISTENER and REMOTE_LISTENER locations, you need to set the dispatchers without the listener option as shown below:

SQL> alter system set dispatchers=""(dispatchers=5)(protocol=tcp)";

System altered.

Once this change is in effect, the dispatchers will refer to LOCAL_LISTENER and REMOTE_LISTENER for registration.

Dispatchers Are Not Registered With Listener Running On Default Port 1521 [ID 465881.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2.0

This problem can occur on any platform.

Symptoms

•       Database is configured for DISPATCHERS with the following init.ora or spfile parameters:

DISPATCHERS=(protocol=TCP)(dispatchers=2)

SHARED_SERVERS=5

MAX_SHARED_SERVERS = Null value

LOCAL_LISTENER parameter is not set.

•       The operating system command ps -ef shows the dispatcher process is running but the lsnrctl services output does not show the dispatchers registered.

•       Connection through dispatchers is not possible. All connections are dedicated.

Cause

The database(PMON) is not registering the dispatcher information with the local listener.

.

Solution

Set the LOCAL_LISTENER parameter to point to the local listener address:

Add following entry in the tnsnames.ora file on the server .

LOCAL_LIST=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST=)(PORT = 1521))

)

Note: Make sure this tns alias LOCAL_LIST is resolvable on the server, this ensures PMON picksup the entry without problem.

The following command should succeed on the server

$ tnsping LOCAL_LIST

Set the local_listener on the database server

SQL > alter system set LOCAL_LISTENER='LOCAL_LIST' scope=both ;

SQL> alter system register;

Note: In the case of RAC, specify the SID field in alter system statement. The SID value would specify the unique INSTANCE_NAME.

SQL > alter system set LOCAL_LISTENER='LOCAL_LIST' scope=both SID='instance_name' ;

SQL> alter system register;

Alternatively, set LOCAL_LISTENER to the full address of the local listener:

SQL>alter system set LOCAL_LISTENER="(address=(protocol=tcp)(port=1521)(host=yourhost))" scope=both sid='instance_name';

Check the lsnrctl services output, it should show the dispatcher information successfully registered with the listener.

Dispatchers Fail to Register Dynamically Against the Listener [ID 889092.1]

Applies to:

Oracle Net Services - Version: 10.2.0.1.0 to 11.1.0.7

This problem can occur on any platform.

This document applies to versions 10g and newer.

Symptoms

Dispatchers are spawning but failing to register against the listener following this command:

ALTER SYSTEM SET DISPATCHERS='(address=(protocol=tcp)(host=myserver))(dispatchers=2)' scope=both sid='instance_name';

Additionally, the "alter system register" command has no effect.

Lsnrctl services output does not show any dispatchers have been registerered. However, a check of ps -ef | grep D000 shows that dispatcher processes have spawned.

The listener is running on the default port of 1521 and LOCAL_LISTENER and REMOTE_LISTENER are set correctly.

.

Changes

This is a new implementation of shared server. This database has previously been running in dedicated server mode.

Cause

The pfile parameter SHARED_SERVERS had been explicitly set to 0.

The following messages appeared in the alert.log:

WARNING: Shared server clients will not be able to connect because

SHARED_SERVERS is 0.

When SHARED_SERVERS is explictly set to 0, shared server is effectively disabled. It would be expected behavior. that the DISPATCHERS are not operational.

Solution

To implement the solution, please execute the following steps:

alter system set SHARED_SERVERS=5 scope=both sid='instance_name';

Check the output of lsnrctl services. The dispatchers should immediately register against the listener.

Shared Server (MTS) Diagnostics [ID 1005259.6]

The components of the Shared Server database configuration consist of the Dispatchers and the Shared Servers. These components run as separate processes in the operating system (or threads in some operating systems). They interact with each other through the use of a Common Queue (CQ - also known as the Virtual Queue, of which there could be multiple CQs) and individual Dispatcher Queues. Both queues reside in the Shared Global Area (SGA) and are sized automatically by the database itself. Another component of Shared Server is not a process but an abstraction of the user session(more of an owned pointer), called a Virtual Circuit (VC). The communication between the Dispatchers and Shared Servers is primarily done by passing ownership of a Virtual Circuit from one to another.

The Dispatchers are not limited to just the Oracle Net protocol. They also are able to understand FTP, HTTP(S), WebDAV, IIOP, SMTP, and TCP protocols.

Dispatchers: Performance

One perspective for interpreting Dispatcher performance is measuring the wait times in the various queues by querying the view V$QUEUE.

In the above example, the WAIT column is the total amount of time all requests have waited in the particular queue. The TOTALQ column is the total number of requests in a queue since the startup of the database. The AVG WAIT denotes the average wait (in seconds) per queued request.

The row with the TYPE of COMMON represents the Common Queue. The CQ holds all client requests to be processed by the Shared Servers. Please note that V$QUEUE view is not related to the Oracle Streams Advance Queuing feature.

Shared Server Performance

Shared Servers are created by PMON. Upon instance startup, PMON will create them according to the value of the SHARED_SERVERS parameter. If more SHARED_SERVERS are needed, PMON will create them up to MAX_SHARED_SERVERS to meet the need. PMON will terminate idle Shared Servers until the number goes back to SHARED_SERVERS. When measuring the performance of the Shared Servers, it is normal to see the lower numbered Shared Servers to be busier then the higher numbered ones.

In the above example, all the Shared Servers are between 1% and 99% busy. Shared Server S008 is very busy processing a single client request and Shared Server S000 has been busy handling numerous smaller requests. In general, the S000 Shared Server will always be the busiest and could easily be 100% busy all the time. This is by design.

The reason that S003-S005 and S007 are not listed is because the SHARED_SERVER parameter was set to 3 so PMON removed those Shared Servers because they went idle long enough to be removed. The idle interval cannot be set, nor does it need to be as it is more efficient to not have to create a Shared Server. S006 and S008 are not idle so they will exist as long as there is work for them to do.

In the case where there is a gap in the %TIME BUSY, such as is illustrated above where higher numbered Shared Servers S006 and S008 are nearly 100% used. This could be due to some sessions having so much work to do that a Shared Server has been dedicated to that particular session. It is sessions like this that should be found and forced to connect with a Dedicated server processes. Such heavy sessions have enough continuous workload that the service time the Dispatcher adds may slow them down.

The STATUS column of the V$SHARED_SERVER view provides useful information about WAIT status. In particular, the WAIT(ENQ) status tells the DBA that the user is waiting for a lock resource, and in rare cases, acts as an alert for a deadlock situation.

An overview of server creation and termination and high-water mark is available from the V$SHARED_SERVER_MONITOR view.

The MAXIMUM_CONNECTIONS is the value of the maximum number of Virtual Circuits in use at one time.

The MAXIMUM_SESSIONS is the highest number of Shared Server sessions in use at one time since the instance started.

The SERVERS_STARTED and SERVERS_TERMINATED columns maintain a running total of Shared Server process creation and termination by PMON (but do not include the number set in the SHARED_SERVERS parameter).

The SERVERS_HIGHWATER value holds the high-water mark for the Shared Server count since the instance startup.

These statistics are useful indicators to check if SERVERS is set too low or too high. If the SERVERS_STARTED or SERVERS_TERMINATED are zero, this is an indication that too many Shared Servers may have been configured. Similarly, if the values of SERVERS_STARTED and SERVERS_TERMINATED grow quickly, the number for SHARED_SERVERS is likely to be too low and should be set to SERVERS_HIGHWATER + 1 (the "+ 1" is for good measure and has no intrinsic meaning).