Oracle users can connect to DB instances in one of two ways: as "dedicated server" processes or "shared server" sessions. By default, users connect as "dedicated server" processes. These processes are created every time a user connection takes place and are deleted when the user connection ends. Alternatively, the Oracle instance can be configured to support "shared" servers. In this configuration, a number of persistent server-side processes are "shared" among incoming client connections. With shared servers, a dispatcher process routes clients to available servers.

The decision to configure an Oracle instance to support shared servers is typically determined by one or both of the following:

  • A high number of user sessions creates a memory constraint on the server.
  • Sessions connect and disconnect so often that the setup and tear-down of dedicated process states creates a CPU constraint, resulting in performance issues.

Disadvantages of shared servers can include:

  • The overhead to manage them can be CPU intensive.
  • Sessions can incur performance issues as they wait for shared servers to become available.
  • Tuning and administration are more complicated than with dedicated servers.

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

A common approach is to use a mix of shared and dedicated servers:

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

Important

The values listed here are provided as a baseline for enabling shared servers with the specified instance size. Oracle Database Aadministrators should apply parameter group settings that optimize memory for their particular use case scenario. For information about Oracle Database memory allocation, see Database Memory Allocation. For information about tuning System Global Area memory, see Tuning the System Global Area.

The following example parameter group settings were used when enabling shared servers on a db.r3.large instance and should provide a generic starting point for this purpose:

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, we recommend starting a test instance with the default parameter group:

SQL> show parameter sessions

2428

3.    Then 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 ensure 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

To view the parameter group settings that are applied to a running Oracle instance, 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');

For more information about Oracle database initialization parameters, see Configuring Oracle Database for Shared Server. For more information about creating a new DB instance parameter group, see Working with DB Parameter Groups.

Note: 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);

Considerations

  • Use of shared servers incurs CPU overhead and may cause performance issues.
  • If SGA memory is insufficient to accommodate shared servers and dispatchers, "large pool free" errors will start appearing in the alert log and trace files of the Oracle instance.
  • Use of dynamic sga SGA parameters (sga_target, sga_max, memory_target, memory_max_target) is convenient, but with shared servers, dynamic reallocation of SGA memory might occur more frequently, causing performance issues.
  • On a database reboot or failover, an application connection storm can swamp the dispatchers if the DISPATCHERS parameter is not set high enough relative to connection throughput. When this happens, the connections will start coming through as DEDICATED until the max number of processes is reached and subsequent connection attempts fail with the error ORA-00020 "Maximum number of processes exceeded". The storm may be so intense that it becomes impractical to raise DISPATCHERS high enough to handle it. In this case the solution is to implement a client-side protocol where the clients implement random delays before connecting over a suitable time period.
  • Running batch processes, long running queries, heavy loads, and long-running DBA tasks on shared servers can cause other jobs to queue up, leading to performance issues. Instead, big jobs should run on dedicated servers.

To enable both dedicated and shared server access to the same Oracle instance:

Dual tnsnames.ora entries can allow for both types of connections to the same DB instance. For 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)

    )

)

Amazon RDS for Oracle, shared server, dedicated server, processes, parameter group, performance, DB instance, dispatcher, ORA-00020, sessions


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