PK
AAoa, mimetypeapplication/epub+zipPK AA iTunesMetadata.plistT
This chapter lists the SQL*Plus scripts that are installed with Oracle In-Memory Database Cache used to perform various configuration, administrative and monitoring tasks, and provides links to more information including examples. All scripts are installed in the TimesTen_install_dir
/oraclescripts
directory.
cacheCleanUp.sql
: Drops Oracle objects such as change log tables and triggers used to implement autorefresh operations. Script is used when a TimesTen database containing autorefresh cache groups is unavailable because the TimesTen system is offline, or the database was destroyed without dropping its autorefresh cache groups. Run this script as the cache administration user. See "Dropping Oracle objects used by autorefresh cache groups" for more information.
cacheInfo.sql
: Returns change log table information for all Oracle tables cached in an autorefresh cache group, and information about Oracle objects used to track DDL statements issued on cached Oracle tables. Script is used to monitor autorefresh operations on cache groups and DDL statements issued on cached Oracle tables. Run this script as the cache administration user. See "Monitoring autorefresh operations on cache groups" and "Tracking DDL statements issued on cached Oracle tables" for more information.
grantCacheAdminPrivileges.sql
: Grants privileges to the cache administration user that are required to automatically create Oracle objects used to manage the caching of Oracle data when particular cache grid and cache group operations are performed. Run this script as the sys
user. See "Automatically create Oracle objects used to manage caching of Oracle data" for more information.
initCacheAdminSchema.sql
: Grants a minimal set of privileges to the cache administration user and manually creates Oracle objects used to manage the caching of Oracle data. Run this script as the sys
user. See "Manually create Oracle objects used to manage caching of Oracle data" for more information.
initCacheGlobalSchema.sql
: Creates the Oracle timesten user, the Oracle tables owned by the timesten user to store information about cache grids, and the TT_CACHE_ADMIN_ROLE
role that defines privileges on these Oracle tables. Script must be run regardless of whether you are automatically or manually creating Oracle objects used to manage caching of Oracle data. Run this script as the sys
user. See "Create the Oracle users" for more information.
initCacheGridSchema.sql
: Manually creates Oracle tables used to store information about TimesTen databases that are associated with a particular cache grid. Run this script as the sys user. See "Manually create Oracle objects used to manage caching of Oracle data" for more information.
README
: Contains descriptions of the SQL*Plus scripts that are installed with Oracle In-Memory Database Cache.
This chapter describes how to create a cache grid. To illustrate the creation and use of cache groups, the chapter describes how to create an explicitly loaded read-only local cache group, and a dynamic updatable global cache group. The chapter also describes how to populate the cache tables, and how to observe the transfer of updates between the cache tables in the TimesTen database and the cached tables in the Oracle database.
This chapter includes the following topics:
Before you can create a cache grid or a cache group, you must first install TimesTen and then configure the Oracle and TimesTen systems. See Oracle TimesTen In-Memory Database Installation Guide for information about installing TimesTen.
Complete the following tasks:
Before you can use Oracle In-Memory Database Cache, you must create some Oracle users:
A user timesten
owns Oracle tables that store information about cache grids.
One or more schema users own the Oracle tables to be cached in a TimesTen database. These may be existing users or new users.
A cache administration user creates and maintains Oracle objects that store information used to manage cache grids and enforce predefined behaviors of particular cache group types.
Start SQL*Plus on the Oracle system from an operating system shell or command prompt, and connect to the Oracle database instance as the sys
user:
% cd TimesTen_install_dir/oraclescripts % sqlplus sys as sysdba Enter password: password
Use SQL*Plus to create a default tablespace that will be used by both the timesten
user and the cache administration user. This tablespace should only be used to store objects for Oracle In-Memory Database Cache and should not be shared with other applications. Then run the SQL*Plus script TimesTen_install_dir
/oraclescripts/initCacheGlobalSchema.sql
to create the following elements:
The timesten
user
The Oracle tables owned by the timesten
user to store information about cache grids
The TT_CACHE_ADMIN_ROLE
role that defines privileges on these Oracle tables
Pass the default tablespace as an argument to the initCacheGlobalSchema.sql
script. In the following example, the name of the default tablespace is cachetblsp
:
SQL> CREATE TABLESPACE cachetblsp DATAFILE 'datfttuser.dbf' SIZE 100M; SQL> @initCacheGlobalSchema "cachetblsp"
Next use SQL*Plus to create a schema user. Grant this user the minimum set of privileges required to create tables in the Oracle database to be cached in a TimesTen database. In the following example, the schema user is oratt
:
SQL> CREATE USER oratt IDENTIFIED BY oracle; SQL> GRANT CREATE SESSION, RESOURCE TO oratt;
Then use SQL*Plus to perform the following operations:
Create a cache administration user.
Run the SQL*Plus script TimesTen_install_dir
/oraclescripts/grantCacheAdminPrivileges.sql
to grant the cache administration user the minimum set of privileges required to perform cache grid and cache group operations.
Pass the cache administration user name as an argument to the grantCacheAdminPrivileges.sql
script. In the following example, the cache administration user name is cacheuser
and the name of its default tablespace is cachetblsp
:
Note: See the comments in thegrantCacheAdminPrivileges.sql script for the required privileges by the user who executes this script and the privileges that this user grants to the cache administration user. |
SQL> CREATE USER cacheuser IDENTIFIED BY oracle 2 DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp; SQL> @grantCacheAdminPrivileges "cacheuser" SQL> exit
The privileges that the cache administration user requires depend on the types of cache groups you create and the operations that you perform on the cache groups.
See "Create the Oracle users" for more information about the timesten
user, the schema users, and the cache administration user.
SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
On UNIX, in the .odbc.ini
file that resides in your home directory or the TimesTen_install_dir
/info/sys.odbc.ini
file, create a TimesTen DSN cachealone1
and set the following connection attributes:
[cachealone1] DataStore=/users/OracleCache/alone1 PermSize=64 OracleNetServiceName=oracledb DatabaseCharacterSet=AL32UTF8
On Windows, create a TimesTen user DSN or system DSN cachealone1
and set the following connection attributes:
Data Store Path + Name: c:\temp\alone1
Permanent Data Size: 64
Oracle Net Service Name: oracledb
Database Character Set: AL32UTF8
Use the default settings for all the other connection attributes.
See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that is used to cache data from an Oracle database.
See "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen DSNs.
Note: The term "data store" is used interchangeably with "TimesTen database". |
In addition to the Oracle users, you must create some TimesTen users before you can use Oracle In-Memory Database Cache:
A cache manager user performs cache grid and cache group operations. The TimesTen cache manager user must have the same name as an Oracle user that can access the cached Oracle tables. For example, the Oracle user must have privileges to select from and update the cached Oracle tables. The Oracle user can be the cache administration user, a schema user, or some other existing user. The password of the cache manager user can be different than the password of the Oracle user with the same name.
The cache manager user creates and configures the cache grid and creates the cache groups. It may perform operations such as loading or refreshing a cache group although these operations can be performed by any TimesTen user that has sufficient privileges. The cache manager user can also monitor various aspects of the caching environment, such as the grid itself or asynchronous operations that are performed on cache groups such as autorefresh.
One or more cache table users own the cache tables. You must create a TimesTen cache table user with the same name as an Oracle schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database. The password of a cache table user can be different than the password of the Oracle schema user with the same name.
The owner and name of a TimesTen cache table is the same as the owner and name of the corresponding cached Oracle table.
Start the ttIsql
utility on the TimesTen system from an operating system shell or command prompt as the instance administrator, and connect to the cachealone1
DSN to create the TimesTen database that will be used to cache data from an Oracle database:
% ttIsql cachealone1
Use ttIsql
to create a cache manager user. Grant this user the minimum set of privileges required to create a cache grid and cache groups, and perform operations on the cache groups. In the following example, the cache manager user name is cacheuser
, which is the same name as the Oracle cache administration user that was created earlier:
Command> CREATE USER cacheuser IDENTIFIED BY timesten; Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
Then use ttIsql
to create a cache table user. In the following example, the cache table user name is oratt
, which is the same name as the Oracle schema user that was created earlier:
Command> CREATE USER oratt IDENTIFIED BY timesten; Command> exit
The privileges that the cache manager user requires depend on the types of cache groups you create and the operations that you perform on the cache groups. See "Create the TimesTen users" for more information about the cache manager user and the cache table users.
See "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen users and privileges.
Start the ttIsql
utility and connect to the cachealone1
DSN as the cache manager user. In the connection string, specify the cache manager user name in the UID
connection attribute. (In this example, the TimesTen cache manager user name is the same as the Oracle cache administration user name.) Specify the cache manager user's password in the PWD
connection attribute and the cache administration user's password in the OraclePWD
connection attribute within the connection string.
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Use ttIsql
to call the ttCacheUidPwdSet
built-in procedure to set the Oracle cache administration user name and password:
Command> call ttCacheUidPwdSet('cacheuser','oracle');
The cache administration user name and password need to be set only once in a TimesTen database. See "Set the cache administration user name and password" for information about how this setting is used by the TimesTen database.
After you have created the Oracle users, the TimesTen database, and the TimesTen users, and set the Oracle cache administration user name and password in the TimesTen database, you need to create a cache grid to define a framework for TimesTen databases that cache tables from an Oracle database.
As the cache manager user, use the ttIsql
utility to call the ttGridCreate
built-in procedure to create a cache grid myGrid
:
Command> call ttGridCreate('myGrid');
Then use ttIsql
to call the ttGridNameSet
built-in procedure to associate the TimesTen database with the myGrid
cache grid:
Command> call ttGridNameSet('myGrid');
See "Configuring a cache grid" for more information about the contents and functionality of a cache grid.
After you have created a cache grid and associated the TimesTen database with the grid, you are ready to create cache groups. You create a read-only cache group as shown in Figure 2-1. Then you create an asynchronous writethrough (AWT) cache group as shown in Figure 2-2.
Figure 2-1 Single-table read-only cache group
Figure 2-2 Single-table writethrough cache group
Complete the following tasks to create a read-only cache group and an AWT cache group:
Start SQL*Plus and connect to the Oracle database as the schema user:
% sqlplus oratt/oracle
Use SQL*Plus to create a table readtab
as shown in Figure 2-3, and a table writetab
as shown in Figure 2-4:
SQL> CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32)); SQL> CREATE TABLE writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
Figure 2-3 Creating an Oracle table to be cached in a read-only cache group
Figure 2-4 Creating an Oracle table to be cached in an AWT cache group
Then use SQL*Plus to insert some rows into the readtab
and writetab
tables, and commit the changes:
SQL> INSERT INTO readtab VALUES (1, 'Hello'); SQL> INSERT INTO readtab VALUES (2, 'World'); SQL> INSERT INTO writetab VALUES (100, 'TimesTen'); SQL> INSERT INTO writetab VALUES (101, 'IMDB'); SQL> COMMIT;
Next use SQL*Plus to grant the SELECT
privilege on the readtab
table, and the SELECT
, INSERT
, UPDATE
and DELETE
privileges on the writetab
table to the cache administration user:
SQL> GRANT SELECT ON readtab TO cacheuser; SQL> GRANT SELECT ON writetab TO cacheuser; SQL> GRANT INSERT ON writetab TO cacheuser; SQL> GRANT UPDATE ON writetab TO cacheuser; SQL> GRANT DELETE ON writetab TO cacheuser;
The SELECT
privilege on the readtab
table is required to create a read-only cache group that caches this table and to perform autorefresh operations from the cached Oracle table to the TimesTen cache table.
The SELECT
privilege on the writetab
table is required to create an AWT cache group that caches this table. The INSERT
, UPDATE
and DELETE
privileges on the writetab
table are required to perform writethrough operations from the TimesTen cache table to the cached Oracle table.
See "Grant privileges to the Oracle users" for more information about the privileges required for the cache administration user to create and perform operations on a read-only cache group and an AWT cache group.
As the cache manager user, use the ttIsql
utility to call the ttCacheStart
built-in procedure to start the cache agent on the TimesTen database:
Command> call ttCacheStart;
See "Managing the cache agent" for more information about starting the cache agent.
As the cache manager user, use the ttIsql
utility to create a read-only cache group readcache
that caches the Oracle oratt.readtab
table and a dynamic AWT global cache group writecache
that caches the Oracle oratt.writetab
table:
Command> CREATE READONLY CACHE GROUP readcache > AUTOREFRESH INTERVAL 5 SECONDS > FROM oratt.readtab > (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32)); Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH GLOBAL CACHE GROUP writecache > FROM oratt.writetab > (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
The cache groups readcache
and writecache
, and their respective cache tables oratt.readtab
and oratt.writetab
, whose owners and names are identical to the cached Oracle tables, are created in the TimesTen database. Figure 2-5 shows that the writecache
cache group caches the oratt.writetab
table.
Figure 2-5 Creating an asynchronous writethrough cache group
Use the ttIsql
cachegroups
command to view the definition of the readcache
and writecache
cache groups:
Command> cachegroups; Cache Group CACHEUSER.READCACHE: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: Paused Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: ORATT.READTAB Table Type: Read Only Cache Group CACHEUSER.WRITECACHE: Cache Group Type: Asynchronous Writethrough global (Dynamic) Autorefresh: No Aging: LRU on Root Table: ORATT.WRITETAB Table Type: Propagate 2 cache groups found.
See "Read-only cache group" for more information about read-only cache groups.
See "Asynchronous writethrough (AWT) cache group" for more information about AWT cache groups.
See "Dynamic cache groups" for more information about dynamic cache groups.
See "Global cache groups" for more information about global cache groups.
As the cache manager user, use the ttIsql
utility to call the ttRepStart
built-in procedure to start the replication agent on the TimesTen database:
Command> call ttRepStart;
The replication agent propagates committed updates on TimesTen cache tables in AWT cache groups to the cached Oracle tables.
See "Managing the replication agent" for more information about starting the replication agent.
If you are creating a local cache group, you do not need to attach the TimesTen database to the cache grid. Before you can perform operations on a global cache group or on its cache tables, you must attach the TimesTen database to the cache grid that it is associated with.
As the cache manager user, use the ttIsql
utility to call the ttGridAttach
built-in procedure to attach the TimesTen database to the myGrid
cache grid:
Command> call ttGridAttach(1,'alone1','mysys',5001);
In this example, alone1
is a name that is used to uniquely identify the grid member, mysys
is the host name of the TimesTen system, and 5001
is the TCP/IP port for the cache agent.Calling the ttGridAttach
built-in procedure automatically starts the cache agent if it is not already running.
Although the example in this chapter contains only one standalone TimesTen database as the sole grid member, it can be extended to include additional grid members such as active standby pairs and other standalone TimesTen databases. See Chapter 6, "Creating Other Cache Grid Members", for details on how to create and add other members to an existing cache grid, and how data in a global cache group is shared among the grid members.
This section shows how to manually load the read-only cache group. Then it shows the TimesTen cache table being automatically refreshed with committed updates on the cached Oracle table.
Complete the following tasks to perform operations on the read-only cache group:
As the cache manager user, use the ttIsql
utility to load the contents of the Oracle oratt.readtab
table into the TimesTen oratt.readtab
cache table in the readcache
cache group:
Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS; 2 cache instances affected. Command> exit
Figure 2-6 shows that the Oracle data is loaded into the oratt.readtab
cache table.
Figure 2-6 Loading a read-only cache group
Start the ttIsql
utility and connect to the cachealone1
DSN as the instance administrator. Use ttIsql
to grant the SELECT
privilege on the oratt.readtab
cache table to the cache manager user so that this user can issue a SELECT
query on this table.
% ttIsql cachealone1 Command> GRANT SELECT ON oratt.readtab TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cachealone1
DSN as the cache manager user. Use ttIsql
to query the contents of oratt.readtab
cache table.
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> SELECT * FROM oratt.readtab; < 1, Hello > < 2, World > 2 rows found.
See "Loading and refreshing a cache group" for more information about manually loading a cache group.
Use SQL*Plus, as the Oracle schema user, to insert a new row, delete an existing row, and update an existing row in the Oracle readtab
table, and commit the changes:
SQL> INSERT INTO readtab VALUES (3, 'Welcome'); SQL> DELETE FROM readtab WHERE keyval=2; SQL> UPDATE readtab SET str='Hi' WHERE keyval=1; SQL> COMMIT;
After 5 seconds, the oratt.readtab
cache table in the readcache
cache group is automatically refreshed with the committed updates on the cached Oracle oratt.readtab
table as shown in Figure 2-7.
Figure 2-7 Automatically refresh the TimesTen cache table with Oracle updates
As the cache manager user, use the ttIsql
utility to query the contents of the oratt.readtab
cache table after the readcache
cache group has been automatically refreshed with the committed updates on the cached Oracle table:
Command> SELECT * FROM oratt.readtab; < 1, Hi > < 3, Welcome > 2 rows found. Command> exit
See "AUTOREFRESH cache group attribute" for more information about automatically refreshing cache groups.
This section shows how to dynamically load the AWT cache group. Then it shows committed updates on the TimesTen cache table being automatically propagated to the cached Oracle table.
Complete the following tasks to perform operations on the AWT cache group:
Start the ttIsql
utility and connect to the cachealone1
DSN as the instance administrator. Use ttIsql
to grant the <d
code>SELECT privilege on the oratt.writetab
cache table to the cache manager user so that this user can issue a dynamic load SELECT
statement on this table.
% ttIsql cachealone1 Command> GRANT SELECT ON oratt.writetab TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cachealone1
DSN as the cache manager user. Use ttIsql
to load a cache instance on demand from the Oracle oratt.writetab
table to the TimesTen oratt.writetab
cache table in the writecache
cache group.
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> SELECT * FROM oratt.writetab WHERE pk=100; < 100, TimesTen > 1 row found. Command> exit
In a dynamic cache group, a cache instance can be loaded into its cache tables on demand with a dynamic load statement. A SELECT
, UPDATE
, DELETE
or INSERT
statement issued on a TimesTen cache table that uniquely identifies a cache instance results in the cache instance being automatically loaded from the cached Oracle table if the data is not found in the cache table. A dynamically loaded cache instance consists of a single row in the root table of the cache group, and all the related rows in the child tables.
See "Dynamically loading a cache instance" for more information about a dynamic load operation.
Data can also be manually loaded into the cache tables of a dynamic cache group using a LOAD CACHE GROUP
statement.
Start the ttIsql
utility and connect to the cachealone1
DSN as the instance administrator. Use ttIsql
to grant the INSERT
, DELETE
and UPDATE
privileges on the oratt.writetab
cache table to the cache manager user so that this user can perform updates on this table.
% ttIsql cachealone1 Command> GRANT INSERT ON oratt.writetab TO cacheuser; Command> GRANT DELETE ON oratt.writetab TO cacheuser; Command> GRANT UPDATE ON oratt.writetab TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cachealone1
DSN as the cache manager user. Use ttIsql
to insert a new row, delete an existing row, and update an existing row in the oratt.writetab
cache table, and commit the changes.
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> INSERT INTO oratt.writetab VALUES (102, 'Cache'); Command> DELETE FROM oratt.writetab WHERE pk=101; Command> UPDATE oratt.writetab SET attr='Oracle' WHERE pk=100; Command> COMMIT; Command> exit
The committed updates on the oratt.writetab
cache table in the writecache
cache group are automatically propagated to the Oracle oratt.writetab
table as shown in Figure 2-8.
Figure 2-8 Automatically propagate TimesTen cache table updates to Oracle
As the Oracle schema user, use SQL*Plus to query the contents of the writetab
table:
SQL> SELECT * FROM writetab; PK ATTR ---------- ------------------------------- 100 Oracle 102 Cache SQL> exit
Complete the following tasks to restore the TimesTen and Oracle systems to their original state prior to creating a cache grid and cache groups:
Start the ttIsql
utility and connect to the cachealone1
DSN as the cache manager user. Use ttIsql
to call the ttGridDetach
built-in procedure to detach the TimesTen database from the myGrid
cache grid.
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttGridDetach;
See "Detaching a TimesTen database from a cache grid" for information about the effects of detaching a TimesTen database from a cache grid.
As the cache manager user, use the ttIsql
utility to call the ttRepStop
built-in procedure to stop the replication agent on the TimesTen database:
Command> call ttRepStop; Command> exit
See "Managing the replication agent" for more information about stopping the replication agent.
Start the ttIsql
utility and connect to the cachealone1
DSN as the instance administrator. Use ttIsql
to grant the DROP ANY TABLE
privilege to the cache manager user so that this user can drop the underlying cache tables when dropping the cache groups.
% ttIsql cachealone1 Command> GRANT DROP ANY TABLE TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cachealone1
DSN as the cache manager user. Use ttIsql
to drop the readcache
read-only cache group and the writecache
AWT cache group.
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> DROP CACHE GROUP readcache; Command> DROP CACHE GROUP writecache;
The cache groups readcache
and writecache
, and their respective cache tables oratt.readtab
and oratt.writetab
are dropped from the TimesTen database.
See "Dropping a cache group" for more information about dropping cache groups.
As the cache manager user, use the ttIsql
utility to call the ttGridDestroy
built-in procedure to destroy the myGrid
cache grid:
Command> call ttGridDestroy('myGrid');
See "Destroying a cache grid" for more information about destroying a cache grid.
As the cache manager user, use the ttIsql
utility to call the ttCacheStop
built-in procedure to stop the cache agent on the TimesTen database:
Command> call ttCacheStop; Command> exit
See "Managing the cache agent" for more information about stopping the cache agent.
Then use the ttDestroy
utility to connect to the cachealone1
DSN and destroy the TimesTen database:
% ttDestroy cachealone1
Start SQL*Plus and connect to the Oracle database as the sys
user. Use SQL*Plus to drop the timesten
user, the schema user oratt
, and the cache administration user cacheuser
.
% sqlplus sys as sysdba
Enter password: password
SQL> DROP USER timesten CASCADE;
SQL> DROP USER oratt CASCADE;
SQL> DROP USER cacheuser CASCADE;
Specifying CASCADE
in a DROP USER
statement drops all objects such as tables and triggers owned by the user before dropping the user itself.
Next use SQL*Plus to drop the TT_CACHE_ADMIN_ROLE
role:
SQL> DROP ROLE TT_CACHE_ADMIN_ROLE;
Then use SQL*Plus to drop the default tablespace cachetblsp
used by the timesten
user and cache administration user including the contents of the tablespace and its data file:
SQL> DROP TABLESPACE cachetblsp INCLUDING CONTENTS AND DATAFILES; SQL> exit
Table 2-1 Instructions for caching Oracle data in a TimesTen database
Task number | Task |
---|---|
1 |
Create the following users in the Oracle database:
Execute See "Create the Oracle users" for more information about the Oracle users. Grant the cache administration user the privileges required to perform the cache grid operations, create the desired types of cache groups, and perform operations on the cache groups. Privileges are granted by running either the See "Automatically create Oracle objects used to manage caching of Oracle data" or "Manually create Oracle objects used to manage caching of Oracle data" to determine the appropriate script to run. If you are manually creating the Oracle objects, you also need to run the Some privileges cannot be granted until the cached Oracle tables have been created. To grant these privileges, execute See "Grant privileges to the Oracle users" for more information about the privileges that must be granted to the cache administration user to perform particular cache operations. |
2 |
Define a DSN that references the TimesTen database that will be used to cache data from an Oracle database. Set the Set the Then connect to the DSN to create the database if this is a standalone database or will be an active database of an active standby pair. See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that will be used to cache data from an Oracle database. |
3 |
Create the following users in the TimesTen database:
Execute See "Create the TimesTen users" for more information about the TimesTen users. Grant the cache manager user the privileges required to perform the cache grid operations, create the desired types of cache groups, and perform operations on the cache groups. Execute See "Grant privileges to the TimesTen users" for more information about the privileges that must be granted to the cache manager user to perform particular cache operations. |
4 |
Set the cache administration user name and password in the TimesTen database either by calling the See "Set the cache administration user name and password" for more information about setting the cache administration user name and password in a TimesTen database. |
5 |
Create a cache grid by calling the See "Create a cache grid" for more information about creating a cache grid. |
6 |
Associate the TimesTen database with the cache grid by calling the See "Associate a TimesTen database with a cache grid" for more information about associating a TimesTen database with a cache grid. |
7 |
Start the cache agent on the TimesTen database either by calling the See "Managing the cache agent" for more information about starting a cache agent on a TimesTen database. |
8 |
Design the schema for the cache groups by determining which Oracle tables to cache and within those tables, which columns and rows to cache. For multiple table cache groups, determine the relationship between the tables by defining which table is the root table, which tables are direct child tables of the root table, and which tables are the child tables of other child tables. For each cached column, determine the TimesTen data type to which the Oracle data type should be mapped. See "Mappings between Oracle and TimesTen data types" for a list of valid data type mappings between the Oracle and TimesTen databases. For each cache group, determine what type to create (read-only, SWT, AWT, user managed) based on the application requirements and objectives. Also, determine whether each cache group will be explicitly loaded or dynamic, and local or global. Then create the cache groups. See "Creating a cache group" for more information about creating a cache group. |
9 |
If this TimesTen database is intended to be an active database of an active standby pair, create an active standby pair replication scheme in the database. |
10 |
If the TimesTen database contains an active standby pair replication scheme or at least one AWT cache group, start the replication agent on the database either by calling the See "Managing the replication agent" for more information about starting a replication agent on a TimesTen database. |
11 |
If the TimesTen database contains at least one global cache group, attach the TimesTen database to the cache grid that the database associated with by calling the See "Attach a TimesTen database to a cache grid" for more information about attaching a TimesTen database to a cache grid. |
12 |
Manually load the cache tables in explicitly loaded cache groups using See "Loading and refreshing a cache group" for more information about manually loading cache tables in a cache group. See "Dynamically loading a cache instance" for more information about dynamically loading cache tables in a dynamic cache group. |
13 |
Subsequent standalone TimesTen databases can be added as members to an existing cache grid. To create a standalone database, perform task 2. Then perform tasks 3 to 4, 6 to 8, and 10 to 11 to configure the database and add it as a member to the grid. See "Creating and configuring a subsequent standalone TimesTen database" for details about creating another standalone TimesTen database and adding that database to an existing cache grid. |
14 |
An active standby pair can be added as a member to an existing cache grid to achieve high availability by replicating the cache tables to another TimesTen database. To create the active database perform task 2. Then perform tasks 3 to 4, and 6 to 11 to configure the database and add it as a member to the grid. See "Create and configure the active database" for details about creating an active database and adding the database to an existing cache grid. To create the standby database from the active database, perform task 2 to create a DSN for the standby database, and then run a See "Create and configure the standby database" for details about creating a standby database and adding the database to an existing cache grid. To create an optional read-only subscriber database from the standby database, perform task 2 to create a DSN for the subscriber database. Then run a See "Create and configure the read-only subscriber database" for details about creating a read-only subscriber database for an active standby pair. |
This section summarizes the new features of Oracle In-Memory Database Cache release 11.2.2 that are documented in this guide and provides links to more information.
New instructions have been added on how to backup and restore a TimesTen database that contains one or more cache groups. For more details, see "Backing up and restoring a database with cache groups".
A new tool, the Cache Advisor, has been added to help determine whether the performance of an existing Oracle Database application that runs a workload of SQL statements can be improved when the application is used with a TimesTen database. Cache Advisor analyzes application performance and generates recommendations of TimesTen cache group definitions based on the SQL usage in the Oracle Database application. For more information, see Chapter 9, "Using the Cache Advisor".
You can configure parallel propagation of changes in AWT cache tables to the corresponding Oracle tables using either the ReplicationParallelism
or CacheAwtParallelism
data store attributes. See "Configuring parallel propagation to Oracle tables".
If you are using parallel propagation, any unique index, unique constraint, or foreign key constraint on the columns of the cached Oracle tables must also be created on the cached tables in the AWT cache group. See "Configuring parallel propagation to Oracle tables".
You can configure parallel propagation of changes in AWT cache tables to the corresponding Oracle tables. See "Configuring parallel propagation to Oracle tables".
The default value for the cacheAWTMethod
first connection attribute has changed. See "Improving AWT throughput".
You can obtain information about the grid node where a global query is being executed. See "Obtaining information about the location of data in the cache grid".
You can perform a local join when executing a global query. See "Performing global queries with local joins".
User's Guide
11g Release 2 (11.2.2)
E21634-05
September 2012
Oracle In-Memory Database Cache User's Guide, 11g Release 2 (11.2.2)
E21634-05
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
The following sections describe and demonstrate how to use the Oracle In-Memory Database (IMDB) Cache Advisor:
The Oracle IMDB Cache Advisor enables Oracle Database customers to determine whether the performance of an existing Oracle Database application can be improved if the application is used with Oracle IMDB Cache, also referred to as a TimesTen database.
Cache Advisor generates recommendations of TimesTen cache group definitions based on the SQL usage in the Oracle Database application. It does this by evaluating either a captured SQL workload from the application or an existing SQL tuning set. Cache Advisor analyzes this information along with the schema definitions of the Oracle Database objects to determine table and column usage patterns. Cache Advisor also analyzes application performance for specified Oracle IMDB cache sizes, so the cache group recommendations may differ depending on the size of the specified cache. For information on SQL tuning sets, see "Automatic SQL Tuning" in the Oracle Database Performance Tuning Guide.
When evaluating the application workload or SQL tuning set, Cache Advisor recommends either using asynchronous writethrough (AWT) cache groups or read-only cache groups in the TimesTen application. It determines the type of cache groups to use based on the number of SQL statement executions in the Oracle Database application that change data values relative to the number of SQL SELECT
statement executions.
Note: Cache Advisor evaluates DML statements (SELECT , INSERT , UPDATE and DELETE ) for execution porting issues, but evaluates only SELECT statements on both TimesTen and Oracle for the performance comparison.
Cache Advisor evaluates each SQL statement in isolation from any other statement and performs a |
After analyzing the application workload or SQL tuning set, and comparing its performance between Oracle Database and Oracle IMDB Cache, Cache Advisor generates an HTML report that contains performance statistics comparing Oracle Database and Oracle IMDB Cache, definitions of the recommended cache tables in the TimesTen cache group that the application accesses, and the SQL statements that reference the cache tables. The report also shows which statements from the workload or SQL tuning set can be executed in Oracle IMDB Cache with no changes, and which statements require modification before they can be executed. See Chapter 13, "Compatibility Between TimesTen and Oracle" for information about differences that may be encountered.
Cache Advisor also generates a ttIsql
script that can be used to implement the recommended cache group definitions. The user-editable script contains SQL statements such as CREATE CACHE GROUP
, LOAD CACHE GROUP
, CREATE INDEX
, CREATE SYNONYM
, and CREATE VIEW
.
Cache Advisor requires the use of three databases:
A target Oracle database on which the user application runs and where the application schema resides. This is where the SQL workload is captured. The workload executing on this database should be as close to the production database workload as possible. In addition, the Cache Advisor relies on statistics in the Oracle target database to calculate the table sizing in TimesTen. Users should ensure statistics in the target Oracle database are collected and are up to date.
A repository Oracle database where Cache Advisor performs analysis of the workload of SQL statements that are executed on the target Oracle database.
A TimesTen database, also referred to as an Oracle IMDB cache, where Cache Advisor defines and evaluates the recommended cache groups whose cache tables correspond to the tables in the target Oracle database that the application workload accesses
Figure 9-1 Demonstration of the three databases used by the Cache Advisor
Before you can use Cache Advisor, you must first install TimesTen and then configure the Oracle and TimesTen systems.
Note: See the Oracle TimesTen In-Memory Database Installation Guide for information about installing TimesTen. |
The following sections provide an example to show how to configure each database and host in order to execute the Cache Advisor. This example uses one of the Quick Start sample programs as a demonstration for the application that executes the SQL workload.
To set up the Oracle and TimesTen hosts and databases, complete the following tasks:
See "Cache Advisor configuration options and usage guidelines" for details on the configuration options and usage guidelines when installing and configuring each host and database included in the Cache Advisor environment.
The target Oracle database is where the application schema is defined. This is the database that the user application will be accessing. Cache Advisor requires that the version of the target database be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later.
Log onto the system where the target database resides and create a directory where Cache Advisor can export workload and schema information to dump files using Data Pump. This directory can be created by any operating system user on the target system. The directory must be created on a device that is local to the target system and not network mounted.
The owner of the directory is referred to as the target Cache Advisor user. In the following example, the target Cache Advisor user is tgtusr
and the directory is /local/tgtusr/targetdir
.
% mkdir /local/tgtusr/targetdir
Determine the file system that the directory resides on. On Linux systems, this information can be obtained by running the df
operating system command. In this example, the file system that the /local/tgtusr/targetdir
directory resides on is /dev/sda1
.
Cache Advisor must be able to access the contents of the dump files from the repository system to perform its analysis. However, the permissions placed on those files by Data Pump prevent them from being accessed through NFS or transferred to the repository system using ftp
. To access the dump files from the repository system, set an access control list (ACL) on the directory where the files will reside on the target system.
As the operating system root user, enable the setting of ACLs on the file system.
# mount -o remount,acl /dev/sda1
Change the permissions on the directory so that only the target Cache Advisor user can read from and write to it. Then set ACLs on the directory and any files created in the directory to read, write, and execute for the target Cache Advisor user and the operating system user that is running the Oracle Database server on the target system (typically the oracle
user). On Linux systems, ACLs can be set by running the operating system setfacl
command.
% chmod 700 /local/tgtusr/targetdir % setfacl -m u:tgtusr:rwx /local/tgtusr/targetdir % setfacl -m d:u:tgtusr:rwx /local/tgtusr/targetdir % setfacl -m u:oracle:rwx /local/tgtusr/targetdir % setfacl -m d:u:oracle:rwx /local/tgtusr/targetdir
Next, set up the target database for use by Oracle IMDB Cache. Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the target database as the Oracle sys
user. In this example, the net service name of the target database is targetdb
.
% cd TimesTen_install_dir/oraclescripts % sqlplus sys@targetdb as sysdba Enter password: password
Use SQL*Plus to create a default tablespace that will be used by both the Oracle timesten
user and the cache administration user. This tablespace must only be used to store objects for Oracle IMDB Cache and should not be shared with other applications. In this example, the name of the default tablespace is cachetblsp
. For more information about the timesten
user, see "Create users in the Oracle database".
Run the SQL*Plus script TimesTen_install_dir
/oraclescripts/initCacheGlobalSchema.sql
to create the timesten
user and its metadata tables, and the TT_CACHE_ADMIN_ROLE
role that defines privileges to be granted to this user. Pass the default tablespace as an argument to the initCacheGlobalSchema.sql
script.
SQL> CREATE TABLESPACE cachetblsp DATAFILE 'datfttuser.dbf' SIZE 100M; SQL> @initCacheGlobalSchema "cachetblsp"
Next, use SQL*Plus to create a target Oracle user, if this user does not already exist.
Note: Since this example is using the Quick Start sample program, the example createsoratt as the schema owner. |
The target Oracle user owns the Oracle objects that will be accessed by the SQL workload application and are candidates for caching in a TimesTen database. The target Oracle user is the same as the schema user that is described in "Create users in the Oracle database".
Grant this user at least the minimum set of privileges required to create tables in the Oracle database to be cached in a TimesTen database. In this example, the target Oracle user is oratt
.
SQL> CREATE USER oratt IDENTIFIED BY oracle; SQL> GRANT CREATE SESSION, RESOURCE TO oratt;
Then use SQL*Plus to create a cache administration user. Run the SQL*Plus script TimesTen_install_dir
/oraclescripts/grantCacheAdminPrivileges.sql
to grant the cache administration user the minimum set of privileges required to process cache group operations. For information on cache groups, see "Cache groups and cache tables".
Note: The target Oracle user and the cache administration user must be different users. In addition, when you create the repository Cache Advisor user, this user also must be a different user. |
Pass the cache administration user name as an argument to the grantCacheAdminPrivileges.sql
script. In this example, the cache administration user is cacheuser
and the name of its default tablespace is cachetblsp
. For more information about the cache administration user, see "Create users in the Oracle database".
SQL> CREATE USER cacheuser IDENTIFIED BY oracache 2 DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp; SQL> GRANT SELECT ANY TABLE, DELETE ANY TABLE 2 INSERT ANY TABLE, UPDATE ANY TABLE TO cacheuser; SQL> @grantCacheAdminPrivileges "cacheuser"
Run the SQL*Plus script TimesTen_install_dir
/oraclescripts/ttca_sysdbaSetupTarget.sql
to perform the following operations:
Create the TTCA_TARGET_ROLE
role that defines privileges to be granted to the target Oracle user.
Create or specify an Oracle directory object used for file operations into and out of the target database. The ttca_sysdbaSetupTarget.sql
script associates the directory object with the local directory that the target Cache Advisor user created earlier. See "CREATE DIRECTORY" in the Oracle Database SQL Language Reference for information about Oracle directory objects.
After running the ttca_sysdbaSetupTarget.sql
script, exit the SQL*Plus session.
SQL> @ttca_sysdbaSetupTarget ... Please enter a target Oracle database user name to access the target database: oratt ... Press ENTER for a list of existing directory objects on the target database <Enter> <existing directory objects on the target database are shown> Please press ENTER to continue <Enter> ******************************************************************************** *** Please do one of the following: *** 1. Enter an existing directory object on the target Oracle database *** using a directory object name listed above that is not defined *** on a network-mounted path *** 2. Enter a new directory object name to be created ******************************************************************************** ? target_dir ******************************************************************************** *** Creating new directory object TARGET_DIR. *** Enter the directory path on the target system to use in the definition *** of TARGET_DIR ******************************************************************************** ? /local/tgtusr/targetdir ... SQL> exit
The repository Oracle database is where Cache Advisor performs analysis of the SQL workload that is being run on the target Oracle database. Cache Advisor also does report and script generation in the repository database, as well as store tasks. A task is an object that contains information about the workload, performance results, and Cache Advisor options specified by the user. The repository database is a scratch database to be used only by Cache Advisor and should not be shared with other applications.
To use Cache Advisor, you must first install and configure a repository database of Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later. The version of the repository database must also be the same or later than the version of the target Oracle database.
Log onto the system where the repository database resides and create a directory where Cache Advisor will import workload and schema information from dump files using Data Pump. This directory can be created by any operating system user on the repository system. The owner of this directory will be referred to as the repository Cache Advisor user. In this example, the repository Cache Advisor user is reposusr
.
The directory must be created on a device that is local to the repository system and not network mounted. In this example, the directory is /local/reposusr/repositorydir
.
% mkdir /local/reposusr/repositorydir
Determine the file system that the directory resides on. On Linux systems, this information can be obtained by running the df
operating system command. In this example, the file system that the /local/reposusr/repositorydir
directory resides on is /dev/sfa1
.
Cache Advisor must be able to access the contents of the dump files from the repository system. However, the permissions placed on those files by Data Pump prevent them from being accessed through NFS or transferred to the repository system using ftp
. To access the dump files from the repository system, set an access control list (ACL) on the directory where the files will reside on the repository system.
As the operating system root user, enable the setting of ACLs on the file system, if is not already enabled.
# mount -o remount,acl /dev/sfa1
Change the permissions on the directory so that only the repository Cache Advisor user can read from and write to it. Then set ACLs on the directory and any files created in the directory to read, write and execute for the repository Cache Advisor user and the operating system user that is running the Oracle Database server on the repository system (typically the oracle
user). On Linux systems, ACLs can be set by running the operating system setfacl
command.
% chmod 700 /local/reposusr/repositorydir % setfacl -m u:reposusr:rwx /local/reposusr/repositorydir % setfacl -m d:u:reposusr:rwx /local/reposusr/repositorydir % setfacl -m u:oracle:rwx /local/reposusr/repositorydir % setfacl -m d:u:oracle:rwx /local/reposusr/repositorydir
Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the repository database as the Oracle sys
user. In this example, the net service name of the repository database is repositorydb
.
% cd TimesTen_install_dir/oraclescripts % sqlplus sys@repositorydb as sysdba Enter password: password
Run the SQL*Plus script TimesTen_install_dir
/oraclescripts/ttca_sysdbaSetupRepository.sql
to perform the following operations:
Create a user that owns the objects in the repository database used to analyze the SQL workload run on the target Oracle database, and create the ttca_ts
tablespace used to store these objects.
Create or specify an Oracle directory object used for file operations into and out of the repository database. The ttca_sysdbaSetupRepository.sql
script associates the directory object with the local directory that the repository Cache Advisor user created earlier. See "CREATE DIRECTORY" in Oracle Database SQL Language Reference for information about Oracle directory objects.
After running the ttca_sysdbaSetupRepository.sql
script, exit the SQL*Plus session.
SQL> @ttca_sysdbaSetupRepository ... Press ENTER to create the repository Oracle database user with user name TTCACHEADVISOR, or enter an alternative user name for the repository database user: <Enter> Please enter a password for the TTCACHEADVISOR user: ttca Please confirm the password for the TTCACHEADVISOR user: ttca ... Press ENTER for a list of existing directory objects on the repository database <Enter> <existing directory objects on the repository database are shown> Please press ENTER to continue <Enter> ******************************************************************************** *** Please do one of the following: *** 1. Enter an existing directory object on the repository Oracle database *** using a directory object name listed above that is not defined on a *** network-mounted path *** 2. Enter a new directory object name to be created ******************************************************************************** ? repository_dir ******************************************************************************** *** Creating new directory object REPOSITORY_DIR. *** Enter the directory path on the repository system to use in the definition *** of REPOSITORY_DIR ******************************************************************************** ? /local/reposusr/repositorydir ... SQL> exit
The TimesTen database is where Cache Advisor defines and evaluates the recommended cache groups whose cache tables correspond to the tables in the target Oracle database. The TimesTen database is a test database to be used only by Cache Advisor and should not be shared with other applications.
In this example, NFS is the network connection that will be used to transfer files, such as the Data Pump dump files that contain workload and schema information, between the target Oracle database system, repository Oracle database system, and TimesTen database system. On the target system, export the directory that was created by the target Cache Advisor user to NFS clients. Similarly on the repository Oracle system, export the directory that was created by the repository Cache Advisor user to NFS clients. On Linux systems, add an entry to the /etc/exports
file for each directory to be exported to NFS clients. This file must be updated on both the target system and the repository system. Then run the exportfs -a
operating system command as root on both systems for updates to the files to take effect.
As the TimesTen instance administrator user, log onto the system where the TimesTen database will reside and create two directories.
% mkdir /home/ttuser/targetdir % mkdir /home/ttuser/repositorydir
As the operating system root user, network mount the first directory to the directory on the target system that was created by the target Cache Advisor user. Then, network mount the second directory to the directory on the repository system that was created by the repository Cache Advisor user.
# mount -t nfs targethost:/local/tgtusr/targetdir /home/ttuser/targetdir # mount -t nfs reposhost:/local/reposusr/repositorydir /home/ttuser/repositorydir
In the following data source name (DSN) example, the Net service name of the target Oracle database is targetdb
and its database character set is AL32UTF8
. The TimesTen database character set must match the database character set of the target Oracle database. You can determine the database character set of an Oracle database by executing the following query in SQL*Plus as any user:
SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
In the .odbc.ini
file that resides in your home directory or the TimesTen_install_dir
/info/sys.odbc.ini
file, create a TimesTen DSN cacheadv
and set the following connection attributes:
Note: In this example, Cache Advisor sets theCacheGridEnable attribute to 0, so that the user is not required to create a grid. For more details, see "CacheGridEnable" in the Oracle TimesTen In-Memory Database Reference. |
[cacheadv] DataStore=/users/OracleCache/cacheadv PermSize=64 OracleNetServiceName=targetdb DatabaseCharacterSet=AL32UTF8 CacheGridEnable=0
Note: See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that is used to cache data from an Oracle database. See "Managing TimesTen Databases" in the Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen DSNs. |
Set up the TimesTen database for use by Oracle IMDB Cache. Start the ttIsql
utility on the TimesTen system from an operating system shell and connect to the cacheadv
DSN as the TimesTen instance administrator user to create the TimesTen database that will be used to cache data from the target Oracle database.
% ttIsql cacheadv
Use ttIsql
to create a cache manager user. Grant this user at least the minimum set of privileges required to create and perform operations on cache groups. In the following example, the cache manager user name is cacheuser
, which is the same name as the Oracle cache administration user that was created in the target Oracle database.
Command> CREATE USER cacheuser IDENTIFIED BY ttcache; Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
Note: For more information about the cache manager user, see "Create users in the TimesTen database". See "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for more information about TimesTen users and privileges. |
Next, use ttIsql
to call the ttCacheUidPwdSet
built-in procedure to set the Oracle cache administration user name and password. Then exit the ttIsql
session.
Command> call ttCacheUidPwdSet('cacheuser','oracache'); Command> exit
The cache administration user name and password need to be set only once in a TimesTen database. See "Set the cache administration user name and password" for information about how this setting is used in the TimesTen database.
The following sections describe supported configuration options and guidelines for using Oracle In-Memory Database (IMDB) Cache Advisor:
Cache Advisor supports the following configuration options for hosts and databases included in the Cache Advisor environment:
The target Oracle database, which must be Oracle0jϕ Database Enterprise Edition 10g Release 2 (10.2.0.4) or later, the repository Oracle database, which must be Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later, and the TimesTen database all reside on separate host systems. The target Oracle database cannot be a later version than the repository Oracle database. This configuration is preferred, because Cache Advisor operations (such as workload analysis and report generation) have minimal impact on the target database, which can be a production database. It accurately evaluates performance for both the target database and the TimesTen database.
The repository Oracle database, which must be Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later, and the TimesTen database reside on the same host system. The target Oracle database, which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later, resides on a separate host system. The target Oracle database cannot be a later version than the repository Oracle database. This configuration accurately evaluates performance for the target database and can accurate evaluate performance for the TimesTen database.
A single Oracle database, which must be Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later, serves as both the target and repository database. The TimesTen database resides on a separate host system. This configuration is convenient, but does not accurately evaluate performance for the target database. It accurately evaluates performance for the TimesTen database. The target Oracle database should be a test database and not a production database.
The target Oracle database, which must be Oracle Database Enterprise Edition 10g Release 2 (10.2.0.4) or later, the repository Oracle database, which must be Oracle Database Enterprise Edition 11g Release 2 (11.2.0.2) or later, where both reside on the same host system. The target Oracle database cannot be a later version than the repository Oracle database. This configuration can be used to demonstrate Cache Advisor on a laptop computer and identify porting issues, but does not accurately evaluate performance for the target database nor the TimesTen database. The target Oracle database should be a test database and not a production database.
The design of the cache schema recommended by Cache Advisor assumes that the user application will establish a connection to the TimesTen database and a separate connection to the target Oracle database.
Cache Advisor supports most TimesTen DSN attribute settings. However, Cache Advisor does not support the following attribute settings:
Temporary=1
(temporary or non-persistent TimesTen database)
TypeMode=1
(TimesTen data type mode)
DDLCommitBehavior=1
(do not automatically commit DDL statements)
DuplicateBindMode=1
(consider dynamic parameters with the same name as identical)
PLSQL=0
(disable the use of TimesTen PL/SQL)
DynamicLoadEnable=0
(disable dynamic loading of data from Oracle tables into TimesTen cache tables)
This example uses the OCI version of the throughput benchmark (tptbmOCI
) to generate a SQL workload on the target Oracle database.
Build and run the demo program as any operating system user on the TimesTen system. The net service name of the target database is targetdb
. The target Oracle user is oratt
. The password of the oratt
user is oracle
. The application table will be populated with 252 = 625 rows and the maximum number of SQL statements per transaction is 1000.
% cd TimesTen_install_dir/quickstart/sample_code/oci % make tptbmOCI % tptbmOCI -service targetdb -user oratt -key 25 -max 1000 Enter password for oratt : password ... Load the oratt.vpn_users table with 625 rows of data Run 10000 txns with 1 process: 80% read, 20% update, 0% insert, 0% delete
While the tptbmOCI
workload application is running on the target Oracle database, in a separate window run the ttCacheAdvisor
utility on the TimesTen system from an operating system shell as the instance administrator user. Specify the target Oracle database, repository Oracle database, and TimesTen database involved in the evaluation.
% ttCacheAdvisor -oraTarget -oraConn "oratt@targetdb" \ -oraDirObject target_dir -oraDirNfs /home/ttuser/targetdir \ -oraRepository -oraConn "ttcacheadvisor@repositorydb" \ -oraDirObject repository_dir -oraDirNfs /home/ttuser/repositorydir \ -ttConn "DSN=cacheadv;UID=cacheuser" \ -report /home/ttuser/CAreport -task sampletask -captureCursorCache 10 -evalSqlPerf Enter password for Oracle user oratt@targetdb: password 31.16:21:03 Info: beginning Oracle batch operation checkAuthorization on oratt@targetdb 31.16:21:03 Info: Oracle batch operation checkAuthorization completed Enter password for Oracle user ttcacheadvisor@repositorydb: password 31.16:21:05 Info: beginning Oracle batch operation checkAuthorization on ttcacheadvisor@repositorydb 31.16:21:06 Info: Oracle batch operation checkAuthorization completed 31.16:21:06 Info: beginning Oracle batch operation checkOraUser on oratt@targetdb 31.16:21:06 Info: Oracle batch operation checkOraUser completed 31.16:21:06 Info: beginning TimesTen batch operation checkUserExists on "dsn=cacheadv;uid=cacheuser" 31.16:21:07 Info: TimesTen batch operation checkUserExists completed Enter password for TimesTen user cacheuser (dsn=cacheadv): password 31.16:21:10 Info: beginning TimesTen batch operation checkTTuserAuthorization on "dsn=cacheadv;uid=cacheuser" 31.16:21:11 Info: TimesTen batch operation checkTTuserAuthorization completed Enter password for Oracle user cacheuser@targetdb: password 31.16:21:14 Info: beginning Oracle batch operation checkTToraclepwdAttribute on cacheuser@targetdb 31.16:21:14 Info: Oracle batch operation checkTToraclepwdAttribute completed 31.16:21:14 Info: beginning Oracle batch operation verifyTargetConfig on oratt@targetdb 31.16:21:25 Info: Oracle batch operation verifyTargetConfig completed ...
The previous example used the ttCacheAdvisor
utility options as follows:
The -oraTarget
option identifies the target Oracle database. The options following this option specifies the following for the target Oracle database:
The -oraConn
option specifies the target Oracle user and the Net service name of the target database in the connection string.
The -oraDirObject
option specifies the Oracle directory object that corresponds to the directory where Cache Advisor will export workload and schema information to dump files.
The -oraDirNfs
option specifies the directory on the TimesTen system that is network mounted to the directory on the target system where Cache Advisor will export workload and schema information to dump files.
The -oraRepository
option identifies the repository Oracle database. The options following this option specifies the following for the repository Oracle database:
The -oraConn
option specifies the user that owns the objects in the repository database used to analyze the SQL workload run on the target Oracle database and the net service name of the repository database in the connection string.
The -oraDirObject
option specifies the Oracle directory object that corresponds to the directory where Cache Advisor will import workload and schema information from dump files.
The -oraDirNfs
option specifies the directory on the TimesTen system that is network mounted to the directory on the repository system where Cache Advisor will import workload and schema information from dump files.
The -ttConn
option identifies the TimesTen database. Specify the DSN and the cache manager user in the connection string.
The -report
option overrides the default directory location where the report files reside.
The -task
option overrides the default task name.
The -captureCursorCache
option specifies that the ttCacheAdvisor
utility analyze the SQL workload running on the target database for 10 minutes.
The -evalSqlPerf
option is specified to generate a performance comparison between the workload run on the target Oracle database and on the TimesTen database.
If the passwords are not specified in the connection strings for each database, the ttCacheAdvisor
utility will prompt for the passwords of each user connecting to the TimesTen and Oracle databases used in the Cache Advisor evaluation.
For this example, the following user passwords are requested:
The password for the target Oracle database user. In this example, the password of oratt@targetdb
is oracle
.
The password for the user that owns the objects in the repository Oracle database used to analyze the SQL workload run on the target Oracle database. In this example, the password of ttcacheadvisor@repositorydb
is ttca
.
The password for the TimesTen cache manager user. In this example, the password of cacheuser
is ttcache
.
The password of the Oracle cache administration user. In this example, the password of cacheuser@targetdb
is oracache
. This password is requested because the -evalSqlPerf
option is specified to generate a performance comparison between the workload run on the target Oracle database and on the TimesTen database.
The ttCacheAdvisor
utility generates periodic status messages as it analyzes the application workload running on the target database.
When ttCacheAdvisor
completes, it creates an HTML report showing performance statistics as well as information such as which SQL statements from the workload can and cannot be executed in TimesTen. By default, the files that constitute the report reside in the task-name directory where the utility was invoked. In this example, the directory is specified with the -report
option. To view the report, open the index.htm
file in the report files directory from a web browser. The task name, by default, is user-name_host-name_timestamp. In this example, the task name is overridden with the -task
option.
The ttCacheAdvisor
utility also generates an implementation script file named ttCacheAdvisor_
task-name_timestamp.sql
in the directory where the utility was invoked. This script can be run with the ttIsql
utility to create objects in the TimesTen database used to implement the caching of the Oracle objects that were accessed by the application.
% ttIsql -f ttCacheAdvisor_sampletask_20120531164101.sql "DSN=cacheadv;UID=cacheuser;OraclePWD=oracache"
For more information about the report and implementation script, see "Viewing the Cache Advisor reports".
For information about the syntax for ttCacheAdvisor
, see "ttCacheAdvisor" in the Oracle TimesTen In-Memory Database Reference.
This section provides examples of the report pages generated by the Cache Advisor. The report can be viewed using the following Web browsers:
Firefox 3.6 or later
Chrome 7 or later
Safari 4 or later
To view the report, open the index.htm
file in the report files directory from a Web browser.
If the -evalSqlPerf
option was specified when the ttCacheAdvisor
utility was executed, the report shows the average response time for the SQL SELECT
statements that were executed in the target Oracle database. It also shows the average response time for these statements when executed in the TimesTen database with the user-specified cache size. The complete IMDB cache size is the minimum TimesTen database size required to cache all of the objects that were accessed by the SQL workload and can be supported by TimesTen.
Figure 9-2 Cache Advisor report home page
Figure 9-3 Cache Advisor findings and recommendations
You can view the SQL statements that were executed in the workload by clicking the link under the SQL Statements column on the home page that indicates the number of statements in the workload. In this case, click the link of the first 2 where it says "2 of 2".
Figure 9-4 Viewing the number of SQL statements executed in the workload
You can click an individual SQL statement to see the response time and other statistics for that statement. In this example, when you click the link of the second statement, you will see the following information about the SELECT
statement:
Figure 9-5 Information for a specific SQL statement executed during Cache Advisor evaluation
You can click the name of the cache group to see the definition of the cache group and its cache tables, as well as the SQL statements that referenced the cache group. In this example, the following report page appears when you click the CG1_USERSPECCACHE
link:
Figure 9-7 SQL statements used for cache group
From the home page, you can access the text of the implementation script by clicking the "Configure an IMDB Cache for your application" link. Then, from the next page, click the "Implementation Script" link.
The following shows an example of an implementation script:
Figure 9-9 Continuation of implementation script example
The name of the script is ttCacheAdvisor_
task-name_
timestamp.sql
and it resides in the directory where the ttCacheAdvisor
utility was invoked. This script can be run with the ttIsql
utility to create objects in the TimesTen database used to implement the caching of the Oracle objects that were accessed by the application.
% ttIsql -f ttCacheAdvisor_sampletask_20120531164101.sql "DSN=cacheadv;UID=cacheuser;OraclePWD=oracache"
You can obtain database and system information about the target Oracle database (Workload Collection), repository Oracle database and TimesTen database (Client) by clicking the "Click here for information about the configuration that was used to generate this report" link from the home page.
Figure 9-11 Repository and client configuration information
Complete the following tasks to restore the Oracle and TimesTen systems to their original state after you have finished evaluating the application workload that was run on the target Oracle database:
Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the target Oracle database as the sys
user. Then, use SQL*Plus as follows to clean up the target Oracle database and its host system:
Drop the timesten
user, the oratt
target Oracle user (if you created this user because it did not exist prior to configuring the target database), and the cacheuser
cache administration user.
% sqlplus sys@targetdb as sysdba
Enter password: password
SQL> DROP USER timesten CASCADE;
SQL> DROP USER oratt CASCADE;
SQL> DROP USER cacheuser CASCADE;
Note: SpecifyingCASCADE in a DROP USER statement drops all objects, such as tables owned by the user, before dropping the user itself. |
Drop the TT_CACHE_ADMIN_ROLE
role, the TTCA_TARGET_ROLE
role, and the target_dir
directory object.
SQL> DROP ROLE TT_CACHE_ADMIN_ROLE; SQL> DROP ROLE TTCA_TARGET_ROLE; SQL> DROP DIRECTORY target_dir; SQL> exit
Drop the cachetblsp
default tablespace used by the timesten
user and cache administration user, including the contents of the tablespace and its data file. Exit the SQL*Plus session.
SQL> DROP TABLESPACE cachetblsp INCLUDING CONTENTS AND DATAFILES; SQL> exit
Note: The above steps do not drop the schemas that were created for the workload by the Cache Advisor. You can keep the schemas for use by another application workload, if they use the same schemas, or if you want to re-execute the same workload after re-creating the user and tablespace. If not, you can either manually drop the schemas created or, if the target database is a test database, destroy the database. |
Start SQL*Plus from an operating system shell on the TimesTen database system and connect to the repository Oracle database as the sys
user. Use SQL*Plus as follows to clean up the repository Oracle database and its host system:
Drop the ttcacheadvisor
user that owns the objects in the repository database used to analyze the SQL workload run on the target Oracle database.
% sqlplus sys@repositorydb as sysdba
Enter password: password
SQL> DROP USER ttcacheadvisor CASCADE;
Drop the sys.wri$_adv_sqla_tt_tabcols
table and the repository_dir
directory object.
SQL> DROP TABLE sys.wri$_adv_sqla_tt_tabcols; SQL> DROP DIRECTORY repository_dir;
Drop the ttca_ts
tablespace used by the ttCacheAdvisor
user, including the contents of the tablespace and its data file. Exit the SQL*Plus session.
SQL> DROP TABLESPACE ttca_ts INCLUDING CONTENTS AND DATAFILES; SQL> exit
Start the ttIsql
utility and connect to the cacheadv
DSN as the TimesTen instance administrator user. Perform the following to clean up the TimesTen database:
Use ttIsql
to grant the DROP ANY TABLE
privilege to the cache manager user so that this user can drop the underlying cache tables when dropping the cache groups. Then, exit this ttIsql
session.
% ttIsql cacheadv Command> GRANT DROP ANY TABLE TO cacheuser; Command> exit
Start the ttIsql
utility and connect to the cacheadv
DSN as the cache manager user. The password of the TimesTen cache manager user cacheuser
is ttcache
. Use ttIsql
to call the ttRepStop
built-in procedure to stop the replication agent on the TimesTen database. Drop the cg1_userspeccache
AWT cache group. Call the ttCacheStop
built-in procedure to stop the cache agent on the TimesTen database. Exit this ttIsql
session.
% ttIsql "DSN=cacheadv;UID=cacheuser;OraclePWD=oracache"
Enter password for 'cacheuser': password
Command> call ttRepStop;
Command> DROP CACHE GROUP cg1_userspeccache;
Command> call ttCacheStop;
Command> exit
Use the ttDestroy
utility to connect to the cacheadv
DSN and destroy the TimesTen database.
% ttDestroy cacheadv
The following sections describe the different types of cache groups and how to define them:
A cache group defines the Oracle data to cache in the TimesTen database. When you create a cache group, cache tables are created in the TimesTen database that correspond to the Oracle tables being cached.
A separate table definition must be specified in the cache group definition for each Oracle table that is being cached. The owner, table name, and cached column names of a TimesTen cache table must match the owner, table name, and column names of the corresponding cached Oracle table. The cache table can contain all or a subset of the columns and rows of the cached Oracle table. Each TimesTen cache table must have a primary key.
Before you define the cache group table, create the Oracle tables that are to be cached. Each table should be either:
An Oracle table with a primary key on non-nullable columns. The TimesTen cache table primary key must be defined on the full Oracle table primary key. For example, if the cached Oracle table has a composite primary key on columns c1
, c2
and c3
, the TimesTen cache table must also have a composite primary key on columns c1
, c2
and c3
.
The following example shows how a cache group is created from an Oracle table with a composite primary key. Create the job_history
table with a composite key on the Oracle database:
SQL> CREATE TABLE job_history (employee_id NUMBER(6) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, job_id VARCHAR2(10) NOT NULL, department_id NUMBER(4), PRIMARY KEY(employee_id, start_date)); Table created.
Create the cache group on TimesTen with all columns of the composite primary key:
Command> CREATE WRITETHROUGH CACHE GROUP job_hist_cg > FROM oratt.job_history > (employee_id NUMBER(6) NOT NULL, > start_date DATE NOT NULL, > end_date DATE NOT NULL, > job_id VARCHAR2(10) NOT NULL, > department_id NUMBER(4), > PRIMARY KEY(employee_id, start_date));
An Oracle table with non-nullable columns, which has a unique index defined on one or more of the non-nullable columns in this table. The TimesTen cache table primary key must be defined on all of the columns in the unique index. For example, if the unique index for the Oracle table is made up of multiple columns c1
, c2
and c3
, the TimesTen cache table must have a composite primary key on columns c1
, c2
and c3
.
The following examples create Oracle unique indexes defined on tables with non-nullable columns.
SQL> CREATE TABLE regions( region_id NUMBER NOT NULL, region_name VARCHAR2(25)); Table created. SQL> CREATE UNIQUE INDEX region_idx ON regions(region_id); Index created. SQL> CREATE TABLE sales( prod_id INT NOT NULL, cust_id INT NOT NULL, quantity_sold INT NOT NULL, time_id DATE NOT NULL); Table created. SQL> CREATE UNIQUE INDEX sales_index ON sales(prod_id, cust_id); Index created.
After creation of the Oracle table and unique index, you can create cache groups on TimesTen for these tables using the unique index columns as the primary key definition as shown below:
Command> CREATE WRITETHROUGH CACHE GROUP region_cg > FROM oratt.regions > (region_id NUMBER NOT NULL PRIMARY KEY, > region_name VARCHAR2(25)); Command> CREATE WRITETHROUGH CACHE GROUP sales_cg > FROM oratt.sales > (prod_id INT NOT NULL, cust_id INT NOT NULL, > quantity_sold INT NOT NULL, time_id DATE NOT NULL, > PRIMARY KEY(prod_id, cust_id));
A TimesTen database can contain multiple cache groups. A cache group can contain one or more cache tables. An Oracle table cannot be cached in more than one cache group within the same TimesTen database.
Creating indexes on a cache table in TimesTen can help speed up particular queries issued on the table in the same fashion as on a TimesTen regular table. You can create non-unique indexes on a TimesTen cache table. Do not create unique indexes on a cache table that do not match any unique index on the cached Oracle table. Otherwise, it can cause unique constraint failures in the cache table that do not occur in the cached Oracle table, and result in these tables in the two databases being no longer synchronized with each other when autorefresh operations are performed.
The simplest cache group is one that caches a single Oracle table. In a single-table cache group, there is a root table but no child tables.
Figure 4-1 shows a single-table cache group target_customers
that caches the customer
table.
Figure 4-1 Cache group with a single table
A multiple-table cache group is one that defines a root table and one or more child tables. A cache group can only contain one root table. Each child table must reference the primary key or a unique index of the root table or of another child table in the cache group using a foreign key constraint. Although tables in a multiple-table cache group must be related to each other in the TimesTen database through foreign key constraints, it is not required that the tables be related to each other in the Oracle database. The root table does not reference any table in the cache group with a foreign key constraint.
Figure 4-2 shows a multiple-table cache group customer_orders
that caches the customer
, orders
and order_item
tables. Each parent table in the customer_orders
cache group has a primary key that is referenced by a child table through a foreign key constraint. The customer
table is the root table of the cache group because it does not reference any table in the cache group with a foreign key constraint. The primary key of the root table is considered the primary key of the cache group. The orders
table is a child table of the customer root table. The order_item
table is a child table of the orders
child table.
Figure 4-2 Cache group with multiple tables
The table hierarchy in a multiple-table cache group can designate child tables to be parents of other child tables. A child table cannot reference more than one parent table. However, a parent table can be referenced by more than one child table.
Figure 4-3 shows an improper cache table hierarchy. Neither the customer nor the product table references a table in the cache group with a foreign key constraint. This results in the cache group having two root tables which is invalid.
Figure 4-3 Problem: Cache group contains two root tables
To resolve this problem and cache all the tables, create a cache group which contains the customer
, orders
, and order_item
tables, and a second cache group which contains the product
and the inventory
tables as shown in Figure 4-4.
Figure 4-4 Solution: Create two cache groups
You create cache groups by using a CREATE CACHE GROUP
SQL statement or by using Oracle SQL Developer, a graphical tool. For more information about SQL Developer, see Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.
Cache groups are identified as either system managed or user managed. System managed cache groups enforce specific behaviors, while the behavior of a user managed cache group can be customized. System managed cache group types include:
See "User managed cache group" for information about user managed cache groups.
The following topics also apply to creating a cache group:
Cache groups must be created by and are owned by the cache manager user.
You cannot cache Oracle data in a temporary database.
A read-only cache group enforces a caching behavior where the TimesTen cache tables cannot be updated directly, and committed updates on the cached Oracle tables are automatically refreshed to the cache tables as shown in Figure 4-5.
If the TimesTen database is unavailable for whatever reason, you can still update the Oracle tables that are cached in a read-only cache group. When the TimesTen database returns to operation, updates that were committed on the cached Oracle tables while the TimesTen database was unavailable are automatically refreshed to the TimesTen cache tables.
The following are the definitions of the Oracle tables that will be cached in the read-only cache groups that are defined in Example 4-1, Example 4-10, Example 4-11, Example 4-19 and Example 4-20. The Oracle tables are owned by the schema user oratt
. The oratt
user must be granted the CREATE SESSION
and RESOURCE
privileges before it can create tables.
CREATE TABLE customer (cust_num NUMBER(6) NOT NULL PRIMARY KEY, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100)); CREATE TABLE orders (ord_num NUMBER(10) NOT NULL PRIMARY KEY, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL);
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT
privilege on the oratt.customer
and oratt.orders
tables in order for the cache manager user to create a read-only cache group that caches these tables, and for autorefresh operations to occur from the cached Oracle tables to the TimesTen cache tables.
Use the CREATE READONLY CACHE GROUP
statement to create a read-only cache group.
Example 4-1 Creating a read-only cache group
The following statement creates a read-only cache group customer_orders
that caches the tables oratt.customer
(root table) and oratt.orders
(child table):
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)), oratt.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
The cache tables in a read-only cache group cannot be updated directly. However, you can set the passthrough level to 2 to allow committed update operations issued on a TimesTen cache table to be passed through and processed on the cached Oracle table, and then have the updates be automatically refreshed into the cache table. See "Setting a passthrough level".
The effects of a passed through statement on cache tables in a read-only cache group do not occur in the transaction in which the update operation was issued. Instead, they are seen after the passed through update operation has been committed on the Oracle database and the next automatic refresh of the cache group has occurred. The Oracle user with the same name as the TimesTen cache manager user must be granted the INSERT
, UPDATE
and DELETE
privileges on the Oracle tables that are cached in the read-only cache group in order for the passed through update operations to be processed on the cached Oracle tables.
If you manually created the Oracle objects used to enforce the predefined behaviors of an autorefresh cache group as described in "Manually create Oracle objects used to manage caching of Oracle data", you need to set the autorefresh state to OFF
when creating the cache group.
Then you need to run the ttIsql
utility's cachesqlget
command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle table that is cached in the read-only cache group. See "Manually creating Oracle objects for autorefresh cache groups" for information about how to create these objects.
The following restrictions apply when using a read-only cache group:
The cache tables cannot be updated directly.
Only the ON DELETE CASCADE
and UNIQUE HASH ON
cache table attributes can be used in the cache table definitions.
See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE
cache table attribute.
See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON
cache table attribute.
A FLUSH CACHE GROUP
statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement.
A TRUNCATE TABLE
statement issued on a cached Oracle table is not automatically refreshed to the TimesTen cache table.
A LOAD CACHE GROUP
statement can only be issued on the cache group if the cache tables are empty, unless the cache group is dynamic.
See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP
statement.
See "Dynamic cache groups" for more information about dynamic cache groups.
The autorefresh state must be PAUSED
before you can issue a LOAD CACHE GROUP
statement on the cache group, unless the cache group is dynamic, in which case the autorefresh state must be PAUSED
or ON
. The LOAD CACHE GROUP
statement cannot contain a WHERE
clause, unless the cache group is dynamic, in which case the WHERE
clause must be followed by a COMMIT EVERY
n
ROWS
clause.
See "AUTOREFRESH cache group attribute" for more information about autorefresh states.
See "Using a WHERE clause" for more information about WHERE
clauses in cache group definitions and operations.
The autorefresh state must be PAUSED
before you can issue a REFRESH CACHE GROUP
statement on the cache group. The REFRESH CACHE GROUP
statement cannot contain a WHERE
clause.
See "Loading and refreshing a cache group" for more information about the REFRESH CACHE GROUP
statement.
All tables and columns referenced in WHERE
clauses when creating, loading or unloading the cache group must be fully qualified. For example:
user_name
.
table_name
and user_name
.
table_name
.
column_name
Least recently used (LRU) aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.
See "LRU aging" for more information about LRU aging.
Read-only cache groups cannot cache Oracle views or materialized views.
An asynchronous writethrough (AWT) cache group enforces a caching behavior where committed updates on the TimesTen cache tables are automatically and asynchronously propagated to the cached Oracle tables as shown in Figure 4-6.
Figure 4-6 Asynchronous writethrough cache group
The transaction commit on the TimesTen database occurs asynchronously from the commit on the Oracle database. This enables an application to continue issuing transactions on the TimesTen database without waiting for the Oracle transaction to complete. However, your application cannot ensure when the transactions are completed on the Oracle database.
Execution of the UNLOAD CACHE GROUP
statement for an AWT cache group waits until updates on the rows have been propagated to the Oracle database.
You can update cache tables in an AWT cache group even if the Oracle database is unavailable. When the Oracle database returns to operation, updates that were committed on the cache tables while the Oracle database was unavailable are automatically propagated to the cached Oracle tables.
The following is the definition of the Oracle table that will be cached in the AWT cache groups that are defined in Example 4-2, Example 4-12 and Example 4-14. The Oracle table is owned by the schema user oratt
. The oratt
user must be granted the CREATE SESSION
and RESOURCE
privileges before it can create tables.
CREATE TABLE customer (cust_num NUMBER(6) NOT NULL PRIMARY KEY, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100));
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT
privilege on the oratt.customer table in order for the cache manager user to create an AWT cache group that caches this table. The Oracle cache administration user must be granted the INSERT
, UPDATE
and DELETE
privileges on the oratt.customer
table for asynchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
Use the CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP
statement to create an AWT cache group.
Example 4-2 Creating an AWT cache group
The following statement creates an asynchronous writethrough cache group new_customers
that caches the oratt.customer
table:
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP new_customers FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num));
The following sections describe configuration, behavior, and management for AWT cache groups:
Performing asynchronous writethrough operations requires that the replication agent be running on the TimesTen database that contains AWT cache groups. Executing a CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP
statement creates a replication scheme that enables committed updates on the TimesTen cache tables to be asynchronously propagated to the cached Oracle tables.
After you have created AWT cache groups, start the replication agent on the TimesTen database.
Example 4-3 Starting the replication agent
The replication agent can be manually started programmatically by calling the ttRepStart
built-in procedure as the cache manager user:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttRepStart;
It can also be started from a command line by running a ttAdmin -repStart
utility command as a TimesTen external user with the CACHE_MANAGER
privilege:
% ttAdmin -repStart cachealone1
The replication agent does not start unless there is at least one AWT cache group or replication scheme in the TimesTen database.
If the replication agent is running, it must be stopped before you can issue another CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP
statement or a DROP CACHE GROUP
statement on an AWT cache group.
Example 4-4 Stopping the replication agent
The replication agent can be manually stopped programmatically by calling the ttRepStop
built-in procedure as the cache manager user:
Command> call ttRepStop;
It can also be stopped from a command line by running a ttAdmin -repStop
utility command as a TimesTen external user with the CACHE_MANAGER
privilege:
% ttAdmin -repStop cachealone1
You can set a replication agent start policy to determine how and when the replication agent process starts on a TimesTen database.
The default start policy is manual
which means the replication agent must be started manually by calling the ttRepStart
built-in procedure or running a ttAdmin -repStart
utility command. To manually stop a running replication agent process, call the ttRepStop
built-in procedure or run a ttAdmin -repStop
utility command.
The start policy can be set to always
so that the replication agent starts automatically when the TimesTen main daemon process starts. With the always
start policy, the replication agent cannot be stopped when the main daemon is running unless the start policy is changed to either manual
or norestart
and then a manual stop is issued by calling the ttRepStop
built-in procedure or running a ttAdmin -repStop
utility command.
With the manual
and always
start policies, the replication agent automatically restarts after a failure such as a database invalidation.
The start policy can be set to norestart
which means the replication agent must be started manually by calling the ttRepStart
built-in procedure or running a ttAdmin -repStart
utility command, and stopped manually by calling the ttRepStop
built-in procedure or running a ttAdmin -repStop
utility command.
With the norestart
start policy, the replication agent does not automatically restart after a failure such as a database invalidation. You must restart the replication agent manually by calling the ttRepStart
built-in procedure or running a ttAdmin -repStart
utility command.
Example 4-5 Setting a replication agent start policy
As the instance administrator, grant the ADMIN
privilege to the cache manager user:
% ttIsql cachealone1 Command> GRANT ADMIN TO cacheuser; Command> exit
The replication agent start policy can be set programmatically by calling the ttRepPolicySet
built-in procedure as the cache manager user:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle" Command> call ttRepPolicySet('manual'); Command> exit
It can also be set from a command line by running a ttAdmin -repPolicy
utility command as a TimesTen external user with the ADMIN
privilege:
% ttAdmin -repPolicy always cachealone1
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 tables in commit order.
Parallel propagation is supported for the following configurations:
AWT cache groups in a standalone database
AWT cache groups with aging
AWT cache groups in an active standby pair replication scheme
AWT cache groups in a cache grid
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 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 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 is 2 times the setting for ReplicationParallelism
. For example, if ReplicationParallelism=3
, the number of threads that apply changes to Oracle 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. 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 tables is 6. This enables the number of threads used to be different for parallel replication and parallel propagation to Oracle tables.
Note: For more information about parallel replication, see "Configuring parallel replication" in Oracle TimesTen In-Memory Database Replication Guide. |
These data store attributes are interrelated. Table 4-1 shows the result with the combination of the various possible attribute values.
Table 4-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 |
Note: For more details on these data store attributes, see "ReplicationApplyOrdering," "ReplicationParallelism," and "CacheAwtParallelism" in the Oracle TimesTen In-Memory Database Reference. |
Any unique index, unique constraint, or foreign key constraint on columns in Oracle tables that are to be cached must also be created on the cached tables within TimesTen. If you cannot create the appropriate unique index, unique constraint, or foreign key constraint on the cached table, either disable parallel propagation or do not cache these columns. For example, if a table referenced by the foreign key constraint is too large to cache in TimesTen, you would not be able to create the foreign key.
Foreign keys in Oracle tables that are to be cached must have indexes created on the foreign keys. Consider these Oracle 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);
Parallel propagation cannot identify the case where two transactions have an implicit dependency if the transactions act on different cache groups. This occurs because there are no foreign keys on tables that are in different cache groups, even when there are foreign keys in the Oracle tables. Do not use parallel propagation to Oracle when your data model includes foreign keys in Oracle that cannot be created on cache tables.
An AWT cache group can guarantee that:
No transactions are lost because of communication failures between the TimesTen and Oracle databases.
If the replication agent is not running or loses its connection to the Oracle database, automatic propagation of committed updates on the TimesTen cache tables to the cached Oracle tables resumes after the agent is restarted or is able to reconnect to the Oracle database.
Transactions are committed in the Oracle database in the same order they were committed in the TimesTen database.
An AWT cache group cannot guarantee that:
All transactions committed successfully in the TimesTen database are successfully propagated to and committed in the Oracle database. Execution errors on Oracle cause the transaction in the Oracle database to be rolled back. For example, an update on Oracle may fail because of a unique constraint violation. Transactions that contain execution errors are not retried.
Execution errors are reported to the TimesTenDatabaseFileName
.awterrs
file that resides in the same directory as the TimesTen database's checkpoint files. See "Reporting Oracle execution errors for AWT cache groups" for more information.
The absolute order of Oracle updates is preserved because TimesTen does not resolve update conflicts. The following are some examples:
An update is committed on a cache table in an AWT cache group. The same update is committed on the cached Oracle table using a passthrough operation. The cache table update, which is automatically and asynchronously propagated to Oracle, may overwrite the passed through update that was processed directly on the cached Oracle table depending on when the propagated update and the passed through update is processed on Oracle.
In two separate TimesTen databases (DB1
and DB2
), different AWT cache groups cache the same Oracle table. An update is committed on the cache table in DB1
. An update is then committed on the cache table in DB2
. The two cache tables reside in different TimesTen databases and cache the same Oracle table. Because the writethrough operations are asynchronous, the update from DB2
may get propagated to the Oracle database before the update from DB1
, resulting in the update from DB1
overwriting the update from DB2
.
Using a dynamic AWT global cache group resolves this write inconsistency. See "Global cache groups" for more information about global cache groups.
The following restrictions apply when using an AWT cache group:
Only the ON DELETE CASCADE
and UNIQUE HASH ON
cache table attributes can be used in the cache table definitions.
See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE
cache table attribute.
See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON
cache table attribute.
A FLUSH CACHE GROUP
statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement
The cache table definitions cannot contain a WHERE
clause.
See "Using a WHERE clause" for more information about WHERE
clauses in cache group definitions and operations.
A TRUNCATE TABLE
statement cannot be issued on the cache tables.
AWT cache groups cannot cache Oracle views or materialized views.
The replication agent must be stopped before creating or dropping an AWT cache group.
See "Managing the replication agent" for information about how to stop and start the replication agent.
Committed updates on the TimesTen cache tables are not propagated to the cached Oracle tables unless the replication agent is running.
To create an AWT cache group, the length of the absolute path name of the TimesTen database cannot exceed 248 characters.
TimesTen does not detect or resolve update conflicts that occur on Oracle. Committed updates made directly on a cached Oracle table may be overwritten by a committed update made on the TimesTen cache table when the cache table update is propagated to Oracle.
TimesTen performs deferred checking when determining whether a single SQL statement causes a constraint violation with a unique index.
For example, suppose there is a unique index on a cached Oracle table's NUMBER
column, and a unique index on the same NUMBER
column on the TimesTen cache table. There are five rows in the cached Oracle table and the same five rows in the cache table. The values in the NUMBER
column range from 1 to 5.
An UPDATE
statement is issued on the cache table to increment the value in the NUMBER
column by 1 for all rows. The operation succeeds on the cache table but fails when it is propagated to the cached Oracle table.
This occurs because TimesTen performs the unique index constraint check at the end of the statement's execution after all the rows have been updated. Oracle, however, performs the constraint check each time after a row has been updated.
Therefore, when the row in the cache table with value 1 in the NUMBER
column is changed to 2 and the update is propagated to Oracle, it causes a unique constraint violation with the row that has the value 2 in the NUMBER
column of the cached Oracle table.
If transactions are not successfully propagated to and committed in the Oracle database, then these execution errors cause the transaction in the Oracle database to be rolled back. For example, an update on Oracle may fail because of a unique constraint violation. Transactions that contain execution errors are not retried.
Execution errors are reported to the TimesTenDatabaseFileName
.awterrs
text file that resides in the same directory as the TimesTen database checkpoint files.
Note: See "Oracle errors reported by TimesTen for AWT" in Oracle TimesTen In-Memory Database Troubleshooting Guide for information about the contents of this file. |
A synchronous writethrough (SWT) cache group enforces a caching behavior where committed updates on the TimesTen cache tables are automatically and synchronously propagated to the cached Oracle tables as shown in Figure 4-7.
Figure 4-7 Synchronous writethrough cache group
The transaction commit on the TimesTen database occurs synchronously with the commit on the Oracle database. When an application commits a transaction in the TimesTen database, the transaction is processed in the Oracle database before it is processed in TimesTen. The application is blocked until the transaction has completed in both the Oracle and TimesTen databases.
If the transaction fails to commit in Oracle, the application must roll back the transaction in TimesTen. If the Oracle transaction commits successfully but the TimesTen transaction fails to commit, the cache tables in the SWT cache group are no longer synchronized with the cached Oracle tables. To manually resynchronize the cache tables with the cached Oracle tables, call the ttCachePropagateFlagSet
built-in procedure to disable update propagation, and then reissue the transaction in the TimesTen database after correcting the problem that caused the transaction commit to fail in TimesTen. You can also resynchronize the cache tables with the cached Oracle tables by reloading the accompanying cache groups.
The following is the definition of the Oracle table that will be cached in the SWT cache group that is defined in Example 4-6. The Oracle table is owned by the schema user oratt. The oratt user must be granted the CREATE SESSION
and RESOURCE
privileges before it can create tables.
CREATE TABLE product (prod_num VARCHAR2(6) NOT NULL PRIMARY KEY, name VARCHAR2(30), price NUMBER(8,2), ship_weight NUMBER(4,1));
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT
privilege on the oratt.product table in order for the cache manager user to create an SWT cache group that caches this table. This Oracle user must also be granted the INSERT
, UPDATE
and DELETE
privileges on the oratt.product table for synchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
Use the CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP
statement to create an SWT cache group.
Example 4-6 Creating a SWT cache group
The following statement creates a synchronous writethrough cache group top_products
that caches the oratt.product
table:
CREATE SYNCHRONOUS WRITETHROUGH CACHE GROUP top_products FROM oratt.product (prod_num VARCHAR2(6) NOT NULL, name VARCHAR2(30), price NUMBER(8,2), ship_weight NUMBER(4,1), PRIMARY KEY(prod_num));
The following restrictions apply when using an SWT cache group:
Only the ON DELETE CASCADE
and UNIQUE HASH ON
cache table attributes can be used in the cache table definitions.
See "ON DELETE CASCADE cache table attribute" for more information about the ON DELETE CASCADE
cache table attribute.
See "UNIQUE HASH ON cache table attribute" for more information about the UNIQUE HASH ON
cache table attribute.
A FLUSH CACHE GROUP
statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement
The cache table definitions cannot contain a WHERE
clause.
See "Using a WHERE clause" for more information about WHERE
clauses in cache group definitions and operations.
A TRUNCATE TABLE
statement cannot be issued on the cache tables.
SWT cache groups cannot cache Oracle views or materialized views.
If the system managed cache groups (read-only, AWT, SWT) do not satisfy your application's requirements, you can create a user managed cache group that defines customized caching behavior. For example:
You can define a user managed cache group to automatically refresh and propagate committed updates between the Oracle and TimesTen databases by using the AUTOREFRESH
cache group attribute and the PROPAGATE
cache table attribute. Using both attributes enables bidirectional transmit, so that committed updates on the TimesTen cache tables or the cached Oracle tables are propagated or refreshed to each other.
You can use the LOAD CACHE GROUP
, REFRESH CACHE GROUP
, and FLUSH CACHE GROUP
statements to manually control the transmit of committed updates between the Oracle and TimesTen databases.
You can specify the READONLY
or the PROPAGATE
cache table attribute on individual cache tables in a user managed cache group to define read-only or synchronous writethrough behavior at the table level.
You can cache Oracle materialized views in a user managed cache group that does not use the PROPAGATE
cache table attribute or the AUTOREFRESH
cache group attribute. The cache group must be manually loaded and flushed. You cannot cache Oracle views.
The following are the definitions of the Oracle tables that will be cached in the user managed cache groups that are defined in Example 4-7 and Example 4-8. The Oracle tables are owned by the schema user oratt
. The oratt
user must be granted the CREATE SESSION
and RESOURCE
privileges before it can create tables.
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'); CREATE TABLE ordertab (orderid NUMBER(10) NOT NULL PRIMARY KEY, custid NUMBER(6) NOT NULL); CREATE TABLE cust_interests (custid NUMBER(6) NOT NULL, interest VARCHAR2(10) NOT NULL, PRIMARY KEY (custid, interest)); CREATE TABLE orderdetails (orderid NUMBER(10) NOT NULL, itemid NUMBER(8) NOT NULL, quantity NUMBER(4) NOT NULL, PRIMARY KEY (orderid, itemid));
Use the CREATE USERMANAGED CACHE GROUP
statement to create a user managed cache group.
Example 4-7 Creating a single-table user managed cache group
The following statement creates a user managed cache group update_anywhere_customers
that caches the oratt.active_customer
table as shown in Figure 4-8:
CREATE USERMANAGED CACHE GROUP update_anywhere_customers AUTOREFRESH MODE INCREMENTAL INTERVAL 30 SECONDS FROM oratt.active_customer (custid NUMBER(6) NOT NULL, name VARCHAR2(50), addr VARCHAR2(100), zip VARCHAR2(12), PRIMARY KEY(custid), PROPAGATE);
Figure 4-8 Single-table user managed cache group
All columns except region are cached. Only customers whose customer ID is greater than or equal to 1001 are cached. Committed updates on the oratt.active_customer
cache table or the oratt.active_customer
cached Oracle table are transmitted to the corresponding table.
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT
privilege on the oratt.active_customer
table in order for the cache manager user to create a user managed cache group that caches this table, and for autorefresh operations to occur from the cached Oracle table to the TimesTen cache table. This Oracle user must also be granted the INSERT
, UPDATE
and DELETE
privileges on the oratt.active_customer
table for synchronous writethrough operations to occur from the TimesTen cache table to the cached Oracle table.
In this example, the AUTOREFRESH
cache group attribute specifies that committed updates on the oratt.active_customer
cached Oracle table are automatically refreshed to the TimesTen oratt.active_customer cache table every 30 seconds. The PROPAGATE cache table attribute specifies that committed updates on the cache table are automatically and synchronously propagated to the cached Oracle table.
See "AUTOREFRESH cache group attribute" for more information about defining an autorefresh mode, interval and state.
If you manually created the Oracle objects used to enforce the predefined behaviors of a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL
cache group attribute as described in "Manually create Oracle objects used to manage caching of Oracle data", you need to set the autorefresh state to OFF
when creating the cache group.
Then you need to run the ttIsql
utility's cachesqlget
command to generate a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle table that is cached in the user managed cache group.
See "Manually creating Oracle objects for autorefresh cache groups" for more information.
Example 4-8 Creating a multiple-table user managed cache group
The following statement creates a user managed cache group western_customers
that caches the oratt.active_customer
, oratt.ordertab
, oratt.cust_interests
, and oratt.orderdetails
tables as shown in Figure 4-9:
CREATE USERMANAGED CACHE GROUP western_customers FROM oratt.active_customer (custid NUMBER(6) NOT NULL, name VARCHAR2(50), addr VARCHAR2(100), zip VARCHAR2(12), region VARCHAR2(12), PRIMARY KEY(custid), PROPAGATE) WHERE (oratt.active_customer.region = 'West'), oratt.ordertab (orderid NUMBER(10) NOT NULL, custid NUMBER(6) NOT NULL, PRIMARY KEY(orderid), FOREIGN KEY(custid) REFERENCES oratt.active_customer(custid), PROPAGATE), oratt.cust_interests (custid NUMBER(6) NOT NULL, interest VARCHAR2(10) NOT NULL, PRIMARY KEY(custid, interest), FOREIGN KEY(custid) REFERENCES oratt.active_customer(custid), READONLY), oratt.orderdetails (orderid NUMBER(10) NOT NULL, itemid NUMBER(8) NOT NULL, quantity NUMBER(4) NOT NULL, PRIMARY KEY(orderid, itemid), FOREIGN KEY(orderid) REFERENCES oratt.ordertab(orderid)) WHERE (oratt.orderdetails.quantity >= 5);
Figure 4-9 Multiple-table user managed cache group
Only customers in the West region who ordered at least 5 of the same item are cached.
The Oracle user with the same name as the TimesTen cache manager user must be granted the SELECT
privilege on the oratt.active_customer
, oratt.ordertab
, oratt.cust_interests
, and oratt.orderdetails
tables in order for the cache manager user to create a user managed cache group that caches all of these tables. This Oracle user must also be granted the INSERT
, UPDATE
and DELETE
privileges on the oratt.active_customer
an d oratt.ordertab
tables for synchronous writethrough operations to occur from these TimesTen cache tables to the cached Oracle tables.
Each cache table in the western_customers
cache group contains a primary key. Each child table references a parent table with a foreign key constraint. The oratt.active_customer
root table and the oratt.orderdetails
child table each contain a WHERE
clause to restrict the rows to be cached. The oratt.active_customer
root table and the oratt.ordertab
child table both use the PROPAGATE cache table attribute so that committed updates on these cache tables are automatically propagated to the cached Oracle tables. The oratt.cust_interests
child table uses the READONLY cache table attribute so that it cannot be updated directly.
The PROPAGATE
cache table attribute can be specified only for cache tables in a user managed cache group. PROPAGATE
specifies that committed updates on the TimesTen cache table are automatically and synchronously propagated to the cached Oracle table such that:
The commit is first attempted in the Oracle database. If the commit fails in Oracle, the commit is not attempted in the TimesTen database and the application must roll back the TimesTen transaction. As a result, the Oracle database never misses updates committed in TimesTen.
If the commit succeeds in the Oracle database, it is then attempted in the TimesTen database. If the commit fails in TimesTen, an error message is returned from TimesTen indicating the cause of the failure. You then need to manually resynchronize the cache tables with the Oracle tables.
See "Synchronous writethrough (SWT) cache group" for information on how to resynchronize the cache tables with the Oracle tables.
By default, a cache table in a user managed cache group is created with the NOT PROPAGATE
cache table attribute such that committed updates on the cache table are not propagated to the cached Oracle table.
When a cache table uses the PROPAGATE
cache table attribute, you may occasionally need to commit updates on the cache table that you do not want propagated to the cached Oracle table. Use the ttCachePropagateFlagSet
built-in procedure to disable automatic propagation so that committed updates on a cache table is not propagated to the cached Oracle table.
The following restrictions apply when using the PROPAGATE
cache table attribute:
If the cache group uses the AUTOREFRESH
cache group attribute, the PROPAGATE
cache table attribute must be specified on all or none of its cache tables.
See "AUTOREFRESH cache group attribute" for more information about using the AUTOREFRESH
cache group attribute.
If the cache group uses the AUTOREFRESH
cache group attribute, the NOT PROPAGATE
cache table attribute cannot be explicitly specified on any of its cache tables.
You cannot use both the PROPAGATE
and READONLY
cache table attributes on the same cache table.
See "READONLY cache table attribute" for more information about using the READONLY
cache table attribute.
A FLUSH CACHE GROUP
statement cannot be issued on the cache group unless one or more of its cache tables use neither the PROPAGATE
nor the READONLY
cache table attribute.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement.
After the PROPAGATE
cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.
The PROPAGATE
cache table attribute cannot be used when caching Oracle materialized views.
TimesTen does not perform a conflict check to prevent a propagate operation from overwriting data that was updated directly on a cached Oracle table. Therefore, updates should only be performed directly on the TimesTen cache tables or the cached Oracle tables, but not both.
In Example 4-7, the oratt.active_customer
cache table uses the PROPAGATE
cache table attribute.
The READONLY
cache table attribute can be specified only for cache tables in a user managed cache group. READONLY
specifies that the cache table cannot be updated directly. By default, a cache table in a user managed cache group is updatable.
Unlike a read-only cache group where all of its cache tables are read-only, in a user managed cache group individual cache tables can be specified as read-only using the READONLY
cache table attribute.
The following restrictions apply when using the READONLY
cache table attribute:
If the cache group uses the AUTOREFRESH
cache group attribute, the READONLY
cache table attribute must be specified on all or none of its cache tables.
See "AUTOREFRESH cache group attribute" for more information about using the AUTOREFRESH
cache group attribute.
You cannot use both the READONLY
and PROPAGATE
cache table attributes on the same cache table.
See "PROPAGATE cache table attribute" for more information about using the PROPAGATE
cache table attribute.
A FLUSH CACHE GROUP
statement cannot be issued on the cache group unless one or more of its cache tables use neither the READONLY
nor the PROPAGATE
cache table attribute.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement.
After the READONLY
cache table attribute has been specified on a cache table, you cannot change this attribute unless you drop the cache group and re-create it.
In Example 4-8, the oratt.cust_interests
cache table uses the READONLY
cache table attribute.
The AUTOREFRESH
cache group attribute can be specified when creating a read-only cache group or a user managed cache group using a CREATE CACHE GROUP
statement. AUTOREFRESH
specifies that committed updates on cached Oracle tables are automatically refreshed to the TimesTen cache tables. Autorefresh is defined by default on read-only cache groups.
The following are the default settings of the autorefresh attributes:
The autorefresh mode is incremental.
The autorefresh interval is 5 minutes.
The autorefresh state is PAUSED
.
TimesTen supports two autorefresh modes:
INCREMENTAL
: Committed updates on cached Oracle tables are automatically refreshed to the TimesTen cache tables based on the cache group's autorefresh interval. Incremental autorefresh mode uses Oracle objects to track committed updates on cached Oracle tables. See "Managing a caching environment with Oracle objects" for information on these objects.
FULL
: All cache tables are automatically refreshed, based on the cache group's autorefresh interval, by unloading all their rows and then reloading from the cached Oracle tables.
Incremental autorefresh mode incurs some overhead to refresh the cache group for each committed update on the cached Oracle tables. There is no overhead when using full autorefresh mode.
When using incremental autorefresh mode, committed updates on cached Oracle tables are tracked in change log tables in the Oracle database. Under certain circumstances, it is possible for some of the change log records to be deleted from the change log table before they are automatically refreshed to the TimesTen cache tables. If this occurs, TimesTen initiates a full automatic refresh on the cache group. See "Monitoring the cache administration user's tablespace" for information on how to configure an action to take when the tablespace that the change log tables reside in becomes full.
The change log table on the Oracle database does not have column-level resolution because of performance reasons. Thus the autorefresh operation updates all of the columns in a row. XLA reports that all of the columns in the row have changed even if the data did not actually change in each column.
The autorefresh interval determines how often autorefresh operations occur in minutes, seconds or milliseconds. Cache groups with the same autorefresh interval are refreshed within the same transaction. You can use the ttCacheAutorefresh
built-in procedure to initiate an immediate autorefresh operation. For more information, see "ttCacheAutorefresh" in Oracle TimesTen In-Memory Database Reference.
The autorefresh state can be set to ON
, PAUSED
or OFF
. Autorefresh operations are scheduled by TimesTen when the cache group's autorefresh state is ON
.
When the cache group's autorefresh state is OFF
, committed updates on the cached Oracle tables are not tracked.
When the cache group's autorefresh state is PAUSED
, committed updates on the cached Oracle tables are tracked in the Oracle database, but are not automatically refreshed to the TimesTen cache tables until the state is changed to ON
.
The following restrictions apply when using the AUTOREFRESH
cache group attribute:
A FLUSH CACHE GROUP
statement cannot be issued on the cache group.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement.
A TRUNCATE TABLE
statement issued on a cached Oracle table is not automatically refreshed to the TimesTen cache table. Before issuing a TRUNCATE TABLE
statement on a cached Oracle table, use an ALTER CACHE GROUP
statement to change the autorefresh state of the cache group that contains the cache table to PAUSED
.
See "Altering a cache group to change the AUTOREFRESH mode, interval or state" for more information about the ALTER CACHE GROUP
statement.
After issuing the TRUNCATE TABLE
statement on the cached Oracle table, use a REFRESH CACHE GROUP
statement to manually refresh the cache group.
A LOAD CACHE GROUP
statement can only be issued if the cache tables are empty, unless the cache group is dynamic.
See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP
and REFRESH CACHE GROUP
statements.
See "Dynamic cache groups" for more information about dynamic cache groups.
The autorefresh state must be PAUSED
before you can issue a LOAD CACHE GROUP
statement on the cache group, unless the cache group is dynamic, in which case the autorefresh state must be PAUSED
or ON
. The LOAD CACHE GROUP
statement cannot contain a WHERE
clause, unless the cache group is dynamic, in which case the WHERE
clause must be followed by a COMMIT EVERY
n
ROWS
clause.
See "Using a WHERE clause" for more information about WHERE
clauses in cache group definitions and operations.
The autorefresh state must be PAUSED
before you can issue a REFRESH CACHE GROUP
statement on the cache group. The REFRESH CACHE GROUP
statement cannot contain a WHERE
clause.
All tables and columns referenced in WHERE
clauses when creating, loading or unloading the cache group must be fully qualified. For example:
user_name
.
table_name
and user_name
.
table_name
.
column_name
To use the AUTOREFRESH
cache group attribute in a user managed cache group, all of the cache tables must be specified with the PROPAGATE
cache table attribute or all of the cache tables must be specified the READONLY
cache table attribute.
You cannot specify the AUTOREFRESH
cache group attribute in a user managed cache group that contains cache tables that explicitly use the NOT PROPAGATE
cache table attribute.
The AUTOREFRESH
cache table attribute cannot be used when caching Oracle materialized views in a user managed cache group.
LRU aging cannot be specified on the cache group, unless the cache group is dynamic where LRU aging is defined by default.
See "LRU aging" for more information about LRU aging.
If you create a unique index on a cache group with the AUTOREFRESH
cache group attribute, the index is changed to a non-unique index to avoid a constraint violation. A constraint violation could occur with a unique index because conflicting updates could occur in the same statement execution on the Oracle table, while each row update is executed separately in TimesTen. If the unique index exists on the Oracle table that is being cached, then uniqueness is enforced on the Oracle table and does not need to be verified again in TimesTen.
In Example 4-7, the update_anywhere_customers
cache group uses the AUTOREFRESH
cache group attribute.
After creating an autorefresh cache group, you can use an ALTER CACHE GROUP
statement to change the cache group's autorefresh mode, interval or state. You cannot use ALTER CACHE GROUP
to instantiate automatic refresh for a cache group that was originally created without autorefresh defined.
If you change a cache group's autorefresh state to OFF
or drop a cache group that has an autorefresh operation in progress:
The autorefresh operation stops if the setting of the LockWait
connection attribute is greater than 0. The ALTER CACHE GROUP
or DROP CACHE GROUP
statement preempts the autorefresh operation.
The autorefresh operation continues if the LockWait
connection attribute is set to 0. The ALTER CACHE GROUP
or DROP CACHE GROUP
statement is blocked until the autorefresh operation completes or the statement fails with a lock timeout error.
Example 4-9 Altering the autorefresh attributes of a cache group
The following statements change the autorefresh mode, interval and state of the customer_orders
cache group:
ALTER CACHE GROUP customer_orders SET AUTOREFRESH MODE FULL; ALTER CACHE GROUP customer_orders SET AUTOREFRESH INTERVAL 30 SECONDS; ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE ON;
If you manually created the Oracle objects used to enforce the predefined behaviors of an autorefresh cache group as described in "Manually create Oracle objects used to manage caching of Oracle data", you need to set the autorefresh state to OFF
when creating the cache group.
Then you need to run the ttIsql
utility's cachesqlget
command with the INCREMENTAL_AUTOREFRESH
option and the INSTALL
flag as the cache manager user. This command generates a SQL*Plus script used to create a log table and a trigger in the Oracle database for each Oracle table that is cached in the autorefresh cache group. These Oracle objects are used to track updates on the cached Oracle tables so that the updates can be automatically refreshed to the cache tables.
Next use SQL*Plus to run the script generated by the ttIsql
utility's cachesqlget
command as the sys
user. Then use an ALTER CACHE GROUP
statement to change the autorefresh state of the cache group to PAUSED
.
Example 4-10 Creating a read-only cache group when Oracle objects were manually created
The first statement creates a read-only cache group customer_orders
with the autorefresh state set to OFF
. The SQL*Plus script generated by the ttIsql
utility's cachesqlget
command is saved to the /tmp/obj.sql
file. The last statement changes the autorefresh state of the cache group to PAUSED
.
CREATE READONLY CACHE GROUP customer_orders
AUTOREFRESH STATE OFF
FROM oratt.customer
(cust_num NUMBER(6) NOT NULL,
region VARCHAR2(10),
name VARCHAR2(50),
address VARCHAR2(100),
PRIMARY KEY(cust_num)),
oratt.orders
(ord_num NUMBER(10) NOT NULL,
cust_num NUMBER(6) NOT NULL,
when_placed DATE NOT NULL,
when_shipped DATE NOT NULL,
PRIMARY KEY(ord_num),
FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> cachesqlget INCREMENTAL_AUTOREFRESH customer_orders INSTALL /tmp/obj.sql;
Command> exit
% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/obj
SQL> exit
ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE PAUSED;
A cache table definition in a CREATE CACHE GROUP
statement can contain a WHERE
clause to restrict the rows to cache in the TimesTen database for particular cache group types.
You can also specify a WHERE
clause in a LOAD CACHE GROUP
, UNLOAD CACHE GROUP
, REFRESH CACHE GROUP
or FLUSH CACHE GROUP
statement for particular cache group types. Some statements, such as LOAD CACHE GROUP
and REFRESH CACHE GROUP
, may result in concatenated WHERE
clauses in which the WHERE
clause for the cache table definition is evaluated before the WHERE
clause in the LOAD CACHE GROUP
or REFRESH CACHE GROUP
statement.
The following restrictions apply to WHERE
clauses used in cache table definitions and cache group operations:
WHERE
clauses can only be specified in the cache table definitions of a CREATE CACHE GROUP
statement for read-only and user managed cache groups.
A WHERE
clause can be specified in a LOAD CACHE GROUP
statement except on an explicitly loaded autorefresh cache group.
See "Loading and refreshing a cache group" for more information about the LOAD CACHE GROUP
statement.
A WHERE
clause can be specified in a REFRESH CACHE GROUP
statement except on an autorefresh cache group.
See "Loading and refreshing a cache group" for more information about the REFRESH CACHE GROUP
statement.
A WHERE
clause can be specified in a FLUSH CACHE GROUP
statement on a user managed cache group that allows committed updates on the TimesTen cache tables to be flushed to the cached Oracle tables.
See "Flushing a user managed cache group" for more information about the FLUSH CACHE GROUP
statement.
WHERE
clauses in a CREATE CACHE GROUP
statement cannot contain a subquery. Therefore, each WHERE
clause cannot reference any table other than the one in its cache table definition. However, a WHERE
clause in a LOAD CACHE GROUP
, UNLOAD CACHE GROUP
, REFRESH CACHE GROUP
or FLUSH CACHE GROUP
statement may contain a subquery.
A WHERE
clause in a LOAD CACHE GROUP
, REFRESH CACHE GROUP
or FLUSH CACHE GROUP
statement can reference only the root table of the cache group, unless the WHERE
clause contains a subquery.
WHERE
clauses in the cache table definitions are only enforced when the cache group is manually loaded or refreshed, or the cache tables are dynamically loaded. If a cache table is updatable, you can insert or update a row such that the WHERE
clause in the cache table definition for that row is not satisfied.
All tables and columns referenced in WHERE
clauses when creating, loading, refreshing, unloading or flushing the cache group must be fully qualified. For example:
user_name
.
table_name
and user_name
.
table_name
.
column_name
In Example 4-8, both the oratt.active_customer
and oratt.orderdetails
tables contain a WHERE
clause.
In a multiple-table cache group, a WHERE
clause in a particular table definition should not reference any table in the cache group other than the table itself. For example, the following CREATE CACHE GROUP
statements are valid:
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)) WHERE (oratt.customer.cust_num < 100), oratt.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)), oratt.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num)); WHERE (oratt.orders.cust_num < 100)
The following statement is not valid because the WHERE
clause in the child table's definition references its parent table:
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)), oratt.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num)) WHERE (oratt.customer.cust_num < 100);
Similarly, the following statement is not valid because the WHERE
clause in the parent table's definition references its child table:
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)) WHERE (oratt.orders.cust_num < 100), oratt.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));
A user-defined PL/SQL function in the Oracle database can be invoked indirectly in a WHERE
clause within a CREATE CACHE GROUP
, LOAD CACHE GROUP
, or REFRESH CACHE GROUP
(for dynamic cache groups only) statement. After creating the function, create a public synonym for the function. Then grant the EXECUTE
privilege on the function to PUBLIC
.
For example, in the Oracle database:
CREATE OR REPLACE FUNCTION get_customer_name (c_num oratt.customer.cust_num%TYPE) RETURN VARCHAR2 IS c_name oratt.customer.name%TYPE; BEGIN SELECT name INTO c_name FROM oratt.customer WHERE cust_num = c_num; RETURN c_name; END get_customer_name; CREATE PUBLIC SYNONYM retname FOR get_customer_name; GRANT EXECUTE ON get_customer_name TO PUBLIC;
Then in the TimesTen database, for example, you can create a cache group with a WHERE
clause that references the Oracle public synonym that was created for the function:
CREATE READONLY CACHE GROUP top_customer FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)) WHERE name = retname(100);
For cache group types that allow a WHERE
clause on a LOAD CACHE GROUP
or REFRESH CACHE GROUP
statement, you can invoke the function indirectly by referencing the public synonym that was created for the function. For example, you can use the following LOAD CACHE GROUP
statement to load the AWT cache group new_customers
:
LOAD CACHE GROUP new_customers WHERE name = retname(101) COMMIT EVERY 0 ROWS;
The ON DELETE CASCADE
cache table attribute can be specified for cache tables in any cache group type. ON DELETE CASCADE
specifies that when rows containing referenced key values are deleted from a parent table, rows in child tables with dependent foreign keys are also deleted.
Example 4-11 Using the ON DELETE CASCADE cache table attribute
The following statement uses the ON DELETE CASCADE
cache table attribute on the child table's foreign key definition:
CREATE READONLY CACHE GROUP customer_orders FROM oratt.customer (cust_num NUMBER(6) NOT NULL, region VARCHAR2(10), name VARCHAR2(50), address VARCHAR2(100), PRIMARY KEY(cust_num)), oratt.orders (ord_num NUMBER(10) NOT NULL, cust_num NUMBER(6) NOT NULL, when_placed DATE NOT NULL, when_shipped DATE NOT NULL, PRIMARY KEY(ord_num), FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num) ON DELETE CASCADE);
All paths from a parent table to a child table must be either "delete" paths or "do not delete" paths. There cannot be some "delete" paths and some "do not delete" paths from a parent table to a child table. Specify the ON DELETE CASCADE
cache table attribute for child tables on a "delete" path.
The following restrictions apply when using the ON DELETE CASCADE
cache table attribute:
For AWT and SWT cache groups, and for TimesTen cache tables in user managed cache groups that use the PROPAGATE
cache table attribute, foreign keys in cache tables that use the ON DELETE CASCADE
cache table attribute must be a proper subset of the foreign keys in the cached Oracle tables that use the ON DELETE CASCADE
attribute. ON DELETE CASCADE
actions on the cached Oracle tables are applied to the TimesTen cache tables as individual deletes. ON DELETE CASCADE
actions on the cache tables are applied to the cached Oracle tables as a cascaded operation.
Matching of foreign keys between the TimesTen cache tables and the cached Oracle tables is enforced only when the cache group is being created. A cascade delete operation may not work if the foreign keys on the cached Oracle tables are altered after the cache group is created.
See the CREATE CACHE GROUP
statement in Oracle TimesTen In-Memory Database SQL Reference for more information about the ON DELETE CASCADE
cache table attribute.