Performance Tuning

This you some simple, easy-to-apply methods for improving the performance of your applications. Using these methods, you can often reduce processing time by 25 percent or more. Topics are:

Causes of Poor Performance

One cause of poor performance is high Oracle communication overhead. Oracle must process SQL statements one at a time. Thus, each statement results in another call to Oracle and higher overhead. In a networked environment, SQL statements must be sent over the network, adding to network traffic. Heavy network traffic can slow down your application significantly.

Another cause of poor performance is inefficient SQL statements. Because SQL is so flexible, you can get the same result using two different statements. Using one statement might be less efficient. For example, the following two SELECT statements return the same rows (the name and number of every department having at least one employee):

EXEC SQL SELECT DNAME, DEPTNO
    FROM DEPT
    WHERE DEPTNO IN (SELECT DEPTNO FROM EMP)
END-EXEC.

Contrasted with:

EXEC SQL SELECT DNAME, DEPTNO
    FROM DEPT
    WHERE EXISTS
    (SELECT DEPTNO FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO)
END-EXEC.

The first statement is slower because it does a time-consuming full scan of the EMP table for every department number in the DEPT table. Even if the DEPTNO column in EMP is indexed, the index is not used because the subquery lacks a WHERE clause naming DEPTNO.

Another cause of poor performance is unnecessary parsing and binding. Recall that before executing a SQL statement, Oracle must parse and bind it. Parsing means examining the SQL statement to make sure it follows syntax rules and refers to valid database objects. Binding means associating host variables in the SQL statement with their addresses so that Oracle can read or write their values.

Many applications manage cursors poorly. This results in unnecessary parsing and binding, which adds noticeably to processing overhead.

Improving Performance

If you are unhappy with the performance of your precompiled programs, there are several ways you can reduce overhead.

You can greatly reduce Oracle communication overhead, especially in networked environments, by

You can reduce processing overhead—sometimes dramatically—by

The following sections look at each of these ways to cut overhead.

Using Host Tables

Host tables can boost performance because they let you manipulate an entire collection of data with a single SQL statement. For example, suppose you want to insert salaries for 300 employees into the EMP table. Without tables your program must do 300 individual inserts—one for each employee. With arrays, only one INSERT is necessary. Consider the following statement:

EXEC SQL INSERT INTO EMP (SAL) VALUES (:SALARY) END-EXEC.

If SALARY is a simple host variable, Oracle executes the INSERT statement once, inserting a single row into the EMP table. In that row, the SAL column has the value of SALARY. To insert 300 rows this way, you must execute the INSERT statement 300 times.

However, if SALARY is a host table of size 300, Oracle inserts all 300 rows into the EMP table at once. In each row, the SAL column has the value of an element in the SALARY table.

For more information, see Host Tables

Using PL/SQL and Java

As Figure C-1 shows, if your application is database-intensive, you can use control structures to group SQL statements in a PL/SQL block, then send the entire block to Oracle. This can drastically reduce communication between your application and the database.

Also, you can use PL/SQL and Java subprograms to reduce calls from your application to the database. For example, to execute ten individual SQL statements, ten calls are required, but to execute a subprogram containing ten SQL statements, only one call is required.

Unlike anonymous blocks, PL/SQL and Java subprograms can be compiled separately and stored in a database. When called, they are passed to the PL/SQL engine immediately. Moreover, only one copy of a subprogram need be loaded into memory for execution by multiple users.

Figure C-1 PL/SQL Boosts Performance

Description of the illustration pco81060.gif

Optimizing SQL Statements

For every SQL statement, the optimizer generates an execution plan, which is a series of steps that Oracle takes to execute the statement. These steps are determined by rules given in the Oracle Database Advanced Application Developer’s Guide. Following these rules will help you write optimal SQL statements.

Optimizer Hints

For every SQL statement, the optimizer generates an execution plan, which is a series of steps that Oracle takes to execute the statement. In some cases, you can suggest the way to optimize a SQL statement. These suggestions, called hints, let you influence decisions made by the optimizer.

Hints are not directives; they merely help the optimizer do its job. Some hints limit the scope of information used to optimize a SQL statement, while others suggest overall strategies. You can use hints to specify the:

Giving Hints

You give hints to the optimizer by placing them in a C-style Comment immediately after the verb in a SELECT, UPDATE, or DELETE statement. You can choose rule-based or cost-based optimization. With cost-based optimization, hints help maximize throughput or response time. In the following example, the ALL_ROWS hint helps maximize query throughput:

EXEC SQL SELECT /*+ ALL_ROWS (cost-based) */ EMPNO, ENAME, SAL
    INTO :EMP-NUMBER, :EMP-NAME, :SALARY
    FROM EMP
    WHERE DEPTNO = :DEPT-NUMBER
END-EXEC.

The plus sign (+), which must immediately follow the Comment opener, indicates that the Comment contains one or more hints. Notice that the Comment can contain remarks as well as hints.

For more information about optimizer hints, see Performance and Scalability.

Trace Facility

You can use the SQL trace facility and the EXPLAIN PLAN statement to identify SQL statements that might be slowing down your application. The trace facility generates statistics for every SQL statement executed by Oracle. From these statistics, you can determine which statements take the most time to process. You can then concentrate your tuning efforts on those statements.

The EXPLAIN PLAN statement shows the execution plan for each SQL statement in your application. You can use the execution plan to identify inefficient SQL statements.

See Also: Tools for Performance for instructions on using trace tools and analyzing their output.

SQL Statement Caching

Performance improvement is achieved in precompiler applications using statement caching. Any program using dynamic SQL statements, where the cursors have to be used with reparsing of the statements will have performance gain with statement caching. With this new feature, the overall execution time will be decreased.

The stmt_cache option can be set to hold the anticipated number of distinct dynamic SQL statements in the application. The customer can set the stmt_cache size with the new precompiler command line option. An optimal value of stmt_cache can not be set, as it depends on the input program behavior.

The performance can be measured with the change in the execution time (with and without statement caching).

Using Indexes

Using rowids, an index associates each distinct value in a table column with the rows containing that value. An index is created with the CREATE INDEX statement. For details, see CREATE INDEX.

You can use indexes to boost the performance of queries that return less than 15% of the rows in a table. A query that returns 15% or more of the rows in a table is executed faster by a full scan, that is, by reading all rows sequentially. Any query that names an indexed column in its WHERE clause can use the index. For guidelines that help you choose which columns to index, see Using Indexes in Database Applications.

Taking Advantage of Row-Level Locking

By default, Oracle locks data at the row level rather than the table level. Row-level locking allows multiple users to access different rows in the same table concurrently. The resulting performance gain is significant.

You can specify table-level locking, but it lessens the effectiveness of the transaction processing option. For more information about table locking, see “Using the LOCK TABLE Statement” on “Using the LOCK TABLE Statement”.

Applications that do online transaction processing benefit most from row-level locking. If your application relies on table-level locking, modify it to take advantage of row-level locking. In general, avoid explicit table-level locking.

Eliminating Unnecessary Parsing

Eliminating unnecessary parsing requires correct handling of cursors and selective use of the following cursor management options:

These options affect implicit and explicit cursors, the cursor cache, and private SQL areas. Note: You can use the ORACA to get cursor cache statistics. See “Using the Oracle Communications Area”.

Handling Explicit Cursors

Recall that there are two types of cursors: implicit and explicit (see “Errors and Warnings”). Oracle implicitly declares a cursor for all data definition and data manipulation statements. However, for queries that return more than one row, you should explicitly declare a cursor and fetch in batches rather than select into a host table. You use the DECLARE CURSOR statement to declare an explicit cursor. How you handle the opening and closing of explicit cursors affects performance.

If you need to reevaluate the active set, simply reopen the cursor. The OPEN statement will use any new host-variable values. You can save processing time if you do not close the cursor first.

Only CLOSE a cursor when you want to free the resources (memory and locks) acquired by OPENing the cursor. For example, your program should close all cursors before exiting.

Note: To make performance tuning easier, the precompiler lets you reopen an already open cursor. However, this is an Oracle extension to the ANSI/ISO embedded SQL standard. So, when MODE=ANSI, you must close a cursor before reopening it.

Cursor Control

In general, there are three factors that affect the control of an explicitly declared cursor:

With the first way, beware of unnecessary parsing. The OPEN statement does the parsing, but only if the parsed statement is unavailable because the cursor was closed or never opened. Your program should declare the cursor, re-open it every time the value of a host variable changes, and close it only when the SQL statement is no longer needed.

With the second way, which is used in dynamic SQL Methods 3 and 4, the PREPARE statement does the parsing, and the parsed statement is available until a CLOSE statement is executed. Your program should prepare the SQL statement and declare the cursor, re-open the cursor every time the value of a host variable changes, re-prepare the SQL statement and re-open the cursor if the SQL statement changes, and close the cursor only when the SQL statement is no longer needed.

When possible, avoid placing OPEN and CLOSE statements in a loop; this is a potential cause of unnecessary re-parsing of the SQL statement. In the next example, both the OPEN and CLOSE statements are inside the outer loop. When MODE=ANSI, the CLOSE statement must be positioned as shown, because ANSI requires a cursor to be closed before being re-opened.

    EXEC SQL DECLARE emp_cursor CURSOR FOR
        SELECT ENAME, SAL FROM EMP
        WHERE SAL >  :SALARY
        AND SAL <= :SALARY + 1000
    END-EXEC.
    MOVE 0 TO SALARY.
TOP.
    EXEC SQL OPEN emp_cursor END-EXEC.
LOOP.
    EXEC SQL FETCH emp_cursor INTO ....
    ...
        IF SQLCODE = 0
             GO TO LOOP
         ELSE
             ADD 1000 TO SALARY
         END-IF.
    EXEC SQL CLOSE emp_cursor END-EXEC.
    IF SALARY < 5000
        GO TO TOP.

With MODE=ORACLE, however, by placing the CLOSE statement outside the outer loop, you can avoid possible re-parsing at each iteration of the OPEN statement.

TOP.
    EXEC SQL OPEN emp_cursor END-EXEC.
LOOP.
    EXEC SQL FETCH emp_cursor INTO ....
    ...
        IF SQLCODE = 0
             GO TO LOOP
         ELSE
             ADD 1000 TO SALARY
         END-IF.
    IF SALARY < 5000
        GO TO TOP.
    EXEC SQL CLOSE emp_cursor END-EXEC.

Using the Cursor Management Options

A SQL statement need be parsed only once unless you change its makeup. For example, you change the makeup of a query by adding a column to its select list or WHERE clause. The HOLD_CURSOR, RELEASE_CURSOR, and MAXOPENCURSORS options give you some control over how Oracle manages the parsing and re-parsing of SQL statements. Declaring an explicit cursor gives you maximum control over parsing.

Private SQL Areas and Cursor Cache

When any statement is executed, its associated cursor is linked to an entry in the cursor cache. The cursor cache is a continuously updated area of memory used for cursor management. The cursor cache entry is in turn linked to a private SQL area.

The private SQL area, a work area created dynamically at run time by Oracle, contains the parsed SQL statement, the addresses of host variables, and other information needed to process the statement. Dynamic Method 3 lets you name a SQL statement, access the information in its private SQL area, and, to some extent, control its processing. Figure C-2 represents the cursor cache after your program has done an insert and a delete.

Figure C-2 Cursors Linked through the Cursor Cache

Description of the illustration pco81062.gif

Resource Use

The maximum number of open cursors for each user session is set by the initialization parameter OPEN_CURSORS.

MAXOPENCURSORS specifies the initial size of the cursor cache. If a new cursor is needed and there are no free cache entries, Oracle tries to reuse an entry. Its success depends on the values of HOLD_CURSOR and RELEASE_CURSOR and, for explicit cursors, on the status of the cursor itself.

If the value of MAXOPENCURSORS is less than the number of statements that need to be cached during the execution of the program, Oracle will search for cursor cache entries to reuse once MAXOPENCURSORS cache entries have been exhausted. For example, suppose the cache entry E(1) for an INSERT statement is marked as reusable, and the number of cache entries already equals MAXOPENCURSORS. If the program executes a new statement, cache entry E(1) and its private SQL area might be reassigned to the new statement. To reexecute the INSERT statement, Oracle would have to re-parse it and reassign another cache entry.

Oracle allocates an additional cache entry if it cannot find one to reuse. For example, if MAXOPENCURSORS=8 and all eight entries are active, a ninth is created. If necessary, Oracle keeps allocating additional cache entries until it runs out of memory or reaches the limit set by OPEN_CURSORS. This dynamic allocation adds to processing overhead.

Thus, specifying a low value for MAXOPENCURSORS with HOLD_CURSOR=NO (the default) saves memory but causes potentially expensive dynamic allocations and de-allocations of new cache entries. Specifying a high value for MAXOPENCURSORS assures speedy execution but uses more memory.

Infrequent Execution

Sometimes, the link between an infrequently executed SQL statement and its private SQL area should be temporary.

When HOLD_CURSOR=NO (the default), after Oracle executes the SQL statement and the cursor is closed, the precompiler marks the link between the cursor and cursor cache as reusable. The link is reused as soon as the cursor cache entry to which it points is needed for another SQL statement. This frees memory allocated to the private SQL area and releases parse locks. However, because a prepared cursor must remain active, its link is maintained even when HOLD_CURSOR=NO.

When RELEASE_CURSOR=YES, after Oracle executes the SQL statement and the cursor is closed, the private SQL area is automatically freed and the parsed statement lost. This might be necessary if, for example, you wish to conserve memory.

When RELEASE_CURSOR=YES, the link between the private SQL area and the cache entry is immediately removed and the private SQL area freed. Even if you tried to specify HOLD_CURSOR=YES, Oracle must still reallocate memory for a private SQL area and re-parse the SQL statement before executing it. Therefore, specifying RELEASE_CURSOR=YES overrides HOLD_CURSOR=YES.

Frequent Execution

The links between a frequently executed SQL statement and its private SQL area should be maintained, because the private SQL area contains all the information needed to execute the statement. Maintaining access to this information makes subsequent execution of the statement much faster.

When HOLD_CURSOR=YES, the link between the cursor and cursor cache is maintained after Oracle executes the SQL statement. Thus, the parsed statement and allocated memory remain available. This is useful for SQL statements that you want to keep active because it avoids unnecessary re-parsing.

Effect on the Shared SQL Area

Oracle caches the parsed representations of SQL statements and PL/SQL in its Shared SQL Cache. These representations are maintained until aged out by the need for the space to be used for other statements. For more information, see the Oracle Database Concepts manual. The behavior of the Oracle server in this respect is unaffected by the Precompiler’s cursor management settings and so can have the following effects:

Embedded PL/SQL Considerations

For the purposes of cursor management, an embedded PL/SQL block is treated just like a SQL statement. When an embedded PL/SQL block is executed, a parent cursor is associated with the entire block and a link is created between the cache entry and the private SQL area in the PGA for the embedded PL/SQL block. Be aware that each SQL statement inside the embedded block also requires a private SQL area in the PGA. These SQL statements use child cursors that PL/SQL manages itself. The disposition of the child cursors is determined through its associated parent cursor. That is, the private SQL areas used by the child cursors are freed after the private SQL area for its parent cursor is freed.

Note:

Using the defaults, HOLD_CURSOR=YES and RELEASE_CURSOR=NO, after executing a SQL statement with an earlier Oracle version, its parsed representation remains available. With Oracle, under similar conditions, the parsed representation remains available only until it is aged out of the Shared SQL Cache. Normally, this is not a problem, but you might get unexpected results if the definition of a referenced object changes before the SQL statement is re-parsed.

Parameter Interactions

HOLD_CURSOR and RELEASE _CURSOR Interactions shows how HOLD_CURSOR and RELEASE_CURSOR interact. Notice that HOLD_CURSOR=NO overrides RELEASE_CURSOR=NO and that RELEASE_CURSOR=YES overrides HOLD_CURSOR=YES.

Table C-1 HOLD_CURSOR and RELEASE _CURSOR Interactions

HOLD_CURSOR RELEASE_CURSOR Links are…
NO NO marked as reusable
YES NO maintained
NO YES removed immediately
YES YES removed immediately

Avoiding Unnecessary Reparsing

When an embedded SQL statement is executed in a loop, it gets parsed only once. However, the execute phase of the SQL statement can result in errors, and statements are reparsed, with the following exceptions:

By correcting the errors, you can eliminate this unnecessary reparsing.

About Session Pooling

This section describes performance tuning in Pro*COBOL Precompiler using session pooling.

Session pooling in precompiler applications provides a managed collection of reusable sessions. Pools are identified by the connection string, support homogeneous and heterogeneous modes, and are cleared when the application exits.

Overview

Session pooling is now supported for Pro*COBOL applications.

Pro*COBOL Precompiler provides embedded SQL constructs for connecting to a database and executing SQL statements. By default, these constructs create dedicated connections and terminate them after the associated work is complete. Session pooling optimizes this lifecycle by maintaining a pool of connections and transparently assigning them to requests. This reduces the overhead of repeatedly creating and closing connections while preserving a seamless application experience. The runtime executes SQL statements and releases the assigned connection when the request completes. Precompilers also define explicit request boundaries that support preliminary high availability (HA) capabilities, including handling known outages. When supported by the session pool, they can leverage Database Resident Connection Pooling (DRCP) and OCI HA features such as Transparent Application Failover (TAF), Application Continuity (AC), and Transparent Application Continuity (TAC).

Configuration

The configuration can be provided as:

The command line options take precedence when the options are provided in both the command line and in the configuration file.

Command Line Parameters

Table 2 Command Line Parameters

Name Configuration Type Default Value Description
SessionPool spool string(Yes/No) No The session pool parameters is a Boolean parameter that takes YES or NO to enable or disable sessions. This parameter is a command-line parameter used to enable session pool right from code generation in precompilers.
SessionPool Homogenous sp_homogenous string(Yes/No) Yes The sp_homogenous parameter is a Boolean command-line parameter that accepts YES or NO to enable homogeneous or heterogeneous pool mode, respectively. It defaults to YES, so specifying spool=YES creates a homogeneous pool using the user from the connection string. To create a heterogeneous pool, explicitly set sp_homogenous=NO.
PoolPingInterval sp_ping_interval Number 60 The ping interval time parameter is crucial for connection pool management. It sets the time interval for the pool to validate a retrieved connection. When a connection request is made, the pool checks the time difference between the application’s release of the connection and the current time. If this difference exceeds the value set in the ping interval time parameter, the pool pings the associated database to validate the connection before returning it to the application. The value of this parameter is set in seconds.
PoolMinimum sp_minimum Number 1 This parameter is NOT for heterogeneous pool. The PoolMinimum parameter plays a key role in configuring the session pool. It sets the initial number of connections to be created during the pool’s creation. The value of this parameter should always be less than the PoolMaximum parameter.
PoolMaximum sp_maximum Number 100 The PoolMaximum parameter is a critical factor in connection pool management. It sets the maximum number of connections that the pool can hold. The value of this parameter is always greater than the PoolMinimum parameter.
PoolIncrement sp_increment Number 1 This parameter is NOT for heterogenous pool. This parameter sets the number of connections to be created when no free connection is available in the pool to serve the connection request. The pool can scale up to the maximum number of connections set in the parameter PoolMaximum.
PoolConnectionMaxUses sp_max_use_session Number 0 This parameter sets the maximum number of times that any session in the session pool can be used. A session that uses more than the value set in this parameter is dropped from the pool. The default value of this parameter is 0, which means that a session can be used unlimited times.
PoolConnectionMaxLifeTime sp_max_lifetime_session Number 0 Sets the lifetime of a session in the session pool. Any session that exceeds the the maximum lifetime is dropped from the pool. This parameter is set in seconds. If this attribute is not set, no connection is dropped unless the PoolConnectionIdleTimeout and PoolConnectionMaxUses parameters are set.
PoolConnectionIdleTimeout sp_idle_timeout Number 0 Sets the idle timeout for the session in the session pool. Any session that remains idle in the session pool for a duration exceeding the timeout set in this parameter is dropped from the pool. If this parameter is not set, the pool removes the least-used session when it needs to make space for a new session. The pool checks for idle sessions only when a session is returned to the pool.
PoolConnectionGetTimeout sp_wait_timeout Number 5000

Sets the maximum wait time for retrieving a session from the session pool. When serving a connection request, the session pool tries to serve the request with a connection within the time limit set in this parameter. The session pool returns an error to the connection request that waited for more than the timeout set in this parameter. The value of this parameter is set in milliseconds. When the value is set to:

  • = 0, then the connection request waits indefinitely until it is served with a session. 0 can be used to disable this feature.

  • > 0, the connection request is served within the set timeout. An error is returned if no free connection is available within the timeout.
PoolConnectionClass sp_cclass String - This parameter sets the connection class name of the connections in the session pool, which will be propagated to associated DRCP connections. This allows the application to categorize a few connections in the DRCP and share them among similar or identical applications. There is no default value for this parameter, but the length of this parameter value should not exceed 128 characters.

Using Session Pooling

To use session pooling in Pro*COBOL applications, a user only needs to compile the .pco file along with option spool=yes and it’ll utilize session pooling underneath.

procob iname=myprog.pco spool=yes

With spool=yes, a connect statement such as the following in a .pco file uses a session from session pool:

EXEC SQL CONNECT :USER USING :HOST;

This statement performs the session get procedure and after that an application is able to perform all SQL operations using that authenticated session.

There are various options with which a user can configure a session pool at the OCI level.

See Also: Session Pooling in OCI for more information about session pooling and how the client can use it

Create a Session Pool

Two types of session pools are supported:

An application can use either type across its modules, but it cannot use both types in the same unit. Therefore, the whole application needs to be compiled with the same spool parameters, and a mix of heterogeneous and homogeneous pools across different translation units.

To enable session pooling in a precompiler application, compile the application with the spool option set to yes. When the application requests a connection, the session pool is created transparently if it does not already exist.

procob iname=test.pco oname=test.cob spool=yes sp_minimum=2 sp_maximum=10 sp_increment=3

To enable a homogeneous session pool, specify sp_homogenous=yes:

procob iname=test.pco oname=test.cob spool=yes sp_minimum=2 sp_maximum=10 sp_increment=3 sp_homogenous=yes

A session pool is a pool of threads with several connection-thread modes enabled by default in the application.

A session pool can be created even if the application does not use multithreading or is not compiled with threads=yes. This allows the session pool configuration to be used independently of the application’s threading model.

One or more session pools can be created. Each session pool is identified by a key derived from the host_connect_string used in the EXEC SQL CONNECT directive.

Transparent Session Pool Creation

The embedded construct EXEC SQL CONNECT initiates session-pool creation if a matching pool does not already exist.

The process is as follows:

  1. Construct a key from host_connect_string.

  2. Search for the key in the session-pool hash table.

  3. If the pool is not found, create a session pool using the parameters listed in Configuration, and return the pool.

  4. If the pool is found, return the existing pool.

  5. Check out a session and return it to the application.

  6. After EXEC SQL CONNECT completes, the application has a valid session checked out from the session pool. The pool is created using the key and the configuration described in Configuration.

  7. Session-pool creation is transparent internally and must be tracked.

The session-pool key algorithm uses the following data from the EXEC SQL CONNECT construct:USING:connect_string.

Get a Session from the Session Pool

The EXEC SQL CONNECT construct transparently creates a session pool, if required, and checks out a connection from the pool.

EXEC SQL CONNECT
    { :user IDENTIFIED BY :oldpswd | :usr_psw }
    [[ AT { dbname | :host_variable } USING :connect_string ]]
    [ { ALTER AUTHORIZATION :newpswd
        | IN { SYSDBA | SYSOPER } MODE } ];

Only SYSDBA is supported when creating a session pool. SYSOPER is not supported.

Release a Session to the Session Pool

A session is released back to the pool by using one of the following constructs:

-

EXEC SQL COMMIT WORK RELEASE;

-

EXEC SQL ROLLBACK WORK RELEASE;

Destroy the Session Pool

All session pools are effectively cleared when the application exits. This ensures a clean shutdown and optimal resource management.

Managing Connections

Precompilers support two types of connections:

  1. Default connections

  2. Named connections

Default Connections

A default connection can be either:

Only one default connection can exist per context.

The following construct obtains the default connection:

EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw;

Named Connections

A named connection can be global or local to a context. It can be specified using a name in the AT clause or by using a host variable:

EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw AT DBNAME;
EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw AT :dbname;

The default connection and the two named-connection forms above produce the same session-pool key. Therefore, they obtain connections from the same pool. The differences among these connection forms do not result in different key values or create a new pool.

If the connection string is not specified explicitly, the precompiler obtains it from the TWO_TASK environment variable.

The following connection uses a different connection string and therefore creates a new session pool:

EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw
    AT DBNAME1 USING :connection_string;

Using an explicit connection string is the only way to create one or more session pools.

Mapping a Session-Pool Connection to the Default Connection

A precompiler always has a default or global context unless the application creates another context.

The following are examples of default connections in the default context:

EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw;
EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw
    USING :connection_string;

If both statements are executed, the last connection replaces the first one. Both connections refer to the same internal data structure, and only one default connection can exist.

When multiple users are used in a multi-file compilation, compiling a second file with the same user does not change the pool parameters.

Mapping a Session-Pool Connection to a Named Connection

The following are examples of named connections. Named connections can be created using a name in the AT clause or a host variable. In this example, they are unique to the global context.

EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw AT DBNAME;
EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw AT :dbname;

Context Management and Session Pools

All contexts (global and local) share the list of global session pools.

A new session pool is created when the connection string used by the EXEC SQL CONNECT construct changes. For example:

EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw
    USING :connection_string1;
EXEC SQL CONNECT :user IDENTIFIED BY :usr_psw
    USING :connection_string2;

These statements create two different session pools. However, only the default connection from the second pool is available through the internally maintained default-connection pointer.

This can cause a problem with the first session pool. A session checked out from the first pool is not checked back in and is effectively lost when the default connection is replaced by a session from the second pool. As a result, the first pool can no longer manage that session, and the application loses its interface to that session.

Per-Thread Context

An application can allocate an explicit context and create or use a session pool within that context:

function1()
{
    sql_context :ctx1; /* Declare local context ctx1. */

    EXEC SQL CONTEXT ALLOCATE :ctx1;
    EXEC SQL CONTEXT USE :ctx1;

    /*
     * Create a session pool in the global session-pool array
     * using :connection_string.
     */
    EXEC SQL CONNECT :user IDENTIFIED BY :password
        USING :connection_string;

    EXEC SQL INSERT INTO ... /* Use local context ctx1. */
    ...
}

Creating or Using Multiple Session Pools with a Per-Thread Context

An application can also allocate an explicit context and create or use one or more session pools within that context:

function1()
{
    sql_context :ctx1; /* Declare local context ctx1. */

    EXEC SQL CONTEXT ALLOCATE :ctx1;
    EXEC SQL CONTEXT USE :ctx1;

    EXEC SQL CONNECT :user IDENTIFIED BY :password
        USING :conn_string;

    EXEC SQL INSERT INTO ... /* Use local context ctx1. */

    EXEC SQL CONNECT AT POOL2CONN1
        :user IDENTIFIED BY :password
        USING :conn_string2;

    /*
     * Create a new pool or use the pool if it already exists.
     */
    EXEC SQL AT POOL2CONN1 INSERT INTO ... /* Use local context ctx1. */
    ...
}

About Using Oracle Connection Manager in Traffic Director Mode

Oracle Connection Manager in Traffic Director Mode is a proxy that is placed between supported database clients and database instances.

Note: A multitenant container database is the only supported architecture in Oracle Database 21c. While the documentation is being revised, legacy terminology may persist. In most cases, “database” and “non-CDB” refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, “non-CDB” refers to a non-CDB from a previous release.

Supported clients from Oracle Database 11g Release 2 (11.2) and later can connect to Oracle Connection Manager in Traffic Director Mode. Oracle Connection Manager in Traffic Director Mode provides improved high availability (HA) for planned and unplanned database server outages, connection multiplexing support, and load balancing. Support for Oracle Connection Manager in Traffic Director Mode is described in more detail in the following sections

Modes of Operation

Oracle Connection Manager in Traffic Director Mode supports the following modes of operation:

Key Features

Oracle Connection Manager in Traffic Director Mode furnishes support for the following:

See Also: