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
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 as the user name and the
OraclePwd connection attribute as the Oracle password.
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.
A transaction that contains operations that are replicated with
TWOSAFE cannot have a
PassThrough setting greater than
PassThrough is greater than 0, an error is returned and the
transaction must be rolled back.
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
SELECToperation on cache tables in any dynamic cache group type.
A dynamic load for an
DELETEoperation 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 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 PassThrough=0"
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
PassThrough=1 to specify that:
DDL statements are always executed on TimesTen.
DELETEstatements 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
DELETEstatements, 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 PassThrough=1"
PassThrough=2 specifies that
DELETE statements performed on tables in
read-only cache groups or user managed cache groups with the
table attribute are passed through to the Oracle database.
Passthrough=1 behavior applies for all other operations and cache group types.
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 PassThrough=2"
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 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
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
set passthrough command which applies to the current
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);
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
TT_DynamicPassthrough optimizer hint to notify TimesTen Classic to pass through qualified
SELECT statements to the Oracle database for cache groups created
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
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:
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 /*+ 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.