How do I configure Amazon RDS for Oracle DB instances to work with shared servers?

Short description

Use either dedicated or shared server processes to connect to the RDS DB instance. Before you use shared servers, check for the following conditions:

• When you use shared servers, this incurs CPU overhead. The CPU overhead might cause performance issues.
• When you use a shared server, this means that the user global area (UGA) allocation is allocated inside a large pool. Make sure that you have sufficient free space inside systems global area (SGA) to accommodate shared servers. Insufficient free space can cause large pool free errors to appear in the instance's alert log and trace files.
• When you use shared servers, this might cause more frequent dynamic reallocation of SGA memory. This dynamic memory relocation can cause performance issues.
• In database reboot or failover, if the DISPATCHERS parameter isn't set to match connection throughput, then an increase in application connections can overwhelm the dispatchers.
• When you run batch processes, long-running queries, heavy loads, or long-running database administrator tasks on shared servers, this can cause other jobs to queue up. These events can also cause performance issues. Use dedicated servers for large jobs.

For more information about Oracle Database shared server and dedicated server architecture, see Understanding Shared Server Architecture on the Oracle website.

Resolution

To balance the benefits and limitations of when you use shared servers:

• Use shared servers for a high number of online transaction processing (OLTP) sessions that frequently connect and disconnect and perform light operations.
• Use dedicated servers for long-running batch operations and heavy administrative tasks. For example, you can create indexes.

Note: The following examples are provided as a baseline for when you turn on shared servers with the specified instance size. Administrators must apply parameter group settings that optimize memory based on their use cases. For more information, see Amazon RDS for Oracle.

1. To modify the custom parameter group to set the following parameters to the maximum permitted value, run the following query:

   dispatchers=(PROTOCOL=TCP)(DISPATCHERS=30)
   max_dispatchers=30

   Or, you can set the parameters in the preceding query to a value that meets your use case.

2. Log in to the instance, and then view the default value of SESSIONS for the host size. If you don't use the default settings, then run the following query to test the instance with the default parameter group:

   SQL> show parameter sessions
   2428

3. To set SHARED_SERVERS and MAX_SHARED_SERVERS to 10% of that value, run the following query:

   sessions=2428
   shared_servers=243
   max_shared_servers=243

4. To set LARGE_POOL_SIZE equal to SHARED_SERVERS value * 1 MB, run the following query:

   large_pool_size= 254803968

5. To be sure that a large pool is adequately sized, run the following v$sgastat query for large_pool_size free memory:

   SQL> select name, pool, bytes/1024/1024 megs from v$sgastat where name='free memory' and pool='large pool';
   Name           POOL        Megs
   -------------- ----------- -------
   free memory    large pool  243

6. View the parameter group settings that are applied to a running Oracle instance, and then run the following SQL query from the instance:

   select name, value from v$parameter where name in ('processes', 'sessions', 'shared_servers', 'dispatchers', 'memory_target', 'memory_max_target', 'large_pool_size');

7. To see if sessions are connecting as shared, run the following SQL query from your Oracle instance:

   SQL> select decode(server,'NONE','SHARED',server), count(*) from v$session
   group by decode(server,'NONE','SHARED',server);

   To activate both dedicated and shared server access to the same Oracle instance, run the following dual tnsnames.ora entries:

   # make the default shared
    
     dbname = 
     (DESCRIPTION= 
         (ADDRESS_LIST= 
             (ADDRESS=(PROTOCOL=TCP)(HOST=dbname.endpoint.amazonaws.com)(PORT=1521))
         )
         (CONNECT_DATA=
             (SID=dbname)
         )
     )
   
     # use the dedicated one for batch processes and dba tasks, such as creating indexes
   
     dbname_d=
     (DESCRIPTION=
         (ADDRESS_LIST=
             (ADDRESS=(PROTOCOL=TCP)(HOST=dbname.endpoint.amazonaws.com)(PORT=1521))
         )
         (CONNECT_DATA=
             (SID=dbname)
             (SERVER=DEDICATED)
         )
   

Related information

Local naming parameters in the tnsnames.ora file on the Oracle website

Working with DB parameter groups in a DB instance

Viewing parameter values for a DB parameter group

Rebooting a DB Instance