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

Last updated: 2020-02-04

How do I configure Amazon Relational Database Service (Amazon RDS) Oracle DB instances to work with shared servers?

Short Description

Oracle users can connect to RDS DB instances using either dedicated or shared server processes. Before using shared servers, consider the following:

  • Using shared servers incurs CPU overhead, which might cause performance issues.
  • Using a shared server means that the UGA allocation is allocated inside a large pool. Be sure that you have sufficient free space inside 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.
  • Using shared servers might cause more frequent dynamic reallocation of SGA memory, which can cause performance issues.
  • During database reboot or failover, a large increase in application connections can overwhelm the dispatchers if the DISPATCHERS parameter isn't set high enough relative to connection throughput.
  • Running batch processes, long-running queries, heavy loads, or long-running DBA tasks on shared servers can cause other jobs to queue up, which can cause performance issues. Use dedicated servers for large jobs.

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

Resolution

To balance the benefits and limitations of using shared servers:

  • Use shared servers for a high number of OLTP sessions that connect and disconnect often and perform light operations.
  • Use dedicated servers for long-running batch operations and heavy administrative tasks such as creating indexes.

Note: The following examples are provided as a baseline for enabling shared servers with the specified instance size (db.r3.large instance). Administrators should apply parameter group settings that optimize memory based on their use cases. For more information, see Oracle on Amazon RDS.

1.    Modify the custom parameter group to set the following parameters to the maximum permitted value or to a value that meets your use case:

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

2.    Log in to the instance, and then view the default value of SESSIONS for the host size. If you’re not using the default settings, test the instance with the default parameter group:

SQL> show parameter sessions
2428

3.    Set SHARED_SERVERS and MAX_SHARED_SERVERS to 10% of that value:

sessions=2428
shared_servers=243
max_shared_servers=243

4.    Set LARGE_POOL_SIZE equal to SHARED_SERVERS value * 1 MB (243 * 1048576 bytes in this case).

large_pool_size= 254803968

5.    Query v$sgastat for large_pool_size '‘free memory'’ to be sure that large pool is adequately sized.

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.    Run the following SQL query from your Oracle instance to see if sessions are connecting as shared:

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

To enable dedicated and shared server access to the same Oracle instance, use dual tnsnames.ora entries, such as in the following example:

  # 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)
      )
  )