一般来说， 平均一个shared server支持10个连接。
当数据库中shared server数目小于或等于shared_servers参数值时，则PMON不会终止任何shared server进程，避免了数据库因压力的波动而自动调整shared server数目。
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
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"
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"
WHERE STATUS != 'QUIT';
The result of this query could look like the following:
Shared Server Processes
Typical This is a typical example of setting the DISPATCHERS initialization parameter.
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.)
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:
关闭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 = '';
Dispatchers Are Not Registering With the Listener When LOCAL_LISTENER is Set Correctly [ID 465888.1]
Oracle Net Services - Version: 10.1.0.2.0 to 188.8.131.52.0
This problem can occur on any platform.
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.
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.
The listener option in the DISPATCHERS configuration will override both the LOCAL_LISTENER and REMOTE_LISTENER settings.
Below is the configuration in the pfile or spfile:
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.
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)";
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]
Oracle Server - Enterprise Edition - Version: 10.2.0.2.0
This problem can occur on any platform.
• Database is configured for DISPATCHERS with the following init.ora or spfile parameters:
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.
The database(PMON) is not registering the dispatcher information with the local listener.
Set the LOCAL_LISTENER parameter to point to the local listener address:
Add following entry in the tnsnames.ora file on the server .
(ADDRESS = (PROTOCOL =
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]
Oracle Net Services - Version: 10.2.0.1.0 to 184.108.40.206
This problem can occur on any platform.
This document applies to versions 10g and newer.
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.
This is a new implementation of shared server. This database has previously been running in dedicated server mode.
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.
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.
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.
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.
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).
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-716746/，如需转载，请注明出处，否则将追究法律责任。