You can also configure the Oracle Shared Server using Enterprise Manager. Once you connect to Enterprise Manager via your web browser, click the Administration tab to display a form that allows you to manage all aspects of your Oracle environment. To modify the initialization parameters for Oracle Shared Server, click the All Initialization Parameters link in the Instance section.
Initially, this form lists only the parameters that are being used for the instance. If youí ve already configured the Oracle Shared Server, you will see the parameters described in the previous section listed with their current values. If you are configuring the Oracle Shared Server for the first time, you will not see these parameters. Click the Show All button to display all the available initialization parameters. You can then scroll down to the initialization parameter that you want to modify and type appropriate values for the parameter.
You can also select only those parameters that you want to modify. Type the partial or full name of the parameter that you want to change in the Filter field and click Go to display only the specific parameters or parameters that match the name entered. Enter the new value for the parameter in the Value field next to each parameter.
It is a good idea to validate the change you are making. To do so, click the Show SQL button to display the actual ALTER SYSTEM command that runs to make the change.
After you make all your changes, you can choose how you want to save them. If you click the Apply button, your changes take effect immediately. The currently running instance is affected by these changes. This is the choice if you want the changes to be effective right away.
If you want to save the changes but not have the changes affect the currently running instance, click the Save To File button. This saves all your currently active initialization parameter values along with your newly modified parameter values to a file. This is the best choice if you are using an Oracle initialization parameter file. You can save a copy of the modifications to a file in the location of your choice and then cut and paste the new values into your initialization file. These changes will then be in force the next time your instance starts. If the Oracle Shared Server parameters were configured dynamically using the ALTER SYSTEM command or at database creation, it isnít necessary to stop and start the server.
When you use the ALTER SYSTEM command to modify the initialization parameters, an additional attribute called SCOPE determines when the change takes effect. The possible settings for the SCOPE attribute are MEMORY, SPFILE, or BOTH. The default setting of the attribute depends on how the database was started.
If your database was started using a server-side spfile, the default action is to modify both the currently running instance and change the SPFILE setting (using the BOTH attribute). The changes are preserved for the next time the database is started using the SPFILE. If the database was started using a parameter file, sometimes called a PFILE, the change affects only the currently running instance (using the MEMORY attribute). If you want to make the changes permanent, you will have to modify the init.ora file resident on the database server. Here is an example of syntax to modify only the currently running instance using the ALTER SYSTEM command:
ALTER SYSTEM SET SHARED_SERVERS = 10 SCOPE=MEMORY;
You can use the lsnrctl command-line listener utility to display information about the dispatcher processes. Remember from the previous section that the Oracle background process PMON registers dispatcher information with the listener. The listener keeps track of the current connection load for all the dispatchers.
Use the lsnrctl services query to view information about dispatchers. The following command shows a listener listening for two TCP/IP dispatchers:
The PADDR column lists the address of the process that owns the queue. This example shows that no items are waiting in the queue because the QUEUED column is zero. We also have not experienced any queue waits because all WAIT values are zero. We would want to make sure that the WAIT column stays close to zero so no processes are waiting in the queues. The TOTALQ column represents the total number of messages that have ever been in the queue.
The V$MTS view has been depreciated and is replaced by the V$SHARED_SERVER_ MONITOR view. V$MTS was the name for this view in Oracle8i.
By default, if Oracle Shared Server is configured, a client is connected to a dispatcher unless the client explicitly requests a dedicated connection. As part of the connection descriptor, the client has to send information requesting a dedicated connection. Clients can request dedicated connections if the names resolution method is localnaming. You cannot use this option when using the hostnaming names resolution method. If localnaming is being used, you can make the necessary changes to the tnsnames.ora file to allow dedicated connections. You can make these changes manually, or you can use Oracle Net Manager.
After you start Oracle Net Manager, follow these steps: 1. Under Service Naming in the left pane, select the service name that you want to modify. 2. Click the Connection Type drop-down list in the Service Identification section and choose Dedicated Server.
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.
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.
If a LARGE_POOL_SIZE is not given, the size of the Large Pool is determined by a number of initialization parameters, including the DISPATCHERS parameter.
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.
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.
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.
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 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.