Caching Oracle Database Tables Using Oracle In-Memory Database Cache

Purpose

This tutorial will go through the process of caching Oracle database tables using Oracle In-Memory Database Cache (IMDB Cache). IMDB Cache improves application transaction response time by caching a performance-critical subset of tables from an Oracle database into a TimesTen in-memory database running in the application tier. This tutorial shows how to use IMDB Cache and get instant access to the cached Oracle data.

Time to Complete

Approximately 45 minutes.

Overview

In this tutorial, it is assumed that you have TimesTen installed in your operating system, and that the TimesTen in-memory database (named ordermatching) has already been created and configured to serve as an in-memory cache database. The steps to set up this database are covered in the Creating an In-Memory Cache Database on Windows tutorial. This tutorial goes through the process of caching database tables using Oracle In-Memory Database Cache. Specifically, we will cache the SYMBOLS and FILLED_ORDERS tables both owned by the TTORAUSER user in the Oracle database into TimesTen. After caching Oracle database tables into TimesTen, we will then demonstrate Oracle In-Memory Database Cache's ability to automatically synchronize data in the Oracle database with data in the TimesTen in-memory cache database.

Prerequisites

Before starting this tutorial, you should:

.

Set up the ordermatching TimesTen in-memory cache database and the TTORAUSER schema in the Oracle database.
Instructions on how to set up both databases can be found in the Creating an In-Memory Cache Database on Windows OBE.

.

Have an installation of SQL Developer 2.1 or higher on the same machine as the TimesTen cache database.
Note: SQL Developer 2.1 will also work for this tutorial, although screenshots and instructions will be slightly different. This tutorial is based on SQL Developer 3.0.

Create Connections to the TimesTen and Oracle Databases Using SQL Developer

For the rest of this tutorial, we will use SQL Developer to visualize the components of the TimesTen and Oracle databases and to run scripts inside the databases. In this section, we will first open SQL Developer, then within the program, we will create connections to both the Oracle database and the TimesTen ordermatching database.

.

Launch SQL Developer

Note: If the TimesTen environment variables are not part of your operating system's path variables, you must make sure that the environment in which you launch SQL Developer contains the TimesTen environment variables. You can do this by opening a command prompt, running <tt_installation_home>/bin/ttenv.*, then finally launching SQL Developer within this command prompt.

 

.

TimesTen connections work best in SQL Developer if the AutoCommit flag is checked.

To do this, go to Tools, and select Preferences.

 

.

Click on + sign next to Database to expand the preferences.
Next, click on Advanced to display all the Advanced Database preferences.
Check the Autocommit checkbox, if not already checked.

 

Create a Connection to the TimesTen Database

Now we will create a connection to the TimesTen in-memory database, ordermatching, in SQL Developer.

.

In the Connections tab, click on the New Connection icon, a green plus + sign.
A New / Select Database Connection dialog box appears.

 

.

Enter ordermatching in the Connection Name field.
Enter ttorauser in the Username field.
Enter timesten in the Password field.
Check the Save Password checkbox. This step is very important. Users typically forget this step which may lead to connection failures later.

Next, click on the TimesTen tab.

 

.

Select ordermatching from the DSN drop down list.
Enter oracle in the Oracle Password (for Cache) field. This is the credential of the user TTORAUSER In the Oracle database.
Click Test.

 

.

Check the status of the connection on the status indicator above the Help button. It should read Success.
To establish and save the connection, click Connect.

 

.

The connection ordermatching has been created. It should appear in the Connections navigator. If not, expand out the connections by clicking on the + sign next to Connections.

 

Create a Connection to the Oracle Database

To observe the propagation of data between the Oracle database and the TimesTen in-memory cache database, we need to create a connection to the Oracle database in SQL Developer. The process of creating a connection to an Oracle database using SQL Developer is very similar to the process described earlier for connecting to a TimesTen database.

.

In the Connections tab, click on the New Connection icon, a green plus + sign.
A New / Select Database Connection dialog box appears.

 

.

Enter oracledb in the Connection Name field.
Enter ttorauser in both the Username field.
Enter oracle in the Password field.
Check the Save Password checkbox.
Select TNS from the Connection Type drop down list.

 

.

Select the Network Alias of the Oracle database from the Network Alias drop down list. The Network Alias is the entry in the TNSNAMES.ORA file pointing to the Oracle database. In our case, this is TTORCL.

Click Connect to create the connection.

 

.

The connection oracledb has been created. It should appear in the Connections navigator. If necessary, click the Refresh icon within the Connections tab.

 

.

Click on the + sign next to the oracledb connection. Expand Tables and Packages to see the tables and PL/SQL package created in the Oracle database.

If you cannot see any objects in the database, this means you did not complete the Creating an In-Memory Cache Database on Windows tutorial which is a pre-requisite for this tutorial.

 

Create Cache Groups in TimesTen

We will now create cache groups within the TimesTen ordermatching database to illustrate Oracle In-Memory Database Cache's caching capability. A cache group is a collection of Oracle tables cached inside a TimesTen in-memory database. A cache database may contain one or more cache groups. Cache groups come in two varieties: readonly or updatable.

Readonly cache group This is a group of Oracle database tables / table fragments cached from an Oracle database for read operations only. Updates to these tables should be done in the Oracle database only and periodically refreshed into the TimesTen in-memory cache database.
Updatable cache group This is a group of Oracle database tables / table fragments cached from an Oracle database for read/write transactions in TimesTen. Committed transactions in TimesTen are then propagated to the Oracle database either asynchronously or synchronously. For the best response time benefit, updatable cache groups should be created as asynchronous writethrough (AWT) cache groups.

In this tutorial we will set up one readonly cache group and one asynchronous writethrough (AWT) cache group within the ordermatching database. Specifically, we will cache the Oracle database table TTORAUSER.SYMBOLS as a readonly cache group and TTORAUSER.FILLED_ORDERS as an asynchronous writethrough cache group. We will create cache groups using SQL Developer.

Set Up a Readonly Cache Group

A readonly cache group specifies the tables cached from the Oracle database that are for read operations only. This means that updates must only be done in the Oracle database then periodically refreshed to TimesTen.

Within the Oracle database that we have configured, there exists a SYMBOLS table owned by the TTORAUSER user that contains different stock ticker symbols and their descriptions. We will now create a readonly cache group for this table within the TimesTen ordermatching database.

Start the Cache Agent

Before we can create a readonly cache group, we must start the TimesTen cache agent. The cache agent process is responsible for managing the interaction and data synchronization between the Oracle database and the cache database.

.

Click on the - sign next to the connection oracledb to collapse the connection, if not already collapsed.

Right click on the connection ordermatching in the connections navigator, and select Start / Stop Cache Agent.

 

.

Check the Start agent check box and click Apply. This starts up the cache agent for ordermatching.

Click OK to close the confirmation dialog box that appears after the cache agent has been started.

 

Create a Readonly Cache Group

.

We will now create a readonly cache group for the Oracle table TTORAUSER.SYMBOLS.

Click on the + sign next to the connection ordermatching to expand out the connection, if not already expanded.

 

.

Right click on Cache Groups and select New Cache Group. This brings up the Create Cache Group dialog box.

 

.

Enter READONLY_SYMBOLS into the Name field.

Make sure that the Type selected is Readonly.

Change the Refresh Interval from "5 minutes" to "5 seconds".

Click on the Tables tab to advance to the next screen.

 

.

Right click on Tables cached and select Add root table. This brings up a dialog box with a listing of the tables belonging to the Oracle user TTORAUSER.

 

.

Select TTORAUSER.SYMBOLS and click OK.

 

.

This will return you to the Create Cache Group dialog box under the Tables tab.
Note the columns information is automatically displayed in the Columns tab on the right. The datatypes and the nullable properties are inherited from the Oracle table.

 

.

Click on the DDL tab to view the corresponding CREATE CACHE GROUP SQL statement for the current cache group.

Click Apply to create the readonly cache group.

Finally, click OK to close the confirmation dialog box that confirms the creation of the READONLY_SYMBOLS cache group.

 

Preload Data into a Readonly Cache Group

Now that we have successfully created a readonly cache group READONLY_SYMBOLS, we will use SQL Developer to preload the readonly cache group with data from the Oracle database.

.

First, let us check the content in TTORAUSER's SYMBOLS cache table, which was created when we created the READONLY_SYMBOLS cache group.

Go back to the connections navigator. Click on the + sign next to Tables in ordermatching to expand out the tables, if not already expanded.

Right click on the table SYMBOLS and select Table > Count Rows.

 

.

Click Apply to execute the Count Rows operation.

 

.

The SYMBOLS cache table has been created, but the table is empty. Click OK to close the dialog.

 

.

Now we will load data into the SYMBOLS cache table.

Click on the + sign next to Cache Groups to expand out the cache groups, if not already expanded.

Right click on the cache group READONLY_SYMBOLS and select Load.
This brings up the Load cache group dialog.

 

.

Click Apply to load all the SYMBOLS records from the TTORAUSER.SYMBOLS table in the Oracle database into ordermatching.

Afterwards, click OK to close the confirmation dialog box that indicates the cache group has been successfully loaded.

 

.

Repeat the Count Rows operation. Right click on the ordermatching SYMBOLS table and select Table > Count Rows, then click Apply.

This time, it should return 101 rows.

Click OK to continue.

 

Refresh TimesTen Cached Data Using the Readonly Cache Group

We will now verify that changes in the SYMBOLS Oracle table will be refreshed into TimesTen. To do this, we will insert a record into the Oracle SYMBOLS table and verify that the record was also inserted into the corresponding table in the TimesTen in-memory cache database.

.

In SQL Developer, go back to the Connections Navigator.

Click on oracledb > Tables > SYMBOLS, and select the Data tab to view the records in SYMBOLS. Here you will see the records of the SYMBOLS table in the Oracle database. Currently, there should be 101 entries.

 

.

We will now add a record into the SYMBOLS table in the Oracle database.

To do this, while still in the Data tab for the SYMBOLS table, click on the Insert Row icon.

 

.

Clicking on the Insert Row icon will enable you to manually enter information into the fields of a database row. Enter some information into the fields. A sample of what data you can enter is shown below:

Column NameValue
SYMBOLZZZ
DESCRIPTIONMy Company

 

.

Click on the Commit Changes icon to commit the changes in the TimesTen database. If the commit is successful, a "Commit Successful" notification should be shown in the Data Editor Log at the bottom of the SQL Developer application.

 

.

If the readonly cache group READONLY_SYMBOLS is functioning correctly, the record we just inserted in oracledb's SYMBOLS table will be refreshed into the TimesTen in-memory cache database, and thus will also appear in the SYMBOLS table in the ordermatching database. We will now verify this.

Go back to the connections navigator. Click on the + sign next to Tables in ordermatching to expand out the tables, if not already expanded.

Click on the Tables > SYMBOLS, and select the Data tab to view the records in SYMBOLS. Here you will see the records of the SYMBOLS table in the TimesTen ordermatching database.

The record we just inserted in oracledb's SYMBOLS table will appear here as well, thus showing the automatic refresh of data from the Oracle database (oracledb) into the TimesTen in-memory database (ordermatching). Click on the Refresh icon if necessary.

 

Set Up an Asynchronous Writethrough (AWT) Cache Group

An asynchronous Write-through (AWT) cache group is an updatable cache group that specifies the tables to be cached from an Oracle database into a TimesTen in-memory cache database for read/write transactions. This means that transactions are committed in TimesTen then asynchronously propagated to the Oracle database.

Within the Oracle database that we have configured, there exists a FILLED_ORDERS table owned by the TTORAUSER user that holds a list of fulfilled trade orders. We will now create an AWT cache group for this table within ordermatching.

Create an Asynchronous Writethrough (AWT) Cache Group

.

Go back to the connections navigator. Click on the + sign next to the connection ordermatching to expand out the connection, if not already expanded.

From there, right click on Cache Groups and select New Cache Group. This brings up the Create Cache Group dialog box.

 

.

Enter AWT_FILLED_ORDERS into the Name field. This will be the name of the cache group we create.

Make sure that the Type selected is Asynchronous Writethrough.

Click on the Tables tab to advance to the next screen.

 

.

Right click on Tables cached and select Add root table. This brings up a dialog box with a listing of the tables belonging to the Oracle user TTORAUSER.

 

.

Select TTORAUSER.FILLED_ORDERS and click OK.

 

.

This will return you to the Create Cache Group dialog box under the Tables tab.
Note the columns information are automatically displayed in the Columns tab on the right. The datatypes and the nullable properties are inherited from the Oracle table.

 

.

Click on the DDL tab to view the corresponding CREATE CACHE GROUP SQL statement for the cache group.

Click Apply to create the cache group.

Finally, click OK to close the confirmation dialog box indicating the AWT_FILLED_ORDERS cache group has been created.

 

.

Expand Cache Groups to verify the two cache groups have been created. If necessary, click on the Refresh icon.

 

.

Expand Tables to view the tables in the ordermatching in-memory cache database. You should see both the FILLED_ORDERS table and the SYMBOLS table. If necessary, hit the Refresh icon.

 

Start the Replication Agent

We have now successfully created an asynchronous writethrough (AWT) cache group AWT_FILLED_ORDERS that automatically synchronizes itself with the Oracle database. However, to enable the propagation of AWT cache group data from the ordermatching TimesTen to the oracledb Oracle databases, the TimesTen replication agent must be running.

.

To start the replication agent, right click on the connection ordermatching in the connections navigator, and select Start / Stop Replication Agent.

 

.

Check the Start agent checkbox and click Apply. This starts up the replication agent for ordermatching.

Click OK in the confirmation dialog box indicating the replication agent has been started.

 

Now that the replication agent is started, all transactions made against the FILLED_ORDERS table in ordermatching are automatically propagated to oracledb.

Propagate Data Into the Oracle Database using the AWT Cache Group

We will insert a record into the TimesTen FILLED_ORDERS cache table and verify that the record was also inserted into the corresponding table in the Oracle database.

.

In SQL Developer, go back to the Connections Navigator.

Click on ordermatching > Tables > FILLED_ORDERS, and select the Data tab to view the records in FILLED_ORDERS. Here you will see the records of the FILLED_ORDERS table in TimesTen. Currently, there should be no data.

 

.

We will now add a record into the FILLED_ORDERS table in TimesTen.

To do this, while still in the Data tab for the FILLED_ORDERS table, click on the Insert Row icon.

 

.

Clicking on the Insert Row icon will enable you to manually enter information into the fields of a database row. Enter some information into the fields. A sample of what data you can enter is shown below:

Column NameValue
ORDER_ID0
USER_ID<your_name_here>
BUY_OR_SELL<"B" or "S">
SYMBOLAAA
QUANTITY100
LIMIT_OR_MARKETL
LIMIT_PRICE<leave blank>
ORDER_PLACED<any date in YYYY-MM-DD format. ex: 2011-02-22>
ORDER_EXPIRES<leave blank>
EXECUTED<leave blank>
EXECUTED_TIME<leave blank>
EXECUTED_PRICE<leave blank>

 

.

Click on the Commit Changes icon to commit the changes in the TimesTen database. If the commit is successful, a "Commit Successful" notification should be shown in the Data Editor Log at the bottom of the SQL Developer application.

 

.

If the AWT cache group AWT_FILLED_ORDERS is functioning correctly, the record we just inserted in ordermatching's FILLED_ORDERS table will be propagated to the Oracle database, and thus will also appear in the FILLED_ORDERS table in the oracledb database. We will now verify this.

Go back to the connections navigator. Click on the + sign next to Tables in oracledb to expand out the tables, if not already expanded.

Click on the Tables > FILLED_ORDERS, and select the Data tab to view the records in FILLED_ORDERS. Here you will see the records of the FILLED_ORDERS table in the Oracle database oracledb.

The record we just inserted in ordermatching's FILLED_ORDERS table will appear here as well, thus showing the propagation of data from the TimesTen in-memory cache database (ordermatching) to the Oracle database (oracledb). Click on the Refresh icon if necessary.

 

In this tutorial, we have demonstrated the ability of Oracle In-Memory Database Cache to cache Oracle database tables from an Oracle database into a TimesTen in-memory cache database. In theory, we can now have a database application that used to run against an Oracle database, now run against a TimesTen in-memory cache database, and see an improvement in the application's average transaction response time. The Running Oracle .NET Applications on TimesTen tutorial goes through the process of converting an existing Oracle .NET application to run with Oracle In-Memory Database Cache.

Summary

In this tutorial, you have learned how to:

Resources

Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved