1. Database Development Guide
  2. Database Development Fundamentals
  3. Performance and Scalability

4 Performance and Scalability

Explains techniques for designing performance and scalability into the database and database applications.

Topics:

4.1 Performance Strategies

Topics:

4.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
4.1.1.1 Analyze the Data Requirements of the Application

Analyze the data requirements of your application by following this basic procedure:

  1. Collect data.

    Interview people to learn about the business, the nature of the application, who uses information and how, and the expectations of end users. Collect business documents—personnel forms, invoice forms, order forms, and so on—to learn how the business uses information.

  2. Analyze the collected data.

    This bottom-up process includes normalization of the data, entity-relationship modeling, and transaction analysis.

  3. Do a functional analysis of the data.

    The end result of this top-down process is a data flow diagram that identifies the main process blocks and how data flows into and out of them over its life time.

4.1.1.2 Create the Database Design for the Application

To create the database design, you must first create the logical design, and then translate this logical design into a physical design.

Topics:

See Also:

Oracle Database Performance Tuning Guide for more information about designing and developing for performance

4.1.1.2.1 Create the Logical Design

The logical design is a graphical representation of the database. The logical design models both relationships between database objects and transaction activity of the application. Effective logical design considers the requirements of different users who must own, access, and update data.

To model relationships between database objects:

  1. Translate the data requirements into data items (columns).
  2. Group related columns into tables.
  3. Map relationships among columns and tables, determining primary and foreign key attributes for each table.
  4. Normalize the tables to minimize redundancy and dependency.

To model transaction activity:

  • Know the most common transactions and those that users consider most important.

  • Trace transaction paths through the logical model.

  • Prototype transactions in SQL and develop a volume table that indicates the size of your database.

  • Determine which tables are accessed by which users in which sequences, which transactions read data, and which transactions write data.

  • Determine whether the application mostly reads data or mostly writes data.

4.1.1.2.2 Create the Physical Design

The physical design is the implementation of the logical design on the physical database.

Because the logical design integrates the information about tables and columns, the relationships between and among tables, and all known transaction activity, you know how the database stores data in tables and creates other structures, such as indexes.

Using this knowledge, create scripts that use SQL data definition language (DDL) to create the schema definition, define the database objects in their required sequence, define the storage requirements to specification, and so on.

4.1.1.3 Implement the Database Application

Implement the database application by following this basic procedure:

  1. Implement the application in a test environment.

    In a test environment that is as similar as possible to the production environment, run the scripts that implement the physical database design. Load the data into the physical schema. Select the programming language in which to develop the application, develop the user interface, create and test the transactions, and so on.

  2. Ensure that the application runs to specification.

    Ensure that all components are exercised, the application is fully operational, and the database features that the application uses are optimally configured.

  3. Run benchmark tests on the application.

    Benchmark tests determine whether the application performs as expected under various workloads (including peak activity) with simulated real-time operations, such as adding data and users. Ensure that the application scales well.

    If the application does not meet the benchmarks, tune your SQL statements to perform optimally, first with no workload and then with increasing workloads.

  4. Implement the application in the production environment.

See Also:

4.1.1.4 Maintain the Database and Database Application

Maintaining the database, the database application, and the operating system are on-going tasks for the database administrator, the application developer, and the system administrator, respectively. The resources that the business allocates to maintenance depend on the importance of the database and the database application, its growth potential, the need to accommodate more users, and so on.

If you are responsible for maintenance, you must periodically monitor the system, schedule maintenance periods, and inform users of upcoming maintenance periods. If maintenance periods require down time, schedule them for periods with little or no database activity.

Application maintenance includes fixing bugs, applying patches, and releasing upgrades. Test maintenance work in a test environment to catch and resolve any before implemented it on production systems.

4.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.

4.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.

See Also:

Oracle Database Performance Tuning Guide for more information about benchmarking applications

4.2 Tools for Performance

Topics:

Several tools report runtime performance information about your application.

See Also:

Oracle Database Testing Guide for more information about tools for tuning the database

4.2.1 DBMS_APPLICATION_INFO Package

Use the DBMS_APPLICATION_INFO package with the SQL Trace facility 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.

See Also:

4.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.

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.

See Also:

4.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.

Note:

You must regularly test code that contains hints, because changing database conditions and query performance enhancements in subsequent releases can significantly impact how hints affect performance.

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.

See Also:

4.3 Monitoring Database Performance

Oracle Database provides advisors and powerful tools to help you manage and tune your database.

Topics:

4.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.)

See Also:

Oracle Database 2 Day + Performance Tuning Guide for more information about configuring ADDM, reviewing ADDM analysis, interpreting ADDM findings, implementing ADDM recommendations, and viewing snapshot statistics using Enterprise Manager

4.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.

See Also:

Oracle Database 2 Day + Performance Tuning Guide for more information about monitoring real-time database performance

4.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.

See Also:

4.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.

See Also:

4.4 Testing for Performance

When testing an application for performance, follow these guidelines:

  • Use Automatic Database Diagnostic Monitor (ADDM) and SQL Tuning Advisor for design validation.

    ADDM determines where database performance problems might exist and recommends corrections. For example, if ADDM finds high-load SQL statements, then you can use SQL Tuning Advisor to analyze those statements and provide tuning recommendations.

  • Test with realistic data volumes and distributions.

    The test database must contain data representative of the production system. Tables must be fully populated and represent the data volume and cardinality between tables found in the production system. All production indexes must be built and the schema statistics must be populated correctly.

  • Test with the optimizer mode to be used in production.

    Because Oracle Database research and development focuses on the query optimizer, Oracle re commands using the query optimizer in both the test and production environments.

  • Test a single user performance first.

    Start testing with a single user on an idle or lightly-used database. If a single user cannot achieve acceptable performance under ideal conditions, then multiple users cannot achieve acceptable performance under real conditions.

  • Get an execution plan for each SQL statement.

    Verify that the optimizer uses optimal execution plans, and that you understand the relative cost of each SQL statement in terms of CPU time and physical I/O. Identify heavily used transactions that would benefit most from tuning.

  • Test with multiple users.

    This testing is difficult to perform accurately because user workload and profiles might not be fully quantified. However, you must test DML transactions in a multiuser environment to ensure that there are no locking conflicts or serialization problems.

  • Test with a hardware configuration as close as possible to the production system.

    Testing on a realistic system is particularly important for network latencies, I/O subsystem bandwidth, and processor type and speed. Testing on an unrealistic system can fail to reveal potential performance problems.

  • Measure steady state performance.

    Each benchmark run must have a ramp-up phase, where users connect to the database and start using the application. This phase lets frequently cached data be initialized into the cache and lets single-execution operations (such as parsing) be completed before reaching the steady state condition. Likewise, each benchmark run must end with a ramp-down period, where resources are freed from the system and users exit the application and disconnect from the database.

See Also:

4.5 Using Client Result Cache

Topics:

4.5.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 the 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 the 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 the client result cache, you must also enable OCI statement caching or cache statements at the application level.

Note:

Oracle Database 18.1 onwards, Client Result Cache supports dynamic binding

See Also:

4.5.2 Benefits of Client Result Cache

The benefits of the client result cache are:

  • Client result cache is on the client, therefore 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.

See Also:

OCIStmtExecute() and OCIStmtFetch2() in Oracle Call Interface Programmer's Guide

4.5.3 Guidelines for Using Client Result Cache

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

  1. Query

    For a specific query, you can enable or disable the client result cache with a SQL hint. 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 the client result cache with a table annotation, without changing the application.

  3. Session

    For all queries in your database session, you can enable or disable the client result cache with a session parameter, without changing the application.

Higher-level settings take precedence over lower-level ones.

Oracle recommends enabling the client result cache only for queries on read-only and seldom-updated tables (tables that are rarely updated). That is, enable the 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 the 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 the 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.

See Also:

Topics:

4.5.3.1 SQL Hints

The SQL hint RESULT_CACHE or NO_RESULT_CACHE applies to a single query, for which it enables or disables the client result cache. These hints take precedence over both table annotations and the RESULT_CACHE_MODE server initialization parameter.

For OCI, the SQL hint /*+ result_cache */ or /*+ no_result_cache */ must be set in SQL text passed to OCIStmtPrepare() and OCIStmtPrepare2() calls.

For JDBC OCI, the SQL hint /*+ result_cache */ or /*+ no_result_cache */ is included in the query (SELECT statement) as part of the query string.

See Also:

4.5.3.2 Table Annotation

A table annotation enables or disables the client result cache for all queries on a specific table. A table annotation takes precedence over the RESULT_CACHE_MODE server initialization parameter, but the SQL hints /*+ RESULT_CACHE */ and /*+ NO_RESULT_CACHE */ take precedence over a table annotation.

You annotate a table with either the CREATE TABLE or ALTER TABLE statement, using the RESULT_CACHE clause. To enable the client result cache, specify RESULT_CACHE (MODE FORCE); to disable it, use RESULT_CACHE (MODE DEFAULT).

Table 4-1 summarizes the table annotation result cache modes.

Table 4-1 Table Annotation Result Cache Modes

Mode Description

DEFAULT

The default value. Result caching is not determined at the table level. You can use this value to clear any table annotations.

FORCE

If all table names in the query have this setting, then the query result is always considered for caching unless the NO_RESULT_CACHE hint is specified for the query. If one or more tables named in the query are set to DEFAULT, then the effective table annotation for that query is DEFAULT.

See Also:

4.5.3.3 Session Parameter

The session parameter RESULT_CACHE_MODE enables or disables the client result cache for all queries for your database session. RESULT_CACHE_MODE can be overruled for specific tables by table annotations and for specific queries by SQL hints.

You can set RESULT_CACHE_MODE in either the server parameter file (init.ora) or the ALTER SESSION or ALTER SYSTEM statement. To enable the client result cache, set RESULT_CACHE_MODE to FORCE; to disable it, set RESULT_CACHE_MODE to MANUAL.

See Also:

Oracle Database Reference for more information about RESULT_CACHE_MODE

4.5.3.4 Effective Table Result Cache Mode

The effective result cache mode for a table depends on both the table annotation result cache mode and the RESULT_CACHE_MODE session parameter setting, as Table 4-2 shows.

Table 4-2 Effective Result Cache Table Mode

Table Annotation Result Cache Mode RESULT_CACHE_MODE = MANUAL RESULT_CACHE_MODE = FORCE

FORCE

FORCE

FORCE

DEFAULT

MANUAL

FORCE

When the effective mode is FORCE, every query is considered for result caching unless the query has the NO_RESULT_CACHE hint, but actual result caching depends on internal restrictions for client and server caches, query potential for reuse, and space available in the client result cache.

The effective table result cache mode FORCE is similar to the SQL hint RESULT_CACHE in that both are only requests.

4.5.3.5 Displaying Effective Table Result Cache Mode

To display the result cache mode for one or more tables, see the RESULT_CACHE column of a *_TABLES static data dictionary view. For example:

  • This query displays the result cache mode for the table T: 

    SELECT result_cache FROM all_tables WHERE table_name = 'T'

  • This query displays the result cache mode for all relational tables that you own:

    SELECT table_name, result_cache FROM user_tables

If the result cache mode is DEFAULT, then the table is not annotated.

If the result cache mode is FORCE, then the table was annotated with the RESULT_CACHE (MODE FORCE) clause of the CREATE TABLE or ALTER TABLE statement.

If the result cache mode is MANUAL, then the session parameter RESULT_CACHE_MODE was set to MANUAL in either the server parameter file (init.ora) or an ALTER SESSION or ALTER SYSTEM statement.

See Also:

Oracle Database Reference for more information about *_TABLES static data dictionary views

4.5.3.6 Result Cache Mode Use Cases

The following examples show when SQL hints take precedence over table annotations and the RESULT_CACHE_MODE session parameter.

  • If the emp table is annotated with ALTER TABLE emp RESULT_CACHE (MODE FORCE) and the session parameter has its default value, MANUAL, then queries on emp are considered for result caching.

    However, results of the query SELECT /*+ no_result_cache */ empno FROM emp are not cached, because the SQL hint takes precedence over the table annotation and session parameter.

  • If the emp table is not annotated, or is annotated with ALTER TABLE emp RESULT_CACHE (MODE DEFAULT), and the session parameter has its default value, MANUAL, then queries on emp are not result cached.

    However, results of the query SELECT /*+ result_cache */ * FROM emp are considered for caching, because the SQL hint takes precedence over the table annotation and session parameter.

  • If the session parameter RESULT_CACHE_MODE is set to FORCE, and no table annotations or SQL hints override it, then all queries on all tables are considered for query caching.

4.5.3.7 Queries Never Result Cached in Client Result Cache

Results of the following queries are never cached in the client result cache (even if the queries include the RESULT_CACHE hint):

  • Queries that contain any of the following:

    • Remote object

    • Complex type in the select list

    • PL/SQL function

  • Snapshot-based queries

  • Flashback queries

  • Queries executed in serializable, read-only transactions

  • Queries of tables on which virtual private database (VPD) policies are enabled

Such queries might be cached on the database if the server result cache feature is enabled—for more information, see Oracle Database Concepts.

4.5.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 cache 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 the client result cache.

This consistency mechanism ensures that the 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 the 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, the 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 the client result cache transparently keeps its result sets consistent with such changes.

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

See Also:

OCIStmtExecute() in Oracle Call Interface Programmer's Guide

4.5.5 Deployment-Time Settings for Client Result Cache

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

  • Server initialization parameters

  • Client configuration parameters

  • Table annotations

  • RESULT_CACHE_MODE session parameter

See Also:

4.5.5.1 Server Initialization Parameters

The server initialization parameters to set for the client result cache when you deploy your application are:

4.5.5.1.1 COMPATIBLE

Specifies the release with which Oracle Database must maintain compatibility. To enable the client result cache, COMPATIBLE must be at least 11.0.0.0. To enable client the result cache for views, COMPATIBLE must be at least 11.2.0.0.

4.5.5.1.2 CLIENT_RESULT_CACHE_SIZE

Specifies the maximum size of the client result set cache for each OCI client process. The default value, 0, means that the client result cache is disabled. To enable the client result cache, set CLIENT_RESULT_CACHE_SIZE to at least 32768 bytes (32 kilobytes (KB)).

If the client result cache is enabled on the server by CLIENT_RESULT_CACHE_SIZE, then its value can be overridden by the sqlnet.ora configuration parameter OCI_RESULT_CACHE_MAX_SIZE. If the client result cache is disabled on the server, then OCI_RESULT_CACHE_MAX_SIZE is ignored and the client result cache cannot be enabled.

Oracle recommends either enabling the client result cache for all Oracle Real Application Clusters (Oracle RAC) nodes or disabling the client result cache for all Oracle RAC nodes. Otherwise, within a client process, some sessions might have caching enabled and other sessions might have caching disabled (thereby getting the latest results from the server). This combination might present an inconsistent view of the database to the application.

CLIENT_RESULT_CACHE_SIZE is a static parameter. Therefore, if you use an ALTER SYSTEM statement to change the value of CLIENT_RESULT_CACHE_SIZE, you must include the SCOPE = SPFILE clause and restart the database before the change will take effect.

The maximum value for CLIENT_RESULT_CACHE_SIZE is the least of these values:

  • Available client memory

  • ((Possible number of result sets to be cached) * (average size of a row in a result set) * (average number of rows in a result set))

  • 2 gigabytes (GB)

    If you specify a value greater than 2 GB, then the value is 2 GB.

Note:

Do not set the CLIENT_RESULT_CACHE_SIZE parameter during database creation, because that can cause errors.

4.5.5.1.3 CLIENT_RESULT_CACHE_LAG

Specifies the maximum time in milliseconds that the client result cache can lag behind changes in the database that affect its result sets. The default is 3000 milliseconds.

CLIENT_RESULT_CACHE_LAG is a static parameter. Therefore, if you use an ALTER SYSTEM statement to change the value of CLIENT_RESULT_CACHE_LAG, you must include the SCOPE = SPFILE clause and restart the database before the change will take effect.

4.5.5.2 Client Configuration Parameters

Client configuration parameters are optional, but if set, they override the equivalent parameters in the server initialization file init.ora.

Client configuration parameters can be set in the oraaccess.xml file, the sqlnet.ora file, or both. When equivalent parameters are set both files, the oraaccess.xml setting takes precedence over the corresponding sqlnet.ora setting. When a parameter is not set in oraaccess.xml, the process searches for its setting in sqlnet.ora.

When a client configuration parameter can be set in both oraaccess.xml and sqlnet.ora, Oracle recommends setting the parameter in oraaccess.xml. However, for a network configuration, sqlnet.ora is the primary file, because oraaccess.xml does not support network level settings.

Table 4-3 describes the equivalent oraaccess.xml and sqlnet.ora client configuration parameters.

Table 4-3 Client Configuration Parameters (Optional)

oraacess.xml Parameter sqlnet.ora Parameter Description

max_size

OCI_RESULT_CACHE_MAX_SIZE

Maximum size in bytes for the client result cache for each process.

Specifying a size less than 32768 in sqlnet.ora disables client result cache for client processes the read sqlnet.ora.

max_rset_size

OCI_RESULT_CACHE_MAX_RSET_SIZE

Maximum size of any result set in bytes in the client result cache for each process.

max_rset_rows

OCI_RESULT_CACHE_MAX_RSET_ROWS

Maximum size of any result set in rows in the client result cache for each process.

The result cache lag cannot be set on the client.

4.5.6 Client Result Cache Statistics

On round trips to the server from the OCI client, OCI periodically sends the 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 the client result cache statistics. More precisely, the cache miss count equals the number of server executions in server Automatic Workload Repository (AWR) reports.

See Also:

4.5.7 Validation of Client Result Cache

Some ways to validate client result cache are:

4.5.7.1 Measure Execution Times

First, measure the execution time of the queries without RESULT_CACHE hints. Then add RESULT_CACHE hints to the queries and measure the execution time again. The difference in execution times is your performance gain.

4.5.7.2 Query V$MYSTAT

Note:

To query the V$MYSTAT view, you must have the SELECT privilege on it.

  1. Run this query 5 times:
    SELECT count(*) FROM table_name
  2. Query V$MYSTAT:
    SELECT * FROM V$MYSTAT
  3. Run this query 5 times:
    SELECT /*+ result_cache */ count(*) FROM table_name

    Because the query results are cached, this step requires fewer round trips between client and server than step 1 did.

  4. Query V$MYSTAT:
    SELECT * FROM V$MYSTAT

    Compare the values of the columns for this query to those for the query in step 2.

Instead of adding the hint to the query in step 3, you can add the table annotation RESULT_CACHE (MODE FORCE) to table_name at step 3 and then run the query in step 1 a few times.

4.5.7.3 Query V$SQLAREA

Note:

To query the V$SQLAREA view, you must have the SELECT privilege on it.

  1. Run this query 5 times:
    SELECT count(*) FROM table_name
  2. Query V$SQLAREA:
    SELECT executions, fetches, parse_calls FROM V$SQLAREA
  WHERE sql_text LIKE '% FROM table_name'
  3. Run this query 5 times:
    SELECT /*+ result_cache */ count(*) FROM table_name
  4. Query V$SQLAREA:
    SELECT executions, fetches, parse_calls FROM V$SQLAREA
  WHERE sql_text LIKE '% FROM table_name'

    Compare the values of the columns executions, fetches, and parse_calls for this query to those for the query in step 2. The difference in execution times is your performance gain.

Instead of adding the hint to the query in step 3, you can add the table annotation RESULT_CACHE (MODE FORCE) to table_name at step step 3 and then run the query in step step 1 a few times.

4.5.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 the server result cache caches result sets and query fragments in server SGA memory.

You can enable the client result cache independently of the server result cache, though they share the result cache SQL hints, table annotations, and session parameter RESULT_CACHE_MODE. Table 4-4 shows the result cache association for result cache parameters, the PL/SQL package DBMS_RESULT_CACHE, and result cache views.

See Also:

Oracle Database Concepts

Table 4-4 Setting Client Result Cache and Server Result Cache

Parameters, PL/SQL Package, and Database Views Result Cache Association

CLIENT_RESULT_CACHE_* parameters:

  • CLIENT_RESULT_CACHE_SIZE

  • CLIENT_RESULT_CACHE_LAG

client result cache

SQL hints:

  • RESULT_CACHE

  • NO_RESULT_CACHE

client result cache, server result cache

sqlnet.ora OCI_RESULT_CACHE* parameters:

  • OCI_RESULT_CACHE_MAX_SIZE

  • OCI_RESULT_CACHE_MAX_RSET_SIZE

  • OCI_RESULT_CACHE_MAX_RSET_ROWS

client result cache

CLIENT_RESULT_CACHE_STATS$ view

client result cache

RESULT_CACHE_MODE parameter

client result cache, server result cache

All other RESULT_CACHE_* parameters (for example, RESULT_CACHE_MAX_SIZE)

server result cache

DBMS_RESULT_CACHE package

server result cache

V$RESULT_CACHE_* and GV$RESULT_CACHE_* views (for example, V$RESULT_CACHE_STATISTICS and GV$RESULT_CACHE_MEMORY)

server result cache

CREATE TABLE annotation

client result cache, server result cache

ALTER TABLE annotation

client result cache, server result cache

4.5.9 Client Result Cache Demo Files

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

4.5.10 Client Result Cache Compatibility with Previous Releases

To use the 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.

See Also:

OCIStmtPrepare(), OCIStmtPrepare2() in Oracle Call Interface Programmer's Guide

4.6 Statement Caching

Statement caching is a feature that establishes and manages a cache of statements for each session. In the server, statement caching lets cursors be used without reparsing the statement, eliminating repetitive statement parsing. You can use statement caching with both connection pooling and session pooling, thereby improving performance and scalability. You can also use statement caching without session pooling in OCI and without connection pooling in OCCI, in the JDBC interface, and in the ODP.NET interface. You can also use dynamic SQL statement caching in Oracle precompiler applications that rely on dynamic SQL statements, such as Pro*C/C++ and ProCOBOL.

In the JDBC interface, you can enable and disable implicit and explicit statement caching independently of the other—you can use either, neither, or both. Implicit and explicit statement caching share a single cache for each connection. You can also disable implicit caching for a particular statement.

See Also:

4.7 OCI Client Statement Cache Auto-Tuning

OCI client statement cache auto-tuning optimizes OCI client session features of middle-tier applications to improve performance without changing your OCI application.

Without auto-tuning, the OCI client statement cache size setting can become suboptimal—for example, when a changing workload causes a different working set of SQL statements. If the size is too low, it causes excess network activity and more parses at the server. If the size is too high, it causes excess memory use. It can be difficult for the client application to keep the cache size optimal.

Auto-tuning solves this potential performance problem by automatically and periodically reconfiguring the OCI statement cache size.

Auto-tuning is achieved by providing a deployment-time setting that provides an option to reconfigure OCI statement caching. These settings are provided as connect-string-based deployment settings in a client oraaccess.xml file that overrides programmatic settings to the user configuration of OCI features.

Middle-tier application developers and database administrators (DBAs) can expect reduced time and effort in diagnosing and fixing performance problems with each part of their system using the auto-tuning OCI client statement caching parameter setting.

See Also:

4.8 Client-Side Deployment Parameters

Beginning with Oracle Database 12c Release 1 (12.1.0.1), OCI deployment parameters are available in a new configuration file (oraaccess.xml).

See Also:

Oracle Call Interface Programmer's Guide.

4.9 Using Query Change Notification

Continuous Query Notification (CQN) lets client applications register queries with the database and receive notifications of DML or DDL changes on the objects (object change notification (OCN)) or result set changes associated with the queries (query result change notification (QRCN)). The database publishes notifications when the DML or DDL transaction commits.

A CQN registration associates one or more queries with a notification type (OCN or QRCN) and a notification handler. To create a CQN registration, you can use:

  • PL/SQL interface

    When you use the PL/SQL interface, the notification handler is a server-side PL/SQL stored procedure. PL/SQL registration can be used by nonthreaded languages and systems, such as PHP. For PHP, the PL/SQL listener invokes a PHP callback when it receives the database notification.

  • Oracle Call Interface (OCI)

    When you use OCI, the notification handler is a client-side C callback procedure.

  • Java Database Connectivity (JDBC) interface

    When you use the JDBC interface, the JDBC driver creates a registration on the server. The JDBC driver launches a new thread that listens for notifications from the server (through a dedicated channel), converts them to Java events, and then notifies all listeners registered with this registration.

See Also:

4.10 Using Database Resident Connection Pool

Database Resident Connection Pool (DRCP) provides a connection pool in the database server for typical web application usage scenarios where the application acquires a database connection, works on the database for a relatively short time, and then releases the connection.

4.10.1 About Database Resident Connection Pool

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

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 configurable at the PDB level, or can be 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).

See Also:

4.10.2 Configuring DRCP

You can configure the DRCP at either the CDB or PDB level.

The database parameter ENABLE_PER_PDB_DRCP controls whether DRCP is configured in CDB DRCP mode or per-PDB DRCP mode.

You can configure Database Resident Connection Pool (DRCP) to use a CDB wide pool by setting the database parameter ENABLE_PER_PDB_DRCP=FALSE. The database administrator must start and configure DRCP using the DBMS_CONNECTION_POOL package in the ROOT container. In CDB DRCP mode, you cannot use subprograms in the DBMS_CONNECTION_POOL package to manage the pool at the PDB level. You can monitor pools usage and statistics in the ROOT container pertaining to the CDB by querying the GV$CPOOL_STATS, GV$CPOOL_CC_STATS, GV$SPOOL_CONN_INFO, GV$AUTHPOOL_STATS and GV$SPOOL_CC_INFO views. The SYS user in the PDB can view statistics from GV$SPOOL_CC_INFO and GV$AUTHPOOL_STATS. 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.

Alternatively, the database administrator can enable the per-PDB DRCP mode by setting the database parameter ENABLE_PER_PDB_DRCP=TRUE. In this mode, you can create an isolated set of pooled servers for each pluggable database. The DRCP connection broker is started by the ROOT container. The PDB administrator can configure, manage and monitor independent pools customized to each PDB specific needs using the DBMS_CONNECTION_POOL subprograms. Processes are not shared between PDBs. ROOT and PDBs only share metadata. They do not share data in pool config tables. Each container stores its own pool configuration data. The statistics in the views will be kept for each PDB. The PDB administrator can neither alter the broker parameters, such as MINSIZE, MAXCONN_CBROK and NUM_CBROK, nor can they set the broker parameters to 2147483647. These parameters can only be modified in the database parameter CONNECTION_BROKERS and apply to the entire CDB. The database parameters MIN_AUTH_SERVERS, MAX_AUTH_SERVERS and DRCP_DEDICATED_OPT are dynamically per PDB modifiable using the 'ALTER SYSTEM' command.

The DRCP_DEDICATED_OPT parameter controls the use of dedicated optimization with DRCP. It is disabled by default when per-PDB mode is enabled.

The MAX_TXN_THINK_TIME parameter specifies the maximum time of inactivity allowed before termination for a client with an open transaction. This can be set to allow more time than the client that does not have transactions (specified by the MAX_THINK_TIME parameter value). This allows efficient pool reuse, while giving incomplete transactions a longer time to conclude.

See Also:

4.10.3 Sharing Proxy Sessions

Starting with Oracle Database 12c Release 2 (12.2.0.1), proxy sessions in DRCP are shared among applications that are connected to the same schema.

4.10.4 Using JDBC with DRCP

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.

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

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.

See Also:

4.10.5 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.

To release a session to the OCI session pool for DRCP, an OCI application invokes OCISessionRelease().

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().

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

See Also:

4.10.6 Session Purity

Session purity specifies whether an OCI application can reuse a pooled session (OCI_SESSGET_PURITY_SELF) or must use a new session (OCI_SESSGET_PURITY_NEW).

The application can set session purity either on the OCIAuthInfo handle before invoking OCISessionGet() or in the mode parameter when invoking OCISessionGet().

Example 4-1 Setting Session Purity for New Session

This example shows how a connection pooling application sets up a new session.

/* OCIAttrSet method */
ub4 purity = OCI_ATTR_PURITY_NEW; 
OCIAttrSet(authInfop, OCI_HTYPE_AUTHINFO, &purity, (ub4)sizeof(purity) OCI_ATTR_PURITY, errhp);

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

Note:

When reusing a pooled session, the NLS attributes of the server override those of the client.

For example, if the client sets NLS_LANG to french_france.us7ascii and then is assigned a German session from the pool, the client session becomes German.

To avoid this problem, use connection classes to restrict sharing.

4.10.7 Connection Class

Connection class defines a logical name for the type of connection that an OCI application needs. When a pooled session has a connection class, OCI ensures that the session is not shared outside of that connection class.

For example, a connection class can prevent the following from sharing pooled sessions:

  • Different users

    (A session first created for user HR is assigned only to subsequent requests by user HR.)

  • Different sessions of the same user

  • Different applications being run by the same user

    (Each application can have its own connection class.)

To set the connection class, you can use the OCI_ATTR_CONNECTION_CLASS attribute of the OCIAuthInfo handle. A connection class name is a string of at most 1024 bytes, and it cannot include an asterisk (*).

4.10.7.1 Example: Setting the Connection Class as HRMS

You can use the OCISessionPoolCreate API to set a connection class as HRMS.

Example 4-2 specifies that an HRMS application needs sessions with the connection class HRMS.

Example 4-2 Setting the Connection Class as HRMS

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);

4.10.8 Session Purity and Connection Class Defaults

Table 4-5 shows the defaults for the attributes and settings of connections that an OCI application gets from the OCI session pool (using OCISessionGet()) and from other sources.

Table 4-5 Session Purity and Connection Class Defaults

Attribute or Setting Default Value for Connection From OCI Session Pool Default Value for Connection Not From OCI Session Pool

OCI_ATTR_PURITY

OCI_ATTR_PURITY_SELF

OCI_ATTR_PURITY_NEW

OCI_ATTR_CONNECTION_CLASS

OCI-generated globally unique name for each client-side session pool, used as the default connection class for all connections in the OCI session pool

SHARED

Sessions shared by ...

Threads that request sessions from the OCI session pool

Connections to a particular database that have the SHARED connection class

4.10.9 Setting the Purity and Connection Class in the Connection String

You can specify the connection class and purity attributes in the CONNECT_DATA section of the Connect strings.

When it is not possible to change the application setting in the application code, you can override the value set in the application by setting the parameters: POOL_CONNECTION_CLASS and POOL_PURITY in the Connect string.

Note:

If the POOL_PURITY is specified as SELF in the Connect string, applications that explicitly get NEW purity connections from the OCI Session Pool do not drop the DRCP pooled session while releasing the connection back to the OCI Session Pool, even if they specify the OCI_SESSRLS_DROPSESS mode. Such applications should continue to use the programmatic way of specifying the purity.

See Also:

4.10.10 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.

See Also:

4.10.11 Enabling DRCP

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

Example 4-3 Enabling DRCP With :POOLED in Easy Connect String

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

Example 4-4 Enabling DRCP With SERVER=POOLED in TNS Connect String

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

4.10.12 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.

See Also:

OCISessionPoolCreate()

OCISessionGet()

OCISessionRelease(),

OCISessionPoolDestroy()

4.10.13 Benefiting from the Scalability of DRCP in a Java Application

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).

See Also:

4.10.14 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.

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 4-5 shows a DRCP application.

See Also:

Example 4-5 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 4-6 and Example 4-7 show connect strings that deploy code in 10 middle-tier hosts that service the BOOKSTORE application from Example 4-5.

In Example 4-6, 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.

Example 4-6 Connect String for Deployment in Dedicated Server Mode Without DRCP

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

In Example 4-7, 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.

Example 4-7 Connect String for Deployment With DRCP

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

4.10.15 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.

See Also:

4.10.16 Using DRCP with Oracle Database Native Network Encryption

You can use the following supported checksum algorithms and encryption algorithms while using DRCP with Oracle Database Native Network Encryption.

Checksum Algorithms

  • SHA1
  • SHA256
  • SHA384
  • SHA512

Encryption Algorithms

  • AES128
  • AES192
  • AES256

The DES, DES40, 3DES112, 3DES168, MD5, RC4_40, RC4_56, RC4_128, and RC4_256 algorithms are deprecated in this release. To transition your Oracle Database environment to use stronger algorithms, download and install the patch described in My Oracle Support note 2118136.2.

4.10.17 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 TCPS connections to DRCP

  • Using Enterprise user security with DRCP

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

  • Using initial client roles

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

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.

You can use Application Continuity with DRCP but you must ensure that the sessions are returned to the pool with the default session state.

Note:

You can use Oracle Advanced Security features such as encryption and strong authentication with DRCP.

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 AES128 for another set of connections, then the application must specify different connection classes for each set.

4.10.18 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. 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.

Note:

An application that specifies the attribute OCI_ATTR_SESSION_STATE or OCI_SESSION_STATELESS must also specify session purity and connection class.

See Also:

4.10.19 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.

See Also:

Using DRCP with Custom Pools

Example 4-8 shows a code fragment that explicitly marks session states.

Example 4-8 Explicitly Marking Sessions Stateful or Stateless

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.

See Also:

Oracle Call Interface Programmer's Guide for more information about OCI_ATTR_SESSION_STATE

4.10.20 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.

4.10.21 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.

4.11 Memoptimize Pool

This pool optimizes the read operation for select statements

Memoptimize Rowstore performs high-performance reads for tables specified with the MEMOPTIMIZE FOR READ clause.

Note:

Deferred inserts cannot be rolled back because they do not use standard locking and redo mechanisms

Related Topics

4.12 Oracle RAC Sharding

This section explains about Oracle RAC Sharding

Oracle RAC Sharding increases the performance and scalability of a Oracle RAC database with minimal application changes. It affinitizes table partitions to Oracle RAC instances, and routes the database requests, which specify a partitioning key to the instance that logically holds the corresponding partition. This provides better cache utilization and reduces block pings across instances. The partitioning key can be added only to the most performance critical requests. Requests that do not specify the key works transparently and can be routed to any instance. This feature can be enabled by executing an ALTER SYSTEM command and without modifying the database schema and SQL statements.

Note:

The partitioning key value must be provided when requesting a database connection.

Related Topics