You can use OCI to access Oracle TimesTen In-Memory Database and Oracle TimesTen Application-Tier Database Cache. See Oracle TimesTen In-Memory Database C Developer's Guide, for information about Times Ten support for Oracle Call Interface.
This chapter contains these topics:
Continuous Query Notification
Database Startup and Shutdown
Implicit Fetching of ROWIDs
Fault Diagnosability in OCI
Client and Server Operating with Different Versions of Time Zone Files
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 at either the object level or is not invoked.
See Also:Oracle Database Advanced Application Developer's Guide, "Using Continuous Query Notification" for a complete discussion of the concepts of this feature
One use of continuous query notification is in middle-tier applications that must have cached data and keep the cache as recent as possible for the back-end database. Oracle Real Application Cluster (Oracle RAC) the database delivers a notification when the first instance starts or the last instance shuts down.
See Also:"Publish-Subscribe Notification in OCI". Note that:"OCI_DTYPE_CQDES" 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".
Optionally associate a client-specific context using
OCI_ATTR_SUBSCR_CTX attribute.
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.
Associate multiple query statements 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 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. Note that. 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 set to as follows: 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:"DES that has the following attributes:
OCI_ATTR_CHDES_ROW_ROWID (
OraText *) - String representation of a
ROWID.
OCI_ATTR_CHDES_ROW_OPFLAGS - Reflects the operation type:
INSERT,
UPDATE,
DELETE, or
OTHER.
Example 10-1 is a simple OCI program,
demoquery.c. See the comments in the listing. The calling session must have the
CHANGE
NOTIFICATION system privilege and
SELECT privileges on all objects that it attempts to register.
Example 10-1/HR and perform the following * DML transactions. It will cause two notifications to be generated. update departments set department_name ='Global Admin' where department_id=10; commit; update departments set department_name ='Adminstration'); }
The OCI functions OCIDBStartup() and OCIDBShutdown() provide the minimal interface needed to start start the instance. A call to OCIDBStartup() starts, or shut down the database when it is connected to a shared server through a dispatcher.
The
OCIAdmin administration handle C data type perform) 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.
Example 10-2 Calling OCIDBStartup() to Perform a Database Startup Operation
... /*()); /* Start up in NOMOUNT mode */ checkerr(errhp, OCIDBStartup(svchp, errhp, admhp, OCI_DEFAULT, 0)); checkerr(errhp, OCIHandleFree((void *) 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
ALTER
DATABASE 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.
Example 10-3 Calling OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL Mode
/*)); ...
Example 10-4 shows a shutdown example that uses
OCI_DBSHUTDOWN_ABORT mode.
Example 10-4 Calling OCIDBShutdown() in_DBSHUTDOWN:
...
FOR
UPDATE 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, 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.
See Also:"OCI_ATTR_FETCH_ROWID"
Use the fragment of a C program in Example 10-5 to build upon.
Example 10-5 Implicit Fetching of ROW, get.
The client result cache. 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.
Client result cache works with OCI features such as OCI session pooling, OCI connection pooling, database resident connection pooling, and OCI transparent application failover (TAF).
See Also:"Statement Caching in OCI"
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 an 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 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
ALTER
TABLE and
CREATE
TABLE 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 ALTER
SESSION and
the ALTER
SYSTEM 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. 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 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.
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.
The
ALTER
TABLE and
CREATE
TABLE 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:
CREATE|ALTER TABLE [<schema>.]<table> ... [RESULT_CACHE (MODE {FORCE|DEFAULT})]
Here is an example of
CREATE
TABLE. It defines the table columns:
CREATE TABLE foo (a NUMBER, b VARCHAR2(20)) RESULT_CACHE (MODE FORCE);
Here is an example of
ALTER
TABLE:
ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);
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.
See Also:
Oracle Database SQL Language Reference for more information about
RESULT_CACHE clause, SQL hints,
ALTER
TABLE, and
CREATE
TABLE
Oracle Database Reference for more information about
RESULT_CACHE_MODE
Table 10-1 summarizes the result cache annotation mode values.
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
ALTER
TABLE
emp
RESULT_CACHE (
MODE
FORCE).
Then execute the following query in the session:
table_name,
result_cache
FROM
user_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:
ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);
See Also:Oracle Database Reference for more information about the
RESULT_CACHEcolumn on these DBA views
The
RESULT_CACHE_MODE parameter enables you to decide result cache mode across tables in your queries. Use this clause in
ALTER
SESSION and
ALTER
SYSTEM statements, or inside the server parameter file (
init.ora) to determine result caching.
See Also:Oracle Database Reference for more information about
RESULT_CACHE_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.
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.
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
ALTER
TABLE
emp
RESULT_CACHE (
MODE
FORCE) 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
/*+ no_result_cache */
empno
FROM
emp, 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
ALTER
TABLE
emp
RESULT_CACHE (
MODE
DEFAULT) 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
/*+ result_cache */
*
FROM
emp, 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.
See Also:Oracle Database SQL Language Reference for more about caching
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
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 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.
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)) 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. 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..
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. disables.
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.
See Also:
Oracle Database Reference for information about the
CLIENT_RESULT_CACHE_STAT$ view
Oracle Database Performance Tuning Guide to find the client process IDs and session IDs for the sessions doing client caching
The following sections provide some more information about performing validations of the client result cache.
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.
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.
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.
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.
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.
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 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.
The location of the ADR base is determined by OCI in the following order:
OCI first looks in the file. is
/home/chuck/obase then the ADR base is
/home/chuck/obase is
/ade/chuck_l1/oracle then the ADR base is
/ade/chuck_l1/oracle/log and 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.
See Also:"OCI Instant Client"
On Windows, if the application is run as a service, the home directory option is skipped.
Temporary directory in the Linux or UNIX operating system:
/var/tmp.
For example, in an Instant Client, if
$HOME is not writable, then the ADR base is
/var/tmp/oradiag_chuck.
See Also:Oracle Database Net Services Reference.
See Also:Oracle Database Utilities for an introduction to ADRCI 11.1.0.5.0 - Beta on Wed May 2 15:53:06/oradiag_chuck"
Next, the
SET
BASE command is described:> quit
When
ADRCI is started,".log in your application's
TNS_ADMIN directory type. value
Footnote LegendFootnote 1: Returns an
ORA-01805error when timezone files on the client and server do not match (regions are not synchronized); returns
OCI_SUCCESSwhen region time zone values are the same (represent the same instant in UTC), though the
TIME ZONEoffsets are different.