| Shared Server (MTS) Diagnostics [ID 1005259.6] | |||||
|
| |||||
| 수정 날짜 29-SEP-2011 유형 BULLETIN 상태 PUBLISHED | |||||
In this Document
Purpose
Scope and Application
Shared Server (MTS) Diagnostics
References
Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 10.2.0.1 - Release: 8.1.5 to 10.2Oracle Net Services - Version: 8.1.5.0.0 to 10.2.0.1.0 [Release: 8.1.5 to 10.2]
Information in this document applies to any platform.
Purpose
Introduction
This document contains information about the Shared Server configuration of the Oracle Database software and some of the SQL scripts necessary to gather diagnostic information with guidelines for interpreting results. Prior to Oracle 9.0, Shared Server was called Multi-Threaded Server; the name was changed to reflect the extensive enhancements made.
Scope and Application
Skill level rating for this Article: IntermediateShared Server (MTS) Diagnostics
Architecture of the Shared Server Database Configuration
The best position to be in will always be based on understanding. 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.
Fig. 1: Diagram of Shared Server Architecture
The stages that Shared Server goes through are quite simple. After the client sends the connection request to the Listener, it will either redirect or hand off (called warm hand-off) the connection to the Dispatcher (the Dispatcher does not necessarily need to be on the same host as the Listener). Once the client has connected to a Dispatcher it stays connected to that Dispatcher. Before the client completes the database log in, the Dispatcher associates a Virtual Circuit (VC) for that database session. There exists exactly one row in the VC view (V$CIRCUIT) for each client connection. This view also shows the current status of the client's VC. Once the VC has been associated with the database session, the client will complete the database logon by passing the username and password to the Dispatcher. This request, as part of the VC for that new session, will be placed in the Common Queue where the first available Shared Server will complete the logon. Once each phase of the logon has completed, the Shared Server will pass the VC back to the Dispatcher, which then passes the response back to the Client (this actually takes several round trips to the client, in just the same manner as if it was a Dedicated connection).
Once the logon has completed, the client starts a normal conversation with the database. When the client makes a request to the database, it is the Dispatcher that picks up this request from the operating system's network protocol. The Dispatcher then determines which client session the request came from (remember that a Dispatcher can be configured for Connection Pooling and Multiplexing: see the Net Administration Guide for more information on those configurations), tags that sessions' VC that there is a new message (there is also a pointer to that session buffer in the VC) and places the VC in the Common Queue. The CQ is serviced by the Shared Servers on a first-in-first-out basis. The next available Shared Server pulls the VC from the CQ and processes the request. Part of the VC structure is the identity of the Dispatcher that created it (and which client is connected to it). When the Shared Server is finished processing the request, it writes the output to the session buffer, changes the VC's ownership back to the Dispatcher that created it, places the VC into that Dispatcher's queue, and posts the Dispatcher it has something in its queue. The Dispatcher then checks its queue and sends what is in the session buffer through the operating system network protocol back to the Client.
In the case where there is a request for a Database Link, it is the Shared Server process that will, from the link definition, create an outbound VC and place it into the least loaded Dispatcher's queue (not necessarily the same Dispatcher the Client is connected to). This Dispatcher then logs into the remote database and passes the query to it for processing. Once the remote database responds, the Dispatcher then places the VC back into that Shared Server's ownership.
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.
The main views containing Shared Server information include:
V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_CONFIG
V$DISPATCHER_RATE
V$QUEUE
V$SESSION
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR
Dispatchers: The Number Of Dispatchers
The number of Dispatchers present in a Shared Server database configuration may vary from zero up to the system INIT.ORA parameter of MAX_DISPATCHERS. The initial number of Dispatchers created at instance startup is the value defined by the DISPATCHERS parameter in the system INIT.ORA and can exceed the MAX_DISPATCHERS value (as of version 10). Both parameters can be altered at runtime by ALTER SYSTEM commands (version 9.0 onwards, consult the SQL Reference manual for the particular version being run).
It is the PMON database background process that starts or stops any background processes, such as Dispatchers and Shared Servers. Dispatchers are not dynamically started or stopped, but must be manually maintained. Apart from restarting the database in order for the database to take on a new value set in the INIT.ORA, the following command may also be issued while the database is running:
SQL> ALTER SYSTEM SET DISPATCHERS='string';Where 'string' is a valid setting for the DISPATCHERS parameter. For example:
SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP )(DISPATCHERS=2)';
SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCPS)(DISPATCHERS=2)';PMON creates or destroys Dispatchers and then informs the Listener with their current state. PMON also updates the Listener about every ten seconds (depending on system load and other factors) with the current number of sessions and database load. To have PMON update the Listener with a change outside its normal cycle (as long as the LOCAL_LISTENER and REMOTE_LISTENER parameters are properly set in the system INIT.ORA) use this command:
SQL> ALTER SYSTEM REGISTER;One can then query then Listener for the new services that have been registered:
|
|
From this output we can see that there are four dispatchers, two using TCP and two using TCPS. The TCP ports that are assigned to the Dispatchers were allocated by the operating system. To specify what port to use, each Dispatcher has to be configured individually specifying the port number in the ADDRESS parameter section (see the Oracle Net Administration Guide on how to assign ports to Dispatchers).
Dispatchers can also be configured for a particular service. By default, each Dispatcher will service all SERVICE_NAMES and the DB_NAME. Dispatchers can be set up to only service a specific Service.
|
|
The command added one Dispatcher, D003, to service the SERVICE_NAME of V10R2_DISP (specified by the SERVICE clause). To get this Dispatcher to service V10R2_DISP the INDEX clause was used (INDEX can also be used to "separate" Dispatchers from other TCP Dispatchers). This new Dispatcher gets a new INDEX:
|
|
If the desire is to add more Dispatchers for a particular protocol, either issue the ALTER SYSTEM command specifying the full DESCRIPTION of the Dispatcher, or the Dispatcher's INDEX. For example, below are three Dispatchers configured for TCP and one configured for TCPS and there is a need to add one more TCP Dispatcher:
|
|
Dispatchers: Shutting Down Dispatchers
If the need is to shut down or reduce the number of Dispatchers by using the ALTER SYSTEM statement, the database can decide, or a specific Dispatchers can be chosen. To identify the name of the specific Dispatcher process to shut down, use the V$DISPATCHER view.
|
|
Each Dispatcher is uniquely identified by a name of the form Dnnn (where n is a number in the 0-9 range). In Unix, the Dispatcher process will have a name like ora_dnnn_sid. For Windows, the Dispatchers run as threads and are only visible using certain utilities, but the Oracle views will be the same. The Dispatchers are grouped by CONF_INDX based on the DESCRIPTION (or ADDRESS and PROTOCOL).
To shut down Dispatcher D002, issue the following statement:
SQL> ALTER SYSTEM SHUTDOWN 'D002';
The Dispatcher stops accepting new connections and will wait until all of the sessions it handles are disconnected before shutting down.
For a more "immediate" shutdown of the Dispatcher (it is in a bad state or it won't shutdown with the above command) issue this command:
SQL> ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';
The IMMEDIATE keyword stops the Dispatcher from accepting new connections and immediately terminates all existing connections through that Dispatcher. After all sessions are cleaned up, the Dispatcher process shuts down.
When a Dispatcher is shut down, other Dispatchers will not inherit the name, so it is possible to see some Dispatchers missing. When starting up a new Dispatcher, it will obtain the next open Dispatcher name. In the case where TCPS D002 Dispatcher was shut down, and a new Dispatcher for TCP was started, the new Dispatcher will be called D002.
Dispatchers: Monitoring Performance
In general, Dispatchers will not be very busy because their tasks are relatively quick to complete. In the example below, Dispatchers are less than 1% busy.
|
|
The OWNED column of V$DISPATCHER view shows the number of clients currently connected to each Dispatcher. In the above example, a D000 has 26 clients connected. D000 is in the process of sending a message to a client as is shown by the SEND status. D004 has OWNED = 0 because it has just been started using the following command:
SQL> ALTER SYSTEM SET DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=5)';
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 Servers: Setting the Quantity of Shared Servers
The quantity of Shared Server processes will vary between INIT.ORA parameters SERVERS and MAX_SERVERS. Initially, the MAX_SERVERS value should be set to some estimated maximum number. For the initial setting, it can be set to the maximum number of expected sessions on the database (this is just a suggestion as it could also be set to far less). The effect of setting this parameter to a large value only affects the size of the Common Queue. This parameter can be changed dynamically by issuing the command:
SQL> ALTER SYSTEM SET MAX_SHARED_SERVERS = {number};
Setting the initial value for SERVERS is also estimated. It can be set to some number under the setting of MAX_SHARED_SERVERS. But once the system is running under production load, the parameter SERVERS can be changed to accommodate the load. The Oracle Database Performance Tuning Guide explains how to monitor V$QUEUE to determine if SERVERS should be increased:
|
|
If the system is suffering from high SYS load due to having to create and destroy many shared servers, then SERVERS might be set to one plus the number in SERVERS_HIGHWATER that is found in the V$SHARED_SERVER_MONITOR view (the name of the view is V$MTS in 8.1 and before).
|
|
Setting this parameter based on the SERVERS_HIGHWATER value will reduce the expense of process creation and match the known maximum of Shared Servers. These are just suggestions as to a starting point to configuring Shared Server. The Shared Server views should be monitored to make sure the settings are appropriate for this instance. Monitoring the instance initially to make sure the settings are correctly configured will assure a well-tuned system.
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).
Virtual Circuits and Sessions
The SERVER column in the V$SESSION view shows the type of Server that is currently servicing each session.
|
|
In the above example, DEDICATED means that client is connected with a dedicated server process. Shared Server connections appear as NONE or SHARED depending on whether a task is currently being serviced by a Shared Server or not. In this case only the OS User "bill" is being serviced by a Shared Server.
The V$CIRCUIT view provides more detailed information about usage of circuits by each session.
|
2 WAITER FROM V$CIRCUIT;
|
The DISPATCHER column identifies the Oracle process ID for the Dispatcher Associated with the session. The SERVER column provides the Oracle process ID for the Shared Server currently servicing the client session, and zero if the session is not being serviced. For the QUEUE column, NONE represents the circuit is idle, SERVER means it is currently being serviced by a Shared Server, DISPATCHER means it is being serviced by a Dispatcher, and COMMON means it is on the Common Queue waiting to be picked up by a Shared Server.
The WAITER column is the Oracle process ID of the process that is currently waiting for data to appear in the Circuit. It will contain "00" when no database operation is in progress, otherwise it will list the Oracle process id for the Dispatcher or a Shared Server. Dispatchers are very quick to complete their work, so quick that it is rare to catch a glimpse of a Dispatcher in the WAITER queue.
References
http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14220%2Ftoc.htm&remark=portal+%28Books%29http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14231%2Ftoc.htm&remark=portal+%28Books%29
http://www.oracle.com/pls/db102/to_toc?pathname=server.102%2Fb14211%2Ftoc.htm&remark=portal+%28Books%29
http://www.oracle.com/pls/db102/to_toc?pathname=network.102%2Fb14212%2Ftoc.htm&remark=portal+%28Books%29
Previous versions of the above manuals
|
| ||
첨부 파일
|
제품
| ||
'ORACLE > ADMIN' 카테고리의 다른 글
| oracle inactive session 정리 (0) | 2011.12.15 |
|---|---|
| oracle Shared Server 와 Dedicate Server 의 접속 방식 (0) | 2011.12.13 |
| oracle MTS (0) | 2011.12.08 |
| 오라클 패치 후 다시 원복 하기 (0) | 2011.12.08 |
| oracle shared pool size얼마나 남았나? (0) | 2011.11.22 |
