Multi-Threaded Server Overview

Contents

  1. Overview
  2. Benefits of MTS
  3. The Trade-offs
  4. Implementing MTS
  5. Bottom Line: Should I use MTS?
  6. Setting up your MTS environment
  7. Changing Database Initialization Parameters
  8. Forcing a Dedicated Server Connection
  9. Viewing MTS Statistics
  10. Potential Problems with MTS
  11. Tuning and Monitoring MTS

Overview

Many Oracle DBAs today are faced with the every growing number of connections to their databases. When using the traditional method of connecting to the database (dedicated server) each client process is given a dedicated server process that runs on the database server. This server process is sometimes referred to as a "shadow process". This server process takes memory away from the system and as the number of connections increase, so does the demand for memory. Not only is memory in demand for with these server processes, but so are CPU requirements. If you have many users connecting and disconnecting from the database server, a good amount of CPU will be required in building and destroying these dedicated server processes. Oracle provides a solution that answers the above two issues called Multi-Threaded Server. This article provides some insight into setting up Multi-Threaded Server (MTS) as well as several scripts that can be used to monitor your MTS environment.

Benefits of MTS

Think about a system in which only 4 client processes need access to the database. With dedicated server, each client process requires one dedicated server processes. If each server process consumes 8 megabytes of memory, you will need 32 megabytes to support this scenario. Now consider 100 dedicated connections. In this scenario, you would require 800 megabytes of memory! Using MTS, you can see in Figure 1 that these same 4 client connections can be supported by only 2 shared server processes.


Figure 1: Dedicated Server vs. Shared Server

Another thing to consider is OLTP environment where users are spending most of their time reading and editing data on their screens and very little time actively executing SQL statements against the database. In this scenario, many of those dedicated server processes are sitting idle and needlessly consuming memory. Because MTS allows the sharing of the server process between many client processes, the DBA can now make more efficient use of the server processes.

Lastly, MTS can improve with environments where clients are constantly connecting and disconnecting from the database. Since shared server processes are not tied directly to a client process, they are not built and destroyed each time a client connects or disconnects. Oracle will maintain an active "pool" of shared server processes. New shared server processes are only created when demand exceeds supply. On the same note, shared server processes are only destroyed when demand drops off and those extra shared server processes are no longer needed.

The following list outlines the advantages of using MTS:

The Trade-offs

MTS is not for everyone! Before implementing MTS in your environment, keep the following in mind:

Implementing MTS

After deciding MTS is right for your environment, it is now time to dig into the implementation details. The DBA will need to consider the following two issues: User Session-Specific Data (UGA) and Routing Client Requests to Shared Server Processes.

User Session-Specific Data (UGA)
Every connection to the Oracle database has a "session-specific" memory associated with it. This memory is referred to as the User Global Area (UGA) and is used to hold the values of PL/SQL variables, the values of bind variables and other items specific to a session. The UGA also contains that part of the sort area specified by the SORT_AREA_RETAINED_SIZE initialization parameter.

With a dedicated server connection, the UGA is stored within the dedicated server process's Program Global Area (PGA). When an MTS connection is made though, the UGA is stored in either the "LARGE POOL" or the "SHARED POOL". This difference is illustrated in Figure 2 .

In an MTS environment, the UGA needs to be moved into a common memory structure like the large pool or shared pool since it contains "session-specific" data. Keep in mind that the use of MTS does not affect the overall "amount" of memory used for session specific data. It is only moved from the memory space of the individual process into a common memory structure accessible by all processes.

Figure 2 shows the UGA located in the large pool. If you do not have the large pool configured, then Oracle will place the UGA in the shared pool. Oracle does not recommend placing the UGA in the shared pool as this will cause fragmentation.


Figure 2: MTS changes where session-specific information is stored

Routing Client Requests to Shared Server Processes
The second major issue of MTS is setting up the dispatcher. When using a dedicate server connection, the client process simply passes the SQL statements to be executed to its dedicated server process. What happens though when in an MTS environment the client process has no dedicated server process to send the SQL to? The answer is that the request gets sent to a dispatcher process.

In an MTS environment, the dispatcher process assumes the role of communicating with the clients and routing your request. The listener no longer hands the client off to a dedicated server process, but rather gives each new client to the most lightly loaded dispatcher process. For the duration of the session, the dispatcher process will handle all communication with the client.

The dispatcher processes will then take the requests (with are often SQL statements) from the client and place them into a request queue . The request queue is located as a memory structure within the System Global Area (SGA) and is where all incoming requests are placed. All shared servers you have setup within your instance will monitor the request queue. When a request is entered into the request queue, the next available shared server process will pull it out of the queue and service it. All requests are handled in a first-in/first-out basis. This can be seen in Figure 3 .


Figure 3: Within an MTS setup, the dispatcher is responsible for all communication to and from the client

When the shared server process completes the task, the results are placed into a response queue. Like the request queue, the response queue is a memory structure located within the SGA. Response queues are directly tied to a specific dispatcher. While each dispatcher has its own response queue, each dispatcher on the other hand will have the same request queue. When a dispatcher detects a response in its response queue, it will send that response back to the client that first initiated the request.

How many dispatchers should you use in your MTS environment? Well, for one you will need at lease one dispatcher for each network protocol that you are supporting. Like shown in Figure 3 , you created one dispatcher for TCP/IP and another for SPX. Aside from the number of network protocols, you also need to decide on the number of connections you are planning to support. There is a limit as to how many connections that each dispatcher process can handle. (This number is operating-system specific).

Bottom Line: Should I use MTS?

Well, this depends on your environment. If you are supporting a large number of client connections and where those connections are mostly inactive, then MTS would be ideal here. MTS would not be a good setup if your environment does not involve much idle time with your client connections. (i.e. batch jobs). Keep in mind that you setup an environment that allows for both MTS and dedicate server processes. Setup user connections who only sporadically accessing the database using MTS, while batch jobs and other data intensive connections can be made using dedicated server connections. Lastly, remember that from the client's perspective, MTS does not enhance their performance, but rather reduces the CPU and memory overhead associated with supporting many client (mostly idle) connections.

Setting up your MTS environment

How Many Dispatchers?
As discussed above, you will need to provide at lease one dispatcher process for each protocol you plan on supporting. Another factor that will affect your decision on the number of dispatcher processes is the operating system's limit on the number of connections that can be made to a single process. If for example, your operating system allows a maximum of 972 connections to one process, and you want to support 3500 TCP/IP connections using MTS, then you will need a minimum of four (4) dispatcher processes for the TCP/IP network protocol.










Determining OS Connection Limits

Determining the connection limit for your particular operating system is fairly easy. You will need to use the "SERVICES" command within the Listener Control utility. Simply perform the following tasks: 

  • Use the MTS_DISPATCHERS initialization parameter to configure a test database for one dispatcher. 

  • Stop and restart your instance to read in the new value of MTS_DISPATHCERS. 
  • Run the Listener Control utility and issue the SERVICES command. The output will include a list of dispatcher processes. 
  • Now find the dispatcher process in the list of services and look at the value for max. This value indicates the maximum number of connections that your operating system allows for one process. 

Here is a small sample. Note that the value for max is 972. 

  LSNRCTL> SERVICES
  Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=SOPDEV2.world))
  Services Summary...
  Service "SOPDEV2" has 2 instance(s).
  Instance "SOPDEV2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
  Instance "SOPDEV2", status READY, has 2 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=appdev)(PORT=47594))

NOTE: Do not attempt to perform the above test on a production database when users are trying to connect. In fact, use a "testing" database to ensure that you will not create connection problems for people in your production environment. 

Table 1: Determining OS Connection Limits

How Many Shared Server Processes?
Setting up the number of shared server processes requires the use of two initialization parameters: MTS_SERVERS and MTS_MAX_SERVERS. They control both the minimum and maximum number of shared server processes that you want to create for your instance.

How Much Memory?
Before implementing MTS in your environment, you will need to decide how much shared pool or large pool memory is required for session-specific memory. An easy way to do this, is to gather the current amount of memory used by a representative session, and then extrapolate from that to cover the number of sessions you expect to have simultaneously. Suppose I want to gather memory information from a user logged in as "JHUNTER". First you will need to get the "SID" for the "JHUNTER" user. After obtaining the proper SID, select the current "UGA memory" and "UGA memory max" from v$sesstat.

  SQL> SELECT sid FROM v$session WHERE username = 'JHUNTER';

         SID
  ----------
          15


  SQL> SELECT sn.name, ss.value
    2  FROM v$sesstat ss, v$statname sn
    3  WHERE ss.statistic# = sn.statistic#
    4    AND sn.name IN ('session uga memory', 'session uga memory max')
    5    AND ss.sid=15;

  NAME                     VALUE
  ------------------------ ----------
  session uga memory       54364
  session uga memory max   57076

The session uga memory value represents the amount of memory that a user is currently using for session-specific information. The other value, session uga memory max represents the maximum amount of session-specific memory that has been used at any one time during the user's current session. Lets say that the "JHUNTER" user (above) was representative of many of the other users in the database that are going to connect via MTS, and you expected 500 such connections concurrently, simply multiply both values (session uga memory and session uga memory max) by 500 to come up with an estimated range of 27,182,000 to 28,538,000 bytes. This will be the amount of additional memory that you will need to allocate in the SGA for use by these 500 MTS sessions. Remember that MTS uses the shared pool for session memory, but Oracle recommends the use of the large pool. By using the large pool, you avoid fragmenting the shared pool. Given the above example, I would add the following to my init.ora file:

 large_pool = 32M

The above allocates a large pool of 32 MB in size. This is all you really have to do in order to have Oracle use it for MTS session memory.
So, if you want to determine the average amount of memory that each connection allocates, you will have to measure the maximum amount of UGA memory used at a given moment and divide that amount by the number of current user sessions. The maximum amount of UGA memory allocated at a given moment can be determined with the following query:

SELECT SUM (value) "MTS max memory allocation"
FROM   v$sesstat ss, v$statname st
WHERE  st.name in ('session uga memory max', 'session uga memory')
AND    ss.statistic# = st.statistic#;

The number of MTS connections currently in existence can be measured with the following query
SELECT COUNT (*)
FROM   v$session
WHERE  server != 'DEDICATED';

The server column in v$session will have a value of either SHARED or NONE if a user is connected through MTS, or DEDICATED if the user is connected through a dedicated server process. Note that the background processes (smon, pmon, etc.) evidently always use dedicated connections.
By running the above queries several times, I was able to determine that the amount of UGA memory used by MTS connections averaged between 160 Kb and 185 Kb per connection - less than 4% of the amount of memory used by dedicated connections. These figures correlate very closely to figures that I have found on MetaLink; the postings that I have seen on MetaLink state that MTS connections usually require between 150 Kb and 200 Kb each.
I then multiplied 185 Kb by 700 connections, and came up with a figure of about 130 Mb as an estimate of how much memory I would need to allocate for the large pool. However, in order to allow lots of padding for unanticipated circumstances, I decided to size the large pool about three times larger than I thought I would need, or 400 Mb.
This database has now been running with MTS for over six weeks, and the MTS connection memory requirements have remained very consistent. I have never seen the maximum UGA memory allocation exceed 140 Mb for 700 concurrent connections.

Step by Step Configuration of Shared Server

1) Shutdown The Database If It Is Running.

2) Edit your init.ora file
Dispatchers = “(PROTOCOL=TCP) (DISPATCHERS=3)”
Shared_Servers = 5
Max_Dispatchers = 10
Max_Shared_Servers = 10
Large_Pool_Size = 30M (Not Required If Already Configured)

3) Startup The Database

4) Confirm That The Database Has Been Configured In Shared Server Mode
SQL> Show Parameter Dispatcher
SQL> Show Parameter Shared_Server

5) Confirm That The Required Shared Server Processes & Dispatchers Are Running :
SQL> Select Name,Status,Idle,Busy From V$Dispatcher;
SQL> Select Name,Status,Idle,Busy From V$Shared_Server;
SQL> Select * From V$Circuit;

6) Now Check Through The Listener[Configured Against Your Database] Whether The Services Against Your Database Is Using Shared Server Mode
Lsnrctl> Services Listener

You Should Get The Details For You Database Service Along With The Dispatcher Details.
You Server Is Now Running In Shared Server Mode.


Changing Database Initialization Parameters

When thinking about setting up your MTS environment, you might be tempted to think that this will require changes to your Net8 configuration files like the listener.ora. As you will see, this is not the case. All parameters for setting up your MTS environment are made to the instance and not any your Net8 config files. Remember that the dispatcher and shared processes are actually part of the instance, so it makes sense that you would configure them in the same place.

This section of the article discusses the changes that will need to be made to the following parameters:

Setting up an MTS environment can be as simple as adding one or more MTS_DISPATCHERS parameters to your instance parameter file and then bouncing the database. It's likely however, that you're going to want to tune some of the other parameters above.

MTS_DISPATCHERS

This is the key initialization parameter that needs to be set in order to implement MTS. The syntax here is very similar to what you would use in Net8 configuration files like the listener.ora file. 

Here is an example of enabling MTS by only specifying the protocol to support. This will accept default values for all other attributes:

  MTS_DISPATCHERS="(PROTOCOL=TCP)"

If you want to support more than one protocol (TCP/IP and SPX for example), you would enter two MTS_DISPATCHERS parameter settings:

  MTS_DISPATCHERS="(PROTOCOL=TCP)"
  MTS_DISPATCHERS="(PROTOCOL=SPX)"

NOTE: When declaring multiple MTS_DISPATCHERS parameters, they must be grouped together in the init.ora file. Blank links are allowed, but do not place any other parameter settings between two MTS_DISPATCHERS settings. Doing this will cause the instance to error when you try to start it.

Two other commonly used attributes in the MTS_DISPATCHERS are setting the "number of dispatchers" and maybe the "number of connections" each dispatcher is allowed to handle. The following example allocates four TCP/IP dispatchers and each of those dispatchers can handle up to 250 connections.

  MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=4)(CONNECTIONS=250)"

Another attribute that deserves attention for MTS_DISPATCHERS is the LISTENER attribute. The LISTENER attribute allows you to specify the listener with which the dispatcher should register. By default, the dispatcher will register with the listener that is monitoring port 1521 on the local database server. Using the LISTENER attribute though, you can override this and have your dispatcher register with a listener assigned to either a different port or with a listener running on another database server. The following is an example of using the LISTENER attribute:

  MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=4)(CONNECTIONS=250) \
                   (LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1526)(HOST=cartman))))"

Here the LISTENER attribute defines an ADDRESS_LIST containing one or more listener addresses. The dispatchers will then register with each of those listeners.

NOTE: There is also a LOCAL_LISTENER initialization parameter that provides the same functionality as the MTS_DISPATCHERS parameter's LISTENER attribute. The LISTENER attribute will override the LOCAL_LISTENER parameter and is the recommended by Oracle Corp.

One final note to make in reference to setting the MTS_DISPATCHERS is where you have the option of specifying the network address that it will monitor. (Similar to how you would set the listener process to listen on port 1521). One of the major reasons for forcing the port number for dispatchers is get around firewall issues.

By default, whenever you specify the protocol to support, the dispatcher will decide on the address automatically. You have the option though of specifying which address the dispatcher uses by replacing the PROTOCOL attribute with either the ADDRESS or DESCRIPTION attribute. Below is an example that uses the ADDRESS attribute. Also notice that in order to specify a port, that ALL dispatchers must be configured separately to ensure that no two dispatchers are sharing the same address:

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1562)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1563)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1564)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

  MTS_DISPATCHERS="(ADDRESS=(PARTIAL=TRUE)(PROTOCOL=TCP)(HOST=cartman)(PORT=1565)) \
                   (DISPATCHERS=1)(CONNECTIONS=250)"

MTS_MAX_DISPATCHERS

The MTS_MAX_DISPATCHERS parameter defines an upper limit on the number of dispatchers that can be running at any one time. The default value for this parameter is 5. Keep in mind that this value is subject to modification based on your values defined in MTS_DISPATCHERS. Examine the following example.

  MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=12)(CONNECTIONS=250)"
  MTS_DISPATCHERS="(PROTOCOL=SPX)(DISPATCHERS=10)(CONNECTIONS=250)"
  MTS_MAX_DISPATCHERS=16

Given the example above, the actuall value for MTS_MAX_DISPATCHERS would be adjusted upward to 22. (12 for TCP and 10 for SPX)

MTS_SERVERS

The MTS_SERVERS parameter controls the number of shared server processes that will be available to service MTS connections. With this parameter, you are defining the number of shared server processes that will be created when the instance starts. (This is also the minimum number of servers for the instance. Oracle will not reduce the number of server processes below this number).

MTS_MAX_SERVERS

The MTS_MAX_SERVERS parameter puts in place an upper limit on the number of shared server processes that can be running at any given time. While an instance is running, the number of shared server processes increases and decreases. However, the number of shared server processes will never exceed the limit placed by use of the MTS_MAX_SERVERS parameter.

LOCAL_LISTENER

As mentioned above, the LOCAL_LISTENER parameter serves the same purpose of the LISTENER attribute of the MTS_DISPATCHERS parameter. It identifies the listener with which MTS dispatchers will register with. Below are two example of setting this parameter:

  LOCAL_LISTENER="(ADDRESS_LIST= \
                           (ADDRESS= \
                             (PROTOCOL=TCP) \
                             (HOST=cartman) \
                             (PORT=1526) \
                           ) \
                         )"
  LOCAL_LISTENER="(ADDRESS_LIST= \
                           (ADDRESS= \
                             (PROTOCOL=TCP)
                             (HOST=localhost)
                             (PORT=1521)
                           )
                           (ADDRESS=
                             (PROTOCOL=IPC)
                             (KEY=O901DB)
                           )
                         )"

The first example specifies one listener address. Since the port for this listener is 1526, rather than the default of 1521, this is considered a non-default listener. The second example shows two addresses and also represents the default value for the LOCAL_LISTENER parameter.

Consider the following when trying to decide on whether to use the LOCAL_LISTENER init.ora parameter or the LISTENER attribute of MTS_DISPATCHERS. Using the LISTENER attribute gives you finer control; you can have different dispatchers register with different listeners. LOCAL_LISTENER on the other hand, provides global control. Oracle still recommends the use of the LISTENER attributed defined in the MTS_DISPATCHERS parameter.

LARGE_POOL

We have discussed the use of the LARGE_POOL several times in this article. Although it is not and MTS specific parameter, it is something that needs strong consideration when implementing MTS. If your instance does not have a large pool defined, the session specific memory for all MTS connections will be taken from the shared pool. Have MTS take from the shared pool causes fragmentation and is not the recommended configuration from Oracle.
The large pool is an area of the SGA similar to the shared pool, but with restrictions on its usage such that only certain types and sizes of memory can be allocated in this pool.

The memory for the large pool does NOT come out of the shared pool but directly out of the SGA (system global area) thus adding to the amount of shared memory Oracle needs at startup.

The size of the large pool is set by <Parameter:LARGE_POOL_SIZE> and the minimum size chunk of memory which can be allocated is determined by <Parameter:LARGE_POOL_MIN_ALLOC>. By default there is no large pool allocated - it must be explicitly configured.

The three main uses of the large pool in Oracle 8 are:

a. For the User Global Area (UGA) of sessions connected using MTS (multi-threaded server)
b. Buffering for sequential file IO (Eg: as used by server managed recovery/RMAN when there are multiple IO slaves)

Beginning in Oracle 8i, the large pool is also used for allocation of parallel execution buffers if PARALLEL_AUTOMATIC_TUNING is TRUE.

The large pool is protected by the 'shared pool' latch for memory allocation and management. Unlike the shared pool there is no LRU (least recently used) mechanism so chunks of memory are never aged out of the large pool - memory has to be explicitly allocated and freed by each session. If there is no free memory left when a request is made then an ORA-4031 will be signalled.


MTS and the Large Pool
If there is a large pool configured MTS will ONLY try to use this pool for a sessions UGA. When a new session is started a small amount of memory (known as the fixed UGA) is allocated in the shared pool and the rest of the session memory (UGA) is taken from the large pool. If there is insufficient space left in the large pool and ORA-4031 error will be returned similar to this:
       ORA-04031: unable to allocate 636 bytes of shared memory
       ("large pool","EMPSCOTT","session heap","define var info")

Memory is allocated from the large pool in chunks of at least LARGE_POOL_MIN_ALLOC bytes in size to help avoid fragmentation. This may impact the amount of memory used by each MTS session when compared to memory usage with no large pool configured. If there is no large pool configured MTS uses the shared pool for the entire UGA as was the case in Oracle7.


Some examples:

init.ora File
# -------------------------------------------------------------
# Parameters for Multi-Threaded Server (MTS)
# --------------------------------------------------------------
mts_service=         "PPP"   #<-- Use YOUR OWN service name here

# The line below tells the dispatchers where the listener resides, so they
# can call it to register this service. The address MUST match a LISTENER.ORA
# listening address.
#
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(HOST=10.111.254.141) (SERVICE=PPP) (PORT=1521))"
# mts_dispatchers= "tcp, 1"      #<-- Start 1 TCP dispatcher
mts_dispatchers ="(ADDRESS=(PROTOCOL=TCP)(HOST=10.111.254.141))(DISPATCHERS=1)(SERVICE=PPP)"
mts_max_dispatchers=5         #<-- No more than 10 dispatchers
mts_max_servers=5             #<-- No more then 10 shared servers
mts_servers=2                 #<-- Start with 4 shared servers
# ------------------------------------------------------------------------

Forcing a Dedicated Server Connection

Remember that when configuring MTS requires no changes being made to your Net8 config files. MTS configuration happens at the instance level, and when configured, new connections start using it. One thing you can control though through the use of your Net8 config files, is forcing a dedicated connection. (Overriding the MTS configuration at the instance). There are basically two ways to force a dedicated server connection: tnsnames.ora and sqlnet.ora.

tnsnames.ora
Simply add the attribute (SERVER=DEDICATED) to the list of CONNECT_DATA attributes.

  O901DB_DEDICATED.world =
  (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS =
            (PROTOCOL = TCP)
            (Host = cartman)
            (Port = 1521)
         )
     )
     (CONNECT_DATA =
         (SID = O901DB)
         (SERVER = DEDICATED)
     )
  )

sqlnet.ora
Use the USE_DEDICATED_SERVER=ON in the sqlnet.ora file to force EVERY session that you initiate from your client to use dedicated server. As you can see, this is a much less flexible approach as it does not allow for specific connections to use dedicate server.


Viewing MTS Statistics

Operating-System Commands

Within UNIX and Linux, each dispatcher and shared server process is a separate operating-system process. The exact naming format varies from platform to platform, but the dispatcher process will always have the format dxxx in their name, and the shared server process will always have sxxx in their name. In almost all cases, the xxx will be a number starting at 000. This number gets incremented sequentially for each new dispatcher and/or shared server process started.

On most UNIX system, you can make use of the ps command to generate a list of these processes. You would then pipe this output into the grep command to filter out the ones that you are interested in. The following is an example:

  O901DB on cartman: ps -ef | grep ora_[ds][0123456789]*_O901DB
  oracle  8742     1  0   Feb 06 ?        0:00 ora_s013_O901DB
  oracle  8740     1  0   Feb 06 ?        0:00 ora_s012_O901DB
  oracle  8720     1  0   Feb 06 ?        0:00 ora_s002_O901DB
  oracle  8724     1  0   Feb 06 ?        0:00 ora_s004_O901DB
  oracle  8716     1  0   Feb 06 ?        0:08 ora_s000_O901DB
  oracle  8738     1  0   Feb 06 ?        0:00 ora_s011_O901DB
  oracle  8718     1  0   Feb 06 ?        0:06 ora_s001_O901DB
  oracle  8728     1  0   Feb 06 ?        0:00 ora_s006_O901DB
  oracle  8736     1  0   Feb 06 ?        0:00 ora_s010_O901DB
  oracle  8722     1  0   Feb 06 ?        0:00 ora_s003_O901DB
  oracle  8744     1  0   Feb 06 ?        0:00 ora_s014_O901DB
  oracle  8726     1  0   Feb 06 ?        0:00 ora_s005_O901DB
  oracle  8730     1  0   Feb 06 ?        0:00 ora_s007_O901DB
  oracle  8734     1  0   Feb 06 ?        0:00 ora_s009_O901DB
  oracle  8732     1  0   Feb 06 ?        0:00 ora_s008_O901DB
  oracle  8746     1  0   Feb 06 ?        0:00 ora_s015_O901DB
  oracle  8748     1  0   Feb 06 ?        0:00 ora_s016_O901DB
  oracle  8750     1  0   Feb 06 ?        0:00 ora_s017_O901DB
  oracle  8752     1  0   Feb 06 ?        0:00 ora_s018_O901DB
  oracle  8754     1  0   Feb 06 ?        0:00 ora_s019_O901DB
  oracle  8756     1  0   Feb 06 ?        0:00 ora_s020_O901DB
  oracle  8758     1  0   Feb 06 ?        0:00 ora_s021_O901DB
  oracle  8760     1  0   Feb 06 ?        0:00 ora_s022_O901DB
  oracle  8762     1  0   Feb 06 ?        0:00 ora_s023_O901DB
  oracle  8764     1  0   Feb 06 ?        0:00 ora_s024_O901DB
  oracle  8766     1  0   Feb 06 ?        0:01 ora_d000_O901DB
  oracle  8768     1  0   Feb 06 ?        0:02 ora_d001_O901DB

The above example shows two dispatcher processes as well as 25 shared server processes.

Listener Control

Using the SERVICES command within the Listener Control utility, you can get a listing of the dispatchers that have been registered with the listener. Here is a sample out of the SERVICES command:

  LSNRCTL> SERVICES listener
  Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cartman)(PORT=1526)))
  Services Summary...
  Service "O901DB" has 1 instance(s).
    Instance "O901DB", status UNKNOWN, has 4 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:194 refused:0
           LOCAL SERVER
        "DEDICATED" established:0 refused:0 state:ready
           LOCAL SERVER
        "DISPATCHER" established:96 refused:23 current:38 max:250 state:ready
           D000 
           (ADDRESS=(PROTOCOL=tcp)(HOST=cartman)(PORT=1219))
  Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Handler(s):
        "DEDICATED" established:0 refused:0
           LOCAL SERVER
  The command completed successfully

Figure 4 (below) gives a short overview of the different parameters in the SERVICES output command.


Figure 4: Output from the Listener Control's SERVICES command

 
 

Dynamic Performance Views






Dynamic Performance View relevant to MTS

View name

Description

V$CIRCUIT

Returns one row to the instance for each MTS connection. A circuit is a connection through a specific dispatcher and shared server process. Columns in this view relate each MTS circuit to a specific dispatcher, shared server process and session.

V$DISPATCHER

Returns one row for each dispatcher process associated with the instance. This view returns information such as the dispatcher's name, network address, process address and status.

V$DISPATCHER_RATE

Returns one row for each dispatcher process, and returns rate statistics for each dispatcher.

V$MTS

Returns only one row. This view provides some statistics that you can use to determine whether or not you have the MTS_SERVERS parameter set to a reasonable value.

V$QUEUE

Returns one row for MTS queue in the instance. Each dispatcher will have one response queue associated with it, and their will always be one common request queue for the instance. The number of rows returned by V$QUEUE is always equal to the number of dispatchers plus one.

V$SHARED_SERVER

Returns one row for each shared server process that is currently running as part of the instance. This view returns the process name, process address, status and other useful statistics.

Table 2: Dynamic Performance View relevant to MTS

Dispatcher Process Status

  SELECT name, status, accept, created, conf_indx, network
      FROM v$dispatcher;

  NAME STATUS ACCEPT CREATED CONF_INDX NETWORK
  ---- ------ ------ ------- --------- --------------------------------------------------
  D000 WAIT   YES    56      0         (ADDRESS=(PROTOCOL=tcp)(HOST=cartman)(PORT=38793))
  D001 WAIT   YES    78      1         (ADDRESS=(PROTOCOL=tcp)(HOST=cartman)(PORT=38794))


Dispatcher Process Utilization

   SELECT name, busy / (busy + idle) * 100
      FROM v$dispatcher;

  NAME BUSY/(BUSY+IDLE)*100
  ---- --------------------
  D000           .000359889
  D001           .000405469

Doesn't look like these dispatchers are very busy! Keep in mind that the BUSY and IDLE values are reported in hundredths of a second. If the BUSY value for a dispatcher is 100, that means the dispatcher had been busy for 1 second.

If dispatcher utilization is very high, you should consider creating more dispatcher processes. If dispatcher utilization is very low, you should consider deleting some dispatcher processes. In the above example, I would consider only running one dispatcher process to support this instance.

Queue Size and Wait Time

  SELECT paddr, type, queued, DECODE(totalq,0,0,wait/totalq)
      FROM v$queue;

  PADDR    TYPE         QUEUED DECODE(TOTALQ,0,0,WAIT/TOTALQ)
  -------- ---------- -------- ------------------------------
  00       COMMON            0                     .009878904
  8008AC54 DISPATCHER        0                     3.38482683
  8008AF70 DISPATCHER        0                     .009591116

You can get an idea of how well work is flowing through the request and response queues by using v$queue.

The DECODE in the query handles the case where the TOTALQ column, which is the divisor, happens to be zero. The average wait time is reported in hundredths of a second. (i.e. If the average wait time of a dispatcher is 37, works out to 0.37 seconds.)

The COMMON queue is where requests are placed so that they can be picked up and executed by a shared server process. If you average wait time is high, you might be able to lower it by creating more shared server processes.

Users and Dispatchers

  SELECT  d.name, s.username, c.status, c.queue
    FROM   v$circuit c, v$dispatcher d, v$session s
    WHERE  c.dispatcher = d.paddr AND c.saddr = s.saddr;

  NAME USERNAME           STATUS           QUEUE
  ---- ------------------ ---------------- ----------------
  D000 JHUNTER            NORMAL           NONE
  D001 AHUNTER            NORMAL           NONE

Shared Server Utilization

  SELECT name, busy / (busy + idle) * 100
      FROM v$shared_server;

  NAME BUSY/(BUSY+IDLE)*100
  ---- --------------------
  S000            .00155527
  S001           .000669656
  S002           .000012313
  S003                    0
  S004                    0
  S005                    0
  S006                    0
  S007                    0
  S008                    0
  S009                    0
  S010                    0
  S011                    0
  S012                    0
  S013                    0
  S014                    0
  S015                    0
  S016                    0
  S017                    0
  S018                    0
  S019                    0
  S020                    0
  S021                    0
  S022                    0
  S023                    0
  S024                    0

Other Shared Server Statistics

The v$mts view is useful when trying to tune both the MTS_SERVERS and MTS_MAX_SERVERS initialization parameters. Here is an example output from v$mts:

   SELECT servers_started, servers_terminated, servers_highwater
      FROM v$mts;

  SERVERS_STARTED SERVERS_TERMINATED SERVERS_HIGHWATER
  --------------- ------------------ -----------------
               94                 18                25

 
RUN-TIME MTS ALTERATIONS

The database administrator may find it necessary to perform administrative functions to adjust the running MTS system. Such functions include monitoring the efficiency of dispatchers and shared servers; and increasing or decreasing the number of dispatchers and shared servers. Although the RDBMS starts/stops additional dispatchers and shared servers, such administrative functions may be necessary. You can monitor the utilization of dispatchers and shared servers by using SQL*DBA or by querying from V$DISPATCHER and V$SHARED_SERVER. To examine the efficiency of the dispatchers:

select name,network,status,
(busy /(busy + idle)) * 100 "% of time busy"
from v$dispatcher;
 

NAME  NETWORK    STATUS           % of time busy
----- ---------- ---------------- --------------
D000  TCP        WAIT             .141240092
D001  TCP        CONNECT          .146774237
D002  TCP        WAIT             .04165972
D003  TCP        WAIT             .04052496
D004  TCP        WAIT             .038870368

Five dispatchers are running on this instance. All have the status WAIT accept one whose status is CONNECTED (D001). You could conclude that you could do with fewer dispatchers.

 view V$SHARED_SERVER:

select name,status,requests,
(busy /(busy + idle)) * 100 "% of time busy"
from v$shared_server

 
NAME STATUS REQUESTS % of time busy
----- ---------------- ---------- --------------
S000 WAIT(COMMON) 1122 .05956579
S001 WAIT(COMMON) 7 .001001586
S002 WAIT(COMMON) 0 .000031547

 
Three shared servers are running on this instance, all of which are waiting for requests to process. Hence, none of these servers are busy. The value of the REQUESTS column is cumulative from instance startup.
To change the number of dispatchers and shared servers, you may use the SQL command ALTER SYSTEM. In our example, MTS_DISPATCHERS = "tcp,5,5" was used.
If we wish to decrease the number of dispatchers, we can issue:

  ALTER SYSTEM SET MTS_DISPATCHERS = 'tcp,3,5';

Two dispatchers will be stopped leaving three dispatchers remaining, each of which can handle up to five connections.

The number of shared servers can be changed using the same ALTER SYSTEM SQL statement. Again, our example has MTS_SERVERS = 3. To increase the number of shared servers to 4, issue:

          ALTER SYSTEM SET MTS_SERVERS = 4;

If decreasing the number of shared servers, the server(s) to be stopped
will first complete amy RDBMS program calls currently in progress. After
one minute from issuing the ALTER SYSTEM command, if all servers are busy,
the RDBMS will mark servers to be stopped once the current call has completed.
Setting MTS_SERVERS to zero, will force all connections to the instance
to acquire dedicated servers.

                                                                                   
        Potential Problems  with MTS
 - It's nearly  always slower than dedicated connections.  Each call to the server has  to:                                                   
   - Bad Session management for dead client sessions.

This is not really documented. If a client session dies then the only
way to clear the session is to use the ALTER SESSION KILL command. This
leaves an entry in the V$SESSION as KILL is intended to mark a session as
KILLED and inform the user with an error when they next issue a call to
the server. For 'dead' client sessions no call is received so the entry
stays in the V$SESSION until the database is bounced. If you limit connections
per userid this can actually stop a user from ever reconnecting. On dedicated
connections you can use an OS kill on the server process.
   Tuning and Monitoring MTS

Configuring the Large Pool and Shared Pool for MTS

Oracle recommends using the large pool to allocate MTS-related UGA (User
Global Area), not the shared pool. This is because Oracle also uses the shared
pool to allocate SGA memory for other purposes such as shared SQL and PL/SQL
procedures. Using the large pool instead of the shared pool also decreases
SGA fragmentation.


To store MTS-related UGA in the large pool, specify a value for the parameter
LARGE_POOL_SIZE. If you do not set a value for LARGE_POOL_SIZE, Oracle uses
the shared pool for MTS user session memory.


If there is a large pool configured MTS will ONLY try to use this pool for
a sessions UGA. When a new session is started a small amount of memory (known
as the fixed UGA) is allocated in the shared pool and the rest of the session
memory (UGA) is taken from the large pool. If there is insufficient space
left in the large pool and ORA-4031 error will be returned similar to this:


ORA-04031: unable to allocate 636 bytes of shared memory


("large pool","unknown","session heap","trigger condition node")

Memory is allocated from the large pool in chunks of at least LARGE_POOL_MIN_ALLOC
bytes in size to help avoid fragmentation. This may impact the amount of
memory used by each MTS session when compared to memory usage with no large
pool configured. If there is no large pool configured MTS uses the shared
pool for the entire UGA .

Use following queries to over different times (peak load, medium load ) to
calculate the approx size of large pool.



PROMPT ====================================================
PROMPT MAX UGA ATTAINED BY THE SESSION AFTER SYSTEM STARTUP
PROMPT =====================================================
SELECT USERNAME, B.VALUE/1024/1024,OSUSER,SERVER, PROGRAM, MODULE
FROM V$SESSION A,
    V$SESSTAT B,
     V$STATNAME C
WHERE A.SID = B.SID
AND B.statistic# = C.statistic#
AND C.NAME = 'session uga memory max'
AND B.VALUE/1024/1024 > 1
order by value;



PROMPT ============================================
PROMPT UGA ATTAINED BY CURRENTLY CONNECTED USERS
PROMPT ============================================
SELECT USERNAME, B.VALUE/1024/1024,OSUSER,SERVER, PROGRAM, MODULE
FROM V$SESSION A,
     V$SESSTAT B,
     V$STATNAME C
WHERE A.SID = B.SID
AND B.statistic# = C.statistic#
AND C.NAME = 'session uga memory'
AND B.VALUE/1024/1024 > 1
order by value;


PROMPT=====================================================
PROMPT SUM(UGA) ATTAINED BY CURRENTLY CONNECTED SESSIONS
PROMPT=====================================================
SELECT SUM(VALUE)/1024/1024
  FROM V$SESSTAT A,
       V$STATNAME B
  WHERE A.statistic# = B.statistic#
  AND b.NAME = 'session uga memory';




MONITORING DISPATCHER AND SHARED SERVERS:

Identifying Dispatcher Contention
To assess dispatcher performance, query the V$DISPATCHER_RATE view and compare the current and average values with the maximums.
If the present system throughput provides adequate response time and current values from this view are near the average and below the maximum, then you likely have an optimally tuned MTS environment.

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

Contention for dispatcher processes are indicated by either of these symptoms:
Run following query to find the performance of dispatchers.

PROMPT==========================================
PROMPT DISPATCHER STATS
PROMPT==========================================
select name "NAME", substr(network,1,15) "PROTOCOL",
status "STATUS", (busy/(busy + idle)) * 100 "%TIME BUSY"
from v$dispatcher;

If  TIME BUSY for all the dispatchers is greater than 50%,Then increase the number of dispatchers
select paddr,type,queued,wait,totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;

In the above Query, the WAIT column is the total amount of time all requests have waited on this queue. The TOTALQ column is the total number of requests on a queue since the database was started.  The AVG WAIT is the average wait (in hundredths of a second) per queued request; If AVG WAIT is increasing at a excessive rate ,consider increasing no of dispatchers


Identifying Contention for Shared Servers

PROMPT==========================================

PROMPT SHARED SERVER STATS
PROMPT==========================================
select name "NAME", paddr,requests,status,
(busy/(busy + idle)) * 100 "%TIME BUSY" from v$shared_server;

In the above query, the shared servers will be between 1% and 99% busy. In general, the #000 shared server will always be the busiest.
If Time Busy for a particular shared server is greater then 98% ,then identify that connection & give a dedicated connection as it is consuming whole of the shared server.
The STATUS column of the V$SHARED_SERVER column gives useful information about WAIT status. In particular, the WAIT(ENQ) status tells the that the user is waiting for a lock resource
An overview of server creation/termination and highwater mark is available from the following query

select maximum_connections "MAX CONN",
servers_started "STARTED",
servers_terminated "TERMINATED",
servers_highwater "HIGHWATER" from v$mts;

These statistics are useful indicators for MTS_SERVERS set too low or too high. If  STARTED and TERMINATED values never change, there may be too many shared servers allocated to the system. Likewise, if the numbers row quickly for STARTED and TERMINATED, the number for MTS_SERVERS is too low.

The SERVER column in the V$SESSION view shows what kind of server is currently servicing each session
select sid,serial#,osuser ,server,module ,status ,program from v$session;

Multi-threaded server connections will always appear as NONE or SHARED in the SERVER column of the V$SESSION view depending on whether or not a task is currently being serviced by a shared server. The V$CIRCUIT view gives more detailed information about the usage of circuits by each session

PROMPT==========================================
PROMPT CIRCUIT STATS
PROMPT==========================================
select saddr, circuit, dispatcher, server,
substr(queue,1,8) "QUEUE", waiter from v$circuit;


Last modified on: Monday, 18-Dic-2003 15:13:43 ESTPage Count: