Monitoring Shared Server in Oracle 10g
After completing this topic, you should be able to recognize how to monitor Shared Server.
1. Adjusting and verifying Shared Server setup
Besides having to edit the
DISPATCHERS initialization parameter for your instance, you may need to adjust other parameters when configuring the Shared Server architecture.
The parameters you may need to fine-tune are
LARGE_POOL_SIZEparameter sets the size of the large pool in bytes. The large pool is used by Oracle Shared Server to store the session information that is usually stored in the PGA in a dedicated server session.
SESSIONSparameter sets the maximum number of sessions that can be created in the system. This may need to be adjusted for Oracle Shared Server, because your system can now service more sessions.
PROCESSESparameter controls the number of server-side processes.
LOCAL_LISTENERparameter defines the port and protocol used by the listeners. If your listener is not using TCP/IP on port 1521, or if you have multiple listeners, you must configure
LOCAL_LISTENERso the dispatchers can register with the listeners.
If you do not set the
LARGE_POOL_SIZE parameter, Oracle uses the shared pool to store the session information for Oracle Shared Server.
This can adversely affect the performance of PL/SQL, SQL,and other services that rely on the shared pool.
Even if you have configured the large pool, Oracle still allocates about 10 KB of memory from the shared pool for every configured session.
Which of these initialization parameters may need to be adjusted with Oracle Shared Server?
SESSIONS parameters may need to be adjusted with Oracle Shared Server.
Option 1 is correct. The
LARGE_POOL_SIZE parameter provides the size of the large pool in bytes. The large pool is where Oracle Shared Server stores session information.
Option 2 is correct. The
PROCESSES parameter controls the number of server-side processes.
Option 3 is correct. The
SESSIONS parameter sets the maximum number of sessions that can be serviced in the system. This can be adjusted so your system can service more sessions.
Option 4 is incorrect. The
SHARED_MEMORY_ADDRESS specifies the starting address of the System Global Area (SGA) at runtime. It does not need to be adjusted when Oracle Shared Server is used.
When the necessary parameters have been adjusted, you can use Oracle Shared Server.
However, you must start the listener first, and then the database. This is so that the dispatcher can register with the listener immediately.
If you restart the listener later, you should allow one minute for the reregistering of services.
You now want to make sure that the dispatcher has registered with the listener.
lsnrctl services and press Enter.
The registration information tells you that both dispatchers in the Shared Server architecture have registered with the listener.
To verify that your connections are using shared servers, you first make the connections.
Then you query the V$CIRCUIT view to make sure there is an entry for every shared server connection.
This query can also verify that the listener is performing load balancing for incoming connections.
You now want to check how many sessions are using Shared Server.
select dispatcher, circuit, server, status from v$circuit into iSQL*Plus and click Execute.
The results for two sessions display, listing their dispatcher, circuit, and server numbers and server status.
You want to establish that all your connections are being made to a shared server, so that the dispatcher can register immediately with the listener.
Type the command that is needed to verify the Shared Server setup.
lsnrctl services to verify the Shared Server setup.
You now want to verify that your shared server connections are working properly by checking the dispatcher and its status. Type the command that lets you view the dispatcher, the circuits, the server, and server status for each shared server connection.
Which of the following options allows you to complete this task?
- You type
select dispatcher, circuit, server, status from v$circuit;and then click Execute.
- You type
select dispatcher, server, circuit, status from v$circuit;and then click Execute.
select dispatcher, circuit, server, status from v$circuit and click Execute to view dispatcher information and server status for each shared server connection.
2. Data dictionary views
Views in general let you look at many aspects of Oracle Shared Server. Data dictionary views such as V$CIRCUIT are especially useful for focusing on specific elements, and you can create custom views for even narrower focus.
The Data Dictionary views are:
- Information shown in the V$CIRCUIT view comes from the virtual circuits, which are user connections to a database that pass through dispatchers and servers. Entries are created with any shared server connection.
- The V$SHARED_SERVER view provides information on shared server processes.
- The dispatcher is used to hold user processes until a shared server is available to process the user request. Information on these dispatcher processes appears in the V$DISPATCHER view.
- The V$SHARED_SERVER_MONITOR view provides information that helps you tune shared server processes.
- The V$QUEUE view provides information on request and response queries.
- The session information for each current session is listed in the V$SESSION view.
Match each data dictionary view to its functionality.
- Provides information on dispatcher processes
- Provides information that helps you tune shared server processes
- Provides information about virtual circuits
- Provides information on shared server processes
V$DISPATCHER provides information on dispatcher processes. V$SHARED_SERVER_MONITOR provides information that helps you tune shared server processes. V$CIRCUIT provides information about virtual circuits, and V$SHARED_SERVER provides information on shared server processes.
Virtual circuits are user connections to a database that pass through dispatchers and servers.
The dispatcher is used to hold user processes until a shared server is available to process the user request.
You can find information on shared server processes in the V$SHARED_SERVER view.
You can fine-tune shared server processes using the information in the V$SHARED_SERVER_MONITOR view.
When configuring Oracle Shared Server, other parameters besides
DISPATCHERS may require adjustment. These include
PROCESSES , and
LOCAL_LISTENER. With Oracle Shared Server, you start the listener first and then the database, so that the dispatcher can register with the listener immediately. To ensure that registration has taken place, you enter the command
lsnrctl service. You can then verify the connections using the V$CIRCUIT view.
You can find out about other aspects of the Shared Server architecture using data dictionary views such as V$CIRCUIT. Other preset data dictionary views are: V$SHARED_SERVER, V$DISPATCHER, V$SHARED_SERVER_MONITOR, V$QUEUE, and V$SESSION. You can focus your enquiry on selected elements by creating custom views.