Continuous Query Notification

Continuous Query Notification enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects or in response to result set changes associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.

During registration, the application specifies a notification handler and associates a set of interesting queries with the notification handler. A notification handler can be either a server-side PL/SQL procedure or a client-side C callback. Registrations are created at either the object level or the query level. If registration is at the object level, then whenever a transaction changes any of the registered objects and commits, the notification handler is invoked. If registration is at the query level, then whenever a transaction commits changes such that the result set of the query is modified, the notification handler is invoked, but if the changes do not affect the result set of the query, the notification handler is not invoked.

Using Query Result Set Notifications

To record QOS (quality of service flags) specific to continuous query (CQ) notifications, set the attribute OCI_ATTR_SUBSCR_CQ_QOSFLAGS on the subscription handle OCI_HTYPE_SUBSCR. To request that the registration is at query granularity, as opposed to object granularity, set the OCI_SUBSCR_CQ_QOS_QUERY flag bit on the attribute OCI_ATTR_SUBSCR_CQ_QOSFLAGS.

The pseudocolumn CQ_NOTIFICATION_QUERY_ID can be optionally specified to retrieve the query ID of a registered query. Note that this does not automatically convert the granularity to query level. The value of the pseudocolumn on return is set to the unique query ID assigned to the query. The query ID pseudocolumn can be omitted for OCI-based registrations, in which case the query ID is returned as a READ attribute of the statement handle. (This attribute is called OCI_ATTR_CQ_QUERYID).

During notifications, the client-specified callback is invoked and the top-level notification descriptor is passed as an argument.

Information about the query IDs of the changed queries is conveyed through a special descriptor type called OCI_DTYPE_CQDES. A collection (OCIColl) of query descriptors is embedded inside the top-level notification descriptor. Each descriptor is of type OCI_DTYPE_CQDES. The query descriptor has the following attributes:

OCI_ATTR_CQDES_OPERATION - can be one of OCI_EVENT_QUERYCHANGE or OCI_EVENT_DEREG.

OCI_ATTR_CQDES_QUERYID - query ID of the changed query.

OCI_ATTR_CQDES_TABLE_CHANGES - array of table descriptors describing DML operations on tables that led to the query result set change. Each table descriptor is of the type OCI_DTYPE_TABLE_CHDES.

Registering for Continuous Query Notification

The calling session must have the CHANGENOTIFICATION system privilege and SELECT privileges on all objects that it attempts to register. A registration is a persistent entity that is recorded in the database, and is visible to all instances of Oracle RAC. If the registration was at query granularity, transactions that cause the query result set to change and commit in any instance of Oracle RAC generate notification.If the registration was at object granularity, transactions that modify registered objects in any instance of Oracle RAC generate notification.

Queries involving materialized views or nonmaterialized views are not supported.

The registration interface employs a callback to respond to changes in underlying objects of a query and uses a namespace extension (DBCHANGE) to AQ.

The steps in writing the registration are as follows:

Create the environment in OCI_EVENTS and OCI_OBJECT mode.

Set the subscription handle attribute OCI_ATTR_SUBSCR_NAMESPACE to namespace OCI_SUBSCR_NAMESPACE_DBCHANGE.

Set the subscription handle attribute OCI_ATTR_SUBSCR_CALLBACK to store the OCI callback associated with the query handle. The callback has the following prototype:

Set the OCI_ATTR_SUBSCR_TIMEOUT attribute to specify a ub4 timeout interval in seconds. If it is not set, there is no timeout.

Set the OCI_ATTR_SUBSCR_QOSFLAGS attribute, the QOS (quality of service) levels, with the following values:

The OCI_SUBSCR_QOS_PURGE_ON_NTFN flag allows the registration to be purged on the first notification.

The OCI_SUBSCR_QOS_RELIABLE flag allows notifications to be persistent. You can use surviving instances of Oracle RAC to send and retrieve continuous query notification messages, even after a node failure, because invalidations associated with this registration are queued persistently into the database. If FALSE, then invalidations are enqueued into a fast in-memory queue. Note that this option describes the persistence of notifications and not the persistence of registrations. Registrations are automatically persistent by default.

Associate multiple query statements with the subscription handle by setting the attribute OCI_ATTR_CHNF_REGHANDLE of the statement handle, OCI_HTYPE_STMT. The registration is completed when the query is executed.

Optionally unregister a subscription. The client can call the OCISubscriptionUnRegister() function with the subscription handle as a parameter.

A binding of a statement handle to a subscription handle is only valid for the first execution of a query. If the application must use the same OCI statement handle for subsequent executions, it must repopulate the registration handle attribute of the statement handle. A binding of a subscription handle to a statement handle is only permitted when the statement is a query (determined at execute time). If a DML statement is executed as part of the execution, then an exception is issued.

Subscription Handle Attributes for Continuous Query Notification

The subscription handle attributes for continuous query notification can be divided into generic attributes (common to all subscriptions) and namespace-specific attributes (particular to continuous query notification).

The WRITE attributes on the statement handle can only be modified before the registration is created.

Generic Attributes - Common to All Subscriptions

OCI_ATTR_SUBSCR_NAMESPACE (WRITE) - Set this attribute to OCI_SUBSCR_NAMESPACE_DBCHANGE for subscription handles.

OCI_ATTR_SUBSCR_CALLBACK (WRITE) - Use this attribute to store the callback associated with the subscription handle. The callback is executed when a notification is received.

When a new continuous query notification message becomes available, the callback is invoked in the listener thread with desc pointing to a descriptor of type OCI_DTYPE_CHDES that contains detailed information about the invalidation.

OCI_ATTR_SUBSCR_QOSFLAGS - This attribute is a generic flag with the following values:

OCI_SUBSCR_QOS_RELIABLE - Set this bit to allow notifications to be persistent. Therefore, you can use surviving instances of an Oracle RAC cluster to send and retrieve invalidation messages, even after a node failure, because invalidations associated with this registration ID are queued persistently into the database. If this bit is FALSE, then invalidations are enqueued in to a fast in-memory queue. Note that this option describes the persistence of notifications and not the persistence of registrations. Registrations are automatically persistent by default.

OCI_SUBSCR_QOS_PURGE_ON_NTFN - Set this bit to allow the registration to be purged on the first notification.

0x1 OCI_SUBSCR_CQ_QOS_QUERY - Set this flag to indicate that query-level granularity is required. Notification should be only generated if the query result set changes. By default, this level of QOS has no false positives.

0x2 OCI_SUBSCR_CQ_QOS_BEST_EFFORT - Set this flag to indicate that best effort filtering is acceptable. It may be used by caching applications. The database may use heuristics based on cost of evaluation and avoid full pruning in some cases.

OCI_ATTR_SUBSCR_TIMEOUT - Use this attribute to specify a ub4 timeout value defined in seconds. If the timeout value is 0 or not specified, then the registration is active until explicitly unregistered.

Namespace- Specific or Feature-Specific Attributes

The following attributes are namespace-specific or feature-specific to the continuous query notification feature.

OCI_ATTR_CHNF_TABLENAMES (data type is (OCIColl *)) - These attributes are provided to retrieve the list of table names that were registered. These attributes are available from the subscription handle, after the query is executed.

OCI_ATTR_CHNF_ROWIDS - A Boolean attribute (default FALSE). If set to TRUE, then the continuous query notification message includes row-level details such as operation type and ROWID.

OCI_ATTR_CHNF_OPERATIONS - Use this ub4 flag to selectively filter notifications based on operation type. This option is ignored if the registration is of query-level granularity. Flags stored are as follows:

OCI_OPCODE_ALL - All operations

OCI_OPCODE_INSERT - Insert operations on the table

OCI_OPCODE_UPDATE - Update operations on the table

OCI_OPCODE_DELETE - Delete operations on the table

OCI_ATTR_CHNF_CHANGELAG - The client can use this ub4 value to specify the number of transactions by which the client is willing to lag behind. The client can also use this option as a throttling mechanism for continuous query notification messages. When you choose this option, ROWID-level granularity of information is not available in the notifications, even if OCI_ATTR_CHNF_ROWIDS was set to TRUE. This option is ignored if the registration is of query-level granularity.

Once the OCISubscriptionRegister() call is invoked, none of the preceding attributes (generic, name-specific, or feature-specific) can be modified on the registration already created. Any attempt to modify those attributes is not reflected on the registration already created, but it does take effect on newly created registrations that use the same registration handle.

Continuous Query Notification Descriptors

The continuous query notification descriptor is passed into the desc parameter of the notification callback specified by the application. The following attributes are specific to continuous query notification. The OCI type constant of the continuous query notification descriptor is OCI_DTYPE_CHDES.

The notification callback receives the top-level notification descriptor, OCI_DTYPE_CHDES, as an argument. This descriptor in turn includes either a collection of OCI_DTYPE_CQDES or OCI_DTYPE_TABLE_CHDES descriptors based on whether the event type was OCI_EVENT_QUERYCHANGE or OCI_EVENT_OBJCHANGE. An array of table continuous query descriptors is embedded inside the continuous query descriptor for notifications of type OCI_EVENT_QUERYCHANGE. If ROWID level granularity of information was requested, each OCI_DTYPE_TABLE_CHDES contains an array of row-level continuous query descriptors (OCI_DTYPE_ROW_CHDES) corresponding to each modified ROWID.

OCI_DTYPE_CHDES

This is the top-level continuous query notification descriptor type.

OCI_ATTR_CHDES_DBNAME (oratext *) - Name of the database (source of the continuous query notification)

OCI_ATTR_CHDES_XID (RAW(8)) - Message ID of the message

OCI_ATTR_CHDES_NFYTYPE - Flags describing the notification type:

0x0 OCI_EVENT_NONE - No further information about the continuous query notification

OCI_ATTR_CHDES_TABLE_CHANGES - A collection type describing operations on tables of data type (OCIColl *). This attribute is present only if the OCI_ATTR_CHDES_NFTYPE attribute was of type OCI_EVENT_OBJCHANGE; otherwise, it is NULL. Each element of the collection is a table of continuous query descriptors of type OCI_DTYPE_TABLE_CHDES.

OCI_ATTR_CHDES_QUERIES - A collection type describing the queries that were invalidated. Each member of the collection is of type OCI_DTYPE_CQDES. This attribute is present only if the attribute OCI_ATTR_CHDES_NFTYPE was of type OCI_EVENT_QUERYCHANGE; otherwise, it is NULL.

OCI_DTYPE_CQDES

This notification descriptor describes a query that was invalidated, usually in response to the commit of a DML or a DDL transaction. It has the following attributes:

OCI_ATTR_CQDES_OPERATION (ub4, READ) - Operation that occurred on the query. It can be one of these values:

OCI_EVENT_QUERYCHANGE - Query result set change

OCI_EVENT_DEREG - Query unregistered

OCI_ATTR_CQDES_TABLE_CHANGES (OCIColl *, READ) - A collection of table continuous query descriptors describing DML or DDL operations on tables that caused the query result set change. Each element of the collection is of type OCI_DTYPE_TABLE_CHDES.

OCI_ATTR_CQDES_QUERYID (ub8, READ) - Query ID of the query that was invalidated.

OCI_DTYPE_TABLE_CHDES

This notification descriptor conveys information about changes to a table involved in a registered query. It has the following attributes:

OCI_ATTR_CHDES_TABLE_NAME(oratext *) - Schema annotated table name.

OCI_ATTR_CHDES_TABLE_OPFLAGS (ub4) - Flag field describing the operations on the table. Each of the following flag fields is in a separate bit position in the attribute:

OCI_ATTR_CHDES_TABLE_ROW_CHANGES - This is an embedded collection describing the changes to the rows within the table. Each element of the collection is a row continuous query descriptor of type OCI_DTYPE_ROW_CHDES that has the following attributes:

Continuous Query Notification Example

Example 10-1 is a simple OCI program, demoquery.c. See the comments in the listing. The calling session must have the CHANGENOTIFICATION system privilege and SELECT privileges on all objects that it attempts to register.

Database Startup and Shutdown

The OCI functions OCIDBStartup() and OCIDBShutdown() provide the minimal interface needed to start and shut down an Oracle database. Before calling OCIDBStartup(), the C program must connect to the server and start a SYSDBA or SYSOPER session in the preliminary authentication mode. This mode is the only one permitted when the instance is not up, and it is used only to start the instance. A call to OCIDBStartup() starts one server instance without mounting or opening the database. To mount and open the database, end the preliminary authentication session and start a regular SYSDBA or SYSOPER session to execute the appropriate ALTERDATABASE statements.

An active SYSDBA or SYSOPER session is needed to shut down the database. For all modes other than OCI_DBSHUTDOWN_ABORT, make two calls to OCIDBShutdown(): one to initiate shutdown by prohibiting further connections to the database, followed by the appropriate ALTERDATABASE commands to dismount and close it; and the other call to finish shutdown by bringing the instance down. In special circumstances, to shut down the database as fast as possible, call OCIDBShutdown() in the OCI_DBSHUTDOWN_ABORT mode, which is equivalent to SHUTDOWNABORT in SQL*Plus.

Both of these functions require a dedicated connection to the server. ORA-106 is signaled if an attempt is made to start or shut down the database when it is connected to a shared server through a dispatcher.

The OCIAdminadministration handle C data type is used to make the interface extensible. OCIAdmin is associated with the handle type OCI_HTYPE_ADMIN. Passing a value for the OCIAdmin parameter, admhp, is optional for OCIDBStartup() and is not needed by OCIDBShutdown().

Examples of Startup and Shutdown in OCI

To perform a startup, you must be connected to the database as SYSOPER or SYSDBA in OCI_PRELIM_AUTH mode. You cannot be connected to a shared server through a dispatcher. To use a client-side parameter file (pfile), the attribute OCI_ATTR_ADMIN_PFILE must be set in the administration handle using OCIAttrSet(); otherwise, a server-side parameter file (spfile) is used. In the latter case, pass (OCIAdmin *)0. A call to OCIDBStartup() starts one instance on the server.

Example 10-2 shows sample code that uses a client-side parameter file (pfile) that is set in the administration handle and performs a database startup operation.

To perform a shutdown, you must be connected to the database as SYSOPER or SYSDBA. You cannot be connected to a shared server through a dispatcher. When shutting down in any mode other than OCI_DBSHUTDOWN_ABORT, use the following procedure:

Call OCIDBShutdown() in OCI_DEFAULT, OCI_DBSHUTDOWN_TRANSACTIONAL, OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL, or OCI_DBSHUTDOWN_IMMEDIATE mode to prohibit further connections.

Use the necessary ALTERDATABASE commands to close and dismount the database.

Call OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL mode to shut down the instance.

Example 10-3 shows sample code that uses a client-side parameter file (pfile) that is set in the administration handle that performs an orderly database shutdown operation.

Implicit Fetching of ROWIDs

ROWID is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed. ROWID values have several important uses. They are unique identifiers for rows in a table. They are the fastest way to access a single row and can show how the rows in the table are stored.

Implicit fetching of ROWIDs in SELECT...FORUPDATE statements means that the ROWID is retrieved at the client side, even if it is not one of the columns named in the select statement. The position parameter of OCIDefineByPos() is set to zero (0). These host variables can be specified for retrieving the ROWID pseudocolumn values:

SQLT_CHR (VARCHAR2)

SQLT_VCS (VARCHAR)

SQLT_STR (NULL-terminated string)

SQLT_LVC (LONGVARCHAR)

SQLT_AFC (CHAR)

SQLT_AVC (CHARZ)

SQLT_VST (OCI String)

SQLT_RDD (ROWID descriptor)

The SELECT...FORUPDATE statement identifies the rows that are to be updated and then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must ensure that another user does not change the row.

When you specify character buffers for storing the values of the ROWIDs (for example, if getting it in SQLT_STR format), allocate enough memory for storing ROWIDs. Remember the differences between the ROWID data type and the UROWID data type. The ROWID data type can only store physical ROWIDs, but UROWID can store logical ROWIDs (identifiers for the rows of index-organized tables) as well. The maximum internal length for the ROWID type is 10 bytes; it is 3950 bytes for the UROWID data type.

Dynamic define is equivalent to calling OCIDefineByPos() with mode set as OCI_DYNAMIC_FETCH. Dynamic defines enable you to set up additional attributes for a particular define handle. It specifies a callback function that is invoked at runtime to get a pointer to the buffer into which the fetched data or a piece of it is to be retrieved.

The attribute OCI_ATTR_FETCH_ROWID must be set on the statement handle before you can use implicit fetching of ROWIDs, in this way:

Dynamic define is not compatible with implicit fetching of ROWIDs. In normal scenarios this mode allows the application to provide buffers for a column, for each row; that is, a callback is invoked every time a column value is fetched.

This feature, using OCIDefineByPos() for position 0, is for fetching an array of data simultaneously into the user buffers and getting their respective ROWIDs at the same time. It allows for fetching of ROWIDs with SELECT....FOR UPDATE statements even when ROWID is not one of the columns in the SELECT query. When fetching the data one by one into the user buffers, you can use the existing attribute OCI_ATTR_ROWID.

If you use this feature to fetch the ROWIDs, the attribute OCI_ATTR_ROWID on the statement handle cannot be used simultaneously to get the ROWIDs. You can only use one of them at a time for a particular statement handle.

Client Result Cache

OCI applications can use client memory to take advantage of the OCI result cache to improve response times of repetitive queries.

The client result cache enables client-side caching of SQL query result sets in client memory. The OCI result cache is completely transparent to OCI applications, and its cache of result set data is kept consistent with any session or database changes that affect its result set.

Applications employing this feature see improved performance for queries that have a cache hit. OCI can transparently use cached results for future executions of these queries. Because retrieving results locally from an OCI client process is faster than making a database call and rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The OCI cache also reduces the server CPU that would have been consumed for processing 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 schema, SQL text, bind values, and session settings. Otherwise, with any dissimilarity, the query execution is directed to the server.

You must enable OCI statement caching or cache statements at the application level when using the client result cache.

Benefits of Client Result Cache

The benefits of OCI client query result cache are as follows:

Because the result cache is on the client side, a cache hit causes OCIStmtExecute() and OCIStmtFetch2() calls to be processed locally, instead of making server round-trips. This can result in huge performance savings for server resources, for example, server CPU and server I/O.

The OCI client-side query result set cache is a transparent and consistent cache.

The result cache on OCI client is per-process, so multiple client sessions can simultaneously use matching cached result sets.

It minimizes the need for each OCI application to have its own custom result set cache.

It transparently manages the caching aspects of the cached result sets, that is: concurrent access by multiple threads, multiple statements, multiple sessions, invalidation, refreshing of result sets in the cache, and cache memory management.

It transparently invalidates the cached result sets on any database changes that may affect the result sets, when an OCI process makes round-trips to the server.

This consistent cache is automatically available to all OCI applications and drivers (such as JDBC OCI, ODP.Net, OCCI, Pro*C/C++, Pro*COBOL, ODBC, and so on) built using OCI.

The cache uses OCI client memory that may be less expensive than server memory.

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

Guidelines for Using Client Result Cache

You can enable client result caching in several ways for your application and establish an order of precedence in its usage based on the methods selected. See "Cache Example Use Cases" for more usage information.

SQL Hints - Annotate a query with a SQL hint /*+ result_cache */ to indicate that results are to be stored in the query result cache. Using SQL hints is the highest order of precedence; it takes precedence over table annotations and session parameters. It is applicable to a single query. This method requires application-level changes.

Table Annotation - Annotate a table during deployment using result cache hints in the ALTERTABLE and CREATETABLE statements. Using table annotation is the next highest order of precedence below SQL hints and above session parameters when using MODE FORCE. It is applicable to all queries for that table. This method requires no application-level changes.

Session Parameters - Works across all tables for all queries; use this method when possible. You can either set the RESULT_CACHE_MODE initialization parameter in the server parameter file (init.ora) or use RESULT_CACHE_MODE clause in the ALTERSESSION and the ALTERSYSTEM statements. Using session parameters is the lowest order of precedence; both SQL hints and table annotations take precedence over session parameters usage. It is the most widely effective usage being applicable to all tables. This method requires no application-level changes.

Oracle recommends that applications annotate tables and queries with result cache hints for read-only or read-mostly database objects. If the result caching happens for queries with large results, these results can use a large amount of cache memory.

As each set of bind values specified by the application creates a different cached result set (for the same SQL text), these result sets together can use a large amount of client result cache memory.

When client result caching is enabled, the query result set can be cached on the client or on the server or both. The client result caching can be enabled even if the server result cache (that is enabled by default) is disabled.

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. It is necessary that an OCIStmtExecute() call be made for each statement handle to be able to match a cached result set. Oracle recommends that applications 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 a result set to be cached, the OCIStmtExecute() or OCIStmtFetch2() calls that transparently create this cached result set must fetch rows until an ORA-01403 "No Data Found" error is returned. Subsequent OCIStmtExecute() or OCIStmtFetch2() calls that match a locally cached result set need not fetch to completion.

SQL Hints

Unless the RESULT_CACHE_MODE server initialization parameter is set to FORCE, you must explicitly specify the queries to be cached using SQL hints. The SQL /*+ result_cache */ or /*+ no_result_cache */ hint must be set in SQL text passed to OCIStmtPrepare() and OCIStmtPrepare2() calls.

Table Annotation

The ALTERTABLE and CREATETABLE statements enable you to annotate tables with result cache mode. There are also session parameters as mentioned in a later section. The matrix of table annotations and session parameters dictates the effective result cache mode for queries on that table. Note that SQL hints override table annotations and session parameters. The syntax is:

This ALTER TABLE statement is used to annotate tables so that results of statements or query blocks (for server result cache) using these tables are stored in the result cache. If a given query has a SQL hint /*+ result_cache / or /*+ no_result_cache */ or if the parameter RESULT_CACHE_MODE is set to FORCE, then the hint or session variable take precedence over the table annotation.

You should annotate all tables you want stored in the result cache. Then all SQL queries, whether single table selects or with joins, for these tables with cache hints, are considered for caching assuming they are cache-worthy.

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

Checking Table Annotation Mode

The RESULT_CACHE column in the DBA views DBA_TABLES, USER_TABLES, and ALL_TABLES shows the result cache mode annotation for the table. If the table has not been annotated, it shows DEFAULT.

Suppose that table emp is annotated as ALTERTABLEempRESULT_CACHE (MODEFORCE).

Then execute the following query in the session:

SELECTtable_name, result_cacheFROMuser_tables

The output is as follows:

TABLE_NAME RESULT_CACHE
---------- ------------
EMP FORCE
FOO DEFAULT

The output shows that table FOO either has not been annotated or has been annotated using the following statement:

Session Parameters

The RESULT_CACHE_MODE parameter enables you to decide result cache mode across tables in your queries. Use this clause in ALTERSESSION and ALTERSYSTEM statements, or inside the server parameter file (init.ora) to determine result caching.

Effective Result Cache Table Mode

The SQL query level result cache hints take precedence over the session parameter RESULT_CACHE_MODE and result cache table annotations. In addition, table annotation FORCE takes precedence over the session parameter MANUAL as indicated in Table 10-2. Table 10-2 compares modes (MANUAL and FORCE) for the session parameter RESULT_CACHE_MODE versus the comparable table annotation modes and shows the effective result cache mode.

Table 10-2 Effective Result Cache Table Mode

RESULT_CACHE_MODE Parameter

MANUAL

FORCE

Table Annotation = FORCE

FORCE

FORCE

Table Annotation = DEFAULT

MANUAL

FORCE

Note that when the effective mode is FORCE, then the actual caching depends on internal restrictions for client and server cache, query cache worthiness (for example, there is no SYSDATE in the query), and space available in the cache. This is similar to the SQL query hint /*+ result_cache */ because it is just a hint. It does not imply that the query is actually cached. Recall that table annotation DEFAULT indicates that result caching is not determined at the table level and session parameter mode MANUAL indicates that the query must be annotated with a SQL hint for the hint to take precedence, so in effect these are equivalent methods for this setting.

Cache Example Use Cases

The following are some use cases that show when SQL hints take precedence over table annotations and session parameter.

If the emp table is annotated as ALTERTABLEempRESULT_CACHE (MODEFORCE) and the session parameter is not set, (it has its default value of MANUAL), this implies queries on table emp are considered for query caching.

If in an example, the SQL query is SELECT/*+ no_result_cache */empnoFROMemp, the query is not cached. This is because SQL hints take precedence over table annotations and session parameter.

If the emp table is not annotated or is annotated as ALTERTABLEempRESULT_CACHE (MODEDEFAULT) and the session parameter is not set (it has a default value of MANUAL), this implies queries are not cached.

If in an example, the SQL query has the hint SELECT/*+ result_cache */*FROMemp, then this query is considered for query caching.

If there is no table annotation and there is no SQL query hint, but the session or system parameter is set to FORCE, all queries on all tables are considered for query caching.

Queries That Are Not Cached

There are queries that are not cached on the OCI client even if the result cache hint is specified. Such queries may be cached on the database if the server result cache feature is enabled (see the discussion of the SQL query result cache in Oracle Database Concepts for more information). If a SQL query includes any of the following, then the result set of that query is not cached in the OCI client result cache:

Remote objects

Complex types in the select list

Snapshot-based queries or flashback queries

Queries executed in a serializable, read-only transaction, or inside a flashback session

Queries that have PL/SQL functions in them

Queries that have virtual private database (VPD) policies enabled on the tables

Client Cache Consistency

The client cache transparently keeps the result set consistent with any session state or database changes that can affect its cached result sets.

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

The cached result sets relevant to database invalidations are immediately invalidated, and no subsequent OCIStmtExecute() calls can match such result sets. The OCI statement handles currently fetching from these cached result sets, at the time such invalidations are received, can continue fetching from this (invalidated) cached result set.

The next OCIStmtExecute() call by the process may cache the new result set if there is space available in the cache. The OCI 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 cache.

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

Note that sometimes when a table is modified, a trigger can cause another table to be modified. OCI client result cache is sensitive to all such changes.

When the session state is altered, for example, if NLS session parameters are modified, this can cause the query results to be different. The OCI result cache is sensitive to such changes and on subsequent query executions, returns the correct query result set. The current cached result sets are kept (and not invalidated) for any other session in the process to match; otherwise, these result sets get "Ruled" after a while. There are new result sets cached corresponding to the new session state.

If the application must keep track of all such database and session changes it can be cumbersome and prone to errors. Hence, OCI result cache transparently keeps the result sets consistent with any database or session changes.

The OCI client result cache does not require thread support in the client.

Deployment Time Settings for Client Result Cache

The client result cache has server initialization parameters and client configuration parameters for its deployment time settings.

These are the server initialization parameters:

CLIENT_RESULT_CACHE_SIZE

The default value is zero, implying that the client cache feature is disabled. To enable the client result cache feature, set the size to 32768 bytes (32 Kilobytes (KB)) or greater. This is the minimum size of the client per-process result set cache. All OCI client processes get this minimum size. This can be overridden by the sqlnet.ora configuration parameter OCI_RESULT_CACHE_MAX_SIZE only if this feature is enabled on the server by the CLIENT_RESULT_CACHE_SIZE initialization parameter.

You can view the current default maximum size by displaying the value of the CLIENT_RESULT_CACHE_SIZE parameter. To increase this maximum size, you can set CLIENT_RESULT_CACHE_SIZE. However, because CLIENT_RESULT_CACHE_SIZE is a static parameter, you must include the SCOPE = SPFILE clause if you use an ALTER SYSTEM statement, and you must restart the database before any changes to this parameter take effect.

Note that if the client result cache feature is disabled at the server, the client configuration parameter OCI_RESULT_CACHE_MAX_SIZE is ignored and the client result cache cannot be enabled at the client.

The cache size can be set to the minimum of:

(available client memory) and

((the possible number of result sets to be cached)

* (the average size of a row in a result set)

* (the average number of rows in a result set)).

Note:

The client result cache has a maximum value of 2 GB; setting it higher causes a truncation to 2 GB.

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

CLIENT_RESULT_CACHE_LAG

The CLIENT_RESULT_CACHE_LAG initialization parameter enables you to specify the maximum amount of time in milliseconds that the client result cache can lag behind any changes in the database that affect its result sets. The default is 3000 milliseconds.

You can view the current lag by displaying the value of the CLIENT_RESULT_CACHE_LAG parameter. To change this value, you can set CLIENT_RESULT_CACHE_LAG. However, because CLIENT_RESULT_CACHE_LAG is a static parameter, you must include the SCOPE = SPFILE clause if you use an ALTER SYSTEM statement, and you must restart the database before any changes to this parameter take effect.

Table annotation. Optional. One can annotate read-only, read-mostly tables for result caching during deployment. No application-level changes are required. Note SQL result cache hints, if specified, override the table annotations. See Oracle Database SQL Language Reference for more information.

compatible

Specifies the release with which Oracle Database must maintain compatibility. This parameter must be set to 11.0.0.0 or higher for the client result cache to be enabled. If you want client caching on views, compatible must be set to 11.2.0.0 or higher.

Client Configuration File

A client configuration file is optional and overrides the cache parameters set in the server init.ora initialization file. These parameters are part of a sqlnet.ora file. The following optional parameters are available for client configuration:

OCI_RESULT_CACHE_MAX_SIZE (optional) - Maximum size in bytes for the per-process query cache. Specifying a size less than 32768 in the client sqlnet.ora file disables the client result cache feature for client processes reading this sqlnet.ora file.

OCI_RESULT_CACHE_MAX_RSET_SIZE (optional) - Maximum size of any result set in bytes in the per-process query cache.

OCI_RESULT_CACHE_MAX_RSET_ROWS (optional) - Maximum size of any result set in rows in the per-process query cache.

Note that the cache lag cannot be set on the client.

Client Cache Statistics

On existing round-trips from the OCI client, OCI periodically sends statistics related to its client cache to the server. These statistics are stored in the CLIENT_RESULT_CACHE_STATS$ view. Information such as the number of result sets successfully cached, number of cache hits, and number of cached result sets invalidated are stored here. 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 as seen in server Automatic Workload Repository (AWR) reports.

Validation of the Client Result Cache

The following sections provide some more information about performing validations of the client result cache.

Timing Measurement

First, to determine the performance gain of adding result cache hints to the queries, measure the time taken to run the queries without the /*+ result_cache */ hints. Then add the /*+ result_cache */ hints to the query and measure the time again. The difference in time is your performance gain.

Using v$mystat

Query the v$mystat view. To query this view, you must be granted permissions. Perform these two queries

Query-1: Measures the "SQL*Net round-trips to and from the client" from v$mystat.

Query-2: Measures the "SQL*Net round-trips to and from the client" without the SQL result cache hint.

The difference between Query-2 and Query-1 queries is the number of round-trips that it usually takes without enabling client result cache.

Note that the Query-1 query itself would make some round-trips (approximately 2) in this calculation.

If you add a result cache hint to the query or add the FORCE table annotation to the query for table emp and perform the query again, the difference between Query-2 and Query-1 is much less.

Using v$sqlarea

Query the v$sqlarea view. To query this view, you must be granted permissions.

Run the following SQL statement:

SELECT COUNT(*) FROM emp

Reexecute this preceding SQL statement a few times.

Then query select executions, fetches, parse_calls from v$sqlarea where sql_text like '% from emp';

Next, add the result cache table hint for emp to the query.

Reexecute the query a few times.

With client caching, the values for column1, column2 are less.

Note that the preceding validations can also be performed with result cache table annotations.

OCI Client-Side Result Cache and Server Result Cache

The client-side result cache is a separate feature from the server result cache. The client-side result cache caches results of top-level SQL queries in OCI client memory, whereas the server result cache caches result sets in server SGA memory.

The server result cache may also cache query fragments. The client-side result caching can be enabled independently of the server result cache, though they both share the result cache SQL hints, table annotation, and session parameter RESULT_CACHE_MODE. See Oracle Database Concepts for more information about SQL query result cache. Table 10-3 shows the specific result cache association for client-site result cache or server result cache, or both, with regard to setting specific parameters, running particular PL/SQL packages, and querying specific Oracle database views.

Table 10-3 Setting Client-Side 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

Statistics view: client_result_cache_stats$

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

Package DBMS_RESULT_CACHE

server result cache

Statistics views v$result_cache_*, gv$result_cache_*.

For example, v$result_cache_statistics, 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

Client Result Cache Demo Files

See the files cdemoqc.sql, cdemoqc.c, and cdemoqc2.c (all are in the demo directory for your operating system) for demonstration files for this feature.

Compatibility with Previous Releases

To use client result cache, applications must be relinked with Oracle Database Release 11.1 or later client libraries and be connected to an Oracle Database Release 11.1 or later database server. This feature is available to all OCI applications including JDBC Type II driver, OCCI, Pro*C/C++, and ODP.NET. The OCI drivers automatically pass the result cache hint to OCIStmtPrepare() and OCIStmtPrepare2() calls, thereby getting the benefits of caching.

Fault Diagnosability in OCI

Fault diagnosability was introduced into OCI in Oracle Database 11g Release 1 (11.1). An incident (an occurrence of a problem) on the OCI client is captured without user intervention in the form of diagnostic data: dump files or core dump files. The diagnostic data is stored in an Automatic Diagnostic Repository (ADR) subdirectory created for the incident. For example, if a Linux or UNIX application fails with a NULL pointer reference, then the core file is written in the ADR home directory (if it exists) instead of the operating system directory. The ADR subdirectory structure and a utility to deal with the output, ADR Command Interpreter (ADRCI), are discussed in the following sections.

An ADR home is the root directory for all diagnostic data for an instance of a particular product such as OCI and a particular operating system user. ADR homes are grouped under the same root directory, the ADR base.

Fault diagnosability and the ADR structure for Oracle Database are described in detail in the discussion of managing diagnostic data in Oracle Database Administrator's Guide.

ADR Base Location

The location of the ADR base is determined by OCI in the following order:

OCI first looks in the file sqlnet.ora (if it exists) for a statement such as (Linux or UNIX):

ADR_BASE=/foo/adr

adr (the name of a directory) must exist and be writable by all operating system users who execute OCI applications and want to share the same ADR base. foo stands for a path name. The location of sqlnet.ora is given in the directory $TNS_ADMIN (%TNS_ADMIN% on Windows). If there is no $TNS_ADMIN then the current directory is used. If ADR_BASE is set and one sqlnet.ora is shared by all users, then OCI stops searching when directory adr does not exist or is not writable by the user. If ADR_BASE is not set, then OCI continues the search, testing for the existence of certain directories.

For example, if sqlnet.ora contains the entry ADR_BASE=/home/chuck/test then the ADR base is /home/chuck/test/oradiag_chuck and the ADR home could be something like /home/chuck/test/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.

$ORACLE_BASE (%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using Oracle Universal Installer.

For example, if $ORACLE_BASE is /home/chuck/obase then the ADR base is /home/chuck/obase and the ADR home could be similar to /home/chuck/obase/diag/clients/user_chuck/host_4144260688_11.

$ORACLE_HOME(%ORACLE_BASE% on Windows) exists. In this case, the client subdirectory exists because it was created during installation of the database using Oracle Universal Installer.

For example, if $ORACLE_HOME is /ade/chuck_l1/oracle then the ADR base is /ade/chuck_l1/oracle/log and the ADR home could be similar to /ade/chuck_l1/oracle/log/diag/clients/user_chuck/host_4144260688_11.

Operating system home directory: $HOME on Linux or UNIX, or %USERPROFILE% on Windows. On Linux or UNIX the location could be something like this for user chuck: /home/chuck/oradiag_chuck. On Windows, a folder named Oracle is created under C:\Documents and Settings\chuck.

For example, in an Instant Client, if $HOME is /home/chuck then the ADR base is /home/chuck/oradiag_chuck and the ADR home could be /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.

Using ADRCI

ADRCI is a command-line tool that enables you to view diagnostic data within the ADR and to package incident and problem information into a zip file for Oracle Support to use. You can use ADRCI interactively and from a script. A problem is a critical error in OCI or the client. Each problem has a problem key. An incident is a single occurrence of a problem and is identified by a unique numeric incident ID. Each incident has a problem key that is a set of attributes: the ORA error number, error parameter values, and other information. Two incidents have the same root cause if their problem keys match.

What follows is a launch of ADRCI in a Linux system, a use of the HELP command for the SHOWBASE command, and then the use of the SHOWBASE command with the option -PRODUCTCLIENT, which is necessary for OCI applications. The ADRCI commands are case-insensitive. User input is shown in bold.

adrci> help set base
Usage: SET BASE <base_str>
Purpose: Set the ADR base to use in the current ADRCI session.
If there are valid ADR homes under the base, all homes will
will be added to the current ADRCI session.
Arguments:
<base_str>: It is the ADR base directory, which is a system-dependent
directory path string.
Notes:
On platforms that use "." to signify current working directory,
it can be used as base_str.
Example:
set base /net/sttttd1/scratch/someone/view_storage/someone_v1/log
set base .
adrci> quit

When ADRCI is started, the default ADR base is for the rdbms server. $ORACLE_HOME is set to /ade/chuck_l3/oracle:

To disable diagnosability, turn off diagnostics by setting the following parameters in sqlnet.ora (the default is TRUE):

DIAG_ADR_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

To turn off the OCI signal handler and reenable standard operating system failure processing, place the following parameter setting in sqlnet.ora:

DIAG_SIGHANDLER_ENABLED=FALSE

As noted previously, ADR_BASE is used in sqlnet.ora to set the location of the ADR base.

Oracle Database client contains advanced features for diagnosing issues, including the ability to dump diagnostic information when important errors are detected. By default, these dumps are restricted to a small subset of available information, to ensure that application data is not dumped. However, in many installations, secure locations for dump files may be configured, ensuring the privacy of such logs. In such cases, it is recommended to turn on full dumps; this can greatly speed resolution of issues. Full dumps can be enabled by adding the following line to the sqlnet.ora file used by your Oracle Database client installation:

DIAG_RESTRICTED=FALSE

To verify that diagnosability features are working correctly:

Upgrade your application to use the latest client libraries.

Start your application.

Check the file sqlnet.log in your application's TNS_ADMIN directory for error messages indicating that diagnosability could not be started (normally this is due to invalid directory names or permissions).

Client and Server Operating with Different Versions of Time Zone Files

In Oracle Database Release 11.2 (or later) you can use different versions of the time zone file on the client and server; this mode of operation was not supported before Oracle database Release 11.2. Both client and server must be 11.2 or later to operate in such a mixed mode. This section discusses the ramifications of operating in such a mode. To avoid these ramifications use the same time zone file version for client and server.

The following behavior is seen when the client and server use different time zones file versions. Note that the use of different time zone file versions only affects the handling of TIMESTAMPWITHTIMEZONE (TSTZ) data type values.

The OCI Datetime and Interval APIs listed here unconditionally raise an error when the input parameters are of TSTZ type. This is because these operations depend on the local time zone file on the client that is not synchronized with the database. Continuing with the computation in such a configuration can result in inconsistent computations across the client and database tiers.

There is a performance penalty when you retrieve or modify TSTZ values. The performance penalty arises because of the additional conversions needed to compensate for the client and server using different time zone file versions.

If new time zone regions are defined by the more recent time zone file, you can see an error operating on a TIMESTAMPWITHTIMEZONE value belonging to the new region on a node that has a time zone file version that does not recognize the new time zone region.

Applications that manipulate opaque type or XMLType instances or both containing TSTZ type attributes must use the same time zone file version on client and server to avoid data loss.

Footnote 1: Returns an ORA-01805 error when timezone files on the client and server do not match (regions are not synchronized); returns OCI_SUCCESS when region time zone values are the same (represent the same instant in UTC), though the TIME ZONE offsets are different.