|Oracle® In-Memory Database Cache Introduction
11g Release 2 (11.2.2)
IMDB Cache provides the ability to transfer data between an Oracle database and an IMDB Cache database.
You can cache data from an Oracle database in an IMDB Cache database by defining a cache grid and then creating cache groups in TimesTen where each cache group maps to a single table in the Oracle database or to a group of tables related by foreign key constraints.
This chapter includes the following topics:
A cache grid is a collection of IMDB Cache databases that collectively manage the application data. A cache grid consists of one or more grid members. A grid member can be either a standalone TimesTen database or an active standby pair. Grid members cache tables from a central Oracle database or Real Application Cluster (Oracle RAC). Cached data is dynamically distributed across multiple grid members without shared storage. This architecture allows the capacity of the cache grid to scale based on the processing needs of the application. When the workload increases or decreases, new grid members attach to the grid or existing grid members detach from the grid without interrupting operations on other grid members.
An IMDB Cache database within a cache grid can contain explicitly loaded and dynamic cache groups as well as global and local cache groups of any cache group type. A cache grid ensures that data is consistent across nodes.
Figure 8-1 shows a cache grid. The grid has three members: two standalone IMDB Cache databases and an active standby pair with a read-only subscriber. The read-only subscriber is not part of the grid.
You can cache data in an Oracle database by creating a cache group in an IMDB Cache database. A cache group can be created to cache a single Oracle Database table or a set of related Oracle Database tables. The cached data from the Oracle database can consist of all the rows and columns or a subset of the rows and columns in the Oracle database tables.
IMDB Cache supports the following features:
Applications can both read from and write to cache groups.
Cache groups can be refreshed (upload the Oracle database data into the cache group) automatically or manually.
Cache updates can be sent to the Oracle database automatically or manually. The updates can be sent synchronously or asynchronously.
The IMDB Cache database interacts with the Oracle database to perform all of the synchronous cache group operations, such as creating a cache group and propagating updates between the cache group and the Oracle database. A process called the cache agent performs asynchronous cache operations, such as loading data into the cache group, manually refreshing the data from the Oracle database to the cache group, and automatically refreshing the data from the Oracle database to the cache group.
Figure 8-2 illustrates the IMDB Cache features and processes.
Each cache group has a root table that contains the primary key for the cache group. Rows in the root table may have one-to-many relationships with rows in child tables, each of which may have one-to-many relationships with rows in other child tables.
A cache instance is the set of rows that are associated by foreign key relationships with a particular row in the root table. Each primary key value in the root table specifies a cache instance. Cache instances form the unit of cache loading and cache aging. No table in the cache group can be a child to more than one parent in the cache group. Each IMDB Cache record belongs to only one cache instance and has only one parent in its cache group.
The most commonly used cache group types are:
Read-only cache group - A read-only cache group enforces a caching behavior in which committed updates to the Oracle database tables are automatically refreshed to the corresponding cache tables in the IMDB Cache database.
Asynchronous writethrough (AWT) cache group - An AWT cache group enforces a caching behavior in which committed updates to cache tables in the IMDB Cache database are automatically propagated to the corresponding the Oracle database tables asynchronously.
Other types of cache groups are:
Synchronous writethrough (SWT) cache group - An SWT cache group enforces a caching behavior in which committed updates to cache tables in the IMDB Cache database are automatically propagated to the corresponding Oracle database tables synchronously.
User managed cache group - A user managed cache group defines customized caching behavior. For example, individual cache tables in a user managed cache are not constrained to be all of the same type. Some tables may be defined as read-only while others may be defined as updatable.
Cache groups can be either dynamically loaded or explicitly loaded.
In explicitly loaded cache groups, the application preloads data into the cache tables from the Oracle database using a load cache group operation. From that point on, all data needed by the application is available in the IMDB Cache database.
In dynamic cache groups, cache instances are automatically loaded into the IMDB Cache from the Oracle database when the application references cache instances that are not already in the IMDB Cache. The use of dynamic cache groups is typically coupled with least recently used (LRU) aging so that less recently used cache instances are aged out of the cache to free up space for recently used cache instances. Using dynamic cache groups is appropriate when the size of the data that qualifies for caching exceeds the size of the memory available for the IMDB Cache database.
All cache group types (read-only, AWT, SWT, user managed) can be defined as a explicitly loaded or dynamic.
Cache groups can be defined as either local or global.
In local cache groups, data in the cached tables is not shared among IMDB Cache databases even if the databases are members of the same cache grid. Consequently, the content of the databases may overlap with no coordination from the IMDB Cache. Local cache groups are appropriate for applications that have logically partitioned their data between different nodes or for read-only cache groups. Any cache group type can be defined as a local cache group. Local cache groups can be explicitly loaded or dynamic.
In global cache groups, data in the cached tables is shared among IMDB Cache databases within the same cache grid. Updates to the same data by different grid members are coordinated by the grid to ensure read/write data consistency across the IMDB Caches.
A dynamic AWT cache group and an explicitly loaded AWT cache group can be defined as a global cache group. New cache instances are loaded into the cache tables of a global cache group on demand. Queries on a dynamic AWT global cache group can be satisfied by data from the local grid member on which the query is made, from remote grid members or from the Oracle database. Queries on an explicitly loaded AWT cache group can be satisfied by data from the local grid member or from remote grid members
The IMDB Cache maintains consistency between cached data and the Oracle database by automatically propagating updates from cache groups to the Oracle database and automatically refreshing data in cache groups from the Oracle database.
The rest of this section includes the following topics:
The following mechanisms are available to keep a cache group synchronized with the corresponding data in the Oracle database tables:
Autorefresh - An incremental autorefresh operation updates only records that have been modified in the Oracle database since the last refresh. The IMDB Cache automatically performs the incremental refresh at specified time intervals. You can also specify a full autorefresh operation, which automatically refreshes the entire cache group at specified time intervals.
Manual refresh - An application issues a
REFRESH CACHE GROUP statement to refresh either an entire cache group or a specific cache instance. It is equivalent to unloading and then loading the cache group or cache instance.
These mechanisms are useful under different circumstances. A full autorefresh may be the best choice if the Oracle database table is updated only once a day and many rows are changed. An incremental autorefresh is the best choice if the Oracle database table is updated often, but only a few rows are changed with each update. A manual refresh is the best choice if the application logic knows when the refresh should happen.
The propagate and flush mechanisms are available to keep the Oracle database up to date with the cache group:
Propagate - The most common way to propagate cache group data to the Oracle database is by using an asynchronous writethrough (AWT) cache group. Other methods of updating the Oracle database tables are using a synchronous writethrough (SWT) cache group or specifying the
PROPAGATE option in a user managed cache group.
Changes to an AWT cache group are committed without waiting for the changes to be applied to the Oracle database tables. AWT cache groups provide better response times and performance than SWT cache groups and user managed cache groups with the
PROPAGATE option, but the IMDB Cache database and the Oracle database do not always contain the same data because changes are applied to the Oracle database tables asynchronously.
You can increase throughput from AWT cache groups to the Oracle database tables by configuring parallel propagation at database creation time. You configure the number of threads for applying updates to the Oracle database tables.
Flush - A flush operation can be used to propagate updates manually from a user managed cache group to the Oracle database.An application initiates a flush operation by issuing a
FLUSH CACHE GROUP statement. Flush operations are useful when frequent updates occur for a limited period of time over a set of records. Flush operations do not propagate deletes.
Records can be automatically aged out of a TimesTen database, and cache instances can be automatically aged out of an IMDB Cache database. Aging can be usage-based or time-based.You can configure both usage-based and time-based aging in the same system, but you can define only one type of aging on a specific cache group.
Dynamic load can be used to reload a requested cache instance that has been deleted by aging.
Applications can send SQL statements to either a cache group or to the Oracle database through a single connection to an IMDB Cache. This single-connection capability is enabled by a passthrough feature that checks whether the SQL statement can be handled locally by the cached tables in the IMDB Cache or if it must be redirected to the Oracle database, as shown in Figure 8-3. The passthrough feature provides settings that specify what types of statements are to be passed through and under what circumstances. The specific behavior of the passthrough feature is controlled by the
PassThrough IMDB Cache general connection attribute.
You can use an active standby pair to replicate AWT cache groups and read-only cache groups.
You can recover from a complete failure of a site by creating a special disaster recovery read-only subscriber as part of the active standby pair configuration. This special subscriber, located at a remote disaster recovery site, can propagate updates to a second Oracle database, also located at the disaster recovery site.
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 more information about IMDB Cache, see Oracle In-Memory Database Cache User's Guide.
For more information about aging in cache groups, see "Implementing aging in a cache group" in Oracle In-Memory Database Cache User's Guide. For information about aging in tables that are not in cache groups, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide.
For more information about the passthrough feature, see "Setting a passthrough level" in Oracle In-Memory Database Cache User's Guide.
For more information about replicating cache groups, see "Cache groups and replication" in Oracle TimesTen In-Memory Database Replication Guide.