Creating a Database Cache Grid with Oracle In-Memory Database Cache

Purpose

This tutorial shows you how to cache 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 provides a step by step guide to create an In-Memory Database Cache Grid with a number of grid members to distribute the cache data on multiple nodes. The tutorial will show how to specify and create different cache groups and how to access the cache data.

Time to Complete

Approximately 50 minutes

Software Used in this Lab

In this tutorial, the following software have been pre-installed and configured:

1. Oracle In-Memory Database Cache 11.2.1.8.0

2. Oracle Database 11.2.0.2

3. SQL Developer 3.0

Overview

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 provides a step by step guide to create an In-Memory Database Cache Grid with a number of grid members to distribute the cache data and workload. The tutorial will show how to specify and create different cache groups and how to access the cache data.

An application typically chooses to cache frequently-accessed data, or performance-sensitive data to improve application response time and increase throughput. IMDB Cache uses the concept of a cache group to define the Oracle tables to be cached. A cache group is a collection of cached tables related to each other by foreign key constraints. A database cache grid is a cluster of TimesTen in-memory databases that collectively manage an application's cache data.

The focus of this tutorial is to illustrate the characteristics of the different types of cache groups deployed inside a cache grid, and to demonstrate the interactions of the cache data between in-memory databases and the Oracle database.

Scenario

In this tutorial, tables from the Oracle user TTHR are cached. Note: the tables in the TTHR schema are identical to those in the HR sample schema.

The tables are cached in 2 different cache groups.

TTHR.REGIONS, TTHR.COUNTRIES and TTHR.LOCATIONS tables are grouped together, and defined inside an updatable cache group, known as an asynchronous writethrough (AWT) cache group. All 3 cache tables are updatable.

The data in the above cache groups are pre-loaded from the Oracle database.

TTHR.EMPLOYESS and TTHR.JOB_HISTORY belong to a dynamic AWT global cache group. This is a global cache group, meaning the cached data are shared between all cache databases inside a database cache grid. Cache data can be loaded on demand from other cache databases or from the Oracle database.

Typically, cache databases and cache groups are set up by a database administrator known as the cache manager, and application users are granted access to the cache data. In this tutorial, the in-memory database user TTHR acts as both the cache manager and the application user.

Creating a Cache Grid

Before you start caching Oracle tables, you need to create a TimesTen in-memory database. This database must include a user with either ADMIN or CACHE_MANAGER privilege. In this tutorial, we have a cache manager with ADMIN privilege; cache manager is responsible for administering the cache grid and cache groups. The Oracle user that owns the tables to be cached must also exist in the cache database, this user is known as the cache table user.

The followings have been pre-created:

- An Oracle database
- Two TimesTen in-memory databases, cachedb1 and cachedb2
- User tthr; who has the credential for the cache manager and cache table user

In this tutorial section, you use SQL Developer to connect to an in-memory database, and run a script to create a cache grid.

Create a connection to the Oracle database in SQL Developer

Double click on the desktop icon "SQL Developer 3.0 " to launch SQL Developer.

Show Screenshot for Step

TimesTen connections work best in SQL Developer if the AutoCommit flag is checked.
Go to Tools, and select Preferences.

Show Screenshot for Step

Click on + sign next to Database to expand the preferences.
Next, click on Advanced to display all the Database Advanced preferences.

Show Screenshot for Step

Check the Autocommit checkbox, if not already checked.

Click OK to apply the changes.

Show Screenshot for Step

In the Connections tab, click on the New Connection icon, a green plus + sign.

(Note that if you have an existing "oracle" connection under "Connections" category, please delete the connection before clicking on the "New Connection" icon.)

Show Screenshot for Step

A "New / Select Database Connection" dialog box appears.

Enter oracle in the Connection Name field.
Enter tthr in both the Username and Password fields.

Select TNS from the Connection Type drop down list..

Show Screenshot for Step

Select TTORCL from the Network Alias drop down list.

Click Connect to create the connection.

Show Screenshot for Step

The connection oracle has been created. It should appear in the Connections navigator. If not click the Refresh icon.

Show Screenshot for Step

Create a connection to the in-memory database cachedb1 in SQL Developer

Now, use SQL Developer to create a connection to the TimesTen database cachedb1.

In the Connections tab, click on the New Connection icon, a green plus + sign.

Show Screenshot for Step

A "New / Select Database Connection" dialog box appears.

Enter cachedb1 in the Connection Name field.

Enter tthr in the Username and Password fields.

Click on the TimesTen tab.

Show Screenshot for Step

Select cachedb1 from the DSN drop down list.

Enter tthr in the Oracle Password (for Cache) field. This is the password for the tthr user in the Oracle database

Click Test.

Show Screenshot for Step

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

Show Screenshot for Step

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

Show Screenshot for Step

Run a SQL script to create a Cache Grid

To reduce typing time, a number of scripts have been pre-created. They are located in the directory /home/oracle/Desktop/Database Track/TimesTen Scripts/imdb_cache/cache_grid_sql . They will be used throughout the tutorial for your convenience.

1.	Click on Open to bring up the Open File dialog box.
2.	Navigate to the SQL script directory. If there is a cache_grid_sql icon on the left panel, click on the icon to go directly to the SQL script directory. Otherwise, use Location: to navigate to the script directory: /home/oracle/Desktop/Database Track/TimesTen Scripts/imdb_cache/cache_grid_sql
3.	Select the file 01_create cache_grid.sql and click Open.
4.	The content of this file appears in a SQL Worksheet tab. Review the code. The procedure ttGridCreate creates a cache grid called "samplegrid". ttGridNameSet associates the current database to "samplegrid". Go to the Worksheet connection drop down list on the right, and select cachedb1 as the database connection.
5.	Click Run Script to execute this script on cachedb1.
6.	The result from running the script is displayed in the Script Output Pane.
7.	Close the file 01_create_cache_grid.sql by clicking on the button X, located on the right of the tab.

Creating Cache Groups

In this topic you will learn how to create cache groups to cache Oracle tables into the in-memory database, cachedb1. You will create cache groups using both the Create Cache Group dialog in SQL Developer, and also by issuing the CREATE CACHE GROUP statement in SQL*Worksheet. The cache agent process is responsible for managing the interaction and data synchronization between the Oracle database and the cache database.

Start the Cache Agent

Before creating cache groups, the cache agent must be running.

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

Show Screenshot for Step

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

Show Screenshot for Step

Click OK to close the confirmation dialog box.

Show Screenshot for Step

Create an Asynchronous Writethrough (AWT) Cache Group

Asynchronous Write-through cache group specifies the tables cached from the Oracle database where read/write transactions are performed in the cache, the committed transactions are asynchronously propagated to the Oracle database

To create an AWT cache group based on the tthr.regions, tthr.countries, and tthr.locations tables using the Create cache group dialog in SQL Developer, perform the following steps:

1.	Go back to the connections navigator. Click on the + sign next to the connection cachedb1 to expand out the connection, if not already expanded.
2.	Right click on Cache Groups and select New Cache Group. This brings up the Create cache group dialog box.
3.	Enter awt into the Name field, and change the cache group Type from Readonly to Asynchronous Writethrough. Click on the Tables tab to advance to the next screen.
4.	The screen defines the tables to be cached. Right click in "Tables cached" and select Add root table. This brings up a dialog box with a listing of the tables belonging to the user TTHR.
5.	Select TTHR.REGIONS and click OK. 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.
6.	Right click on "Tables cached" and select Add child tables.
7.	Select both child tables TTHR.COUNTRIES and TTHR.LOCATIONS and click OK. Use Ctrl + table name to do multi-select. Note the relationship between the tables are automatically inherited, based on the primary and foreign key relationships defined in the Oracle database.
8.	Click on the DDL tab to view the corresponding CREATE CACHE GROUP statement for the cache group.
9.	Click Apply to create the cache group. Click OK to close the confirmation dialog box.
10.	Expand Tables to view the tables cached in the in-memory database.

Create a Global Dynamic Asynchronous Writethrough Cache Group

Global dynamic AWT cache group specifies that the cache data can be shared across the grid members within the cache grid

To create a global dynamic AWT cache group based on the tthr.employees and tthr.job_history tables, perform the following steps:

1.	Click on Open to bring up the Open File dialog box.
2.	Click on the cache_grid_sql icon on the left panel to go directly to the SQL script directory. Select file 02_create_global_dynamic.sql and click Open.
3.	Select cachedb1 as the database connection. Click Run Script to create a dynamic AWT global cache group on the TTHR.EMPLOYEES and TTHR.JOB_HISTORY tables. The result from running the script is displayed in the Script Output Pane. Close the file 02_create_global_dyanmic.sql by clicking on the button X.
4.	Go back to the connections navigator, select Cache Groups and click Refresh to view the new cache groups. Select Tables and click Refresh to view the corresponding cache tables.

Start the Replication Agent and Attach cachedb1 to a Cache Grid

1.	To enable the propagation of AWT data between the cache tables and the Oracle database, the replication agent must be running. Right-click on the connection cachedb1 in the connections navigator, and select Start / Stop Replication Agent.
2.	Check the Start agent checkbox and click Apply. This starts up the replication agent on cachedb1. Click OK to close the confirmation dialog box.
3.	We will now attach cachedb1 to the grid. Click on Open to bring up the Open File dialog box. Select the script 03_attach_cachedb1.sql from the cache_grid_sql folder. Change the SQL Worksheet connection to cachedb1 and click Run Script.
4.	Script Output shows cachedb1 is now attached to the cache grid samplegrid. Close the file 03_attach_cachedb1.sql by clicking on the button X, located on the right of the tab.

Loading Data into Cache Groups

Data from the Oracle database can be preloaded into a cache group. Depending on the cache group type, an optional WHERE condition can also be specified to restrict or partition the data to be cached. For dynamic cache groups, data will be loaded on-demand when SELECT, INSERT, UPDATE or DELETE statement is issued targeting the cache tables. In this topic you use SQL Developer to preload the non-dynamic cache groups.

Preload data into an Asynchronous Writethrough Cache Group

1.	Check the content of the COUNTRIES cache tables. Go back to the Connection Navigator. Right click on the table COUNTRIES and select Table -> Count Rows. Click Apply to execute the Count Rows operation.
2.	The COUNTRIES cache table has been created, but the table is empty. Click OK to close the dialog.
3.	To load data into the cache group AWT, perform the following steps. Right click on the cache group AWT and select Load. This brings up the Load cache group dialog.
4.	Enter the following condition into the Where clause field. regions.region_id=1 In this example, cachedb1 caches only the countries from Europe (region_id=1). Click Apply to load all the countries belonging to region_id 1 into cachedb1. This also loads in the corresponding child records for COUNTRIES and LOCATIONS. Click OK to close the confirmation dialog box.
5.	Repeat the Count Rows operation. Right click on table COUNTRIES and select Table -> Count Rows. Click Apply.
6.	This time, it should return 8 rows. Click OK to continue.

Adding a Second Database into the Cache Grid

After setting up the first cache database cachedb1, you will now repeat some of the previous steps to add a second cache database cachedb2 into the cache grid.

The same Global cache groups must exist in all the grid members. Local cache groups do have this requirement, each grid member can define it's own set of local cache groups.

To create, configure and attach another cache database into the cache grid, perform the following steps:

1.	Create a database connection to cachedb2. In the Connections tab, click on the green + sign, a "New / Select Database Connection" dialog box appears Enter cachedb2 in the Connection Name field, and tthr in the Username and Password fields. Click on the TimesTen tab. Select cachedb2 from the DSN drop down list. Enter tthr in the Oracle Password (for Cache) field. Click Connect. The connection cachedb2 has been created. It should appear in the connections navigator. If not, expand out the connections and refresh.
2.	Run a SQL script to associate cachedb2 with the cache grid samplegrid. Click on Open to bring up the Open File dialog box. Select the script 04_associate_cachedb2_grid.sql from the cache_grid_sql folder. Change the SQL Worksheet connection to cachedb2 and click Run Script. The procedure ttGridNameset associates the cachedb2 to the cache grid samplegrid. Close the file 04_associate_cachedb2_grid.sql by clicking on the button X.
3.	Start the cache agent on cachedb2. Right-click on the connection cachedb2 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 cachedb2. Click OK to close the confirmation dialog box.
4.	Create the same global dynamic AWT cache group based on the employees and job_history tables in cachedb2. Click on Open to bring up the Open File dialog box. Select the script 02_create_global_dynamic.sql from the cache_grid_sql folder. Change the SQL Worksheet connection to cachedb2 and click Run Script. The result from running the CREATE CACHE GROUP script is displayed in the Script Output Pane. Close the file 02_create_global_dyanmic.sql by clicking on the button X.
5.	Go back to the Connection Navigator. Click on the + sign to expand cachedb2, if not already expanded. Expand Tables to verify EMPLOYEES and JOB_HISTORY tables have been created.
6.	Right-click on the connection cachedb2 in the connections navigator, and select Start / Stop Replication Agent. Check the Start agent check box and click Apply. This starts up the replication agent for cachedb2. Click OK to close the confirmation dialog box.
7.	Run a SQL script to attach cachedb2 to the cache grid samplegrid. Click on Open to bring up the Open File dialog box. Select the script 05_attach_cachedb2.sql from the cache_grid_sql folder. Change the SQL Worksheet connection to cachedb2 and click Run Script. The Script Output pane shows that both cachedb1 and cachedb2 are attached to the cache grid. Close the file 05_attach_cachedb2.sql by clicking on the button X.

Showing Cache Data Transactional Consistency and Synchronization between the Databases

In the previous sections, you learned how to create and configure a cache grid with two grid members, and how to create different types of cache groups. In this topic, you will learn about the properties of these cache groups, and verify the data transfer between the cache grid members and the data synchronization between the Oracle database and the Cache Grid.

Demonstrate data propagation from cache database to Oracle database in an AWT cache group

1.	Select the cachedb1 table COUNTRIES from the connections navigator. Note: COUNTRIES is an updatable cache table belonging to an AWT cache group. Updates to the cache tables are asynchronously write-through to the Oracle database.
2.	Click on the Data tab and view records cached in the COUNTRIES table.
3.	Open and run a script to update the TTHR.COUNTRIES table on cachedb1 using SQL Worksheet. Click on Open to bring up the Open File dialog box. Select the script 06_insert_countries.sql from the cache_grid_sql folder and click Open. Change the SQL Worksheet connection to cachedb1 and click Run Script.
4.	The Script Output pane confirms 4 countries have been inserted, and the country name for UK has also been updated. Close the file 06_insert_countries.sql by clicking on the button X.
5.	Go back to the data tab showing the COUNTRIES table from cachedb1, click Refresh to see the changes in the cache table.
6.	Now go to the oracle connection and check for the the updates in the corresponding COUNTRIES table. In the connections navigator, expand Tables under the connection oracle, and select the table COUNTRIES.
7.	Click on the Data tab to view the records in the Oracle COUNTRIES table. Notice the updates in the cache database have been replicated to the Oracle database. Remember the Oracle table contains countries from different regions, cachedb1 caches only those from region_id=1.

Demonstrate the interaction of global cache data between grid members with a global cache group

1.	Load a subset of data into global cache group G_AWT into cachedb1 Right click on the cache group G_AWT and select Load. This brings up the Load cache group dialog.
2.	Enter the following condition into the Where clause field. manager_id=100 In this example, cachedb1 caches only the employees who's manager's id is 100 (manager_id=100). Click Apply to load all the employees reporting to the manager with id=100 into cachedb1. Click OK to close the confirmation dialog box.
3.	In the connections navigator, select the table EMPLOYEES from the cachedb1 connection. Note: EMPLOYEES is an updatable cache table belonging to a global dynamic global cache group.
4.	Click on the Data tab to view the content in the cache table. Note: EMPLOYEES table now has 14 records loaded.
5.	Update Neena's phone number to 650.555.5555 Double click into PHONE_NUMBER cell, and replace the current phone number with 650.555.5555. Press Enter. Click Commit Changes to commit the change. The Data Editor - Log returns the result of the above operation.
6.	Run a SQL script to update Neena's record from the other grid member cachedb2 . Suppose another application connection wants to update the same set of records (Neena's record) from the other grid member, cachedb2. We will now demonstrate the location transparency functionality, where the desired record is automatically transferred from cachedb1 to cachedb2 when needed cachedb2 currently does not contain any employee records. Click on Open to bring up the Open File dialog box. Select the script 07_update_employees.sql from the cache_grid_sql folder. Change the SQL Worksheet connection to cachedb2 and click Run Script. The Script Output pane returns the result of the SQL operation. There are 3 SQL operations involved in this script: The SELECT count() operation confirms there are no employee records in cachedb2. The UPDATE operation on cachedb2* causes Neena's record to ship over from cachedb1. Within the same operation, Neena's salary is increased from “17000” to “18000”. The SELECT count() operation confirms that Neena's record is in cachedb2*.
7.	View the data in the EMPLOYEES table on cachedb2. In the connections navigator, expand the connection cachedb2, select the table EMPLOYEES. Click on the Data tab. Now we see that EMPLOYEES table on cachedb2 has the record for employee Neena, and her updated phone number and salary.
8.	Go back to the EMPLOYEES table in cachedb1 and check the content. In the connections navigator, select the table EMPLOYEES from the cachedb1 connection. Click on the Data tab. Note: EMPLOYEES table on cachedb1 now has only 13 records left (because Neena's employee record was shipped over to cachedb2).

Demonstrate global processing of a SQL query across multiple grid members within a global cache group

In this section, we will run a SQL script to find out the total number of employees and the highest salary among the employee records loaded into the cache grid (across all grid members).

Click on Open to bring up the Open File dialog box. Select the script 08_global_query.sql from the cache_grid_sql folder.

Show Screenshot for Step

Change the SQL Worksheet connection to cachedb1 and click Run Script.

Show Screenshot for Step

The Script Output pane returns the result of the SQL operation.

Show Screenshot for Step

There are 5 operations involved in this script:

The "autocommit off" operation switches off AutoCommit;
The "call ttOptSetFlag" operation enables "global query processing", allowing the following SQLs in this transaction to access data on all the nodes of a cache grid.
The "select count(*)" operation shows that there are 14 records in the employees table. This SQL was processed globally across the cache grid because we know that, after the previous experiment, there were 13 records in cachedb1 and only 1 record in cachedb2;
The "select max(salary)" operation returns the highest salary as 18000. Remember that the highest salary "18000" is from employee Neena, whose record is in cachedb2.
The "autocommit on" operation reverts AutoCommit back to on;

Note that, these "select count(*)" query and the "select max(salary)" query are executed on database cachedb1, but with "GlobalProcessing" enabled, they return the results reflecting both database cachedb1 and cachedb2.

Removing Cache Groups and Destroying a Cache Grid (optional)

In this topic you will learn how to detach cachedb1 and cachedb2 from the cache grid, and remove all the cache groups that were created in this tutorial.

Note that this section is to clean up the environment you have just created. You may skip this optional section.

Drop cache group and detach cachedb2 from the cache grid

1.	Before global cache groups can be dropped, the database must be detached from the cache grid. Run a SQL script to detach cachedb2 from the cache grid samplegrid. Click on Open to bring up the Open File dialog box. Select the script 09_detach_cachedb.sql from the cache_grid_sql folder. Change the SQL Worksheet connection to cachedb2 and click Run Script. The Script Output pane shows that only cachedb1 is now attached to the cache grid. Close the file 09_detach_cachedb.sql by clicking on the button X.
2.	To disable the propagation of AWT data from the cache tables to the Oracle database, the replication agent must be stopped. Right-click on the connection cachedb2 in the connections navigator, and select Start / Stop Replication Agent.
3.	Check the Stop agent checkbox and click Apply. Click OK to close the confirmation dialog box.
4.	Perform the following steps to remove the cache group from cachedb2. Click on the + sign next to Cache Groups to expand out the cache groups in cachedb2, if not already expanded.
5.	Right click on the cache group G_AWT and select Drop. This brings up the Drop cache group dialog box.
6.	Click Apply to drop the cache group. Click OK to close the confirmation dialog box.
7.	Expand Tables to verify the cache tables EMPLOYEES and JOB_HISTORY have been removed. If necessary, click on the icon Refresh to refresh the connections navigator.
8.	We will now stop the cache agent. Right-click on the connection cachedb2 in the connections navigator, and select Start / Stop Cache Agent.
9.	Check the Stop agent check box and click Apply. This stops the cache agent on cachedb2. Click OK to close the confirmation dialog box.

Drop cache groups and detach cachedb1 from the cache grid

1.	Run a SQL script to detach cachedb1 from the cache grid samplegrid. Click on Open to bring up the Open File dialog box. Select the script 09_detach_cachedb.sql from the sql folder. Change the SQL Worksheet connection to cachedb1 and click Run Script. The Script Output pane shows that no database is now attached to the cache grid. Close the file 09_detach_cachedb.sql by clicking on the button X.
2.	To disable the propagation of AWT data from the cache tables to the Oracle database, the replication agent must be stopped. Right-click on the connection cachedb1 in the connections navigator, and select Start / Stop Replication Agent.
3.	Check the Stop agent checkbox and click Apply. Click OK to close the confirmation dialog box.
4.	Perform the following steps to remove cache groups from cachedb1. Click on the + sign next to Cache Groups to expand out the cache groups in cachedb1, if not already expanded.
5.	Right click on the cache group AWT and select Drop. This brings up the Drop cache group dialog box.
6.	Click Apply to drop the cache group. Click OK to close the confirmation dialog box.
7.	Repeat steps 5 and 6 to drop the remaining cache group G_AWT in cachedb1 .
8.	Expand Tables to verify all the cache tables have been removed. If necessary, click on the icon Refresh to refresh the connections navigator.
9.	We will now stop the cache agent. Right-click on the connection cachedb1 in the connections navigator, and select Start / Stop Cache Agent.
10.	Check the Stop agent check box and click Apply. This stops the cache agent on cachedb1. Click OK to close the confirmation dialog box.

Run a SQL script to remove the Cache Grid

1.	Click on Open to bring up the Open File dialog box. Select the script 10_destroy_cache_grid.sql from the cache_grid_sql folder and click Open.
2.	The content of this file appears in a SQL Worksheet tab. Review the code. The procedure ttGridDestroy removes the cache grid "samplegrid". Go to the Worksheet connection drop down list on the right, and select cachedb1 as the database connection. Click Run Script to execute this script.
3.	The result from running the script is displayed in the Script Output Pane. The cache grid samplegrid has been removed.
4.	Select File -> Exit to close SQL Developer.

Summary

In this lesson, you learned how to:

	Create SQL Developer connections to TimesTen In-Memory databases
	Create and drop a cache grid using SQL Worksheet
	Create and drop different types of cache groups using SQL Developer
	Preload data to populate cache groups
	Load data on demand into dynamic cache groups
	Start up and shut down the cache and replication agents

Related Information

To learn more about In-Memory Database Cache you can refer to:

Oracle In-Memory Database Product Center on OTN - http://www.oracle.com/technology/products/timesten/imdb_cache

Viewing Screenshots

To create, configure and attach another cache database into the cache grid, perform the following steps: