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 ofReplicationParellelism
cannot exceed half the value ofLogBufParallelism
. -
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 andReplicationParallelism
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 forReplicationParallelism
. For example, ifReplicationParallelism=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, andCacheAWTParallelism
is greater than 1. The value forCacheAWTParallelism
must be greater than or equal to the value set forReplicationParallelism
and less than or equal to 31.If
CacheAWTParallelism
is not specified, thenReplicationParallelism
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 setReplicationParallelism
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
andCacheAWTParallelism
attributes are set, the value set inCacheAWTParallelism
configures the number of threads used for parallel propagation. The setting forCacheAWTParallelism
determines the number of apply threads for parallel propagation and the setting forReplicationParallelism
determines the number of threads for parallel replication. Thus, ifReplicationParallelism
is set to 4 andCacheAWTParallelism
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 |
Set to 0, which enables parallel propagation |
Set to > 16 and <= 32 for multiple tracks. |
Not specified. |
Error is thrown. If |
Set to 0, which enables parallel propagation |
Set to > 1 and <= 32 for multiple tracks. |
Set to >= to |
Set to number specified by |
Set to 0, which enables parallel propagation |
Set to > 1 and <= 32 for multiple tracks. |
Set to < |
Error is thrown at database creation. The |
Set to 0, which enables parallel propagation |
Set to 1 or not specified. Single track. |
Set to > 1 |
Set to number specified by |
Set to 1, which disables parallel propagation. |
N/A |
Set to > 1 |
Error is thrown at database creation, since parallelism is turned off, but |
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 thettCacheCheck
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
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
, andDELETE
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
, orDELETE
) 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
andVARCHAR/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.