Configure the Database Instance to Support Shared Server Connections

1. Documentation in Tahiti -> Masters Book List -> Administrator’s Guide -> 5 Managing Processes -> Configuring Oracle Database for Shared Server

2. The objective of SHARED SERVERS configure connections to reduce resource consumption of the database. The example that is put in the documentation is a certain type of DB where orders are taken. We assume we have 1,000 telephone operators taking orders over the phone and recording them in the database. With dedicated connections could have 1,000 sessions in the database, one for each operator. But when you consider that most of the time, talking to the operator (90% time) client instead of putting the data in the database, we can conclude that we need only 10% of the sessions. We could configure shared connections 100 (instead of 1000) and thus releasing the memory 900 unused sessions. This free memory may be useful to us in other areas of the server (more CACHE, another instance of DB, DB outsiders running on the same machine, etc … processes).

We review the documentation specific example, where memory compared various settings: Dedicate, SHARED and DRCP (this will be discussed later). In the example taken into account we have 5000 connections and memory required for each session is 400KB and 4MB per server process. POOL size for DRCP is 100 and we have 100 SHARED SERVERS.

     Dedicate SERVER => 5000 CONN X (400KB / SESS + 4 MB / SERVPROC) = 22GB
     SHARED SERVER => (5000 CONN X 400KB / SESS) + (100 SHAREDSERVERS X 4 MB / SERVPROC) = 2.5GB
     DRCP => 100 POOLSERVERS X (400KB / SESS + 4 MB / SERVPROC) + (5,000 x CONN 35KB / CONN) = 615MB

3. First let’s review the default settings that have to be installed with the DB DBCA.

– We go to the database with SQL * Plus and reviewed the most important parameters of the BD
show parameter Dispatchers
show parameter shared_servers
– We see that we have a dispatcher configured for OCMXDB service (XML DB)
– The shared_servers parameter takes the default value (1), because we defined the parameter Dispatchers
Dispatchers show spparameter
show spparameter shared_servers
– As we have raised the BD with the dispatchers parameter is set to 1 shared_servers
– If we raise the BD without dispatchers or shared_servers necessarily we have to define shared_servers
– Obtain configuration DISPATCHER
SELECT * FROM V$DISPATCHER_CONFIG;

4. We enable shared server connections for OCM service (currently only have it available for OCMXDB).
First we have to define a value for shared_servers recommended. Suppose you have 100 concurrent sessions (representing 100 users), and they are only working on the BD 10% of the time. Then we set up 10 server processes => shared_servers = 10.

– Define the parameter shared_servers
Shared_servers ALTER SYSTEM SET SCOPE = BOTH = 10;
– We reviewed the processes that have been created
SELECT * FROM V $ shared_servers;
– We set up to SHARED SERVERS (generally is limited by the PROCESSES parameter)
ALTER SYSTEM SET max_shared_servers = 100 SCOPE = BOTH;

– We can limit the number of virtual circuits using Dispatchers
– A CIRCUIT represents an area of ​​shared memory that is used to handle requests and responses
– We can limit your number with the parameter CIRCUITS = & gt; It can be used to limit the impact on the SGA
– ALTER SYSTEM SET CIRCUITS = 100 SCOPE = BOTH; – We will not do in this exercise

5. Once we have defined a set of SHARED SERVERS we will set the dispatchers.
An initial value can be obtained diviendo the maximum number of concurrent sessions between the maximum number of connections for each dispatcher. In our machine we have defined a limit of 1,024 connections per process (maximum number of open file descriptors => ulimit -n). If we could get to have 4,000 concurrent sessions, then we define 4000/1024 = 4 (rounding up).

– The calculation we have done is CEIL (USU MAX concurrents / CONN / PROCESS…) = CEIL (4000/1024) = 4
– We enable a new service dispatcher for OCM
– If you do not specify INDEX, the dispatcher with the same is amended DESCRIPTION, ADDRESS, or PROTOCOL
– Use INDEX = 1 to avoid changing the service dispatcher OCMXDB
– We can see that INDEX must each dispatcher at the CONF_INDX field in V $ DISPATCHER_CONFIG
ALTER SYSTEM SET dispatchers = ‘(INDEX = 1) (PROTOCOL = TCP) (dispatchers = 4) (SERVICE = OCM)’ SCOPE = BOTH;
– We found that two processes have been set DISPATCHER:
– · We will see four rows with CONF_INDX = 1
– · We also found the service dispatcher OCMXDB (CONF_INDX = 0)
SELECT * FROM V $ DISPATCHER;

6. We will configure the connection descriptor in the file $ ORACLE_HOME / network / admin / tnsnames.ora to make a test by connecting SHARED SERVERS.

# Add the following entry to the file $ ORACLE_HOME / network / admin / tnsnames.ora
# We can configure with the assistant netmgr very sencillita way (I omit this exercise)
OCMTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = ocm.dbajunior.com) (PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = CMO)
)
)
# We connect using this descriptor
sqlplus system @ ocmtest
# Using another terminal, review the OCM service dispatchers and see if we have connected
# In our example we have connected via D004:
# ·’ll See this line = & gt; “D004” established: 1 refused: 0 current: 1 max: 1022 state: ready
lsnrctl services

We may find that sessions are connected via SHARED SERVERS

– The column SERVER V $ SESSION will be NONE (inactive) or SHARED (active) for sessions using SHARED SERVERS
– · If we launch this consultation session SHARED SERVERS see the SERVER = “SHARED” value
– · If lazamos this query from another session will see the SERVER = “None” for the same SID
SELECT SID, USERNAME, PROGRAM, SERVICE_NAME, SERVER FROM V $ SESSION WHERE IN SERVER (‘SHARED’, ‘none’);
– We can see state of SHARED SERVERS
SELECT * FROM V $ shared_servers;

7. We have the possibility of eliminating the dispatcher process we want. Here’s an example:

– Eliminated the dispatcher D003
ALTER SYSTEM SHUTDOWN IMMEDIATE ‘D003’;
– We check that is eliminated. This operation may take some time, but specify the IMMEDIATE
SELECT NAME, STATUS, ACCEPT FROM V $ DISPATCHER;

8. We reverse all changes.

– We leave only the original dispatcher and resetamos the shared_servers and parameter max_shared_servers
Dispatchers ALTER SYSTEM RESET SCOPE = SPFILE;
ALTER SYSTEM SET dispatchers = ‘(PROTOCOL = TCP) (SERVICE = OCMXDB)’ SCOPE = SPFILE;
ALTER SYSTEM RESET shared_servers SCOPE = SPFILE;
ALTER SYSTEM RESET max_shared_servers SCOPE = SPFILE;
– Restart the instance to get rid of the configuration of the second DISPATCHER
IMMEDIATE SHUTDOWN
STARTUP

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s