Improving AWT Throughput

There are best practice methods to improve throughput for AWT cache groups.

Improving AWT Throughput With Parallel Propagation to the Oracle Database

To improve throughput for an AWT cache group, you can configure multiple threads that act in parallel to propagate and apply transactional changes to the Oracle database. Parallel propagation enforces transactional dependencies and applies changes in AWT cache tables to Oracle Database tables in commit order.

Parallel propagation is supported for AWT cache groups with the following configurations:

AWT cache groups involved in an active standby pair replication scheme
AWT cache groups in a single TimesTen database (without a replication scheme configuration)
AWT cache groups configured with any aging policy

The following data store attributes enable parallel propagation and control the number of threads that operate in parallel to propagate changes from AWT cache tables to the corresponding Oracle Database tables:

ReplicationApplyOrdering enables parallel propagation by default.
ReplicationParallelism defines the number of transmitter threads on the source database and the number of receiver threads on the target database for parallel replication in a replication scheme. This value can be between 2 and 32 when used solely for parallel replication. The default is 1. In addition, the value of ReplicationParellelism cannot exceed half the value of LogBufParallelism.
CacheAWTParallelism, when set, determines the number of threads used in parallel propagation of changes from AWT cache tables to the Oracle Database tables. Set this attribute to a number from 2 to 31. The default is 1.

Parallel propagation for an AWT cache group is configured with one of the following scenarios:

ReplicationApplyOrdering is set to 0 and ReplicationParallelism is greater than 1.

If you do not set CacheAWTParallelism, the number of threads that apply changes to Oracle Database is 2 times the setting for ReplicationParallelism. For example, if ReplicationParallelism=3, the number of threads that apply changes to Oracle Database tables is 6. In this case, ReplicationParallelism can only be set from 2 to 16; otherwise, twice the value would exceed the maximum number of 31 threads for parallel propagation. If the value is set to 16, the maximum number of threads defaults to 31.
ReplicationApplyOrdering is set to 0, ReplicationParallelism is equal to or greater than 1, and CacheAWTParallelism is greater than 1. The value for CacheAWTParallelism must be greater than or equal to the value set for ReplicationParallelism and less than or equal to 31.

If CacheAWTParallelism is not specified, then ReplicationParallelism is used to determine the number of threads that are used for parallel propagation to Oracle Database. However, since this value is doubled for parallel propagation threads, you can only set ReplicationParallelism to a number from 2 to 16. If the value is set to 16, the maximum number of threads defaults to 31.

If both ReplicationParallelism and CacheAWTParallelism attributes are set, the value set in CacheAWTParallelism configures the number of threads used for parallel propagation. The setting for CacheAWTParallelism determines the number of apply threads for parallel propagation and the setting for ReplicationParallelism determines the number of threads for parallel replication. Thus, if ReplicationParallelism is set to 4 and CacheAWTParallelism is set to 6, then the number of threads that apply changes to Oracle Database tables is 6. This enables the number of threads used to be different for parallel replication and parallel propagation to Oracle Database tables.

Note:

See Configuring Parallel Replication in the Oracle TimesTen In-Memory Database Replication Guide. See ReplicationApplyOrdering, ReplicationParallelism, and CacheAWTParallelism in the Oracle TimesTen In-Memory Database Reference.

These data store attributes are interrelated. Table 7-1 shows the result with the combination of the various possible attribute values.

Table 7-1 Results of Parallel Propagation Data Store Attribute Relationships

ReplicationApply Ordering	ReplicationParallelism	CacheAWTParallelism	Number of Parallel Propagation Threads
Set to 0, which enables parallel propagation	Set to > 1 for multiple tracks and <= 16.	Not specified.	Set to twice the value of `ReplicationParallelism`.
Set to 0, which enables parallel propagation	Set to > 16 and <= 32 for multiple tracks.	Not specified.	Error is thrown. If `CacheAWTParallelism` is not set, then 2 times the value set in `ReplicationParallelism` specifies the number of threads. Thus, in this case, `ReplicationParallelism` cannot be greater than 16.
Set to 0, which enables parallel propagation	Set to > 1 and <= 32 for multiple tracks.	Set to >= to `ReplicationParallelism`.	Set to number specified by `CacheAWTParallelism`.
Set to 0, which enables parallel propagation	Set to > 1 and <= 32 for multiple tracks.	Set to < `ReplicationParallelism`.	Error is thrown at database creation. The `CacheAWTParallelism` must be set to a value greater than or equal to `ReplicationParallelism`.
Set to 0, which enables parallel propagation	Set to 1 or not specified. Single track.	Set to > 1	Set to number specified by `CacheAWTParallelism`.
Set to 1, which disables parallel propagation.	N/A	Set to > 1	Error is thrown at database creation, since parallelism is turned off, but `CacheAWTParallelism` is set to a value, expecting parallel propagation to be enabled.

Foreign keys in Oracle Database tables that are to be cached must have indexes created on the foreign keys. Consider these Oracle Database tables:

CREATE TABLE parent (c1 NUMBER PRIMARY KEY NOT NULL);
CREATE TABLE child (c1 NUMBER PRIMARY KEY NOT NULL, 
                    c2 NUMBER REFERENCES parent(c1));
CREATE TABLE grchild (c1 NUMBER PRIMARY KEY NOT NULL, 
                      c2 NUMBER REFERENCES parent(c1), 
                      c3 NUMBER REFERENCES parent(c1));

These indexes must be created:

CREATE INDEX idx_1 ON child(c2);
CREATE INDEX idx_2 ON grchild(c2);
CREATE INDEX idx_3 ON grchild(c3);

The following sections describe restrictions, configuration and checks for parallel propagation:

Table Constraint Restrictions When Using Parallel Propagation for AWT Cache Groups

When you use parallel propagation for AWT cache groups, you must manually enforce data consistency.

Any unique index, unique constraint, or foreign key constraint that exists on columns in the Oracle Database tables that are to be cached should also be created on the AWT cache tables within TimesTen. If you cannot create these constraints on the AWT cache tables and you have configured for parallel propagation, then TimesTen serializes any transactions with DML operations to any table with missing constraints. For example, if a unique index created on a table in the Oracle database cannot be created on the corresponding cached table in TimesTen, all transactions for this table are serialized.

TimesTen automatically checks for missing constraints on the Oracle database that are not cached on TimesTen when you issue any of the following SQL statements:

When you create an AWT cache group with the CREATE ASYNCHRONOUS CACHE GROUP statement
When you create a unique index on an AWT cache table with the CREATE UNIQUE INDEX statement
When you drop a unique index on an AWT cache table with the DROP INDEX statement

Note:

You can manually initiate a check for missing constraints with the ttCacheCheck built-in procedure. For example, TimesTen does not automatically check for missing constraints after a schema change on cached Oracle Database tables. After any schema change on the Oracle database, you should perform an manual check for missing constraints by running ttCacheCheck on the TimesTen database.

See Manually Initiate Check for Missing Constraints for an AWT Cache Group for other conditions where you should manually check for missing constraints.

If the check notes missing constraints on the cached tables, TimesTen issues warnings about each missing constraint.

For the following scenarios, the cached table is marked so that transactions that include DML operations are serialized when propagated to the Oracle database.

Transactions that apply DML operations to AWT cache tables that are missing unique indexes or unique constraints.
Missing foreign key constraints for tables within a single AWT cache group.
- If both the referencing table and the referenced table for the foreign key relationship are in the same AWT cache group and the foreign key relationship is not defined, both tables are marked for transaction serialization.
- If the referencing table is in an AWT cache group and the referenced table is not in an AWT cache group, the table inside the cache group is not marked for transaction serialization. Only a warning is issued to notify the user of the missing constraint.
- If the referenced table is in an AWT cache group and the referencing table is not in an AWT cache group, the table inside the cache group is not marked for transaction serialization. Only a warning is issued to notify the user of the missing constraint.
Missing foreign key constraints between cache groups. When you have tables defined in separate AWT cache groups that are missing a foreign key constraint, both tables are marked for serialized transactions.
If a missing foreign key constraint causes a chain of foreign key constraints to be broken between two AWT cache groups, transactions for all tables within both AWT cache groups are serialized.

Note:

An Oracle Database trigger may introduce an operational dependency of which TimesTen may not be aware. In this case, you should either disable parallel propagation for the AWT cache group or do not cache the table in an AWT cache group on which the trigger is created.

The following is an example of missing constraints when creating an AWT cache group. This example creates two tables in the sales schema in the Oracle database. There is a foreign key relationship between active_customer and the ordertab tables. Because the examples use these tables for parallel propagation, an index is created on the foreign key in the ordertab table.

SQL> CREATE TABLE active_customer
       (custid NUMBER(6) NOT NULL PRIMARY KEY,
        name VARCHAR2(50),
        addr VARCHAR2(100),
        zip VARCHAR2(12),
        region VARCHAR2(12) DEFAULT 'Unknown');
Table created.
 
SQL> CREATE TABLE ordertab
       (orderid NUMBER(10) NOT NULL PRIMARY KEY,
        custid NUMBER(6) NOT NULL);
Table created.
 
SQL> ALTER TABLE ordertab 
      ADD CONSTRAINT cust_fk 
       FOREIGN KEY (custid) REFERENCES active_customer(custid);
Table altered.

SQL> CREATE INDEX order_idx on ordertab (custid);

TimesTen automatically checks for missing constraints when each CREATE CACHE GROUP is issued. In the following example, a single cache group is created that includes the active_customer table. Only a warning is issued since the active_customer is the referenced table and the referencing table, ordertab, is not in any AWT cache group. The active_customer table is not marked for serialized transactions.

CREATE WRITETHROUGH CACHE GROUP update_cust
 FROM sales.active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
 name VARCHAR2(50),
 addr VARCHAR2(100),
 zip VARCHAR2(12));
Warning  5297: The following Oracle foreign key constraints on AWT cache table 
SALES.ACTIVE_CUSTOMER contain cached columns that do not have corresponding 
foreign key constraints on TimesTen: SALES.CUST_FK [Outside of CG].

The following example creates two AWT cache groups on TimesTen, one that includes the active_customer table and the other includes the ordertab table. There is a missing foreign key constraint between the cache groups. Thus, a warning is issued for both tables, but only the ordertab table is marked for serial transactions since it is the referencing table that should contain the foreign key.

CREATE WRITETHROUGH CACHE GROUP update_cust
 FROM sales.active_customer
 (custid NUMBER(6) NOT NULL PRIMARY KEY,
 name VARCHAR2(50),
 addr VARCHAR2(100),
 zip VARCHAR2(12);
Warning  5297: The following Oracle foreign key constraints on AWT cache table 
sales.update_customer contain cached columns that do not have corresponding 
foreign key constraints on TimesTen: ordertab.cust_fk [Outside of CG].

CREATE WRITETHROUGH CACHE GROUP update_orders
 FROM sales.ordertab
 (orderid NUMBER(10) NOT NULL PRIMARY KEY,
  custid NUMBER(6) NOT NULL);
Warning  5295: Propagation will be serialized on AWT cache table 
SALES.ORDERTAB because the following Oracle foreign key constraints on this 
table contain cached columns that do not have corresponding foreign key 
constraints on TimesTen: ORDERTAB.CUST_FK [Across AWT cache groups].

Manually Initiate Check for Missing Constraints for an AWT Cache Group

The ttCacheCheck built-in procedure performs the same check for missing constraints for cached tables on the Oracle database as performed automatically by TimesTen.

The ttCacheCheck provides appropriate messages about missing constraints and the tables marked for serialized propagation. With the ttCacheCheck built-in procedure, you can check for missing constraints for a given cache group or for all cache groups in TimesTen to ensure that all cache groups are not missing constraints.

Note:

Since ttCacheCheck updates system tables to indicate if DML performed against a table should or should not be serialized, you must commit or roll back after the ttCacheCheck built-in completes.

See ttCacheCheck in the Oracle TimesTen In-Memory Database Reference.

You may need to manually call the ttCacheCheck built-in procedure to update the known dependencies after any of the following scenarios:

After dropping a series of AWT cache groups on TimesTen with the DROP CACHE GROUP statement.
After adding or dropping a unique index, unique constraint, or foreign key on an Oracle Database table that is cached in an AWT cache group. If you do not call the ttCacheCheck built-in procedure after adding a constraint, you may receive a run time error on the AWT cache group. After dropping a constraint, TimesTen may serialize transactions even if it is not necessary. Calling the ttCacheCheck built-in procedure verifies whether serialization is necessary.
You can use this built-in procedure to determine why some transactions are being serialized.

Note:

The ttCacheCheck built-in procedure cannot be called while the replication agent is running.

If a DDL statement is being performed on an AWT cache group when ttCacheCheck is called, then ttCacheCheck waits for the statement to complete or until the timeout period is reached.

If you have not defined the CacheAwtParallelism data store attribute to greater than one or the specified cache group is not an AWT cache group, then the ttCacheCheck built-in procedure returns an empty result set.

The following example shows the user manually running the ttCacheCheck built-in procedure to determine if there are any missing constraints for an AWT cache group update_orders that is owned by cacheadmin. A result set is returned that includes the error message. The ordertab table in the update_orders cache group is marked for serially propagated transactions.

Command> call ttCacheCheck(NULL, 'cacheadmin', 'update_orders');

< CACHEADMIN, UPDATE_ORDERS, CACHEADMIN, ORDERTAB, Foreign Key, CACHEADMIN, 
CUST_FK, 1, Transactions updating this table will be serialized to Oracle
because: The missing foreign key connects two AWT cache groups., 
table CACHEADMIN.ORDERTAB constraint CACHEADMIN.CUST_FK foreign key(CUSTID) 
references CACHEADMIN.ACTIVE_CUSTOMER(CUSTID) >
1 row found.

Whenever the cache group schema changes in either the TimesTen or Oracle databases, you can call ttCacheCheck against all AWT cache groups to verify all constraints. The following example shows the user manually running the ttCacheCheck built-in procedure to determine if there are any missing constraints for any AWT cache group in the entire TimesTen database by providing a NULL value for all input parameters. A result set is returned that includes any error messages.

Command> call ttCacheCheck(NULL, NULL, NULL);

< CACHEADMIN, UPDATE_ORDERS, CACHEADMIN, ORDERTAB, Foreign Key, CACHEADMIN, 
CUST_FK, 1, Transactions updating this table will be serialized to Oracle
because: The missing foreign key connects two AWT cache groups., 
table CACHEADMIN.ORDERTAB constraint CACHEADMIN.CUST_FK foreign key(CUSTID) 
references CACHEADMIN.ACTIVE_CUSTOMER(CUSTID) >
1 row found.

Configuring Batch Size for Parallel Propagation for AWT Cache Groups

When using AWT cache groups, TimesTen batches together one or more transactions that are to be applied in parallel to the back-end Oracle database. The CacheParAwtBatchSize parameter configures a threshold value for the number of rows included in a single batch. Once the maximum number of rows is reached, TimesTen includes the rest of the rows in the transaction (TimesTen does not break up any transactions), but does not add any more transactions to the batch.

For example, a user sets the CacheParAwtBatchSize to 200. For the next AWT propagation, there are three transactions, each with 120 rows, that need to be propagated and applied to the Oracle database. TimesTen includes the first two transactions in the first batch for a total of 240 rows. The third transaction is included in a second batch.

The default value for the CacheParAwtBatchSize parameter is 125 rows. The minimum value is 1. See ttDBConfig in the Oracle TimesTen In-Memory Database Reference.

You can retrieve the current value of CacheParAwtBatchSize as follows:

call ttDBConfig('CacheParAwtBatchSize');
< CACHEPARAWTBATCHSIZE, 125 >
1 row found.

You can set the CacheParAwtBatchSize parameter to 200 as follows:

call ttDBConfig('CacheParAwtBatchSize','200');
< CACHEPARAWTBATCHSIZE, 200 >
1 row found

Set the CacheParAwtBatchSize parameter only when advised by Oracle Support, who analyzes the workload and any dependencies in the workload to determine if a different value for CacheParAwtBatchSize could improve performance. Dependencies exist when transactions concurrently change the same data. Oracle Support may advise you to reduce this value if there are too many dependencies in the workload.

Improving AWT Throughput With SQL Array Processing

The CacheAWTMethod connection attribute setting determines whether to use the PL/SQL processing method or SQL array processing method for asynchronous writethrough propagation when applying changes to the Oracle database.

PL/SQL processing method: AWT bundles all pending operations into a single PL/SQL collection that is sent to the Oracle database server to be performed. This processing method is appropriate when there are mixed transactions and network latency between TimesTen and the Oracle database server. It is efficient for most use cases when the workload consists of mixed INSERT, UPDATE, and DELETE statements to the same or different tables. By default, TimesTen uses the PL/SQL processing method (CacheAWTMethod=1).
SQL array processing method: Consider changing CacheAWTMethod to 0 when the changes consist of mostly repeated sequences of the same operation (INSERT, UPDATE, or DELETE) against the same table. For example, SQL array processing is very efficient when a user does an update that affects several rows of a table. Updates are grouped together and sent to the Oracle database in a single batch.

The PL/SQL processing method transparently falls back to SQL array processing mode temporarily when it encounters one of the following:

A statement that is over 32761 bytes in length.
A statement that references a column of type BINARY FLOAT, BINARY DOUBLE and VARCHAR/VARBINARY of length greater than 4000 bytes.

Note:

You can also set this value with the ttDBConfig built-in procedure with the CacheAwtMethod parameter. See ttDBConfig in the Oracle TimesTen In-Memory Database Reference.

See CacheAWTMethod in Oracle TimesTen In-Memory Database Reference.