5 Managing Processes

Dedicated Server Processes

Figure 5-1, "Oracle Database Dedicated Server Processes" illustrates how dedicated server processes work. In this diagram two user processes are connected to the database through dedicated server processes.

In general, it is better to be connected through a dispatcher and use a shared server process. This is illustrated in Figure 5-2, "Oracle Database Shared Server Processes". A shared server process can be more efficient because it keeps the number of processes required for the running instance low.

In the following situations, however, users and administrators should explicitly connect to an instance using a dedicated server process:

• To submit a batch job (for example, when a job can allow little or no idle time for the server process)

• To use Recovery Manager (RMAN) to back up, restore, or recover a database

To request a dedicated server connection when Oracle Database is configured for shared server, users must connect using a net service name that is configured to use a dedicated server. Specifically, the net service name value should include the SERVER=DEDICATED clause in the connect descriptor.

Figure 5-1 Oracle Database Dedicated Server Processes

Description of "Figure 5-1 Oracle Database Dedicated Server Processes"

Shared Server Processes

Consider an order entry system with dedicated server processes. A customer phones the order desk and places an order, and the clerk taking the call enters the order into the database. For most of the transaction, the clerk is on the telephone talking to the customer. A server process is not needed during this time, so the server process dedicated to the clerk's user process remains idle. The system is slower for other clerks entering orders, because the idle server process is holding system resources.

Shared server architecture eliminates the need for a dedicated server process for each connection (see Figure 5-2).

Figure 5-2 Oracle Database Shared Server Processes

Description of "Figure 5-2 Oracle Database Shared Server Processes"

In a shared server configuration, client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.

An idle shared server process picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. This approach enables a small pool of server processes to serve a large number of clients. A significant advantage of shared server architecture over the dedicated server model is the reduction of system resources, enabling the support of an increased number of users.

For even better resource management, shared server can be configured for connection pooling. Connection pooling lets a dispatcher support more users by enabling the database server to time out protocol connections and to use those connections to service an active session. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.

Shared server architecture requires Oracle Net Services. User processes targeting the shared server must connect through Oracle Net Services, even if they are on the same system as the Oracle Database instance.

Comparing DRCP to Dedicated Server and Shared Server

Table 5-1 lists the differences between dedicated server, shared server, and database resident connection pooling.

Example of Memory Usage for Dedicated Server, Shared Server, and Database Resident Connection Pooling

Consider an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.

If there are 5000 client connections, the memory used by each configuration is as follows:

• Dedicated Server

  Memory used = 5000 X (400 KB + 4 MB) = 22 GB

• Shared Server

  Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB

  Out of the 2.5 GB, 2 GB is allocated from the SGA.

• Database Resident Connection Pooling

  Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB

Restrictions on Using Database Resident Connection Pooling

You cannot perform the following activities when connected with pooled servers:

• Shut down the database

• Stop the database resident connection pool

• Change the password for the connected user

• Use shared database links to connect to a database resident connection pool

• Use Advanced Security Option (ASO) options such as encryption, certificates, and so on

• Use migratable sessions on the server side directly by using the OCI_MIGRATE option or indirectly through OCIConnectionPool

DDL statements that pertain to database users in the pool need to be performed carefully, as the pre-DDL sessions in the pool can still be given to clients post-DDL. For example, while dropping users, ensure that there are no sessions of that user in the pool and no connections to the Broker that were authenticated as that user.

Sessions with explicit roles enabled, that are released to the pool, can be later handed out to connections (of the same user) that need the default logon role. Avoid releasing sessions with explicit roles, and instead terminate them.

Initialization Parameters for Shared Server

The following initialization parameters control shared server operation:

• SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.

• MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.

• SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.

• DISPATCHERS: Configures dispatcher processes in the shared server architecture.

• MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.

• CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.

Memory Management for Shared Server

Shared server requires some user global area (UGA) in either the shared pool or large pool. For installations with a small number of simultaneous sessions, the default sizes for these system global area (SGA) components are generally sufficient. However, if you expect a large number of sessions for your installation, you may have to tune memory to support shared server.

See the "Configuring and Using Memory" section of Oracle Database Performance Tuning Guide for guidelines.

Enabling Shared Server

Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured. Dispatchers are discussed in "Configuring Dispatchers".

Shared server can be started dynamically by setting the SHARED_SERVERS parameter to a nonzero value with the ALTER SYSTEM statement, or SHARED_SERVERS can be included at database startup in the initialization parameter file. If SHARED_SERVERS is not included in the initialization parameter file, or is included but is set to 0, then shared server is not enabled at database startup.

ALTER SYSTEM SET SHARED_SERVERS = 5;

Configuring Dispatchers

The DISPATCHERS initialization parameter configures dispatcher processes in the shared server architecture. At least one dispatcher process is required for shared server to work.If you do not specify a dispatcher, but you enable shared server by setting SHARED_SERVER to a nonzero value, then by default Oracle Database creates one dispatcher for the TCP protocol. The equivalent DISPATCHERS explicit setting of the initialization parameter for this configuration is:

dispatchers="(PROTOCOL=tcp)"

You can configure more dispatchers, using the DISPATCHERS initialization parameter, if either of the following conditions apply:

• You must configure a protocol other than TCP/IP. You configure a protocol address with one of the following attributes of the DISPATCHERS parameter:

  • ADDRESS

  • DESCRIPTION

  • PROTOCOL

• You want to configure one or more of the optional dispatcher attributes:

  • DISPATCHERS

  • CONNECTIONS

  • SESSIONS

  • TICKS

  • LISTENER

  • MULTIPLEX

  • POOL

  • SERVICE

    Note:

    Database Configuration Assistant helps you configure this parameter.

(DESCRIPTION=(ADDRESS=...))

(PROTOCOL=tcp) 

Number of dispatchers =
   CEIL ( max. concurrent sessions / connections for each dispatcher )

DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT=tcps)(DISP=3)'

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"

DISPATCHERS='(PROT=tcp)(DISP=2)', '(PROT=tcps)(DISP=2)'

ALTER SYSTEM SET DISPATCHERS = '(INDEX=0)(DISP=3)', '(INDEX=1)(DISP=1)';

ALTER SYSTEM SET DISPATCHERS = '(PROT=tcp)(DISP=3)', '(PROT=tcps)(DISP=1)';

ALTER SYSTEM SET DISPATCHERS = '(INDEX=2)(PROT=tcp)(POOL=on)';

SELECT NAME, NETWORK FROM V$DISPATCHER;

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

Disabling Shared Server

You disable shared server by setting SHARED_SERVERS to 0. You can do this dynamically with the ALTER SYSTEM statement. When you disable shared server, no new clients can connect in shared mode. However, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of SHARED_SERVERS or the value of the MAX_SHARED_SERVERS parameter, whichever is smaller. If both SHARED_SERVERS and MAX_SHARED_SERVERS are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS or MAX_SHARED_SERVERS is raised again.

To terminate dispatchers once all shared server clients disconnect, enter this statement:

ALTER SYSTEM SET DISPATCHERS = '';

Shared Server Data Dictionary Views

The following views are useful for obtaining information about your shared server configuration and for monitoring performance.

Enabling Database Resident Connection Pooling

Oracle Database includes a default connection pool called SYS_DEFAULT_CONNECTION_POOL. By default, this pool is created, but not started. To enable database resident connection pooling, you must explicitly start the connection pool.

To enable database resident connection pooling:

1. Start the database resident connection pool, as described in "Starting the Database Resident Connection Pool".

2. Route the client connection requests to the connection pool, as described in "Routing Client Connection Requests to the Connection Pool".

Starting the Database Resident Connection Pool

To start the connection pool, use the following steps:

1. Start SQL*Plus and connect to the database as the SYS user.

2. Issue the following command:

   SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
   

Once started, the connection pool remains in this state until it is explicitly stopped. The connection pool is automatically restarted when the database instance is restarted if the pool was active at the time of instance shutdown.

In an Oracle Real Application Clusters (Oracle RAC) environment, you can use any instance to manage the connection pool. Any changes you make to the pool configuration are applicable on all Oracle RAC instances.

Routing Client Connection Requests to the Connection Pool

In the client application, the connect string must specify the connect type as POOLED.

The following example shows an easy connect string that enables clients to connect to a database resident connection pool:

examplehost.company.com:1521/books.company.com:POOLED

The following example shows a TNS connect descriptor that enables clients to connect to a database resident connection pool:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myhost)
       (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales)
       (SERVER=POOLED)))

Disabling Database Resident Connection Pooling

To disable database resident connection pooling, you must explicitly stop the connection pool. Use the following steps:

1. Start SQL*Plus and connect to the database as the SYS user.

2. Issue the following command:

   SQL> EXECUTE DBMS_CONNECTION_POOL.STOP_POOL();
   

   See Also:

   Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_CONNECTION_POOL package.

Configuring the Connection Pool for Database Resident Connection Pooling

The connection pool is configured using default parameter values. You can use the procedures in the DBMS_CONNECTION_POOL package to configure the connection pool according to your usage. In an Oracle Real Application Clusters (Oracle RAC) environment, the configuration parameters are applicable to each Oracle RAC instance.

Table 5-2 lists the parameters that you can configure for the connection pool.

Using the CONFIGURE_POOL Procedure

The CONFIGURE_POOL procedure of the DBMS_CONNECTION_POOL package enables you to configure the connection pool with advanced options. This procedure is usually used when you must modify all the parameters of the connection pool.

Using the ALTER_PARAM Procedure

The ALTER_PARAM procedure of the DBMS_CONNECTION_POOL package enables you to alter a specific configuration parameter without affecting other parameters.For example, the following command changes the minimum number of pooled servers used:

SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','10');

The following example, changes the maximum number of connections that each connection broker can handle to 50000.

SQL> EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXCONN_CBROK','50000');

Before you execute this command, ensure that the maximum number of connections allowed by the platform on which your database is installed is not less than the value you set for MAXCONN_CBROK.

For example, in Linux, the following entry in the /etc/security/limits.conf file indicates that the maximum number of connections allowed for the user test_user is 30000.

test_user HARD NOFILE 30000

To set the maximum number of connections that each connection broker can allow to 50000, first change the value in the limits.conf file to a value not less than 50000.

Restoring the Connection Pool Default Settings

If you have made changes to the connection pool parameters, but you want to revert to the default pool settings, use the RESTORE_DEFAULT procedure of the DBMS_CONNECTION_POOL package. The command to restore the connection pool to its default settings is:

SQL> EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS();

Data Dictionary Views for Database Resident Connection Pooling

Table 5-3 lists the data dictionary views that provide information about database resident connection pooling. Use these views to obtain information about your connection pool and to monitor the performance of database resident connection pooling.

About Parallel Execution Servers

When an instance starts up, Oracle Database creates a pool of parallel execution servers which are available for any parallel operation. A process called the parallel execution coordinator dispatches the execution of a pool of parallel execution servers and coordinates the sending of results from all of these parallel execution servers back to the user.

The parallel execution servers are enabled by default, because by default the value for PARALLEL_MAX_SERVERS initialization parameter is set >0. The processes are available for use by the various Oracle Database features that are capable of exploiting parallelism. Related initialization parameters are tuned by the database for the majority of users, but you can alter them as needed to suit your environment. For ease of tuning, some parameters can be altered dynamically.

Parallelism can be used by several features, including transaction recovery, replication, and SQL execution. In the case of parallel SQL execution, the topic discussed in this book, parallel server processes remain associated with a statement throughout its execution phase. When the statement is completely processed, these processes become available to process other statements.

Altering Parallel Execution for a Session

You control parallel SQL execution for a session using the ALTER SESSION statement.

ALTER SESSION DISABLE PARALLEL DDL;

ALTER SESSION ENABLE PARALLEL DML;

ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;

About External Procedures

External procedures are procedures that are written in C, C++, Java, or other language, compiled and stored outside the database, and then called by user sessions. For example, a PL/SQL program unit could call one or more C routines that are required to perform special-purpose processing.

These callable routines are stored in a dynamic link library (DLL), or a libunit in the case of a Java class method, and are registered with the base language. Oracle Database provides a special-purpose interface, the call specification (call spec), that enables users to call external procedures in other languages.

When a user session calls an external procedure, the database starts an external procedure agent on the database host computer. The default name of the agent is extproc. Each session has its own dedicated agent. When a session terminates, the database terminates its agent.

User applications pass to the external procedure agent the name of the DLL or libunit, the name of the external procedure, and any relevant parameters. The external procedure agent then loads the DLL or libunit, runs the external procedure, and passes back to the application any values returned by the external procedure.

DBA Tasks to Enable External Procedure Calls

Enabling external procedure calls may involve the following DBA tasks:

• Configuring the listener to start the extproc agent

  By default, the database starts the extproc process. Under the following circumstances, you must change this default configuration so that the listener starts the extproc process:

  • You want to use a multithreaded extproc agent

  • The database is running in shared server mode on Windows

  • An AGENT clause in the LIBRARY specification or an AGENT IN clause in the PROCEDURE or FUNCTION specification redirects external procedures to a different extproc agent

  Instructions for changing the default configuration are found in the subsection entitled "Set Up the Environment" in the chapter "Developing Applications with Multiple Programming Languages" in Oracle Database Advanced Application Developer's Guide.

• Managing libraries or granting privileges related to managing libraries

  The database requires DLLs to be accessed through a schema object called a library. For security purposes, by default, only users with the DBA role can create and manage libraries. Therefore, you may be asked to:

  • Use the CREATE LIBRARY statement to create the library objects that the developers need.

  • Grant the following privileges to developers: CREATE LIBRARY, CREATE ANY LIBRARY, ALTER ANY LIBRARY, EXECUTE ANY LIBRARY, and EXECUTE ON library_name.

  Only make an explicit grant of these privileges to trusted users, and never to the PUBLIC role. If you plan to create PL/SQL interfaces to libraries, then only grant the EXECUTE privilege to the PL/SQL interface. Do not grant EXECUTE on the underlying library. You must have the EXECUTE object privilege on the library to create the PL/SQL interface. However, users have this privilege automatically in their own schemas. Explicit grants of EXECUTE object privilege on a library are rarely required.

Identifying Which Session to Terminate

To identify which session to terminate, specify the session index number and serial number. To identify the system identifier (SID) and serial number of a session, query the V$SESSION dynamic performance view. For example, the following query identifies all sessions for the user jward:

SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'JWARD';

SID    SERIAL#    STATUS
-----  ---------  --------
    7         15  ACTIVE 
   12         63  INACTIVE

A session is ACTIVE when it is making a SQL call to Oracle Database. A session is INACTIVE if it is not making a SQL call to the database.

Terminating an Active Session

If a user session is processing a transaction (ACTIVE status) when you terminate the session, the transaction is rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the database, Oracle Database returns the following message:

ORA-01012: not logged on

An active session cannot be interrupted when it is performing network I/O or rolling back a transaction. Such a session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated. If the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been marked to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of KILLED and a server that is something other than PSEUDO.

Terminating an Inactive Session

If the session is not making a SQL call to Oracle Database (is INACTIVE) when it is terminated, the ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, the STATUS of the session in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

In the following example, an inactive session is terminated. First, V$SESSION is queried to identify the SID and SERIAL# of the session, and then the session is terminated.

SELECT SID,SERIAL#,STATUS,SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  INACTIVE   DEDICATED
   12        63  INACTIVE   DEDICATED
2 rows selected.

ALTER SYSTEM KILL SESSION '7,15';
Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  KILLED     PSEUDO
   12        63  INACTIVE   DEDICATED
2 rows selected.