5.5. Tuning the Shared Server Option

Before tuning the Oracle Shared Server, examine the performance of the dispatchers and the shared server processes. You want to make sure that you have enough dispatchers so that clients are not waiting for dispatchers to respond to their requests, and you want enough shared server processes so that requests are not waiting to be processed. You also want to configure the Large Pool SGA memory area. The Large Pool stores the UGA, which contains much of the information that is stored in the PGA in a dedicated server configuration.

The Large Pool is designed to allow the database to request large amounts of memory from a separate area of the SGA. Before the database had a Large Pool design, memory allocations for Shared Server came from the Shared Pool. This caused Shared Server to compete with other processes updating information in the Shared Pool. The Large Pool alleviates the memory burden on the Shared Pool and enhances performance of the Shared Pool.

You need to understand how to set the Large Pool to the appropriate size and to monitor the performance of the dispatchers and shared servers in the Oracle Shared Server environment. The following section discusses how to appropriately size the Large Pool and how to determine if the correct number of dispatchers and shared servers have been configured.

5.5.1. Configuring the Large Pool

You can configure the Large Pool by setting the parameter LARGE_POOL_SIZE in the init.ora file. You can set this parameter to a minimum of 300KB and a maximum of at least 2GB; the maximum setting is operating-system dependent. When you use a default value, Oracle adds 250KB per session for each shared server if you specified the DISPATCHERS parameter. If you do not configure a Large Pool, Oracle places the UGA in the Shared Pool. Because of this, configure a Large Pool when using Oracle Shared Server so that you don't affect the performance of the Shared Pool.

Here is an example of setting the LARGE_POOL_SIZE parameter in the init.ora file:

LARGE_POOL_SIZE = 50M

As with many of the Oracle 10g parameters, you can also modify the LARGE_POOL_SIZE parameter dynamically using the ALTER SYSTEM command. For example:

ALTER SYSTEM SET LARGE_POOL_SIZE = 100M

This example sets the large pool size to 100 megabytes.

You can see how much space the Large Pool is using by querying the V$SGASTAT view. The free memory row shows the amount available in the Large Pool, and the session heap row shows the amount of space used in the Large Pool. Here is a listing that shows an example of the query:

SQL> select * from v$sgastat where pool = 'large pool';

POOL        NAME                            BYTES
----------- -------------------------- ---------
large pool  free memory                    251640
large pool  session heap                    48360

5.5.2. Sizing the Large Pool

The Large Pool should be large enough to hold information for all your shared server connections. Generally, each connection needs between 1 and 3 megabytes of memory, but this depends on that client's type of activity. Clients that do a great deal of sorting or open many cursors will use more memory.

You can gauge how much memory shared server connections are using by querying the V$SESSTAT view. This view contains information about memory utilization per user. The following query shows how to measure the maximum amount of memory for all shared server sessions since the instance was started. You can use this as a guide to determine how much memory you should allocate for the Large Pool. This example shows that the maximum amount of memory used for all shared server sessions is about 240KB:

select sum(value) "Max MTS Memory Allocated"from v$sesstat ss, v$statname st
where name = 'session uga memory max'and ss.statistic# =st.statistic#;

Max MTS Memory Allocated
------------------------
                  244416

You can determine a good starting point for the Large Pool by taking into account the number of shared server connections that you want to manage and multiply that by the maximum UGA memory for a session. In this example, a single Oracle Shared Server connection happens to be using 240KB of Large Pool space. If you want to support 200 concurrent connections in this environment, configure LARGE_POOL_SIZE to about 50MB (240KB multiplied by 200 concurrent connections). This would be a good starting point for the Large Pool.

If the Large Pool has not been sized correctly, clients can encounter connection errors. The error message looks similar to this:

ORA-04031: unable to allocate 490 bytes of shared memory
      ("large pool","MWEIS","session heap","define var info")

This error message indicates that the LARGE_POOL_SIZE needs to be increased. In Oracle 10g, you can modify the LARGE_POOL_SIZE dynamically using the ALTER SYSTEM command. Here is an example of how to modify the LARGE_POOL_SIZE using the ALTER SYSTEM command:

ALTER SYSTEM SET LARGE_POOL_SIZE = 51200000 SCOPE=MEMORY

You can limit the amount of space that a session can allocate in the SGA by using a session profile. Session profiles allow you to control and manage thresholds for a variety of characteristics of a database session. The PRIVATE_SGA setting in a profile sets the per session memory threshold. A discussion of profiles is beyond the scope of this book. For a complete description of profiles, please consult Oracle Database SQL Reference 10g Release (10.1), Part Number B10759-01.

5.5.3. Determining Whether You Have Enough Dispatchers

You can monitor the dispatcher processes by querying the V$DISPATCHER view, which contains information about how busy the dispatcher processes are. Query this view to determine whether it will be advantageous to start more dispatchers.

The following sample query runs against the V$DISPATCHER view to show what percentage of the time dispatchers are busy:

Select name, (busy / (busy + idle))*100
"Dispatcher % busy Rate"
From V$DISPATCHER

Protocol          Dispatcher % Busy Rate
------------      ---------------------------
D000               .00070079
D001               .0059

These dispatchers show little busy time. If dispatchers are busy more than 50 percent of the time, consider starting more dispatchers. You can do so dynamically using the ALTER SYSTEM command. The following example would set the number of TCP/IP dispatchers to 4.

ALTER SYSTEM SET DISPATCHERS="(PRO=TCP)(DIS=4)";

Add one or two more dispatchers and monitor the busy rates of the dispatchers to see if they fall below 50 percent.

5.5.4. Measuring How Long Users Are Waiting for Dispatchers

To measure how long users are waiting for the dispatchers to execute their request, look at the combined V$QUEUE and V$DISPATCHER views. The following listing shows an example:

SELECT decode(sum(totalq),0,'No Responses',
               Sum(wait)/sum(totalq)) "Average Wait time"
FROM V$QUEUE q, V$DISPATCHER d
WHERE q.type = 'DISPATCHER'
AND q.paddr = d.paddr;

Average Wait Time
------------------
  .0413

In this example, the average wait time for dispatchers is a little more than 0.04 second. Monitor this measure over time. If the number increases consistently, consider adding more dispatchers. You can do so dynamically using the ALTER SYSTEM command.

5.5.5. Determining Whether You Have Enough Shared Servers

You can monitor shared servers by using the V$SHARED_SERVER and V$QUEUE dictionary views. The shared servers are responsible for executing client requests and placing the requests in the appropriate dispatcher response queue.

The measurement you are most interested in is how long client requests are waiting in the request queue. The longer the request remains in the queue, the longer the client will wait for a response. The following statement tells you how long requests are waiting in the queue:

Select decode(totalq,0,'No Requests') "Wait Time",
Wait/totalq || ' hundredths of seconds'
"Average Wait time per request"
from V$QUEUE
where type = 'COMMON'

Wait Time Average Wait time per request
--------  -----------------------------------
.023132   hundredths of a second

The average wait time in the request queue is a little more than 0.02 second. Monitor this measure over time. If the number increases consistently, consider adding more shared servers. You can do so dynamically using the ALTER SYSTEM command.

The following example shows how you would query V$SHARED_SERVER:

SQL> select name,status,requests,messages,bytes,breaks from v$shared_server;
NAME STATUS             REQUESTS   MESSAGES      BYTES     BUSY
---- ---------------- ---------- ---------- ---------- ----------
S000 WAIT(COMMON)            300        212    1024963     210433

In this example, one shared server is configured. We can see how many requests and messages the server has handled in the REQUESTS and MESSAGES columns and the total size of the messages in the BYTES column. We can also see many milliseconds of time this shared server has been actively processing requests since the last instance startup: it has been busy for roughly 2,100 seconds since instance startup.