• Describe how to manage Oracle Shared Server using the listener utility and dynamic views.
• Identify the steps necessary to tune Oracle Shared Server.
Focused Explanation
Managing Oracle Shared Server
You can manage the Oracle Shared Server using the listener utility and various dynamic views. To manage the Oracle Shared Server, it is necessary to know how to view the information about the Oracle Shared Server. Therefore, Oracle provides the listener utility to collect information about the Oracle Shared Server connections.
Displaying Information Using the Listener Utility
The lsnrctl command-line listener utility is used to display the information about the dispatcher processes. The listener utility traces all the information about the current connection load for all the dispatchers. To view information about dispatchers, you use the lsnctrl services command at the command prompt.
Oracle also provides dynamic performance views to gather information about the Oracle Shared Server configuration and performance.
Displaying Information Using Dynamic Performance Views
Oracle offers dynamic performance views, which provide system performance information about Oracle Shared Server. The dynamic performance views are also referred to as V$ views. These views contain information about the number of dispatchers, activity among shared servers, and activity in the request and response queues. The dynamic performance views also provide information about the clients that are connected via shared server connections.
The various dynamic performance views used to obtain information about Oracle Shared Server are:
• V$DISPATCHER – Contains information about dispatcher activity and the number of connections that are currently being handled by each dispatcher. The V$DISPATCHER dictionary view also provides the information about the total number of connections that each dispatcher has handled since startup.
• V$DISPATCHER_CONFIG– Contains configuration information about each dispatcher, such as the protocol and the listener address of the dispatcher. This view also contains information about the service names for connections and sessions.
• V$DISPATCHER_RATE– Displays statistics for the dispatchers, such as the maximum number of inbound and outbound connections and the average rate of bytes processed per client
connection.
•
V$QUEUE– Contains information about the request and response queues, such as waiting time of requests in the queue.•
V$CIRCUIT– Displays information about Oracle Shared Server virtual circuits, such as the current status of the client connection and the amount of information exchanged between the client and the dispatcher.• V$SHARED_SERVER – Contains information about the shared server processes, such as the number of requests and the information processed by the shared servers.
• V$SHARED_SERVER_MONITER– Contains information that helps in tuning the Oracle Shared Server. The V$SHARED_SERVER_MONITER view includes the information about the maximum number of concurrent connections and the total number of servers started since startup.
• V$SESSION– Contains information about client sessions, such as user name, session status, client session address and the operating system user name.
Tuning the Shared Server
You need to examine the performance of the dispatcher and the shared server processes before tuning the Oracle Shared Server. You need to ensure that there are enough dispatchers to respond to the client requests. For tuning the shared server, many shared server processes should be provided to process client requests. You need to configure the Large Pool SGA memory area to store the information for a dedicated server. The Large Pool enhances the performance of the memory area of the Shared Pool.
To tune the shared server, you need to perform the following tasks:
• Configure the Large Pool.
• Set the Large Pool to an appropriate size.
• Monitor the performance of the dispatcher and shared servers.
Configuring the Large Pool
You can set the LARGE_POOL_SIZEparameter in the init.orafile to configure the Large Pool. The value of theLARGE_POOL_SIZEparameter ranges from 300 KB to 2 GB. By default, Oracle sets the value of theLARGE_POOL_SIZEparameter to 250 KB per session for each shared server when specifying the DISPATCHERparameter. If a Large Pool is not configured, Oracle places the UGA in the Shared Pool, which affects the performance of the Shared Pool.
For example, you can set the LARGE_POOL_SIZE parameter in the init.ora file as 100M using this entry:
LARGE_POOL_SIZE=100M
To modify the LARGE_POOL_SIZE parameter at run-time, you need to use the ALTER SYSTEM statement. For example:
ALTER SYSTEM SET LARGE_POOL_SIZE=50M;
Setting the Large Pool to an Appropriate Size
Each shared server connection in the Large Pool needs memory between 1 MB and 3 MB. The Large Pool should be large enough to store information for all the shared server connections. The V$SESSTAT view enables you to determine the memory space used by the shared server connections. The
V$SESSTAT view also contains information about the memory used per user. A client encounters a connection error if the size of the Large Pool area is too small. If the value of the LARGE_POOL_SIZE parameter is less than the required size, then you can modify the LARGE_POOL_SIZE parameter using the ALTER SYSTEM statement.
Monitoring the Performance of the Dispatcher and Shared Servers
To monitor the performance of the dispatchers and the shared servers, you should perform the following tasks:
• Determine the number of dispatchers and shared servers.
• Measure the wait time for dispatchers.
Determining the Number of Dispatchers and Shared Servers
The V$DISPATCHER view displays information about the dispatcher processes. You can use this view to determine whether or not it would be advantageous to initiate more dispatcher processes. For example, the following query calculates the percentage of time for which a dispatcher is busy:
SELECT name,(busy/(busy+idle))*100 "Dispatcher % busy Rate"
FROM V$DISPATCHER;
The V$SHARED_SERVER and V$QUEUE dictionary views are used to monitor shared servers. The shared servers execute client requests and place the client requests in the appropriate dispatcher response queue. For example, the following query shows how long the client requests are waiting in the request 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';
Measuring the Wait Time for Dispatchers
You can use the V$DISPATCHER and V$QUEUE views to determine the time taken by the dispatcher to execute a request. For example, the following query shows the average waiting time for the dispatcher:
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;