1. Cache Guide
  2. Methods for Transmitting Changes Between TimesTen and Oracle Databases
  3. Setting a Passthrough Level

Setting a Passthrough Level

When an application issues SQL statements on a TimesTen connection, the SQL statement can be performed in the TimesTen database or passed through to the Oracle database to be performed. Whether the SQL statement is performed in the TimesTen or Oracle database depends on the composition of the statement and the setting of the PassThrough connection attribute.

You can set the PassThrough connection attribute to define which statements are to be performed locally in TimesTen and which are to be redirected to the Oracle database for processing.

The passthrough level can be set at any time and takes effect immediately. The value can be set to 0 through 3. When appropriate within passthrough levels 1 through 3, TimesTen connects to the Oracle database using the current user's credentials. You can use either an Oracle Wallet set up with the cache administration user credentials pointed to by the PWDWallet connection attribute or provide the cache administration user name in the UID connection attribute and the OraclePwd connection attribute as the Oracle password. See Specifying Both Cache Administration Users and Passwords in the Oracle TimesTen In-Memory Database Security Guide.

Passing through update operations to the Oracle database for processing is not recommended when issued on cache tables in an AWT or SWT cache group. See Considerations for Using Passthrough.

Note:

A transaction that contains operations that are replicated with RETURN TWOSAFE cannot have a PassThrough setting greater than 0. If PassThrough is greater than 0, an error is returned and the transaction must be rolled back.

When PassThrough is set to 0, 1, or 2, the following behavior occurs when a dynamic load condition exists:

  • A dynamic load can occur for a SELECT operation on cache tables in any dynamic cache group type.

  • A dynamic load for an INSERT, UPDATE, or DELETE operation can only occur on cached tables with dynamic AWT or SWT cache groups.

See Dynamic Cache Groups.

The following sections describe the different passthrough options:

PassThrough=0

PassThrough=0 is the default setting and specifies that all SQL statements are to be performed in the TimesTen database.

Figure 5-1 shows that Table A is updated on the TimesTen database. Table F cannot be updated because it does not exist in TimesTen.

Figure 5-1 PassThrough=0

Description of Figure 5-1 follows
Description of "Figure 5-1 PassThrough=0"

PassThrough=1

PassThrough=1 specifies that all DDL are run on TimesTen and most SQL statements are run on TimesTen unless the tables referenced only exist on the Oracle database or the SQL statement can only be parsed or understood on the Oracle database.

Set PassThrough=1 to specify that:

  • DDL statements are always executed on TimesTen.

  • INSERT, UPDATE and DELETE statements are run on TimesTen unless they reference one or more tables that do not exist in TimesTen. If they reference one or more tables that do not exist in TimesTen, then these statements are passed through to run on the Oracle database.

  • If SQL statements generate a syntax error in TimesTen, include keywords that do not exist in TimesTen SQL, or if one or more tables referenced within the statement do not exist in TimesTen, then these statements are passed through to run on the Oracle database.

  • If TimesTen cannot parse INSERT, UPDATE or DELETE statements, TimesTen returns an error and the statement is not passed through to the Oracle database.

Figure 5-2 shows that Table A is updated in the TimesTen database, while Table G is updated in the Oracle database because Table G does not exist in the TimesTen database.

Figure 5-2 PassThrough=1

Description of Figure 5-2 follows
Description of "Figure 5-2 PassThrough=1"

PassThrough=2

PassThrough=2 specifies that INSERT, UPDATE and DELETE statements performed on tables in read-only cache groups or user managed cache groups with the READONLY cache table attribute are passed through to the Oracle database.

Passthrough=1 behavior applies for all other operations and cache group types.

Note:

You are responsible in preventing conflicts that may occur if you update the same row in a cache table in TimesTen as another user updates the cached Oracle Database table concurrently.

Figure 5-3 shows that updates to Table A and Table G in a read-only cache group are passed through to the Oracle database.

Figure 5-3 PassThrough=2

Description of Figure 5-3 follows
Description of "Figure 5-3 PassThrough=2"

PassThrough=3

PassThrough=3 specifies that all statements are passed through to the Oracle database for processing.

Figure 5-4 shows that Table A is updated on the Oracle database for a read-only or updatable cache group. A SELECT statement that references Table G is also passed through to the Oracle database.

Figure 5-4 PassThrough=3

Description of Figure 5-4 follows
Description of "Figure 5-4 PassThrough=3"

Considerations for Using Passthrough

Passing through update operations to the Oracle database for processing is not recommended when issued on cache tables in an AWT or SWT cache group.

  • Committed changes on cache tables in an AWT cache group are automatically propagated to the cached Oracle Database tables in asynchronous fashion. However, passing through an update operation to the Oracle database for processing within the same transaction as the update on the cache table in the AWT cache group renders the propagate of the cache table update synchronous, which may have undesired results.

  • Committed changes on cache tables in an SWT cache group can result in self-deadlocks if, within the same transaction, updates on the same tables are passed through to the Oracle database for processing.

A PL/SQL block cannot be passed through to the Oracle database for processing. Also, you cannot pass through to Oracle Database for processing a reference to a stored procedure or function that is defined in the Oracle database but not in the TimesTen database.

For more information about how the PassThrough connection attribute setting determines which statements are performed in the TimesTen database and which are passed through to the Oracle database for processing and under what circumstances, see PassThrough in Oracle TimesTen In-Memory Database Reference.

Note:

The passthrough feature uses OCI to communicate with the Oracle database. The OCI diagnostic framework installs signal handlers that may impact signal handling that you use in your application. You can disable OCI signal handling by setting DIAG_SIGHANDLER_ENABLED=FALSE in the sqlnet.ora file. Refer to Fault Diagnosability in OCI in Oracle Call Interface Programmer's Guide.

Changing the Passthrough Level for a Connection or Transaction

You can override the current passthrough level using the ttIsql utility's set passthrough command which applies to the current transaction.

You can also override the setting for a specific transaction by calling the ttOptSetFlag built-in procedure with the PassThrough flag. The following procedure call sets the passthrough level to 3: 

CALL ttOptSetFlag('PassThrough', 3);

The PassThrough flag setting takes effect when a statement is prepared and it is the setting that is used when the statement is performed even if the setting has changed from the time the statement was prepared to when the statement is performed. After the transaction has been committed or rolled back, the original connection setting takes effect for all subsequently prepared statements.

Automatic Passthrough of Dynamic Load to the Oracle Database

Set the TT_DynamicPassthrough optimizer hint to notify TimesTen Classic to pass through qualified SELECT statements to the Oracle database for cache groups created without a WHERE clause.

When an application issues statements on a TimesTen connection, the statement can be executed in the TimesTen database or passed through to the Oracle database for resolution. If passed through to the Oracle database, the results are returned but the cache instance is not loaded. Whether the statement is executed on the TimesTen or Oracle databases depends on the composition of the statement and the setting of the PassThrough connection attribute.

In TimesTen Classic, for cache groups that are created without a WHERE clause, you can limit the number of rows that are dynamically loaded from the Oracle database into the cache instance. You can set the TT_DynamicPassthrough(N) optimizer hint, where N is the limit to the number of rows allowed to load into the cache instance. If any SELECT statement to the Oracle database would return a result with > N number of rows, then the statement is passed through to the Oracle database and the results are not loaded into the cache instance.

By default, the SELECT statement for a dynamic load of a cache group that qualifies for dynamic load is executed on the TimesTen Classic database and all rows of the cache instances are loaded. In addition, if you provide the optimizer hint and set N=0, then all rows are loaded into the cache instance on the TimesTen Classic database.

This optimizer hint is supported as connection and statement level hints.

Statement level hint:

/*+TT_DynamicPassThrough (1)*/

Connection level hint:

OptimizerHint = TT_DynamicPassThrough (1)

The following example is a statement level optimizer hint requesting a dynamic passthrough of a SELECT statement to the Oracle database if a dynamic load returns 1000 rows or more for the SELECT statement. 

SELECT /*+ TT_DynamicPassThrough(1000)*/ ...

See Setting a Passthrough Level.

See Optimizer Hints in the Oracle TimesTen In-Memory Database SQL Reference and Use Optimizer Hints to Modify the Execution Plan in the Oracle TimesTen In-Memory Database Operations Guide.