1. Programmer's Guide
  2. More OCI Advanced Topics

11 More OCI Advanced Topics

This chapter describes more advanced topics related to OCI.

You can use OCI to access Oracle TimesTen In-Memory Database and Oracle TimesTen Application-Tier Database Cache.

This chapter includes the following topics:

See Also:

Oracle TimesTen In-Memory Database C Developers Guide for information about Times Ten support for Oracle Call Interface.

About Continuous Query Notification

Continuous Query Notification (CQN) 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.

Query change notification can be registered for the following types of statements: OCI_STMT_SELECT, OCI_STMT_BEGIN, OCI_STMT_DECLARE, and OCI_STMT_CALL.

Query change notification assumes that the PLSQL code performs only SELECT statements and registers for every SELECT statement. Otherwise, it raises an error if there are any non SELECT statements in the PLSQL code.

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.

The notification includes the following information:

  • Query IDs of queries whose result sets have changed. This is if the registration was at query granularity.

  • Names of the modified objects or changed rows.

  • Operation type (INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE).

  • ROWIDs of the changed rows and the associated DML operation (INSERT, UPDATE, DELETE).

  • Global database events (STARTUP, SHUTDOWN). In Oracle Real Application Cluster (Oracle RAC) the database delivers a notification when the first instance starts or the last instance shuts down.

See Also:

Database Startup and Shutdown

Describes topics about database startup and shutdown.

This section describes the following topics:

About OCI 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 ALTER DATABASE 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 ALTER DATABASE 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 SHUTDOWN ABORT 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 OCIAdmin administration 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().

See Also:

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 11-1 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:

  1. Call OCIDBShutdown() in OCI_DEFAULT, OCI_DBSHUTDOWN_TRANSACTIONAL, OCI_DBSHUTDOWN_TRANSACTIONAL_LOCAL, or OCI_DBSHUTDOWN_IMMEDIATE mode to prohibit further connections.
  2. Use the necessary ALTER DATABASE commands to close and dismount the database.
  3. Call OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL mode to shut down the instance.

Example 11-1 Calling OCIDBStartup() to Perform a Database Startup Operation

...

/*  Example 0 - Startup:  */
OCIAdmin *admhp;
text *mount_stmt = (text *)"ALTER DATABASE MOUNT";
text *open_stmt = (text *)"ALTER DATABASE OPEN";
text *pfile = (text *)"/ade/viewname/oracle/work/t_init1.ora";

/* Start the authentication session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp,
         OCI_CRED_RDBMS, OCI_SYSDBA|OCI_PRELIM_AUTH));

/* Allocate admin handle for OCIDBStartup */
checkerr(errhp, OCIHandleAlloc((void *) envhp, (void **) &admhp,
         (ub4) OCI_HTYPE_ADMIN, (size_t) 0, (void **) 0));

/* Set attribute pfile in the admin handle 
(do not do this if you want to use the spfile) */
checkerr (errhp, OCIAttrSet( (void *) admhp, (ub4) OCI_HTYPE_ADMIN,
          (void *) pfile, (ub4) strlen(pfile),
          (ub4) OCI_ATTR_ADMIN_PFILE, (OCIError *) errhp));

/* Start up in NOMOUNT mode */
  checkerr(errhp, OCIDBStartup(svchp, errhp, admhp, OCI_DEFAULT, 0));
   checkerr(errhp, OCIHandleFree((void *) admhp, (ub4) OCI_HTYPE_ADMIN));

/* End the authentication session */
OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);

/* Start the sysdba session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
         OCI_SYSDBA));

/* Mount the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, mount_stmt, (ub4)
         strlen((char*) mount_stmt),
         (CONST OraText *) 0, (ub4) 0, (ub4) OCI_NTV_SYNTAX, (ub4)
         OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
         (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* Open the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, open_stmt, (ub4)
         strlen((char*) open_stmt),
         (CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
         (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* End the sysdba session */
OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);
...

Example 11-2 Calling OCIDBShutdown() in OCI_DBSHUTDOWN_FINAL Mode

/*  Example 1 - Orderly shutdown:  */
...
text *close_stmt = (text *)"ALTER DATABASE CLOSE NORMAL";
text *dismount_stmt = (text *)"ALTER DATABASE DISMOUNT";

/* Start the sysdba session */
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
         OCI_SYSDBA));

/* Shutdown in the default mode (transactional, transactional-local,
  immediate would be fine too) */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0, OCI_DEFAULT));

/* Close the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, close_stmt, (ub4)
         strlen((char*) close_stmt),
         (CONST OraText *)0, (ub4)0, (ub4) OCI_NTV_SYNTAX,
         (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
        (OCISnapshot *) NULL,
        (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* Dismount the database */
checkerr(errhp, OCIStmtPrepare2(svchp, &stmthp, errhp, dismount_stmt,
         (ub4) strlen((char*) dismount_stmt), (CONST OraText *)0, (ub4)0,
         (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4)0,
         (OCISnapshot *) NULL,
         (OCISnapshot *) NULL, OCI_DEFAULT));
checkerr(errhp, OCIStmtRelease(stmthp, errhp, (OraText *)0, 0, OCI_DEFAULT));

/* Final shutdown */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
         OCI_DBSHUTDOWN_FINAL));

/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...

Example 11-3 Calling OCIDBShutdown() in OCI_DBSHUTDOWN_ABORT Mode

/*  Example 2 - Shutdown using abort:  */
...
/* Start the sysdba session */
...
checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS,
         OCI_SYSDBA));

/* Shutdown in the abort mode */
checkerr(errhp, OCIDBShutdown(svchp, errhp, (OCIAdmin *)0,
         OCI_DBSHUTDOWN_ABORT));

/* End the sysdba session */
checkerr(errhp, OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT));
...

Example 11-2 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 11-3 shows a shutdown example that uses OCI_DBSHUTDOWN_ABORT mode.

See Also:

Implicit Fetching of ROWIDs

This section describes the following topics:

About 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 ... FOR UPDATE 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 (LONG VARCHAR)

  • SQLT_AFC (CHAR)

  • SQLT_AVC (CHARZ)

  • SQLT_VST (OCI String)

  • SQLT_RDD (ROWID descriptor)

The SELECT ... 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() or OCIDefineByPos2() 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: 

OCIAttrSet(stmthp, OCI_HTYPE_STMT, 0, 0 , OCI_ATTR_FETCH_ROWID, errhp);

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

See Also:

Example of Implicit Fetching of ROWIDs

Shows an example of implicit fetching of ROWIDs.

Use the fragment of a C program in Example 11-4 to build upon.

Example 11-4 Implicit Fetching of ROWIDs

#include <oci.h>
 
int main()
{
 ...
 text *mySql = (text *) "SELECT emp_name FROM emp FOR UPDATE";
 text rowid[100][15] = {0};
 text empName[100][15] = {0};
 ...
 
 /* Set up the environment, error handle, etc. */
 ...
 
 /* Prepare the statement -  select ... for update. */
 
 
  if (OCIStmtPrepare (select_p, errhp,
                      mySql, strlen(mySql), OCI_NTV_SYNTAX, OCI_DEFAULT))
  {
    printf ("Prepare failed \n");
    return (OCI_ERROR);
  }
 
 /* Set attribute for implicit fetching of ROWIDs on the statement handle. */
 if (OCIAttrSet(select_p, OCI_HTYPE_STMT, 0, 0, OCI_ATTR_FETCH_ROWID, errhp))
 {
   printf ("Unable to set the attribute - OCI_ATTR_FETCH_ROWID \n");
   return OCI_ERROR;
 }
  /* 
   * Define the positions: 0 for getting ROWIDs and other positions 
   * to fetch other columns.
   * Also, get the define conversion done implicitly by fetching 
   * the ROWIDs in the string format. 
   */
 
  if (OCIDefineByPos ( select_p,
                       &defnp0,
                       errhp,
                       0,
                       rowid[0],
                       15,
                       SQLT_STR,
                       (void *) ind,
                       (void *) 0,
                       (void *) 0,
                       OCI_DEFAULT) ||
       OCIDefineByPos(select_p,
                       &defnp1,
                       errhp,
                       1,
                       empName[0],
                       15,
                       SQLT_STR,
                       (void *) 0,
                       (void *) 0,
                       (void *) 0,
                       OCI_DEFAULT)
                       )
  {
    printf ("Failed to define\n");
    return (OCI_ERROR);
  }
 
 
  /* Execute the statement. */
 
 if (errr = OCIStmtExecute(svchp,
                            select_p,
                            errhp,
                            (ub4) 5,
                            (ub4) 0,
                            (OCISnapshot *) NULL,
                            (OCISnapshot *) NULL,
                            (ub4) OCI_DEFAULT))
  {
    if (errr != OCI_NO_DATA) 
       return errr;
  }
 
  printf ("Column 0  \t Column 1\n");
  printf ("_________ \t ________\n");
 
  for (i =0 ;i<5 i++)
   {
     printf("%s \t %s \n", rowid[i], empName[i]);
   }
 
 return OCI_SUCCESS;  
}

OCI Support for Implicit Results

Beginning with Oracle Database 12c Release 1 (12.1) , PL/SQL can return results (cursors) implicitly from stored procedures and anonymous PL/SQL blocks. OCIStmtGetNextResult() is provided to retrieve and process the implicit results.

PL/SQL provides a subprogram RETURN_RESULT in the DBMS_SQL package to return the result of an executed statement as shown in Example 11-5. In the current release, only SELECT query result-sets can be implicitly returned by a PL/SQL procedure block. OCIStmtGetNextResult() returns an OCI statement handle on which the usual OCI define and fetch calls are done to retrieve the rows.

Example 11-6 shows a PL/SQL stored procedure to implicitly return result-sets (cursors) to the client.

Example 11-7 shows the same approach using an anonymous PL/SQL block sent by the client. This example shows how applications can use the implicit results feature to implement batching of SQL statements from an OCI application. An OCI application can dynamically form a PL/SQL anonymous block to execute multiple and variable SELECT statements and return the corresponding cursors using DBMS_SQL.RETURN_RESULT.

Example 11-8 lists an OCI program showing how to use the OCIStmtGetNextResult() call to retrieve and process the implicit results returned by a PL/SQL stored procedure (see Example 11-6) or an anonymous PL/SQL block (see Example 11-7).

OCIStmtGetNextResult() can be called iteratively by the application to retrieve each implicit result from an executed PL/SQL statement. Applications retrieve each result-set sequentially but can fetch rows from any result-set independently. The top-level OCI statement handle tracks all the associated result-set statement handles. Freeing or releasing the top-level OCI statement handle automatically closes and frees all the implicit result-sets.

The attribute OCI_ATTR_IMPLICIT_RESULT_COUNT is provided on the OCI statement handle to determine the number of implicit results available.

The rtype parameter of OCIStmtGetNextResult() returns the type of the result. In this release only the type: OCI_RESULT_TYPE_SELECT is supported. The describe metadata of the returned result set can be accessed similar to any SELECT ResultSet.

Note:

The following OCI code can be used in external procedures too, to fetch from the implicit results. In that case, OCI_PREP2_IMPL_RESULTS_CLIENT should be passed as the mode to the OCIStmtPrepare2() call.

Example 11-5 DBMS_SQL RETURN_RESULT Subprogram

procedure return_result(rc        in out sys_refcursor,
                        to_client in boolean default true);

procedure return_result(rc        in out integer,
                        to_client in boolean default true);

Example 11-6 A PL/SQL Stored Procedure to Implicitly Return Result-Sets (Cursors) to the Client

CREATE PROCEDURE foo AS
  c1 sys_refcursor;
  c2 sys_refcursor;
begin
  open c1 for select * from emp;
  dbms_sql.return_result(c1); --return to client
  -- open 1 more cursor
  open c2 for select * from dept;
  dbms_sql.return_result (c2); --return to client
end;

Example 11-7 An Anonymous PL/SQL Block to Implicitly Return Result-Sets (Cursors) to the Client

declare
  c1 sys_refcursor;
  c2 sys_refcursor;
begin
  open c1 for select * from emp;
  dbms_sql.return_result (c1); --return to client
  -- open 1 more cursor
  open c2 for select * from dept;
  dbms_sql.return_result (c2); --return to client
end;

Example 11-8 Using OCIStmtGetNextResult() to Retrieve and Process the Implicit Results Returned by Either a PL/SQL Stored Procedure or Anonymous Block

  OCIStmt *stmthp;
  ub4      rsetcnt;
  void    *result;
  ub4      rtype;
  char    *sql = "begin foo; end;";
  
  /* Prepare and execute the PL/SQL procedure. */
  OCIStmtPrepare2(svchp, &stmthp, errhp, (oratext *)sql, strlen(sql),
                 NULL, 0, OCI_NTV_SYNTAX, OCI_DEFAULT);
  OCIStmtExecute(svchp, stmthp, errhp, 1, 0,
                 (const OCISnapshot *)0,
                 (OCISnapshot *)0, OCI_DEFAULT);
 
  /* Now check if any implicit results are available. */
  OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, &rsetcnt, 0,
             OCI_ATTR_IMPLICIT_RESULT_COUNT, errhp);
 
  /* Loop and retrieve the implicit result-sets.
   * ResultSets are returned in the same order as in the PL/SQL
   * procedure/block.
   */
  while (OCIStmtGetNextResult(stmthp, errhp, &result, &rtype,
                              OCI_DEFAULT) == OCI_SUCCESS)
  {
    /* Check the type of implicit ResultSet, currently
     * only supported type is OCI_RESULT_TYPE_SELECT
     */
    if (rtype == OCI_RESULT_TYPE_SELECT)
    {
      OCIStmt *rsethp = (OCIStmt *)result;
 
      /* Perform normal OCI actions to define and fetch rows. */
    }
    else
      printf("unknown result type %d\n", rtype);
 
    /* The result set handle should not be freed by the user. */
  }
 
  OCIStmtRelease(stmthp, errhp, NULL, 0, OCI_DEFAULT); /* Releases the statement handle. */

See Also:

Client Result Cache

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

See Also:

Oracle Database Development Guide for complete information about using the OCI client result cache

Client Statement Cache Auto-Tuning

Describes topics about client statement cache auto-tuning.

This section describes the following topics:

About Auto-Tuning Client Statement Cache

Auto-tuning optimizes OCI client session features of mid-tier applications to gain higher performance without the need to reprogram your OCI application.

Auto tuning operations like increasing or decreasing cache memory happens implicitly during OCIStmtPrepare2() and OCIStmtRelease() calls on a periodic basis. Calling OCIAttrGet() with OCI_ATTR_STMTCACHESIZE on the service handle gives the current cache size being used, if you need to check.

It is possible for the coded OCI client statement cache size setting to be sub optimal. This can happen, for example, with changing the workload causing a different working set of SQL statements. If the size is too low, it will cause excess network activity and more parses at the server. If the size is too high, there will be excess memory used. It can be difficult for the client side application to always keep this cache size optimal.

Auto-tuning automatically reconfigures the OCI statement cache size on a periodic basis. Auto-tuning is achieved by providing a deployment time setting that provides an option to reconfigure OCI statement caching to resolve this potential performance issue.

These settings are provided as connect string based deployment settings in the client oraaccess.xml file that overrides manual settings to the user configuration of OCI features.

See Also:

Benefit of Auto-Tuning Client Statement Cache

The more specific benefit of auto-tuning client statement cache is to transparently detect, monitor, and adjust the statement cache size to improve performance or decrease memory usage.

Developers and DBAs can expect to see the following benefits of using auto-tuning for their OCI client applications:

  • Reduced time and effort in diagnosing and fixing performance problems with each part of their system, such as statement caching

  • Minimized manual modifications needed to configurations of this OCI feature to improve performance. Usually, this manual correction requires applications to restart more than once with different configuration parameters, thus further reducing the high availability of the client

  • One solution that can be used by all OCI applications to improve performance right out-of-the-box without having to make any application changes

  • OCI applications are freed from making custom implementations (that can be error prone) to auto-tune their OCI application to optimize performance and memory usage. Here auto-tuning is limited to internal automatic tuning of OCI Client-side statement cache size only.

Client Statement Cache Auto-Tuning Parameters

The following connection specific parameters in oraccess.xml can be set per configuration alias or across all connect strings using default connection specific parameters.

Values specified in the client oraaccess.xml configuration file override programmatic settings.

This section includes the following topics:

See Also:

About Specifying Defaults for Connection Parameters for more information about setting per configuration alias or across all connect strings using default connection specific parameters

<statement_cache>

This parameter is optional and sets the limit for the statement caching tunable component. 

<statement_cache>
  <size>100</size>
</statement_cache>

The limit is the maximum number of statements that can be cached per session. If auto-tuning is enabled or not, this setting in oraaccess.xml overrides the programmatic setting of OCI statement cache size.

If auto-tuning is enabled, this setting will be the upper bound on statement cache size while its being dynamically tuned.

If the session is not using statement caching APIs as in OCIStmtPrepare2() and OCIStmtRelease(), this setting is ignored.

Default values are as follows:

  • If auto-tuning is enabled, statement caching is dynamically tuned and the initial statement cache size is set to 100 statements.

  • If auto-tuning is disabled, this setting serves as the deployment setting of statement caching size, overriding any programmatic setting.

See Also:

<auto_tune>

This section specifies auto tune parameters.

If the OCI session is not using statement caching APIs as in OCIStmtPrepare2() or OCIStmtRelease(), auto tuning parameters are ignored for that session. It is possible in a process that some sessions or connections can have auto-tuning enabled and some disabled.

This section includes the following topics:

See Also:

<enable>true</enable>

This parameter turns auto tuning on or off.

The default is auto tuning off (FALSE) or disabled. 

<auto_tune>
  <enable>true</enable>
</auto_tune>

Auto-tuning is enabled along with internal default settings.

See Also:

<statement_cache> for more information about auto-tuning is enabled along with internal default settings

<ram_threshold>

This parameter is optional. 

<auto_tune>
   <enable>true</enable>
   <ram_threshold>0.1</ram_threshold>
</auto_tune>

The default value is 0.01%. It is specified as percentage of installed RAM. This specifies the total memory available across the auto tuning sessions in a process sharing this setting. This setting can be specified per process or per connect string alias.

Note that if specified per connect string alias, the total auto tuning memory used by a client process can add up.

Therefore, it may be preferable to specify auto tuning limits in the <default_parameters> section of oraaccess.xml file. This way you have a common pool of memory for all sessions in a client process.

A smaller limit uses less RAM for auto tuning, but minimizes the chance other programs running on the system do not degrade in performance.

This parameter must be specified within the <auto_tune></auto_tune> deployment setting.

See Also:

About oraaccess.xml

<memory_target>

This parameter is optional.

<auto_tune>
  <enable>true</enable>
  <memory_target>40M</memory_target>
</auto_tune>

Specified in bytes. Default is undefined. It specifies the total memory available across the auto tuning sessions in a process sharing this setting. This setting can be specified per process or per connect string alias.

Note that if specified per connect string alias, the total auto tuning memory used by a client process can add up.

Therefore, it may be preferable to specify auto tuning limits in the <default_parameters> section of oraaccess.xml file. This way you have a common pool of memory for all sessions in a client process.

This parameter must be specified within the <auto_tune></auto_tune> deployment setting.

Using this parameter ensures the use of a consistent memory limit for auto tuning irrespective of installed RAM on that system.

If not specified, the auto tuning memory limit is based on the <ram_threshold> parameter setting.

If both <ram_threshold> and <memory_target> parameters are specified, the effective limit is the minimum of the two parameters.

See Also:

About oraaccess.xml

Comparison of the Connection Specific Auto-Tuning Parameters

Lists and describes a comparison of all auto-tuning parameters.

Table 11-1 shows a comparison of the connection specific auto-tuning parameters.

Table 11-1 Comparison of Some Connection Specific Auto-Tuning Parameters

Parameter Setting and Semantics For Auto-Tuning or Deployment Setting

<statement_cache>

Optional setting.

Per session cache size.

If auto-tuning is enabled (see "Enabling and Disabling OCI Client Auto-Tuning"), this is the upper bound of each sessions statement cache size while its tuned by auto tuning.

Or else it refers to the deployment setting for statement caching.

<auto_tune>

Optional setting.

Specify this parameter to use auto-tuning. Applies to all connections using this connect string or all connections if null connect string is specified.

Only auto-tuning related

<ram_threshold>0.1</ram_threshold>

Optional setting.

Converts the percentage setting to a memory value based on installed RAM on that client or mid-tier system.

This is the upper limit of memory used for auto tuning within a client process.

For installed RAM of 8GB, not specifying this parameter gives 800 KB of memory among the sessions.

Note each connection can potentially have its own setting of auto tuning parameters so these values can add up for the whole process based on configuration settings. It is preferable to use this parameter hence in the <default_parameters> section of the oraaccess.xml file. See "File (oraaccess.xml) Properties" for a description of the syntax.

Only auto-tuning related. If auto-tuning is disabled, this parameter setting is ignored. This parameter must be specified within the <auto_tune></auto_tune> deployment setting.

<memory_target>1048576</memory_target>

Optional setting.

This is the upper limit of memory used for auto tuning within a client process.

Note each connection can potentially have its own setting of auto tuning parameters so these values can add up for the whole process based on configuration settings. It is preferable to use this parameter hence in the <default_parameters> section of the oraaccess.xml file.

See "File (oraaccess.xml) Properties" for a description of the syntax.

Value is in bytes. 1,048,576 bytes is 1 MB.

Only auto-tuning related. If auto-tuning is disabled, this parameter setting is ignored. This parameter must be specified within the <auto_tune></auto_tune> deployment setting.

Usage Examples of Client Statement Cache Auto Tuning

The following are some usage examples showing use and interaction of client statement cache auto-tuning parameters that are also connection specific parameters.

<statement_cache>
  <size>100</size>
</statement_cache>

The programmatic statement cache size will be replaced by this setting. Auto-tuning is disabled and cache is managed per LRU. In this case, the application developer believes the OCI application statement prefetching programmatic settings do not need to be overridden.

<auto_tune>
  <enable>true</enable>
</auto_tune>

Auto-tuning is enabled along with internal default settings.

<statement_cache>
  <size>100</size>
</statement_cache>
<auto_tune>
  <enable>true</enable>
  <memory_target>40M</memory_target>
</auto_tune>

This statement caching deployment setting of 100 will replace the programmatic statement cache size and because auto-tuning is enabled, statement caching will be auto-tuned. The memory target setting is in effect because auto-tuning is enabled.

Auto tuning will always try to limit total statement cache memory used around a memory target. If a memory target is not specified, it is based on the percentage of total installed RAM.

In this case, the memory limit is the specified memory target.

See Also:

<statement_cache> for more information about auto-tuning is enabled along with internal default settings

Enabling and Disabling OCI Client Auto-Tuning

Describes conditions that enable and disable OCI client auto-tuning.

The following conditions enable and disable OCI client auto-tuning:

  • Auto-tuning is enabled when the client oraaccess.xml <auto_tune> section is added with enable specified as true, <enable>true</enable>

  • Auto-tuning is disabled by default or when enable is set to false, <enable>false</enable> in oraaccess.xml under the <auto_tune> section.

Usage Guidelines for Auto-Tuning Client Statement Cache

Describes guidelines to use when setting the auto-tuning parameters.

The following are some guidelines to use when setting the auto-tuning parameters:

  • When either client response, memory allocation, or client CPU is high and you want to gain performance without rebuilding the OCI application, you can use <auto_tune> settings or deployment <statement_cache> settings. Auto tuning may also decrease the network bytes transferred between client and server.

  • When AWR or ADDM reports lots of parses and you cannot or you may prefer not to programmatically modify the statement cache size, you can specify auto-tuning for statement cache or use the deployment statement cache setting <statement_cache>.

OCI Client-Side Deployment Parameters Using oraaccess.xml

Describes the OCI client-side deployment parameters using oraaccess.xml.

This section includes the following topics:

About oraaccess.xml

Starting with Oracle Database Release 12c Release 1 (12.1), Oracle provides an oraaccess.xml file, a client-side configuration file.

You can use the oraaccess.xml file to configure selected OCI parameters (some of which are accepted programatically in various OCI API calls), thereby allowing OCI behavior to be changed during deployment without modifying the source code that calls OCI.

Updates to the oraaccess.xml file will not affect already running clients. In order to pick up any updates to the oraaccess.xml file, already running clients need to be restarted.

The oraaccess.xml file is read from the directory specified by the TNS_ADMIN environment variable in regular and instant client installations. This is the $ORACLE_HOME/network/admin directory on UNIX operating systems and the %ORACLE_HOME%\NETWORK\ADMIN directory on Microsoft Windows operating systems, if TNS_ADMIN is not set in regular client installations.

About Client-Side Deployment Parameters Specified in oraaccess.xml

When equivalent parameters are set both in the sqlnet.ora and oraaccess.xml files, the oraaccess.xml file setting takes precedence over the corresponding sqlnet.ora file setting.

In such cases, Oracle recommends using the oraaccess.xml file settings moving forward. For any network configuration, the sqlnet.ora file continues to be the primary file as network level settings are not supported in the oraaccess.xml file.

High Level Structure of oraaccess.xml

Describes the high-level structure of the oraaccess.xml file.

The oraaccess.xml file has a top-level node <oraaccess> with the following three elements:

  • <default_parameters> - This element describes any default parameter settings shared across connections. These default parameters include:

    • Defaults for global parameters - These global parameters can only be specified once and hence are applicable to all connections and cannot be overridden at the connection level. These parameters are specified using the following tags:

      • <events> - Creates the OCI Environment in OCI_EVENTS mode, which is required for Fast Application Notification (FAN) and runtime connection load balancing

      • <result_cache> - Sets OCI client result cache parameters

      • <diag> - Sets OCI fault diagnosability parameters

    • Defaults for connection-specific parameters - Connection parameters can be set to different values for specific connections. However, they too can be defaulted, and overridden on a per connection string basis as needed. These defaults are shared across all connections (unless overridden at the connection level, which is described in the <config_descriptions> list item) that follows. These defaults are specified by the following tags:

      • <prefetch> - Sets the number of prefetch rows for all queries; specified using the <rows> parameter.

      • <statement_cache> - Sets the maximum number of statements that can be cached per session; specified using the <size> parameter.

      • <auto tune> - Consists of: <enable> to turn auto tuning on or off; <ram_threshold>, which sets the memory threshold for auto-tuning to stop using more memory when available physical memory on the client system has reached this threshold; and <memory_target>, which sets the memory limit that OCI auto-tuning can use per client process.

      • <fan_subscription_failure_action> - Sets the action upon subscription failure to be either the value trace or error.

      • <ons> - Sets a variety of ONS client-side deployment configuration parameters used for FAN notifications.

  • <config_descriptions> - This element associates a configuration alias element (<config_alias>), which is basically a name, with a specific set of parameters (<parameters>) that contain one or more connection parameters. These connection parameters are the same connection parameters within the element <default_parameters> described previously, namely: <prefetch>, <statement_cache>, <auto_tune>, <fan_subscription_failure_action> and <ons>.

  • <connection_configs> - This element associates one or more connection strings used by an application with a config alias, thus allowing multiple connection string elements to share the same set of parameters.

    A connection configuration element (<connection_config>) associates a connection string element (<connection_string>) with a configuration alias element (<config_alias>).

    A connection string is indirectly associated with a set of parameters through the configuration alias, which allows multiple connection string elements to share the same set of parameters.

The sections that follow describe these client-side deployment parameters in more detail.

See Also:

About Specifying Defaults for Connection Parameters

About Specifying Global Parameters in oraaccess.xml

As described, the <default_parameters> tag allows specifying default values for various OCI parameters.

Of these, some parameters can only be specified once and hence apply to all connections. These global parameters are described using the following tags:

  • <events>

    This creates the OCI Environment in OCI_EVENTS mode, which is required for Fast Application Notification (FAN) and runtime connection load balancing. 

    <events>
  true <!--value could be false also -->
</events>

  • <result_cache>

    • <max_rset_rows> - Maximum size of any result set in rows in the per-process query cache. Equivalent to OCI_RESULT_CACHE_MAX_RSET_ROWS in the sqlnet.ora file.

    • <max_rset_size> - Maximum client result cache size. Set the size to 32,768 bytes (32 Kilobytes (KB)) or greater. Equivalent to OCI_RESULT_CACHE_MAX_RSET_SIZE in the sqlnet.ora file.

    • <max_size> - Maximum size in bytes for the per-process query cache. Specifying a size less than 32,768 in the client disables the client result cache feature. Equivalent to OCI_RESULT_CACHE_MAX_SIZE in the sqlnet.ora file. 

    <result_cache>
      <max_rset_rows>10</max_rset_rows>
      <max_rset_size>65535</max_rset_size>
      <max_size>65535</max_size>
</result_cache>

    When equivalent parameters are set both in the sqlnet.ora and oraaccess.xml files, the oraaccess.xml file setting takes precedence over the corresponding sqlnet.ora file setting.

    See Table 11-2 for a listing of equivalent OCI parameter settings.

  • <diag>

    You can specify the following elements:

    • <adr_enabled> - Enables or disables diagnosability. Equivalent to DIAG_ADR_ENABLED in the sqlnet.ora file. Values: true or false.

    • <dde_enabled> - Enables or disables DDE. Values: true or false.

    • <adr_base> - Sets the ADR base directory, which is a system-dependent directory path string to designate the location of the ADR base to use in the current ADRCI session. Equivalent to ADR_BASE in the sqlnet.ora file. Value: directory path for ADR base directory.

    • <sighandler_enabled> - Enables or disables OCI signal handler. Values: true or false.

    • <restricted> - Enables or disables full dump files. 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 specifying a value of false. Values: true or false.

    • <trace_events> - Indicates the trace event number and the level of tracing to be in effect. Currently only event 10883 is supported. The available levels are 5 and 10. 

    <diag>
   <adr_enabled>false</adr_enabled>
   <dde_enabled>false</dde_enabled>
   <adr_base>/foo/adr</adr_base>
   <sighandler_enabled>false</sighandler_enabled>
   <restricted>true</restricted>
   <trace_events>
     <trace_event>
       <number>10883</number>
       <level>5</level>
     </trace_event>
   </trace_events>
</diag>

    When equivalent parameters are set both in the sqlnet.ora and oraaccess.xml files, the oraaccess.xml file setting takes precedence over the corresponding sqlnet.ora file setting.

    See Table 11-2 for a listing of equivalent OCI parameter settings.

    Table 11-2 Equivalent OCI Parameter Settings in oraaccess.xml and sqlnet.ora

    Parameter Group oraaccess.xml Parameters sqlnet.ora Parameters

    OCI client result cache

    <max_rset_rows>

    OCI_RESULT_CACHE_MAX_RSET_ROWS

    OCI client result cache

    <max_rset_size>

    OCI_RESULT_CACHE_MAX_RSET_SIZE

    OCI client result cache

    <max_size>

    OCI_RESULT_CACHE_MAX_SIZE

    OCI fault diagnosability

    <adr_enabled>

    DIAG_ADR_ENABLED

    OCI fault diagnosability

    <dde_enabled>

    DIAG_DDE_ENABLED

    OCI fault diagnosability

    <adr_base>

    ADR_BASE

See Also:

About Specifying Defaults for Connection Parameters

Describes the default values you can set for connection parameters shared across connections.

You can specify the following connection parameters that are shared across connections:

  • <prefetch> - Specifies prefetch row count for SELECT statements. 

    <prefetch>
  <rows>100</rows>
</prefetch>

    Setting this parameter appropriately can help reduce round-trips to the database, thereby improving application performance.

    Note that this only overrides the OCI_ATTR_PREFETCH_ROWS parameter (whether explicitly specified by the application or not). If the application has specified OCI_ATTR_PREFETCH_MEMORY explicitly, then the actual prefetch row count will be determined by using both constraints. The OCI_ATTR_PREFETCH_MEMORY constraint equivalent cannot be specified in the oraaccess.xml file.

    Also note that OCI prefetching may still get disabled if the SELECT statement fetches columns of specific data types. For more details, see About Fetching Results for information about limitations of OCI prefetch.

  • <statement_cache> - Specifies the number of OCI Statement handles that can be cached per session. 

    <statement_cache>
  <size>100</size>
</statement_cache>

    Caching statement handles improves repeat execute performance by reducing client side and server side CPU consumption and network traffic.

    Note that for this parameter to take effect, the application must be programmed to use OCIStatementPrepare2() and OCIStatementRelease() calls (and not the older OCISatementPrepare() and OCIHandleFree() equivalents for getting and disposing of statement handles.

  • <auto_tune> - Used to enable OCI Auto tuning. 

    <auto_tune>
  <enable>true</enable>
  <ram_threshold>0.1</ram_threshold><!--percentage -->
  <memory target>2M</memory_target>
</auto_tune>

    Enabling auto-tuning can help OCI automatically tune the statement-cache size based on specified memory constraints. This can help dynamically tune the statement cache size to an appropriate value based on runtime application characteristics and available memory resources.

    Note that for auto tuning OCI Statement Cache, the application must be programmed to use OCIStatementPrepare2() and OCIStatementRelease() calls (and not the older OCISatementPrepare() and OCIHandleFree() equivalents for getting and disposing of statement handles.

  • <fan_subscription_failure_action> - Used to determine how OCI responds to a failure to subscribe for FAN notifications.

    A value of trace records any failure to subscribe for FAN notifications (if FAN is enabled) in the trace file and OCI proceeds ignoring the failure. A value of error makes OCI return an error if an attempt to subscribe for FAN notifications fails. 

    <fan>
  <!--only possible values are "trace" and "error" -->
  <subscription_failure_action>
    trace
  </subscription_failure_action>
</fan>

  • <ons> - Sets up Oracle Notification Service (ONS) parameters.

    You can specify the following connection parameters:

    • <subscription_wait_timeout> - Length of time in seconds the client waits for its subscription to the ONS server.

    • <auto_config> - true or false. If true, the configuration specified in this section will augment the auto configuration information that the client receives from the database. If false, it will override the same.

    • <thread_stack_size> - Size in bytes of the event notification thread stack.

    • <debug> - true or false. Whether debug mode is on (true) or off (false).

    • <wallet_location> - Directory that contains an auto logon wallet file for a secure ONS subscription.

    • <servers> - Host list with ports and connection distribution. 

    <ons> 
  <!--values or in seconds -->
  <subscription_wait_timeout>
    5
  </subscription_wait_timeout>
  <auto_config>true</auto_config> <!--boolean -->
  <threadstacksize>100k</threadstacksize>
  <debug>true</debug>
  <wallet_location>/etc/oracle/wallets/</wallet_location>
  <servers>
    <address_list>
       <name>pacific</name>
       <max_connections> 3 <\max_connections>
       <hosts>
         10.228.215.121:25293,
         10.228.215.122:25293
       </hosts>
    </address_list>
    <address_list>
       <name>Europe</name>
       <max_connections>3<\max_connections>
       <hosts>
         myhost1.mydomain.com:25273,
         myhost2.mydomain.com:25298,
         myhost3.mydomain.com:30004
       </hosts>
    </address_list>
  </servers>
</ons>

See Also:

Overriding Connection Parameters at the Connection-String Level

Using the oraaccess.xml file also allows you to override the very same set of connection-specific parameters at the connection-string level as well.

This allows for overriding those connection-specific parameters based on requirements of individual applications.

Using the <config_descriptions> tag, you can specify a set of connection-specific parameters (<parameters>) to be associated with a configuration alias (<config_alias>, which creates a named group of connection-specific parameters). Thereafter, using the <connection_configs> tag, you can associate one or more connection-strings (specified using the <connection-string> tag) with a <config_alias>. This permits a level of indirection that allows multiple <connection_string> elements to share the same set of <parameters>.

Example 1

This example shows a very simple oraaccess.xml file configuration that highlights defaulting of global and connection parameters applicable across all connections. 

<?xml version="1.0" encoding="ASCII" ?> 
<!--
     Here is a sample oraaccess.xml.
     This shows defaulting of global and connection parameters 
     across all connections. 
-->
 <oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
  xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
  schemaLocation="http://xmlns.oracle.com/oci/oraaccess
  http://xmlns.oracle.com/oci/oraaccess.xsd">
  <default_parameters>
    <prefetch>
      <rows>50</rows> 
    </prefetch>
    <statement_cache>
      <size>100</size> 
    </statement_cache>
    <result_cache>
      <max_rset_rows>100</max_rset_rows> 
      <max_rset_size>10K</max_rset_size> 
      <max_size>64M</max_size> 
    </result_cache>
  </default_parameters>
</oraaccess>

Example 2

This example shows connection parameters being overridden at the connection level.

<?xml version="1.0" encoding="ASCII" ?> 
<!-- 
        Here is a sample oraaccess.xml. 
        This highlights some connection parameters being
        overridden at the connection level
     --> 
 <oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
  xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
  schemaLocation="http://xmlns.oracle.com/oci/oraaccess
  http://xmlns.oracle.com/oci/oraaccess.xsd">
  <default_parameters>
    <prefetch>
      <rows>50</rows> 
    </prefetch>
    <statement_cache>
      <size>100</size> 
    </statement_cache>
    <auto_tune>
      <enable>true</enable> 
      <ram_threshold>2.67</ram_threshold> 
      <memory_target>204800</memory_target> 
    </auto_tune>
    <result_cache>
      <max_rset_rows>100</max_rset_rows> 
      <max_rset_size>10K</max_rset_size> 
      <max_size>64M</max_size> 
    </result_cache>
  </default_parameters>
    <!-- 
         Create configuration descriptions, which are  
         groups of connection parameters associated with
         a config_alias. 
    --> 
  <config_descriptions>
    <config_description>
      <config_alias>bar</config_alias> 
      <parameters>
        <prefetch>
          <rows>20</rows> 
        </prefetch>
      </parameters>
    </config_description>
    <config_description>
      <config_alias>foo</config_alias> 
      <parameters>
        <statement_cache>
          <size>15</size> 
        </statement_cache>
      </parameters>
    </config_description>
  </config_descriptions>
  <!--   
         Now map the connection string used by the application 
         with a config_alias.  
  --> 
  <connection_configs>
    <connection_config>
      <connection_string>hr_db</connection_string> 
      <config_alias>foo</config_alias> 
    </connection_config>
    <connection_config>
      <connection_string>finance_db</connection_string> 
      <config_alias>bar</config_alias> 
    </connection_config>
  </connection_configs>
</oraaccess>

Example 3

This example highlights setup for FAN notifications. 

<?xml version="1.0" encoding="ASCII" ?> 
   <!--
    Here is a sample for oraaccess.xml for 
    setting up for FAN notifications.
   --> 
  <oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
    xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
    schemaLocation="http://xmlns.oracle.com/oci/oraaccess
    http://xmlns.oracle.com/oci/oraaccess.xsd">
    <default_parameters>
      <fan>
        <!-- only possible values are "trace" or "error" --> 
        <subscription_failure_action>
          error
        </subscription_failure_action> 
      </fan>
      <ons>
        <subscription_wait_timeout>
          5
        </subscription_wait_timeout> 
        <auto_config>true</auto_config>  
      </ons>
    <events>true</events> 
    </default_parameters>
  </oraaccess>

Example 4

This example highlights an advanced oraaccess.xml file configuration usage with manual ONS settings. Manual ONS settings should be used rarely. 

  <?xml version="1.0" encoding="ASCII" ?> 
    <!-- 
       Here is a sample for oraaccess.xml that highlights
       manual ONS settings.
    --> 
  <oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"
    xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"
    schemaLocation="http://xmlns.oracle.com/oci/oraaccess
    http://xmlns.oracle.com/oci/oraaccess.xsd">
    <default_parameters>
      <fan>
       <!-- only possible values are "trace" or "error"  --> 
        <subscription_failure_action>
          error
        </subscription_failure_action> 
      </fan>
      <ons>
        <subscription_wait_timeout>
          5
        </subscription_wait_timeout> 
        <auto_config>true</auto_config>  
        <!--This provides the manual configuration for ONS.
            Note that this functionality should not need to be used
            as auto_config can normally discover this 
            information. --> 
        <servers>
          <address_list>
            <name>pacific</name> 
            <max_connections>3</max_connections> 
            <hosts>10.228.215.121:25293, 10.228.215.122:25293</hosts> 
          </address_list>
          <address_list>
            <name>Europe</name> 
            <max_connections>3</max_connections> 
            <hosts>myhost1.mydomain.com:25273, 
              myhost2.mydomain.com:25298, 
              myhost3.mydomain.com:30004</hosts> 
          </address_list>
        </servers>
      </ons>
      <events>true</events> 
    </default_parameters>
  </oraaccess>

See Also:

About Specifying Defaults for Connection Parameters about overriding the very same set of connection-specific parameters at the connection-string level

File (oraaccess.xml) Properties

Lists some high level rules with regards to the oraaccess.xml file syntax stated here for simplicity.

The XML schema specified in the oraaccess.xsd file is the ultimate formal reference for oraaccess syntax:

  • The contents of the file are case sensitive, and all elements (tags, parameter names) are in lower case.

  • Comments are allowed between parameters (nodes); for example, Comment "<!-- comments -->".

  • For the syntax with respect to the order of the parameters, see the XML Schema: oraaccess.xsd file (see information about the oraaccess.xsd file later in this list).

  • For memory size, valid values and formats are 100, 100k, 100K, 1000M, and 1121m. This means only suffixes 'M', 'm', 'K', 'k', or no suffix are allowed. 'K' or 'k' means kilobytes and 'M' or 'm' means megabytes. No suffix means the size is in bytes.

  • <ram_threshold> should be a decimal number between 0 and 100 and indicates a percentage value.

  • Where a number is expected, only positive unsigned integers are allowed; no sign is allowed. An example of a valid number usage is <statement_cache> <size>100</size> </statement_cache>).

  • Configuration alias names (<config_alias>foo</config_alias>) are not case-sensitive

  • String parameters (such as <config_alias>) are not expected to be quoted.

  • These rules are all encapsulated in the schema definition.

  • OCI will report an error if OCI is provided an invalid oraaccess.xml file.

  • Before deploying an oraaccess.xml file, Oracle recommends that you validate it with the Oracle supplied XML schema file: oraaccess.xsd. The schema file is installed under ORACLE_HOME/rdbms/admin in a regular client and under instantclient_12_2/sdk/admin in an instant client SDK. Customers can use their own favorite XML validation tools to perform the validation after modifying the oraaccess.xml file.

  • Sample oraaccess.xml files can be found in the ORACLE_HOME/rdbms/demo directory in a regular client and in the instantclient_12_2/sdk/demo in an instant client. The parameters in these files are for demonstration purpose only and should be modified and tested as per the application's requirement before deploying it.

Fault Diagnosability in OCI

This section describes the following topics:

About 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. Up to Release 11.2.0.1, the diagnostic data was 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. However, beginning with Release 11.2.0.2, the diagnostic data is stored in the current directory.

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

Describes how the location of the ADR base is determined.

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

  1. For all diagnosability parameters, OCI first looks in the file oraaccess.xml. If these parameters are not set there, then OCI looks next in 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.

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

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

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

  5. On Windows, if the application is run as a service, the home directory option is skipped.

  6. 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 and the ADR home could be /var/tmp/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11.

    Temporary directories in the Windows operating system, searched in this order:

    1. %TMP%

    2. %TEMP%

    3. %USERPROFILE%

    4. Windows system directory

If none of these directory choices are available and writable, or the ADR base is not created and there are no diagnostics.

See Also:

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 SHOW BASE command, and then the use of the SHOW BASE command with the option -PRODUCT CLIENT, which is necessary for OCI applications. The ADRCI commands are case-insensitive. User input is shown in bold. 

% adrci
 
ADRCI: Release 12.2.0.0.0 - Development on Wed Dec 2 18:26:29 2015 
Copyright (c) 1982, 2015, Oracle.  All rights reserved. ADR base = "/u01/app/oracle/log"

 
adrci> help show base
 
  Usage: SHOW BASE [-product <product_name>]
 
  Purpose: Show the current ADR base setting.
 
  Options:
    [-product <product_name>]: This option allows users to show the
    given product's ADR Base location. The current registered products are
    "CLIENT" and "ADRCI".
 
  Examples: 
    show base -product client
    show base
 
adrci> show base -product client
ADR base = "/u01/app/oracle/log"

Next, the SET BASE command is described: 

adrci> help set base
 
  Usage:  SET BASE <base_str> | -product <product_name>
 
  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.
    -product <product_name>: This option allows users to set the 
    given product's ADR Base location. The current registered products are
    "CLIENT" and "ADRCI".
 
  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 -product client
    set base .
 
adrci> quit

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

% adrci
 
ADRCI: Release 12.2.0.0.0 - Development on Wed Dec 2 18:26:29 2015
 
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
 
ADR base = "/u01/app/oracle/log"
 adrci>

For OCI application incidents you must check and set the base:

adrci> show base -product client
ADR base is "/u01/app/oracle/log" 
adrci> set base /home/chuck_13/oradiag_chuck

For Instant Client there is no $ORACLE_HOME, so the default base is the user's home directory: 

adrci> show base -product client
ADR base is "/home/chuck_13/oradiag_chuck"
adrci> set base /home/chuck/oradiag_chuck
adrci> show incidents
 
ADR Home = /home/chuck/oradiag_chuck/diag/clients/user_chuck/host_4144260688_11:
*************************************************************************
INCIDENT_ID    PROBLEM_KEY           CREATE_TIME
-------------------------------------------------------------------------
1                     oci 24550 [6]              2015-12-02 17:20:02.803697 -07:00                      
1 rows fetched
 
adrci>

See Also:

About OCI Instant Client

Oracle Database Utilities for an introduction to ADRCI

Controlling ADR Creation and Disabling Fault Diagnosability Using sqlnet.ora

Describes how to control ADR creation and disabling fault diagnosability using sqlnet.ora.

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:

  1. Upgrade your application to use the latest client libraries.
  2. Start your application.
  3. 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).

See Also:

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 TIMESTAMP WITH TIMEZONE (TSTZ) data type values.

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.

See Also:

Oracle Database Globalization Support Guide for information about upgrading the time zone file and timestamp with time zone data

Support for Pluggable Databases

The multitenant architecture enables an Oracle database to contain a portable collection of schemas, schema objects, and nonschema objects that appear to an Oracle client as a separate database.

A multitenant container database (CDB) is an Oracle database that includes one or more pluggable databases (PDBs).

OCI clients can connect to a PDB using a service whose pluggable database property has been set to the relevant PDB.

In general, OCI calls behave the same way whether connected to a pluggable database or a normal database. OCI calls and features that require special consideration with a CDB are described in the sections that follow:

See Also:

Oracle Database Administrator’s Guide for more information about PDBs and for more details about configuring the services to connect to various PDBs

Enhancements on OCI API Calls with Multitenant Container Databases (CDB) in General

These enhancements are the result of restrictions that are now removed for Oracle Database 12c Release 2 (12.2) and later.

The following enhancements are the result of restrictions that are now removed for Oracle Database 12c Release 2 (12.2) and later.

  • Client result cache works with connections to Pluggable Databases.

OCI Enhancements for ALTER SESSION SET CONTAINER

Some OCI restrictions about using the ALTER SESSION SET CONTAINER statement are removed.

The client must be Oracle Database Release 12c Release 2 (12.2) to perform these operations. Lower version clients are returned an error if any of the following operations is attempted.

  • The ALTER SESSION SET CONTAINER statement supports applications that use TIMESTAMP WITH TIMEZONE or TIMESTAMP WITH LOCAL TIMEZONE data types in OCI. The semantics of these types are preserved when the application switches between pluggable databases having different database time zone settings or different database time zone file version settings.

  • The ALTER SESSION SET CONTAINER statement supports the use of this command to switch an OCI connection between any two pluggable databases whose character sets are different.

Restrictions on OCI API Calls with Multitenant Container Databases (CDB) in General

Descriptions general restrictions.

  • An attempt to logon in OCI_PRELIM_AUTH mode when connected to any container other than CDB$ROOT will result in an ORA-24542 error.

  • An attempt to issue OCIDBStartup() when connected to any container other than CDB$ROOT results in an ORA-24543 error.

  • An attempt to issue OCIDBShutdown() when connected to any container other than CDB$ROOT results in an ORA-24543 error. When OCIDBShutdown() is issued connected to CDB$ROOT, it brings down the whole instance.

  • OCI Continuous Query Notification (CQN) is not supported with CDB.

  • OCI applications linked against a client library older than release 12.1 or higher and connecting to a pluggable database will not be able to utilize Fast Application Notification (FAN) High Availability (HA) functionality when connected as a normal (non-common) user. As a workaround, such applications should connect as a common user. This restriction does not exist for release 12.1 or higher OCI clients.

See Also:

Restrictions on OCI Calls with ALTER SESSION SET CONTAINER

Describes specific restrictions.

The ALTER SESSION SET CONTAINER statement can be used to switch an OCI connection from one pluggable database to another. However, applications that use the ALTER SESSION SET CONTAINER statement to switch between pluggable databases need to ensure that their usage is consistent with the OCI restrictions described as follows.

  • The ALTER SESSION SET CONTAINER statement is disallowed for OCI migratable sessions (such as sessions created with OCI_MIGRATE mode during logon) and the combination results in an ORA-65135 error.

  • The ALTER SESSION SET CONTAINER statement is not supported with OCI connection pool (which is the old OCI connection pool API) and the combination results in an ORA-65135 error.

  • The ALTER SESSION SET CONTAINER statement is not supported in conjunction with OCI session switching (wherein multiple OCI user handles share the same OCI server handle).

  • If the client initially connects to a container with a EXTENDED MAX_STRING_SIZE setting, and then within the same session switches to a container (using an ALTER SESSION SET CONTAINER statement) with an STANDARD MAX_STRING_SIZE setting, then a subsequent OCIStmtExecute() call will result in an ORA-14697 error if an attempt is made to use any bind variables of size greater than 4000 bytes.

  • An attempt to fetch from an OCI statement handle using OCIStmtFetch() or OCIStmtFetch2() in the context of a different container than the one in which it was executed will result in an ORA-65108 error.

  • OCI client result cache is disabled if an ALTER SESSION SET CONTAINER statement is done in OCI.

  • Fast Application Notification (FAN) and Runtime Connection Load Balancing notifications are not supported for applications that switch connections between pluggable databases using an ALTER SESSION SET CONTAINER statement.

  • The ALTER SESSION SET CONTAINER statement sets the current transaction, if any, to read only and any attempt to perform any of the OCI transaction calls (OCITransStart(), OCITransDetach(), OCITransCommit(), OCITransRollback(), OCITransPrepare(), OCITransMultiPrepare(), OCITransForget()) will return an error in the new container. In order to issue any of these calls, you need to switch back to the original container.

  • If an OCISubscriptionUnRegister() call is attempted in the context of an incorrect container (different from the container on which the corresponding OCISubscriptionRegister() call was done), then an ORA-24950 is returned.

  • A OCIDescribeAny() call with OCI_PTYPE_DATABASE describes the database to which the connection is connected. After an ALTER SESSION SET CONTAINER statement is done, if the application wants to see the current database description, the OCIDescribeAny() call will need to be reissued.

  • Calls to any OCI Any Data, collection, or object functions that are used to manipulate an object from a different container are not supported.

  • An OCIObjectFlush() call is supported only in the container where the object instance was created with an OCIObjectNew() call.

  • Oracle recommends that OCIObjectFlush() be called prior to switching containers with an ALTER SESSION SET CONTAINER statement. Note that an OCIObjectFlush() call will start a transaction if one is not already started.

  • An OCIObjectFlush() call done after switching containers may return an error if a transaction was already started earlier on another container by the same session (either as a result of explicit DMLs or as a result of an OCIObjectFlush()call).

  • An OCIObjectFlush() call only flushes objects dirtied in the context of the container in which the OCIObjectFlush() call is issued.

  • Various session attributes may change on an ALTER SESSION SET CONTAINER statement. If an application caches these attributes, their settings may no longer be the same after an ALTER SESSION SET CONTAINER statement. Examples of attributes that can be obtained with an OCIAttrGet() call and which can change on an ALTER SESSION SET CONTAINER statement include the following:

See Also:

Restrictions on OCI Calls with ALTER SESSION SWITCH CONTAINER SWITCH SERVICE

Describes a restriction on OCI calls with the ALTER SESSION SWITCH CONTAINER SWITCH SERVICE statement.

Beginning with Oracle Database 12c Release 2 (12.2), the ALTER SESSION SWITCH CONTAINER SWITCH SERVICE statement is added, which may result in a change of service attributes if the new service is configured differently than when connected to the original service. However, OCI will not alter its processing based on the new settings with this statement; instead, it will continue to use the original settings as when connected to the original service. For example, FAN on/off, TAF on/off settings, and so forth will remain the same as when first connected to the original service. This is considered the desired behavior after the switch as the normal use case for the ALTER SESSION SWITCH CONTAINER SWITCH SERVICE statement is in a multitenant environment with the same application being in use after the switch.

About the OCI Interface for Using Shards

Describes the OCI interface for using Oracle Sharding.

Shards are locations in a set of databases where each database stores some part of the data. The part of the data stored at each database is represented by a set of chunks, where each chunk is associated with a certain range of the data.

To make requests that read from or write to a chunk, your application must be routed to the appropriate database (shard) that stores that chunk during the connection initiation step. This routing is accomplished by using a data key. The data key enables routing to the specific chunk by specifying its sharding key or to a group of chunks by specifying its super sharding key. In order to get a connection to the correct shard containing the chunk you wish to operate on, you must specify a key in your application before getting a connection to a sharded Oracle database for either standalone connections or connections obtained from an OCI Session pool. For an OCI Session pool, you must specify a data key before you check out connections from the pool.

For OCI Session pools and stand alone connections, the steps to form sharding keys and shard group keys and get a session with an underlying connection include:

  1. Allocate the sharding key descriptor by calling OCIDescriptorAlloc() and specifying the descriptor type parameter as OCI_DTYPE_SHARDING_KEY to form the sharding key.

    1. Add all of the columns of the sharding key by calling OCIShardingKeyColumnAdd() as many times as is needed to form the complete sharding key.

    2. Call OCIAttrSet() and specify the OCI_ATTR_SHARDING_KEY attribute to set the sharding key on the authentication handle.

  2. Allocate the shard group key descriptor by calling OCIDescriptorAlloc() and specifying the descriptor type parameter as OCI_DTYPE_SHARDING_KEY to form the shard group key.

    1. Add all of the group columns of the sharding key by calling OCIShardingKeyColumnAdd() as many times as is needed to form the complete shard group key.

    2. Call OCIAttrSet() and specify the OCI_ATTR_SUPER_SHARDING_KEY attribute to set the shard group key on the authentication handle.

  3. Call OCISessionGet() using the initialized authentication handle from the previous step containing the sharding key and shard group key information to get the database connection to the shard and chunk specified by the sharding key and group of chunks as specified by the shard group key.

For custom pools, the steps to form sharding keys and shard group keys and check out a connection from the pool include:

  1. If there are no existing connections in your custom pool, go directly to Step 7. Otherwise, do the following steps.

  2. Allocate the sharding key descriptor by calling OCIDescriptorAlloc() and specifying the descriptor type parameter as OCI_DTYPE_SHARDING_KEY to form the sharding key. Add all of the columns of the sharding key by calling OCIShardingKeyColumnAdd() as many times as is needed to form the complete sharding key.

  3. Allocate the shard group key descriptor by calling OCIDescriptorAlloc() and specifying the descriptor type parameter as OCI_DTYPE_SHARDING_KEY to form the shard group key. Add all of the group columns of the sharding key by calling OCIShardingKeyColumnAdd() as many times as is needed to form the complete shard group key.

  4. Call OCIShardInstancesGet() with the sharding key, super sharding key descriptors and the connect string to return the instance name or names that contain the desired chunk for the specified sharding key descriptor and super sharding key descriptor.

  5. Examine each connection in the custom pool to see if it points to one of the instances whose name was returned by OCIShardInstancesGet(). To check the instance that a connection in your custom pool points to, you can get the instance name using OCI_ATTR_INSTNAME on the service context handle (OCISvcCtx *).

  6. If you find a suitable connection in your custom pool that points to one of the desired instances, then call OCIAttrSet() to associate the sharding key and super sharding key with that connection. Now the connection is ready for executing your application's OCI calls on the desired shard. You can skip Step 7. If you do not find a suitable connection in your custom pool that points to the desired instance, continue to Step 7.

  7. If there is no matching connection found, create a new connection with the sharding key, super sharding key, and connect string and call OCISessionGet() and specify the OCI_SESSGET_CUSTOM_POOL mode to explicitly cache the shard topology that contains the shard to chunking mapping information. Now you have a connection to the desired shard. Now the connection is ready for executing your application's OCI calls on the desired shard.

Providing the sharding key or a super sharding key to OCI enables it to get a connection to the desired shard. As previously noted, these keys must be specified before getting a standalone connection to the database, or before checking out a connection from the OCI Session pool so that an appropriate connection to the desired shard can be returned.

In the case of custom pools, when the pool is empty, the custom pool implementer can use the steps as described above to first create standalone connections to the desired shards by providing the sharding key and super sharding key (with OCI_SESSGET_CUSTOM_POOL mode) to populate the custom pool, and secondly, for subsequent requests for connections to specific shards, use the OCIShardInstancesGet() call as described in conjunction with OCIAttrGet() of OCI_ATTR_INSTNAME to determine if an existing connection to the desired shard already exists in the custom pool, and if it does, then the connection can be reused.

The section describes the OCI interfaces that:

  • Create a sharding key and a super sharding key using OCI data types.

  • Create a connection specifying a sharding key and a super sharding key.

  • Specify the sharding key and super sharding key to the connection request from an OCI session pool.

  • Use custom connection pooling to get the shard name for a given connection and to get the shard name and the chunk name, given a sharding key and super sharding key.

About Specifying a Sharding Key and Super Sharding Keys for a Standalone Connection

Use the OCISessionGet() call for creating the connection. This call takes an authentication handle, authp, as input, on which various properties are set, including the two attributes to support creating connections to specific shards: OCI_ATTR_SHARDING_KEY for the sharding key and OCI_ATTR_SUPER_SHARDING_KEY for the super sharding key.

About Creating a Sharding Key and Super Sharding Key

Use the OCI descriptor type OCIShardingKey for forming the sharding key and the super sharding key. This descriptor wraps the key value (for a single part key) or multiple values (for a compounded key).

Use the following OCIShardingKeyColumnAdd() call to add all of the columns of the key to form the complete key. 
OCIShardingKeyColumnAdd(OCIShardingKey *shardingKey, 
                     OCIError    *errhp, 
                     void        *col, 
                     ub4          colLen, 
                     ub2          colType, 
                     ub4          mode)
You should perform this call as many times as there are columns in the compound key (or just once for a simple sharding key) in the order in which the key is defined in the database. The columnType parameter indicates the data type of the column.

The following table shows the supported OCI data type values for the columnType parameter and its corresponding C data type.

OCI Data Type C Data Type
SQLT_NUM ub1*
SQLT_CHR OraText*
SQLT_DATE ub1*
SQLT_TIMESTAMP OCIDateTime*
SQLT_RAW ub1*
SQLT_VNU ub1*
SQLY_INT int*

The character key values are assumed to be in the client character set (specified by NLS_LANG or OCIEnvNLSCreate() calls) .

After forming the sharding key and the super sharding key using theOCIShardingKeyColumnAdd() call, the keys can be set on the authentication handle using the sharding key attribute OCI_ATTR_SHARDING_KEY and the super sharding key attribute OCI_ATTR_SUPER_SHARDING_KEY as follows: 

OCIAttrSet(authp, 
           OCI_HTYPE_AUTHINFO, 
           shardKey, 
           sizeof(shardKey),
           OCI_ATTR_SHARDING_KEY,
           errhp);
OCIAttrSet(authp, 
           OCI_HTYPE_AUTHINFO, 
           shardGroupKey, 
           sizeof(shardGroupKey),
           OCI_ATTR_SUPER_SHARDING_KEY, 
           errhp);

When you use this authp parameter in an OCISessionGet() call, it will ensure that you create a connection to the shard containing the data corresponding to the sharding key and super sharding key values that are set.

About Getting the Actual Sharding Key and Super Sharding Key Values

If you want to know the Base64 representation of the sharding key and super sharding key for diagnostic purposes, you can use the attribute OCI_ATTR_SHARDING_KEY_B64, on the OCIShardingKey descriptor. The OCIAttrGet() call takes the OCIShardingKey descriptor as input and returns the text value in base64 format of the sharding key and super sharding key. 

OCIAttrGet((dvoid *) OCIShardingKey,
   (ub4)             OCI_DTYPE_SHARDING_KEY,
   (dvoid *)         &sekyVale, 
   (ub4*)            &skeyValueLen,
                     OCI_ATTR_SHARDING_KEY_B64, 
   (OCIError *)      errhp);

In addition, you can use the OCIShardingKeyReset() call shown as follows for your application to reset and reuse the allocated descriptor for creating a new sharding key and super sharding key. 

sword OCIShardingKeyReset(OCIShardingKey *shardKey, 
                          OCIError       *errhp, 
                          ub4             mode);
This section includes the following topics:

See Also:

About Specifying a Sharding Key and Super Sharding Key for Getting a Connection from an OCI Session Pool

Describes how to specify a sharding key and super sharding key to get a connection from an OCI Session Pool.

By default, the OCISessionGet() call creates a new connection. You can also use this call to get an existing connection from an OCI Session pool. When you use the OCI_ATTR_SHARDING_KEY and OCI_ATTR_SUPER_SHARDING_KEY attribute settings, you can get a connection to the desired shard from an OCI Session pool. In an OCI Session pool, sessions in the pool can represent a variety of shards that are each authenticated by the database credentials passed in by the OCISessionGet() call using the initialized authentication handle authp.

The following example shows how to get a connection to the desired database shard from an OCI Session Pool created with the homogeneous and statement caching modes specified. Even though this example uses a homogeneous pool, you are not restricted to that type of pool.

OCIShardingKey   *shardKey, *shardGroupKey;
  /* Error handling is omitted for brevity. */
  /* Create a homogeneous session pool. */
  checkerr(&status, errhp,
           OCISessionPoolCreate(envhp, errhp,
                 spoolhp,                                     /* session pool handle */
                (OraText **) poolName, poolNameLenp,          /* returned poolname, length */
                (const OraText *) connstr, strlen(connstr),   /* connect string */
                 min, max, increment,                         /* pool size constraints */
                (OraText *) "hr", strlen((char *) "hr"),      /* username */
                (OraText *) apppassword,                      /* password */
                 strlen((char *) apppassword),
                 OCI_SPC_HOMOGENEOUS|OCI_SPC_STMTCACHE));     /* modes */
                
/* Allocate the sharding key and super sharding key descriptors. */
OCIDescriptorAlloc(envhp,(dvoid **)&shardKey,
               OCI_DTYPE_SHARDING_KEY, 0,(dvoid **)0)))
text *name = “KK”;
text *gname = “GOLD”;
int  empid = 150;

/* Add all the columns of the key to form the final sharding key. */
OCIShardingKeyColumnAdd(shardKey,(ub1*)&empid, sizeof(empid), 
                               SQLT_INT, errhp, OCI_DEFAULT);
OCIShardingKeyColumnAdd(shardKey, name, strlen(name), 
                               SQLT_CHAR, errhp, OCI_DEFAULT));

OCIAttrSet(authp, OCI_HTYPE_AUTHINFO,
           shardKey, sizeof(shardKey),
           OCI_ATTR_SHARDING_KEY, errhp);

/* Setting a shard group key. */
/* Create a shard group key, in the same way as for a sharding key. */
OCIDescriptorAlloc(envhp,(dvoid **)&shardGroupKey,
                   OCI_DTYPE_SHARDING_KEY, 0, (dvoid **)0));

/* Add the column of the key to form the final super sharding key. */
OCIShardingKeyColumnAdd(shardGroupKey, gname, strlen(gname),
                     SQLT_CHAR, errhp, OCI_DEFAULT)); 

OCIAttrSet(authp, OCI_HTYPE_AUTHINFO,
           shardGroupKey, sizeof(shardGroupKey),
           OCI_ATTR_SUPER_SHARDING_KEY, errhp));

/* Get the database connection from the OCI Session Pool. */
checkerr(&status,
         errhp, OCISessionGet(envhp, errhp,
                &svchp,                              /* returned database connection */
                authp,                               /* initialized authentication handle */
               (OraText *) poolName, poolNameLen,    /* connect string */
                NULL, 0, NULL, NULL, NULL,           /* session tagging parameters: optional */ 
                OCI_DEFAULT));                       /* modes */

Chunk Migrations and OCISessionGet() in Sharding

During chunk migrations, when a chunk migrates from one shard instance to another, OCI Session Pool can ensure that OCISessionGet() returns a connection to an instance having a writable chunk by implicitly doing retrials during chunk migration. This requires that a couple of properties be set:

  • Setting READONLY_CHUNK_OK to FALSE in the connect string.

  • Setting pool handle attributes: OCI_ATTR_SPOOL_GETMODE and OCI_ATTR_SPOOL_WAIT_TIMEOUT to OCI_SPOOL_ATTRVAL_TIMEDWAIT and a suitable time out value. If the pool is unable to get a connection to a writable instance within the time out period, OCISessionGet() returns an ORA-24495 error.

If the application can use read only chunks, it can set READONLY_CHUNK_OK=true in the connect string. In that case a connection available to the instance with chunks marked read-only also may be dispensed. If the application attempts any database write operation on such a connection, it gets in return suitable errors.

About Specifying a Sharding Key and Super Sharding Key for Getting a Connection from a Custom Pool

Describes features that support specifying a sharding key and super sharding key to get a connection from a custom pool.

This section describes the following features that support applications specifying a sharding key and super sharding key for getting a connection from a custom pool:

  • OCISessionGet() mode OCI_SESSGET_CUSTOM_POOL — Used to explicitly cache the shard topology every time OCI connects to a new shard instance.

  • OCIShardInstancesGet() — Returns instance names for a given sharding key descriptor, super sharding key descriptor, and connection string.

OCI_ATTR_INSTNAME Attribute

If your OCI client application uses custom connection pooling, you must be able to return connections to specific shards. To do this, you must know the shard name to which shard a connection has been made and the sharding key and super sharding key to shard name mapping to enable a look up for a matching connection.

This is possible when you use the attribute OCI_ATTR_INSTNAME on the service context (svchp) for this purpose. This attribute returns the instance name for a given connection. The instance name is unique for the shard instance pointed to by a given connection. Every shard instance has a unique name. The following code sample shows how this attribute is used to get the instance name from a given service context svchp. 

OraText shardName[OCI_INSTNAME_MAXLEN];
ub4 shardNameLen;  
   OCIAttrGet(svchp,
   OCI_HTYPE_SVCCTX,
   shardName,
   (ub4 *) &shardNameLen,
   OCI_ATTR_INSTNAME,
   errhp);

OCISessionGet() mode OCI_SESSGET_CUSTOM_POOL

OCI clients that do custom pooling must use the OCISessionGet() mode OCI_SESSGET_CUSTOM_POOL to explicitly cache the shard topology that contains the shard to chunking mapping information every time OCI connects to a new shard not previously visited. OCI clients that do not use custom pooling do not need to use this mode as this caching is done implicitly, for example, when you use OCI Session Pools.

OCIShardInstancesGet()

OCIShardInstancesGet() returns instance names for a given sharding key descriptor and super sharding key descriptor. This method has the following signature: 

sword OCIShardInstancesGet(
           void            **shTopoCtx,
           OCIError         *errhp,
           const OraText    *connstr,
           ub4               constrl,
           OCIShardingKey   *shardingKey,
           OCIShardingKey   *superShardingKey,
           OCIShardinst   ***shardinsts,
           ub4               numShardInsts,
           ub4               mode);

This call looks up the shard topology cache on the client.

If any connections were made with OCI_SESSGET_CUSTOM_POOL mode, then OCI maintains the shard topology cache locally.

The returned value may be NULL if the shard topology cache does not yet have the mapping either because no connections were made with OCI_SESSGET_CUSTOM_POOL or the connections that were made so far with OCI_SESSGET_CUSTOM_POOL did not connect to a shard that contains the chunk for the sharding key and super sharding key requested. In either case, the custom pool should create a new connection explicitly using OCISessionGet() with the OCI_ATTR_SHARDING_KEY and OCI_ATTR_SUPER_SHARDING_KEY attributes set as applicable with OCI_SESSGET_CUSTOM_POOL mode enabled. Doing this adds information about the new shard to the OCI shard topology cache. Subsequent OCIShardInstancesGet() calls will look for key ranges belonging to chunks located in these shards and return these shard instance names.

Note that a sharding key and super sharding key may refer to multiple shards as a result of the chunk being replicated. When the custom pool has a connection to the desired shard, the custom pool should ensure that the OCI_ATTR_SHARDING_KEY and OCI_ATTR_SUPER_SHARDING_KEY properties are set on the connection before dispensing. Setting these properties ensures that the chunk usage is tracked on the database side and to determine if there have been any chunk splits occurring on the database.

See Also:

OCIShardInstancesGet() for an example that uses custom pooling

About Using the XStream Interface

Since Oracle Database 11g Release 2, Oracle Streams provides enhanced APIs, known as eXtended Streams (XStream) Out and XStream In, to enable high performance, near real-time information-sharing infrastructure between Oracle databases and non-Oracle databases, non-RDBMS Oracle products, file systems, third party software applications, and so on.

XStream is built on top of Streams infrastructure.

This section includes the following topics:

See Also:

OCI XStream Functions

XStream Out

XStream Out allows a remote client to attach to an outbound server (a Streams apply process) and extract row changes in the form of Logical Change Records (LCRs).

To use XStream Out, a capture and an apply process must be created similar to other Streams setup. All data types supported by Oracle Streams including LOB, LONG, and XMLType are supported by XStreams. Such an apply process is called an outbound server. The capture and the outbound server may or may not be on the same database instance. After the capture and the outbound server have started, row changes will be captured and sent to the outbound server. An external client can then connect to this outbound server using OCI. After the connection is established, the client can loop waiting for LCRs from the outbound server. The client can register a client-side callback to be invoked each time an LCR is received. At anytime, the client can detach from the outbound server as needed. Upon restart, the outbound server knows where in the redo stream to start streaming LCRs to the client.

This section includes the following topics:

See Also:

LCR Streams

Describes characteristics of an LCR stream.

  • An LCR stream must be repeatable.

  • An LCR stream must contain a list of assembled and committed transactions.

  • LCRs from one transaction are contiguous. There is no interleaving of transactions in the LCR stream.

  • Each transaction within an LCR stream must have an ordered list of LCRs and a transaction ID.

  • The last LCR in each transaction must be a commit LCR.

  • Each LCR must have a unique position.

  • The position of all LCRs within a single transaction and across transactions must be strictly increasing.

The Processed Low Position and Restart Considerations

If the outbound server or the client aborts abnormally, the connection between the two is automatically broken. The client needs to maintain the processed low position to properly recover after a restart.

The processed low position is a position below which all LCRs have been processed by the client. This position should be maintained by the client while applying each transaction. Periodically this position is sent to the server while the client executes XStream Out APIs. This position indicates to the server that the client has processed all LCRs below or equal to this position; thus, the server can purge redo logs that are no longer needed.

Upon restart, the client must re-attach to the outbound server. During the attach call, the client can notify the outbound server of the last position received by the client. The outbound server then sends LCRs with position greater than this last position. If the client does not specify the last position (that is, a NULL is specified), the outbound server will retrieve the processed low position from its system tables and derive the starting position to mine the redo logs. It will send to the client the LCRs with position greater than this processed low position.

XStream In

To replicate non-Oracle data into Oracle databases use XStream In which allows a remote client to attach to an inbound server (a Streams apply process) and send row and DDL changes in the form of LCRs.

An external client application connects to the inbound server using OCI. After the connection is established, the client application acts as the capture agent for the inbound server by streaming LCRs to it. A client application can attach to only one inbound server per database connection. Each inbound server only allows one client attaching to it.

XStream In uses the following features of Oracle Streams:

  • High performance processing of DML changes using an apply process and, optionally, apply process parallelism.

  • Apply process features such as SQL generation, conflict detection and resolution, error handling, and customized processing with apply handlers.

  • Streaming network transmission of information with minimal network round trips.

XStream In supports all of the data types that are supported by Oracle Streams, including LOBs, LONG, LONG RAW, and XMLType. A client application sends LOB and XMLType data to the inbound server in chunks. Several chunks make up a single column value of LOB or XMLType.

This section includes the following topics:
Processed Low Position and Restart Ability

The processed low position is the position below which the inbound server no longer requires any LCRs.

This position corresponds with the oldest SCN for an Oracle Streams apply process that applies changes captured by a capture process.

The processed low position indicates that the LCRs less than or equal to this position have been processed by the inbound server. If the client re-attaches to the inbound server, it only needs to send LCRs greater than the processed low position because the inbound server discards any LCRs that are less than or equal to the processed low position.

If the client application aborts abnormally, then the connection between the client application and the inbound server is automatically broken. Upon restart, the client application retrieves the processed low position from the inbound server and instructs its capture agent to retrieve changes starting from this processed low position.

To limit the recovery time of a client application using the XStream In interface, the client application can send activity, such as empty transactions, periodically to the inbound server. When there are no LCRs to send to the server, the client can send a row LCR with a commit directive to advance the inbound server's processed low position. This activity acts as an acknowledgement so that the inbound server's processed low position can be advanced. The LCR stream sent to an inbound server must follow the LCR stream properties for XStream Out defined above.

Stream Position

Stream position refers to the position of an LCR in a given LCR stream.

For transactions captured outside Oracle databases the stream position must be encoded in certain format (for example, base-16 encoding) that supports byte comparison. The stream position is key to the total order of transaction stream sent by clients using the XStream In interface.

Security of XStreams

Describes the security characteristics of XStreams.

XStream Out allows regular users to receive LCRs without requiring system level privileges. System level privileges, such as DBA role, are required to configure XStream Out. The user who configures XStream Out can specify a regular user as the connect user who can attach to an outbound server to receive LCRs.

XStream In allows regular users to update tables in its own schema without requiring system level privileges (for example, DBA) to configure XStream In.

XStream cannot assume that the connected user to the inbound or outbound server is trusted.

OCI clients must connect to an Oracle database prior to attaching to an XStream outbound or inbound server created on that database. The connected user must be the same as the connect_user configured for the attached outbound server or the apply_user configured for the attached inbound server; otherwise, an error is raised.

See Also:

Oracle Database XStream Guide for more about configuring Oracle XStreams


Footnote Legend

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.