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

Oracle users can connect to 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 is not set high enough relative to connection throughput.
  • Running batch processes, long-running queries, heavy loads, and 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 Understanding Shared Server Architecture.

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 and security group settings that optimize memory based on their use cases. For more information, see Oracle on Amazon RDS.

1.   Set DISPATCHERS to the maximum permitted value:

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

2.   Log in to the instance and 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 * 1MB (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)
      )
  )

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2016-11-11

Updated: 2018-05-22