Monitoring Shared Server in Oracle 10g

Learning objective

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_SIZE
• SESSIONS
• PROCESSES
• LOCAL_LISTENER

LARGE_POOL_SIZE

The LARGE_POOL_SIZE parameter 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.

SESSIONS

The SESSIONS parameter 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.

PROCESSES

The PROCESSES parameter controls the number of server-side processes.

LOCAL_LISTENER

The LOCAL_LISTENER parameter 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_LISTENER so 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.

Question

Which of these initialization parameters may need to be adjusted with Oracle Shared Server?

Options:

0. LARGE_POOL_SIZE
0. PROCESS ES
0. SESSION S
0. SHARED_MEMORY_ADDRESS

Answer

The LARGE_POOL_SIZE, PROCESS ES, and 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.

You type 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.

You type 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.

Question

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.

Answer

You type lsnrctl services to verify the Shared Server setup.

Question

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?

Options:

0. You type select dispatcher, circuit, server, status from v$circuit; and then click Execute.
0. You type select dispatcher, server, circuit, status from v$circuit; and then click Execute.

Answer

You type 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:

• V$CIRCUIT
• V$SHARED_SERVER
• V$DISPATCHER
• V$SHARED_SERVER_MONITOR
• V$QUEUE
• V$SESSION

V$CIRCUIT

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.

V$SHARED_SERVER

The V$SHARED_SERVER view provides information on shared server processes.

V$DISPATCHER

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.

V$SHARED_SERVER_MONITOR

The V$SHARED_SERVER_MONITOR view provides information that helps you tune shared server processes.

V$QUEUE

The V$QUEUE view provides information on request and response queries.

V$SESSION

The session information for each current session is listed in the V$SESSION view.

Question

Match each data dictionary view to its functionality.

Options:

0. V$CIRCUIT
0. V$DISPATCHER
0. V$SHARED_SERVER
0. V$SHARED_SERVER_MONITOR

Targets:

0. Provides information on dispatcher processes
0. Provides information that helps you tune shared server processes
0. Provides information about virtual circuits
0. Provides information on shared server processes
   
   
   

Answer

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.

Summary

When configuring Oracle Shared Server, other parameters besides DISPATCHERS may require adjustment. These include LARGE_POOL_SIZE, SESSIONS, 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.