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

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

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.

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.

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.

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

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.

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

Oracle recommends enabling client result cache only for queries on read-only and seldom-updated 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.

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.

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

• Server initialization parameters

• Client configuration parameters

• Table annotations

• RESULT_CACHE_MODE session parameter

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.

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 3-4 shows the result cache association for result cache parameters, the PL/SQL package DBMS_RESULT_CACHE, and result cache views.

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

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 supports shared connections in both PDB and non-PDB environments.

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

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.

Starting with Oracle Database 12c Release 2 (12.2.0.1) , the new 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.

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

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.

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.

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

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.

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)

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.

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

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.

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.

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.