2 Performance and Scalability

2.1.1 Designing Your Data Model to Perform Well

This topic briefly describes the important concepts of data modeling. Entire books about this subject are available; refer to them for details and further guidance.

Design your data model for optimal performance of the most important and frequent queries, following this basic procedure:

1. Analyze the Data Requirements of the Application

2. Create the Database Design for the Application

3. Implement the Database Application

4. Maintain the Database and Database Application

2.1.2 Setting Performance Goals (Metrics)

Start your application development project by setting performance goals (metrics), including:

• Expected number of application users

• Expected number of transactions per second at peak load times

• Expected query response times at peak load times

• Expected number of records for each table per unit of time (such as one day, one month, or one year)

Use these metrics to create benchmark tests.

2.1.3 Benchmarking Your Application

Benchmarks are tests that measure aspects of application performance. Benchmark results either validate application design or raise issues that you can resolve before putting the application into production.

Usually, you first run benchmarks on an isolated single-user system to minimize interference from other factors. Results from such benchmarks provide a performance baseline for the application. For meaningful benchmark results, you must test the application in the environment where you expect it to run.

You can create small benchmarks that measure performance of the most important transactions, compare different solutions to performance problems, and help resolve design issues that could affect performance.

You must develop much larger, more complex benchmarks to measure application performance during peak user loads, peak transaction loads, or both. Such benchmarks are especially important if you expect the user or transaction load to increase over time. You must budget and plan for such benchmarks.

After the application is in production, run benchmarks regularly in the production environment and store their results in a database table. After each benchmark run, compare the previous and new records for transactions that cannot afford performance degradation. Using this method, you isolate issues as they arise. If you wait until users complain about performance, you might be unable to determine when the problem started.

2.2.1 DBMS_APPLICATION_INFO Package

Use the DBMS_APPLICATION_INFO package with the SQL Trace facility (described in Section 2.2.2) and Oracle Trace and to record the names of executing modules or transactions in the database. System administrators and performance tuning specialists can use this recorded information to track the performance of individual modules and for debugging. System administrators can also use this information to track resource use by module.

When you register the application with the database, its name and actions are recorded in the views V$SESSION and V$SQLAREA.

The DBMS_APPLICATION_INFO package provides subprograms that set the following columns in the V$SESSION view:

• MODULE (name of application or package)

• ACTION (name of transaction or packaged subprogram)

• CLIENT_INFO (additional information about the client application, such as initial bind variable values for the current session)

The DBMS_APPLICATION_INFO package also provides subprograms that return information from the preceding V$SESSION columns for the current session.

You can also use the DBMS_APPLICATION_INFO package to track the progress of commands that take many seconds to display results (such as those that create indexes or update many rows). The DBMS_APPLICATION_INFO package provides a subprogram that stores information about the command in the V$SESSION_LONGOPS view. The V$SESSION_LONGOPS view shows when the command started, how far it has progressed, and its estimated time to completion.

2.2.2 SQL Trace Facility (SQL_TRACE)

Use the SQL Trace facility, SQL_TRACE, to trace all SQL statements and PL/SQL blocks that your application executes. By enabling the SQL Trace facility for a single statement or module and then disabling it after the statement or module runs, you can get trace information for only that statement or module.

For best results, use the SQL Trace facility with TKPROF and the EXPLAIN PLAN statement. The SQL Trace facility provides performance information for individual SQL statements. TKPROF formats the trace file contents in a readable file. The EXPLAIN PLAN statement shows the execution plans chosen by the optimizer and the execution plan for the specified SQL statement if it were executed in the current session (for more information, see Section 2.2.3).

Use the SQL Trace facility while designing your application, so that you know what you want to trace and how the application performs before putting it into production. If you wait until performance problems develop in the production environment, your application structure might make using the SQL Trace facility almost impossible.

2.2.3 EXPLAIN PLAN Statement

When you run a SQL statement, the optimizer generates several possible execution plans, calculates the cost of each plan, and uses the plan with the lowest cost.

Plan cost is based on statistics such as the data distribution and storage characteristics of the tables, indexes, and partitions that the SQL statement accesses. The cost of access paths and join orders is based on estimated use of computer resources such as I/O, CPU, and memory.

When you use the statement EXPLAIN PLAN FOR statement before running statement, the EXPLAIN PLAN statement stores the execution plan for statement in a plan table. By querying the plan table, you can examine the execution plan that the optimizer chose.

The plan table presents the execution plan as a row source tree, which shows the steps that Oracle Database uses to execute the SQL statement. The row source tree shows the order in which the statement references tables, the join method for tables affected by join operations, and data operations such as filtering, sorting, and aggregation. The plan table also contains optimization information, such as the cost and cardinality of each operation, partitioning information (such as the set of accessed partitions), and parallel execution information (such as the distribution method of join inputs). This information provides valuable insight into how and why the optimizer chose the execution plan.

If you have information about the data that the optimizer does not have, you can give the optimizer a hint, which might change the execution plan. If tests show that the hint improves performance, keep the hint in your code.

The EXPLAIN PLAN statement shows only how Oracle Database would run the SQL statement when the statement was explained. If the execution environment or explain plan environment changes, the optimizer might use a different execution plan. Therefore, Oracle recommends using SQL plan management to build a SQL plan baseline, which is a set of accepted execution plans for a SQL statement.

First, use the EXPLAIN PLAN statement to see the statement's execution plan. Second, test the execution plan to verify that it is optimal, considering the statement's actual resource consumption. Finally, if the plan is optimal, use SQL plan management.

2.3.1 Automatic Database Diagnostic Monitor (ADDM)

Automatic Database Diagnostic Monitor (ADDM) is an advisor that analyzes data captured in Automatic Workload Repository (AWR). ADDM and AWR are part of the Oracle Diagnostic Pack.

ADDM determines where database performance problems might exist and where they do not exist, and recommends corrections for the former.

ADDM performs its analysis after each AWR snapshot and stores the results in the database. By default, the AWR snapshot interval is 1 hour and the ADDM results retention period is 8 days.

Oracle Enterprise Manager Cloud Control (Cloud Control) displays ADDM Findings on the Database home page. This AWR snapshot data confirms ADDM results, but lacks the analysis and recommendations that ADDM provides. (AWR snapshot data is similar to Statspack data that database administrators used for performance analysis.)

2.3.2 Monitoring Real-Time Database Performance

Using Oracle Enterprise Manager Cloud Control (Cloud Control), you can monitor real-time database performance from the Performance page. From the Performance page, you can access pages that identify performance issues and resolve those issues without waiting for the next ADDM analysis.

2.3.3 Responding to Performance-Related Alerts

The Database home page in Oracle Enterprise Manager Cloud Control (Cloud Control) displays performance-related alerts generated by the database. You can improve database performance by resolving problems indicated by these alerts. Oracle Database by default enables alerts for tablespace usage, snapshot too old, recovery area low on free space, and resumable session suspended. You can view metrics and thresholds, set metric thresholds, respond to alerts, clear alerts, and set up direct alert email notification. Using this built-in alerts infrastructure allows you to be notified for these special performance-related alerts.

2.3.4 SQL Advisors and Memory Advisors

Oracle Database provides SQL advisors and memory advisors that you can use to help improve database performance.

SQL advisors include SQL Tuning Advisor and SQL Access Advisor, which are part of the Oracle Database Tuning Pack.

SQL Tuning Advisor accepts one or more SQL statements as input and returns specific tuning recommendations. Each recommendation includes a rationale and expected benefit. Recommendations are based on object statistics, new index creation, SQL statement restructuring, and SQL profile creation.

SQL Access Advisor enables you to optimize data access paths of SQL queries by recommending a set of materialized views and view logs, indexes, and partitions for a given SQL workload.

Memory advisors include Memory Advisor, SGA Advisor, Shared Pool Advisor, Buffer Cache Advisor, and PGA Advisor. Memory advisors provide graphical analyses of total memory target settings, SGA and PGA target settings, and SGA component size settings. Use these analyses to tune database performance and to plan for possible situations.

2.7.1 About Client Result Cache

Applications that use Oracle Database drivers and adapters built on OCI libraries—including C, C++, Java (JDBC-OCI), PHP, Python, Ruby, and Perl—can use client result cache to improve response times of repetitive queries.

Client result cache enables client-side caching of SQL query (SELECT statement) result sets in client memory. Because retrieving results from a client process is faster than calling the database and rerunning the query, frequently run queries perform significantly faster when their results are cached. Client result cache also reduces the server CPU time that would have been used to process the query, thereby improving server scalability.

OCI statements from multiple sessions can match the same cached result set in the OCI process memory if they have similar schemas, SQL text, bind values, and session settings. If not, the query execution is directed to the server.

Client result cache is transparent to applications, and its cache of result set data is kept consistent with session or database changes that affect its result set data.

Applications that use client result cache benefit from faster performance for queries that have cache hits. These applications use the cached result sets on clients or middle tiers.

Client result cache works with OCI features such as the OCI session pool, the OCI connection pool, DRCP, and OCI transparent application failover (TAF).

When using client result cache, you must also enable OCI statement caching or cache statements at the application level.

2.7.2 Benefits of Client Result Cache

The benefits of client result cache are:

• Because client result cache is on the client, a cache hit causes fetch (OCIStmtFetch2()) and execute (OCIStmtExecute()) calls to be processed locally, eliminating a server round trip. Eliminating server round trips reduces the use of server resources (such as server CPU and server I/O), significantly improving performance.

• Client result cache is transparent and consistent.

• Client result cache is available to every process, so multiple client sessions can simultaneously use matching cached result sets.

• Client result cache minimizes the need for each OCI application to have its own custom result cache.

• Client result cache transparently manages:

  • Concurrent access by multiple threads, multiple statements, and multiple sessions

  • Invalidation of cached result sets that might have been affected by database changes

  • Refreshing of cached result sets

  • Cache memory management

• Client result cache is automatically available to applications and drivers that use OCI libraries, including JDBC OCI, ODP.Net, OCCI, Pro*C/C++, Pro*COBOL, and ODBC.

• Client result cache uses OCI client memory, which might be less expensive than server memory.

• A local cache on the client has better locality of reference for queries executed by that client.

2.7.3 Guidelines for Using Client Result Cache

You can enable or disable client result cache at three levels, which are, in order of descending precedence:

1. Query

   For a specific query, you can enable or disable client result cache with a SQL hint (for details, see Section 2.7.3.1). To add or change a SQL hint, you must change the application.

2. Table

   For all queries on a specific table, you can enable or disable client result cache with a table annotation, without changing the application (for details, see Section 2.7.3.2).

3. Session

   For all queries in your database session, you can enable or disable client result cache with a session parameter, without changing the application (for details, see Section 2.7.3.3).

Higher-level settings take precedence over lower-level ones. For examples, see Section 2.7.3.6.

Oracle recommends enabling client result cache only for queries on read-only and seldom-read tables (tables that are rarely updated). That is, enable client result cache:

• At query level only for queries of read-only and seldom-read tables

• At table level only for read-only and seldom-read tables

• At session level only when running applications that query only read-only or seldom-read tables

Enabling client result cache for queries that have large result sets or many sets of bind values can use a large amount of client result cache memory. Each set of bind values (for the same SQL text) creates a different cached result set.

When client result cache is enabled for a query, its result sets can be cached on the client, server, or both. Client result cache can be enabled even if the server result cache (which is enabled by default) is disabled.

For OCI, the first OCIStmtExecute() call of every OCI statement handle call always goes to the server even if there might be a valid cached result set. An OCIStmtExecute() call must be made for each statement handle to be able to match a cached result set. Oracle recommends that applications either have their own statement caching for OCI statement handles or use OCI statement caching so that OCIStmtPrepare2() can return an OCI statement handle that has been executed once. Multiple OCI statement handles, from the same or different sessions, can simultaneously fetch data from the same cached result set.

For OCI, for a result set to be cached, the OCIStmtExecute() or OCIStmtFetch2() calls that transparently create this cached result set must fetch rows until ORA-01403 (No Data Found) is returned. Subsequent OCIStmtExecute() or OCIStmtFetch2() calls that match a locally cached result set need not fetch to completion.

Topics:

• SQL Hints

• Table Annotation

• Session Parameter

• Effective Table Result Cache Mode

• Displaying Effective Table Result Cache Mode

• Result Cache Mode Use Cases

• Queries Never Result Cached in Client Result Cache

2.7.4 Client Result Cache Consistency

Client result cache transparently keeps its result sets consistent with any database or session state changes that affect them.

When a transaction modifies the data or metadata of any database object used to construct a cached result set, invalidation of that cached result set is sent to the client on its next round trip to the server. If the application does no database calls for a period of time, then the client result cache lag setting forces the next OCIStmtExecute() call to make a database call to check for such invalidations.

Cached result sets relevant to database invalidations are immediately invalidated. Any OCI statement handles that are fetching from cached result sets when their invalidations are received can continue fetching from them, but no subsequent OCIStmtExecute() calls can match them.

The next OCIStmtExecute() call by the process might cache the new result set if the client result cash has space available. Client result cache periodically reclaims unused memory.

If a session has a transaction open, OCI ensures that its queries that reference database objects changed in this transaction go to the server instead of client result cache.

This consistency mechanism ensures that client result cache is always close to committed database changes. If the application has relatively frequent calls involving database round trips due to queries that cannot be cached (DMLs, OCILob calls, and so on), then these calls transparently keep client result cache consistent with database changes.

Sometimes, when a table is modified, a trigger causes another table to be modified. Client result cache is sensitive to such changes.

When the session state is altered—for example, when NLS session parameters are modified—query results can change. Client result cache is sensitive to such changes and for subsequent query executions, returns the correct result set. However, client result cache keeps the current cached result sets (and does not invalidate them) so that other sessions in the process can match them. If other processes do not match them, these result sets are "Ruled" after a while. Result sets that correspond to the new session state are cached.

For an application, keeping track of database and session state changes that affect query result sets can be cumbersome and error-prone, but client result cache transparently keeps its result sets consistent with such changes.

Client result cache does not require thread support in the client.

2.7.5 Deployment-Time Settings for Client Result Cache

When you deploy your application, you can set the following for client result cache (without changing the application):

• Server initialization parameters (see section Section 2.7.5.1)

• Client configuration parameters (see section Section 2.7.5.2)

• Table annotations (see section Section 2.7.3.2)

• RESULT_CACHE_MODE session parameter (see section Section 2.7.3.3)

2.7.6 Client Result Cache Statistics

On round trips to the server from the OCI client, OCI periodically sends client result cache statistics to the server. These statistics, shown in the CLIENT_RESULT_CACHE_STATS$ view, include number of result sets successfully cached, number of cache hits, and number of cached result sets invalidated. The number of cache misses for queries is at least equal to the number of Create Counts in client result cache statistics. More precisely, the cache miss count equals the number of server executions in server Automatic Workload Repository (AWR) reports.

2.7.7 Validation of Client Result Cache

Some ways to validate client result cache are:

• Measure Execution Times

• Query V$MYSTAT

• Query V$SQLAREA

2.7.8 Client Result Cache and Server Result Cache

Client result cache is different from server result cache. Client result cache caches results of top-level SQL queries in OCI client memory, whereas server result cache caches result sets and query fragments in server SGA memory.

You can enable client result cache independently of server result cache, though they share the result cache SQL hints, table annotations, and session parameter RESULT_CACHE_MODE. Table 2-4 shows the result cache association for result cache parameters, the PL/SQL package DBMS_RESULT_CACHE, and result cache views. For more information about server result cache, see Oracle Database Concepts.

2.7.9 Client Result Cache Demo Files

For OCI applications, demonstration files for client result cache are cdemoqc.sql, cdemoqc.c, and cdemoqc2.c (in the demo directory for your operating system).

2.7.10 Client Result Cache Compatibility with Previous Releases

To use client result cache, applications must be relinked with Oracle Database 11g Release 1 (11.1) or later client libraries and be connected to an Oracle Database 11g Release 1 (11.1) or later database server. Client result cache is available to all OCI applications, including JDBC Type II driver, OCCI, Pro*C/C++, and ODP.NET. OCI drivers automatically pass the SQL hint RESULT_CACHE to OCIStmtPrepare() and OCIStmtPrepare2() calls.

2.7.11 Where to Find More Information About Client Result Cache

More information about client result cache is in the following documentation:

• Oracle Call Interface Programmer's Guide

• Oracle C++ Call Interface Programmer's Guide

• Oracle Database JDBC Developer's Guide

• Oracle Database Performance Tuning Guide

• Oracle Database Concepts

• Oracle Database SQL Language Reference for information about SQL hints and RESULT_CACHE clause of ALTER TABLE, and CREATE TABLE

• Oracle Database Reference for information about RESULT_CACHE_MODE initialization parameter and CLIENT_RESULT_CACHE_STAT$ view

2.12.1 About Database Resident Connection Pool

DRCP pools server processes, each of which is the equivalent of a dedicated server process and database session combined; these are called pooled servers. Pooled servers can be shared by multiple applications running on the same or several hosts. A connection broker process manages the pooled servers at the database instance level.

DRCP is a configurable feature, chosen at application runtime. It allows concurrent use of traditional and DRCP-based connection architectures.

DRCP is especially useful for architectures with multiprocess single-threaded application servers (such as PHP and Apache) that cannot do middle-tier connection pooling. DRCP is also very useful in large-scale web deployments where hundreds or thousands of web servers or middle-tiers need database access and client-side pools (even in multithreaded systems and languages such as Java). Using DRCP, the database can scale to tens of thousands of simultaneous connections. If your database web application must scale to large numbers of connections, DRCP is your connection pooling solution.

DRCP complements middle-tier connection pools that share connections between threads in a middle-tier process. DRCP also enables sharing of database connections across middle-tier processes on the same middle-tier host, across multiple middle-tier hosts, and across multiple middle-tiers (web servers, containers) that accommodate applications written in different languages. This sharing significantly reduces the database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and increasing the scalability of both middle and database tiers. Having a pool of readily available servers also reduces the cost of creating and releasing client connections.

Clients get connections from the DRCP, which is connected to an Oracle Database background process called the connection broker. The connection broker implements the pool functionality and multiplexes pooled servers among persistent inbound connections from the client.

When a client needs database access, the connection broker gets a server process from the pool and gives it to the client. The client is then directly connected to the server. After the server executes the client request, the server process returns to the pool and the connection from the client is restored to the connection broker as a persistent inbound connection from the client process. In DRCP, releasing resources leaves the session intact, but no longer associated with a connection (server process). Because this session stores its user global area (UGA) in the program global area (PGA), not in the system global area (SGA), a client can reestablish a connection transparently upon detecting activity.

DRCP is typically recommended for applications with a large number of connections. Shared servers are recommended for applications with a medium number of connections and dedicated sessions are recommended for applications with a small number of connections. The threshold sizes depend on the amount of memory available on the database host.

DRCP has these advantages:

• DRCP enables resource sharing among multiple client applications and middle-tier application servers.

• DRCP improves scalability of databases and applications by reducing resource usage on the database host.

Compared to client-side connection pooling and shared servers:

• DRCP provides a direct connection to the database server, furnished by client-side connection pooling (like client-side connection pooling but unlike shared servers).

• DRCP can pool database servers (like client-side connection pooling and shared servers).

• DRCP can pool sessions (like client-side connection pooling but unlike shared servers).

• DRCP can share connections across middle-tier boundaries (unlike client-side connection pooling).

DRCP offers a unique connection pooling solution that addresses scalability requirements in environments requiring large numbers of connections with minimal database resource usage.

2.12.2 Configuring DRCP

DRCP is installed by default, but the DBA must start and configure it, using the DBMS_CONNECTION_POOL package. Configuration options include minimum and maximum number of pooled servers, number of connection brokers, maximum number of connections that each connection broker can handle, and so on.

OCI session pool APIs have been extended to interoperate with DRCP.

2.12.3 Using JDBC with DRCP

Beginning with Oracle Database 12c Release 1 (12.1.0.1), Oracle JDBC drivers support DRCP. The DRCP implementation creates a pool on the server side, which is shared across multiple client pools. These client pools use Universal Connection Pool for JDBC. Using Universal Connection Pool significantly lowers memory consumption (because of the reduced number of server processes on the server) and increases the scalability of the Database server.

To track check-in and checkout operations of server-side connections, Java applications must use a client-side pool such as Universal Connection Pool for JDBC or a third-party Java connection pool.

Section 2.12.6 explains how the DBA starts DRCP on the server side.

To enable DRCP on the client side, you must do the following:

• Pass a non-NULL, nonempty String value to the connection property oracle.jdbc.DRCPConnectionClass.

• Pass (SERVER=POOLED) in the long connection string.

You can also specify (SERVER=POOLED) in the short URL form as follows:

jdbc:oracle:thin:@//<host>:<port>/<service_name>[:POOLED]

For example:

jdbc:oracle:thin:@//localhost:5221/orcl:POOLED

For an example of enabling JDBC on the client side, see the section on enabling DRCP on the client side in Oracle Database JDBC Developer's Guide.

By setting the same DRCP Connection class name for all the pooled server processes on the server using the connection property oracle.jdbc.DRCPConnectionClass, you can share pooled server processes on the server across multiple connection pools.

In DRCP, you can also apply a tag to a given connection and easily retrieve that tagged connection later. For more information about enabling connection tagging in DRCP, see Oracle Database JDBC Developer's Guide.

2.12.4 Using OCI Session Pool APIs with DRCP

The OCI session pool APIs OCISessionPoolCreate(), OCISessionGet(), and OCISessionRelease() interoperate with DRCP.

An OCI application initializes the environment for the OCI session pool for DRCP by invoking OCISessionPoolCreate(), which is described in Oracle Call Interface Programmer's Guide.

To get a session from the OCI session pool for DRCP, an OCI application invokes OCISessionGet(), specifying OCI_SESSGET_SPOOL for the mode parameter. For information about OCISessionGet(), see Oracle Call Interface Programmer's Guide.

To release a session to the OCI session pool for DRCP, an OCI application invokes OCISessionRelease(), which is described in Oracle Call Interface Programmer's Guide.

To improve performance, the OCI session pool can transparently cache connections to the connection broker. An OCI application can reuse the sessions within which the application leaves sessions of a similar state either by invoking OCISessionGet() with the authInfop parameter set to OCI_ATTR_CONNECTION_CLASS and specifying a connection class name or by using the OCIAuthInfo handle before invoking OCISessionGet(). For details, see Section 2.12.5.

DRCP also supports features offered by the traditional client-side OCI session pool, such as tagging, statement caching, and TAF.

2.12.5 Session Purity and Connection Class

In Oracle Database 11g Release 1 (11.1), OCI introduced two settings that can be specified when obtaining a session using OCISessionGet(): session purity and connection class.

Topics:

• Session Purity

• Connection Class

• Session Purity and Connection Class Defaults

/* OCIAttrSet method */

ub4 purity = OCI_ATTR_PURITY_NEW;
OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO, &purity, sizeof (purity),
            OCI_ATTR_PURITY, errhp);
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL);
/* poolName is the name returned by OCISessionPoolCreate() */

/*  OCISessionGet mode method */
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
               NULL, NULL, NULL, OCI_SESSGET_SPOOL | OCI_SESSGET_PURITY_NEW);
/* poolName is the name returned by OCISessionPoolCreate() */

OCISessionPoolCreate (envhp, errhp, spoolhp, &poolName, &poolNameLen, "HRDB",
    strlen("HRDB"), 0, 10, 1, "HR", strlen("HR"), "HR", strlen("HR"),
    OCI_SPC_HOMOGENEOUS);
 
OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO, "HRMS", strlen ("HRMS"),
    OCI_ATTR_CONNECTION_CLASS, errhp);
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
    NULL, NULL, NULL, OCI_SESSGET_SPOOL);

OCISessionPoolCreate (envhp, errhp, spoolhp, &poolName, &poolNameLen, "HRDB",
    strlen("HRDB"), 0, 10, 1, "HR", strlen("HR"), "HR", strlen("HR"),
    OCI_SPC_HOMOGENEOUS);
 
OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO,  "RECMS", strlen("RECMS"),
    OCI_ATTR_CONNECTION_CLASS, errhp);
OCISessionGet (envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
    NULL, NULL, NULL, OCI_SESSGET_SPOOL);

2.12.6 Starting Database Resident Connection Pool

The DBA must log on as SYSDBA and start the default pool, SYS_DEFAULT_CONNECTION_POOL, using DBMS_CONNECTION_POOL.START_POOL with the default settings.

For detailed information about configuring the pool, see Oracle Database Administrator's Guide.

2.12.7 Enabling DRCP

To enable DRCP in an application, specify either :POOLED in the Easy Connect string (as in Example 2-4) or (SERVER=POOLED) in the TNS connect string (as in Example 2-5).

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

BOOKSDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost.company.com)
 (PORT=1521))(CONNECT_DATA = (SERVICE_NAME=books.company.com)(SERVER=POOLED)))

2.12.8 Benefiting from the Scalability of DRCP in an OCI Application

Consider the following OCI application scenarios and how they benefit from DRCP:

• An application neither uses the OCI session pool nor specifies a connection class or purity setting (or specifies PURITY=NEW).

  The application gets a new session from DRCP. When the application returns a connection to the pool, the session is not shared with other instances of the same application by default. Therefore, the pooled server remains assigned to the client for the life of the client session. (SQL*Plus is an example of a client that does not use the OCI session pool. SQL*Plus keeps connections even when they are idle.)

  The application benefits from reusing an existing pooled server.

• An application invokes OCISessionGet() outside of the OCI session pool, or to specify the connection class and PURITY=SELF.

  The application can reuse both DRCP pooled servers and sessions. However, after an OCISessionRelease() call, OCI terminates the connection to the connection broker. On the next OCISessionGet() call, the application reconnects to the broker, and then DRCP assigns a pooled server (and session) belonging to the specified connection class. Reconnecting incurs the cost of connection establishment and reauthentication.

  The application achieves better sharing of DRCP resources (processes and sessions) but does not benefit from caching connections to the connection broker.

• An application uses OCI session pool APIs, specifies a connection class, and specifies PURITY=SELF.

  The application uses all DRCP functionality, reusing both the pooled server and the associated session and benefiting from cached connections to the connection broker. Cached connections do not incur the cost of reauthentication on the OCISessionGet() call.

2.12.9 Benefiting from the Scalability of DRCP in a Java Application

Java applications benefit from DRCP as OCI applications do (see Section 2.12.8).

A customer who uses Universal Connection Pool (UCP), or uses ConnectionPoolDataSource as the connection factory, can upgrade to using DRCP by changing only the configuration (not the code).

2.12.10 Best Practices for Using DRCP

The steps for designing an application that can benefit from the full power of DRCP are very similar to those for an application that uses the OCI session pool (for details, see Oracle Call Interface Programmer's Guide).

The only additional step is that for best performance, when deployed to run with DRCP, the application must explicitly specify a connection class.

Multiple instances of the same application must specify the same connection class for best performance and enhanced sharing of DRCP resources. Ensure that the different instances of the application can share database sessions.

Example 2-6 shows a DRCP application.

/* Assume that all necessary handles are allocated. */
 
/*   This middle tier uses a single database user. Create a homogeneous
     client-side session pool */
OCISessionPoolCreate (envhp, errhp, spoolhp, &poolName, &poolNameLen, "BOOKSDB",
    strlen("BOOKSDB"), 0, 10, 1, "SCOTT", strlen("SCOTT"), "password",
    strlen("password"), OCI_SPC_HOMOGENEOUS);
 
while (1)
{
   /* Process a client request */
   WaitForClientRequest();
   /* Application function */
 
   /* Set the Connection Class on the OCIAuthInfo handle that is passed as
      argument to OCISessionGet*/
   
   OCIAttrSet (authInfop, OCI_HTYPE_AUTHINFO,  "BOOKSTORE", strlen("BOOKSTORE"),
               OCI_ATTR_CONNECTION_CLASS, errhp);
 
   /* Purity need not be set, as default is OCI_ATTR_PURITY_SELF for OCISessionPool
       connections */
 
   /* You can get a SCOTT session released by Middle-tier 2 */
   OCISessionGet(envhp, errhp, &svchp, authInfop, poolName, poolNameLen, NULL, 0,
                 NULL, NULL, NULL, OCI_SESSGET_SPOOL); 
 
   /* Database calls using the svchp obtained above  */
   OCIStmtExecute(...)
 
   /* This releases the pooled server on the database for reuse */
   OCISessionRelease (svchp, errhp, NULL, 0, OCI_DEFAULT);
}
 
/* Middle tier is done - exiting */
OCISessionPoolDestroy (spoolhp, errhp, OCI_DEFAULT);

Example 2-7 and Example 2-8 show connect strings that deploy code in 10 middle-tier hosts that service the BOOKSTORE application from Example 2-6.

In Example 2-7, assume that the database is Oracle Database 12c (or earlier) in dedicated server mode with DRCP not enabled and that the client has 12c libraries. The application gets dedicated server connections from the database.

BOOKSDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost.company.com)
 (PORT=1521))(CONNECT_DATA = (SERVICE_NAME=books.company.com)))

In Example 2-8, assume that DRCP is enabled on the Oracle Database 12c database. All middle-tier processes can benefit from the pooling capability of DRCP. The database resource requirement with DRCP is much less than it would be in dedicated server mode.

BOOKSDB = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost.company.com)
 (PORT=1521))(CONNECT_DATA = (SERVICE_NAME=books.company.com)(SERVER=POOLED)))

2.12.11 Compatibility and Migration

An OCI application linked with Oracle Database 12c client libraries works unaltered with:

• An Oracle Database 12c database with DRCP disabled

• A database server from a release earlier than Oracle Database 12c

• An Oracle Database 12c database server with DRCP enabled, when deployed with the DRCP connect string

Suitable clients benefit from enhanced scalability offered by DRCP if they are appropriately modified to use the OCI session pool APIs with the connection class and purity settings previously described.

As of Oracle Database 12c Release 1 (12.1.0.1), Oracle JDBC drivers support DRCP. For more information, see Oracle Database JDBC Developer's Guide.

2.12.12 DRCP Restrictions

The following cannot be performed with pooled servers:

• Shutting down the database

• Stopping DRCP

• Changing the password for the connected user

• Using shared database links to connect to a DRCP that is on a different instance

• Using Advanced Security Options (ASO) with TCPS

• Using Enterprise user security with DRCP

• Using migratable sessions on the server side, either directly (using the OCI_MIGRATE option) or indirectly (invoking OCISessionPoolCreate())

• Using initial client roles

• Using application context attributes (such as OCI_ATTR_APPCTX_NAME and OCI_ATTR_APPCTX_VALUE)

• Using Transaction Guard (explained in Chapter 25) with DRCP

• Using Application Continuity (explained in Chapter 26) with DRCP

Sessions created before DDL statements run can be assigned to clients after DDL statements run. Therefore, be careful when running DDL statements that affect database users in the pool. For example, before dropping a user, ensure that there are no sessions of that user in the pool and no connections to the broker that were authenticated as that user.

If sessions with explicit roles enabled are released to the pool, they can later be assigned to connections (of the same user) that need the default logon role. Therefore, avoid releasing sessions with explicit roles; instead, terminate them.

Users can mix data encryption/data integrity combinations. However, users must segregate each such combination by using connection classes. For example, if the user application must specify AES256 as the encryption mechanism for one set of connections and DES for another set of connections, then the application must specify different connection classes for each set.

2.12.13 Using DRCP with Custom Pools

Oracle highly recommends using the OCI session pool, which is already integrated with DRCP, FAN, and RLB. However, an application that does not use the OCI session pool can still use DRCP if either of the following is true:

• The application was built using its own custom connection pool.

• The application uses no pool, but has periods when it does not use its session (and could therefore release it to a pool) and does not depend on getting back the same session

To use DRCP with such an application, the session must be stateful; that is, the session must have the OCI_ATTR_SESSION_STATE attribute (described in Oracle Call Interface Programmer's Guide). When an application is stateful and DRCP is enabled, OCI transparently assigns it an appropriate session from the DRCP pool. If the application is stateless (has the OCI_SESSION_STATELESS attribute) and DRCP is enabled, OCI transparently returns the session to the DRCP pool.

Applications must identify session state as promptly as possible for efficient utilization of underlying database resources. For more information, see Section 2.12.14.

2.12.14 Explicitly Marking Sessions Stateful or Stateless

An application typically requires a specific database session for the duration of a unit of work. For this duration, the session is stateful. After this duration, if the application does not depend on retaining the specific session for subsequent units of work, then the session is stateless.

When an application or caller detects a session's transition from stateful to stateless, or the reverse, the application can explicitly inform OCI of the transition by using the OCI_ATTR_SESSION_STATE or OCI_SESSION_STATELESS attribute. This information lets OCI and Oracle Database transparently perform scalability optimizations, such as reassigning the session that the application is not using to someone else and then assigning the application a new session when necessary.

Example 2-9 shows a code fragment that explicitly marks session states.

wait_for_transaction_request();
do {
 
ub1 state;
 
/* mark  database session as STATEFUL  */
state = OCI_SESSION_STATEFUL;
checkerr(errhp, OCIAttrSet(usrhp, OCI_HTYPE_SESSION,
        &state, 0, OCI_ATTR_SESSION_STATE, errhp));
/* do database work consisting of one or more related calls to the database */
 
...
 
/* done with database work, mark session as stateless */
state = OCI_SESSION_STATELESS;
checkerr(errhp, OCIAttrSet(usrhp, OCI_HTYPE_SESSION,
         &state, 0,OCI_ATTR_SESSION_STATE, errhp));
 
wait_for_transaction_request();
 
} while(not _done); 

A session obtained from outside the OCI session pool is marked OCI_SESSION_STATEFUL and remains OCI_SESSION_STATEFUL unless the application explicitly marks it OCI_SESSION_STATELESS.

A session obtained from the OCI session pool is marked OCI_SESSION_STATEFUL by default when the first call is initiated on that session. When the session is released to the pool, it is marked OCI_SESSION_STATELESS by default. Therefore, you need not explicitly mark sessions as stateful or stateless when you use the OCI session pool.

2.12.15 Using DRCP with Oracle Real Application Clusters

Oracle Real Application Clusters (Oracle RAC) is a database option in which a single database is hosted by multiple instances on multiple nodes. When DRCP is configured in a database in an Oracle RAC environment, the pool configuration is applied to each database instance. Starting or stopping the pool on one instance starts or stops the pool on all instances.

2.12.16 Using DRCP with Pluggable Databases

The DRCP in a multitenant container database (CDB) is configured and managed in the root container. You can configure, start, and stop the pool when you are connected to the root container. The pool maintains the pooled servers of different pluggable databases to which the clients are connected using different service names.

2.12.17 DRCP with Data Guard

When operating DRCP in a Data Guard environment:

• On a physical standby database:

  • You can start the pool only if the pool is running on the primary database.

  • You can stop the pool only if the pool is stopped on the primary database.

  • You cannot configure, restore to defaults, or alter pool parameters.

  The preceding restrictions cease to apply to the physical standby database if it becomes the primary database.

• On a logical standby database, all pool operations are allowed.

2.13.1 About Runtime Connection Load Balancing

Oracle Real Application Clusters (Oracle RAC) is a database option in which a single database is hosted by multiple instances on multiple nodes. The Oracle RAC shared disk method of clustering databases increases scalability. The nodes can easily be added or freed to meet current needs and improve availability, because if one node fails, another can assume its workload. Oracle RAC adds high availability and failover capacity to the database, because all instances have access to the whole database.

Work requests are balanced at both connect time (connect time load balancing, provided by Oracle Net Services) and runtime (runtime connection load balancing). For Oracle RAC environments, session pools use service metrics received from the Oracle RAC load balancing advisory through Fast Application Notification (FAN) events to balance application session requests. The work requests coming into the session pool can be distributed across the instances of Oracle RAC offering a service, using the current service performance.

Connect time load balancing occurs when an application creates a session. Pooled sessions must be well distributed across Oracle RAC instances when the sessions are created to ensure that sessions on each instance can execute work.

Runtime connection load balancing occurs when an application selects a session from an existing session pool (and thus is a very frequent activity). Runtime connection load balancing routes work requests to sessions in a session pool that best serve the work. For session pools that support services at only one instance, the first available session in the pool is adequate. When the pool supports services that span multiple instances, the work must be distributed across instances so that the instances that are providing better service or have greater capacity get more work requests.

OCI, OCCI, JDBC, and ODP.NET client applications all support runtime connection load balancing.

2.13.2 Enabling and Disabling Runtime Connection Load Balancing

Enabling and disabling runtime connection load balancing on the client depends on the client environment.

Topics:

• OCI

• OCCI

• JDBC

• ODP.NET

2.13.3 Receiving Load Balancing Advisory FAN Events

Your application can receive load balancing advisory FAN events only if all of these requirements are met:

• Oracle RAC environment with Oracle Clusterware is set up and enabled.

• The server is configured to issue event notifications.

• The application is linked with the threads library.

• The OCI environment is created in OCI_EVENTS and OCI_THREADED modes.

• The OCCI environment is created in THREADED_MUTEXED and EVENTS modes.

• You configured or modified the Oracle RAC environment using the DBMS_SERVICE package.

  You must modify the service to set up its goal and the connection load balancing goal as follows:

  EXEC DBMS_SERVICE.MODIFY_SERVICE("myService",
       DBMS_SERVICE.GOAL_SERVICE_TIME,
       clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);
  

  The constant GOAL_SERVICE_TIME specifies that Load Balancing Advisory is based on elapsed time for work done in the service plus bandwidth available to the service.

  The constant CLB_GOAL_SHORT specifies that connection load balancing uses Load Balancing Advisory, when Load Balancing Advisory is enabled.You can set the connection balancing goal to CLB_GOAL_LONG. However, CLB_GOAL_LONG is typically useful for closed workloads (that is, when the rate of completing work is equal to the rate of starting new work).