A dedicated server process services only one user process.

Figure 3-1 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 3-2. 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 3-1 Oracle Database Dedicated Server Processes

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

A shared server process can service multiple user 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 3-2).

Figure 3-2 Oracle Database Shared Server Processes

Description of "Figure 3-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 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.

Understand the differences between dedicated server, shared server, and database resident connection pooling.

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

A set of initialization parameters control shared server operation.

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.

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.

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.

• About Determining a Value for SHARED_SERVERS
  The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.
• Decreasing the Number of Shared Server Processes
  You can decrease the minimum number of shared servers that must be kept active by dynamically setting the SHARED_SERVERS parameter to a lower value. Thereafter, until the number of shared servers is decreased to the value of the SHARED_SERVERS parameter, any shared servers that become inactive are marked by PMON for termination.
• Limiting the Number of Shared Server Processes
  The MAX_SHARED_SERVERS initialization parameter specifies the maximum number of shared servers that can be automatically created by PMON. It has no default value.
• Limiting the Number of Shared Server Sessions
  The SHARED_SERVER_SESSIONS initialization parameter specifies the maximum number of concurrent shared server user sessions.
• Protecting Shared Memory
  The CIRCUITS initialization parameter sets a maximum limit on the number of virtual circuits that can be created in shared memory. This parameter has no default. If it is not specified, then the system can create circuits as needed, limited by the DISPATCHERS initialization parameter and system resources.

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.

dispatchers="(PROTOCOL=tcp)"

• DISPATCHERS Initialization Parameter Attributes
  You can set several attributes for the DISPATCHERS initialization parameter.
• Determining the Number of Dispatchers
  Once you know the number of possible connections for each process for the operating system, calculate the initial number of dispatchers to create during instance startup, for each network protocol.
• Setting the Initial Number of Dispatchers
  You can specify multiple dispatcher configurations by setting DISPATCHERS to a comma separated list of strings, or by specifying multiple DISPATCHERS initialization parameters in the initialization parameter file.
• Altering the Number of Dispatchers
  You can control the number of dispatcher processes in the instance. Unlike the number of shared servers, the number of dispatchers does not change automatically. You change the number of dispatchers explicitly with the ALTER SYSTEM statement. You can increase the number of dispatchers to more than the limit specified by the MAX_DISPATCHERS parameter.
• Shutting Down Specific Dispatcher Processes
  With the ALTER SYSTEM SET DISPATCHERS statement, you leave it up to the database to determine which dispatchers to shut down to reduce the number of dispatchers. Alternatively, it is possible to shut down specific dispatcher processes.

You disable shared server by setting the SHARED_SERVERS initialization parameter to 0. You can do this dynamically with the ALTER SYSTEM statement.

ALTER SYSTEM SET DISPATCHERS = '';

You can query data dictionary views for information about your shared server configuration and to monitor performance.

Oracle Database includes a default connection pool called SYS_DEFAULT_CONNECTION_POOL. This connection pool must be started to enable database resident connection pooling.

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

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

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.

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.

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

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

test_user HARD NOFILE 30000

SQL> EXECUTE DBMS_CONNECTION_POOL.RESTORE_DEFAULTS();

• Configuration Parameters for Database Resident Connection Pooling
  You can specify parameters for subprograms in the DBMS_CONNECTION_POOL package to configure database resident connection pooling.

You can query data dictionary views to obtain information about your connection pool and to monitor the performance of database resident connection pooling.

The execution of many SQL statements can be parallelized. The degree of parallelism is the number of parallel execution servers that can be associated with a single operation.

The degree of parallelism is determined by any of the following:

• A PARALLEL clause in a statement

• For objects referred to in a query, the PARALLEL clause that was used when the object was created or altered

• A parallel hint inserted into the statement

• A default determined by the database

An example of using parallel SQL execution is contained in "Parallelizing Table Creation".

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 the PARALLEL_MAX_SERVERS initialization parameter value is set to greater than 0 by default. 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 execution 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.

• Disabling Parallel SQL Execution
  You disable parallel SQL execution with an ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY statement. All subsequent DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER), or query (SELECT) operations are executed serially after such a statement is issued. They will be executed serially regardless of any parallel attribute associated with the table or indexes involved. However, statements with a PARALLEL hint override the session settings.
• Enabling Parallel SQL Execution
  You enable parallel SQL execution with an ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY statement. Subsequently, when a PARALLEL clause or parallel hint is associated with a statement, those DML, DDL, or query statements will execute in parallel. By default, parallel execution is enabled for DDL and query statements.
• Forcing Parallel SQL Execution
  You can force parallel execution of all subsequent DML, DDL, or query statements for which parallelization is possible with the ALTER SESSION FORCE PARALLEL DML|DDL|QUERY statement.

External procedures are procedures that are written in a programming language such as C, C++, or Java, compiled, and stored outside of the database, and then called by user sessions. For example, a PL/SQL program unit can 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.

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. Optionally, you can create a credential so that the agent runs as a particular operating system user. 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.

To enable external procedure calls, you must modify the listener and manage libraries.

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 in Oracle Database Development Guide.

• Managing libraries or granting privileges related to managing libraries

  The database requires DLL statements 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:

  • Create a directory object using the CREATE DIRECTORY statement for the location of the library. After the directory object is created, a CREATE LIBRARY statement can specify the directory object for the location of the library.

  • Create a credential using the DBMS_CREDENTIAL.CREATE_CREDENTIAL PL/SQL procedure. After the credential is created, a CREATE LIBRARY statement can associate the credential with a library to run the extproc agent as a particular operating system user.

  • 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, EXECUTE ON library_name, and EXECUTE ON directory_object.

    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.

To identify which session to terminate, specify the session index number and serial number.

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

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

Terminating an active session ends the session.

ORA-00028: your session has been killed

ORA-01012: not logged on

ALTER SYSTEM KILL SESSION '7,15' NOREPLAY;

BEGIN
  DBMS_SERVICE.DISCONNECT_SESSION(
    service_name      => 'sales.example.com',
    disconnect_option => DBMS_SERVICE.NOREPLAY);
END;
/

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.

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.

You can cancel a SQL statement in a session using the ALTER SYSTEM CANCEL SQL statement.

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';

ALTER SYSTEM CANCEL SQL '20, 51142, 8vu7s907prbgr';