When Microseconds CountBy Jonathan Gennick
The Oracle TimesTen in-memory database is always ready.
Oracle Corporation has made several strategic acquisitions over the past couple of years, and one of the more interesting is an in-memory database called TimesTen. Oracle TimesTen enables the development of mission-critical, real-time applications in which response time must be measured in microseconds, not milliseconds. It can be used standalone or as a fast, bidirectional cache for frequently accessed data from Oracle Database.
An emergency response dispatch system, the sort you might get connected to by dialing 911 in the U.S., is a good example of an application that can benefit from Oracle TimesTen. (In fact, Oracle TimesTen is actually deployed in at least one 911-related application.) Response time in such systems is more than critical—it's a matter of life and death.
When you dial 911 in most parts of the U.S., the dispatcher immediately sees your phone number and address on the screen, and often the system provides a map showing your location and perhaps even information on known health problems at your location. For example, a patient with a severe allergy who has a prescribed epinephrine autoinjector may choose to record that information with emergency-response dispatchers. Immediate access to location and other critical information is key to getting help dispatched quickly.
This article describes the implementation of a rudimentary database system that might be used to display critical information to emergency-response dispatchers. It shows how Oracle TimesTen can work with Oracle Database to provide reliable, microsecond response time, ensuring that critical data is always available instantly.
Figure 1 shows a view of the architecture behind Oracle TimesTen. In the context of Oracle TimesTen, a database is referred to as a data store . The on-disk representation of a data store is called a checkpoint file . When a data store is first opened, the entire contents of the data store are read into memory from the checkpoint file. Subsequent INSERT, DELETE, SELECT, UPDATE, and other database operations take place in memory. Data store changes from those operations are periodically and asynchronously written to the on-disk checkpoint file. When the data store is shut down, any remaining unwritten changes are written to the checkpoint file before the data store is closed.
Oracle TimesTen also uses disk storage as a recovery mechanism to protect transactions. Transactions are recorded in log files. If the system fails and is restarted, the checkpoint file will be read into memory, the transaction logs will be applied, and the data store will be open for business. The periodic writing of database changes to the checkpoint file during regular operation minimizes the time needed for any such recovery operation that might occur.
Oracle TimesTen can run as described as a standalone database, but it can also optionally run as a performance accelerator for data moving to or from Oracle Database. Figure 1 shows the cache agent and replication service that can connect Oracle TimesTen to Oracle Database. In this article's scenario, some data is stored in Oracle TimesTen to illustrate the standalone approach and other data is stored in Oracle Database to illustrate performance acceleration. You can combine both approaches as needed.
Creating an Oracle TimesTen Database
Oracle TimesTen runs on several platforms, including various Linux distributions, Windows XP and Windows Server 2003, Solaris, and HP-UX. The Oracle TimesTen In-Memory Database Installation Guide provides install instructions for all supported platforms. The Windows installation is very straightforward and is what was used to run the example in this article.
Access to Oracle TimesTen is ultimately through ODBC. (Java applications use JDBC, and the TimesTen JDBC driver, in turn, uses ODBC.) Creating an Oracle TimesTen data store is as simple as defining an ODBC datasource and then connecting to it. Assume, for the purposes of this article, that you have an Oracle Database schema named DISPATCH with the tables shown in Listing 1. Further assume that you have installed Oracle TimesTen and your emergency response dispatch application, the one that brings up critical information when a call comes in, on a Windows server. Use the following steps to create an Oracle TimesTen data store to support the dispatch application and cache critical data from Oracle Database:
Code Listing 1: DISPATCH schema in Oracle Database 10g
CREATE TABLE phones ( phone_num VARCHAR2(8), street_addr VARCHAR2(20), city VARCHAR2(15), PRIMARY KEY (phone_num)); CREATE TABLE call_log ( call_num NUMBER(9), event_time TIMESTAMP, event VARCHAR2(80), PRIMARY KEY (call_num, event_time)); GRANT SELECT, UPDATE, INSERT ON call_log TO ttdispatch; INSERT INTO phones VALUES ('555-1234','100 W. Munising Ave', 'Munising'); INSERT INTO phones VALUES ('555-2345','101 E. Varnum', 'Munising'); INSERT INTO phones VALUES ('555-3456','E2904 S. First', 'Trenary'); INSERT INTO phones VALUES ('555-4567','N3284 M-67', 'Limestone'); INSERT INTO phones VALUES ('555-5678','N7569 Spruce St.', 'AuTrain'); INSERT INTO phones VALUES ('555-6789','112 Colwell', 'Grand Marais'); COMMIT;
1. Go to the Control Panel, and open the ODBC Data Source Administrator. Select Start -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC) .
2. Add a system datasource, using the TimesTen Data Manager 6.0 driver.
3. From the Data Store tab (see Figure 2), enter a datasource name (DSN), a data store path, and a log directory. The data store path should end in a filename, but do not provide an extension.
4. Go to the General Connection tab, find the User ID field, and type the name of an Oracle Database user with access to the tables in Listing 1. You can use the schema owner's name here.
5. Go to the Cache Connect tab, find the Oracle Password field, and enter a password for the username used in Step 4. Then find the Oracle ID field, and enter the net service name (from tnsnames.ora) for Oracle TimesTen to use when connecting to Oracle Database.
6. Click OK to create the ODBC datasource.
Note: When running under Linux or other UNIX-based systems, you define a datasource by editing a .odbc.ini file. See the Oracle TimesTen In-Memory Database Operations Guide for details.
7. Connect to your newly created ODBC datasource, using the Oracle TimesTen interactive SQL utility ttIsql (analogous to SQL*Plus for Oracle Database). Then issue a CONNECT command to open the datasource, thereby creating the data store. For example
C:\A>ttisql Copyright (c) 1996-2006, Oracle. . . . Command> CONNECT dsn=ttdispatch; . . . Connection successful:
You now have a running Oracle TimesTen data store in memory. By default, Oracle TimesTen automatically creates a data store the first time it is used. To see a list of system tables in that data store, enter the TABLES command. Type HELP to get a list of all available ttIsql commands. Be sure to type a semicolon ( ; ) after any ttIsql command you execute.
In your data store path directory (c:\a\timesten in Figure 2), you'll see the two copies of the checkpoint file with the extensions .ds0 and .ds1. The name of these files is the name you provided in Step 3 above. In your log directory (also c:\a\timesten in this example), you'll see the log files with the extension .log.
By using the TimesTen Data Manager 6.0 driver (in Step 2), you enable your application to interact directly with the data store. There are no context switches and no queries sent out over the network—just direct, fast access to the data. Multiple applications using the data store all share access to the data store through a shared memory segment.
By default, Oracle TimesTen reads all data into memory when the first user connects to a data store and writes data back to disk when the last user disconnects. This is one of the ways Oracle TimesTen approaches zero maintenance. You have additional options here, though. For example, you can set Oracle TimesTen to read a data store into memory upon server startup, so that the data is already there when the first user connects.
You create a table in Oracle TimesTen just as you would in any other database. For example, create the following table to allow residents to record potentially lifesaving data for emergency dispatchers to access:
CREATE TABLE emergency_info ( phone_num VARCHAR(8), info VARCHAR(160), PRIMARY KEY (phone_num));
Now you can record comments that might prove helpful to dispatchers and responders in an emergency:
INSERT INTO emergency_info VALUES ('555-1234', 'Child Jeff allergic to egg white. Epinephrine autoinjector in orange box near refrigerator.'); INSERT INTO emergency_info VALUES ('555-2345', 'Bedridden resident needs help exiting home in case of fire.');
Oracle TimesTen datatypes are not exactly the same as datatypes in Oracle Database. For example, in Oracle TimesTen, you use VARCHAR rather than VARCHAR2. See the Oracle TimesTen In-Memory Database API and the SQL Reference Guide documentation for detailed information on datatypes, including how Oracle Database datatypes can be mapped onto Oracle TimesTen datatypes.
Caching Read-Only Data
Phone number and address data in an emergency dispatch system is likely to be read-only. You want dispatchers to see the address for a given phone number, but you don't want those same dispatchers distracted from their jobs by having to edit that address information. It's reasonable, then, to make the PHONES table read-only for dispatchers.
Before you create a read-only cache of phone/address data, create a cache administrator user in Oracle Database. This user owns the triggers and tables that Oracle TimesTen creates in Oracle Database to track changes to data so that those changes can be used to efficiently refresh the cached data in Oracle TimesTen. For example, execute the following statement while logged in as the system user to create a cache administrator named ttdispatch:
CREATE USER ttdispatch identified by ttdispatch default tablespace users quota unlimited on users;
Then make the following grant to allow the cache administrator to create triggers on tables owned by other users (such as the DISPATCH schema owner):
grant create any trigger to ttdispatch;
Next, log in as the dispatch schema owner and grant SELECT access on the PHONES table to the cache administrator:
GRANT SELECT ON phones TO ttdispatch;
Now, switch over to Oracle TimesTen and create a cache group. To do this,
1. Connect to your data store from ttIsql.
2. Make a call to the built-in ttCacheUidPwdSet() procedure and specify the username/password for Oracle TimesTen to use when connecting to Oracle Database as a cache administrator.
3. Call ttCacheStart() to start the TimesTen cache agent, which is responsible for doing the actual work of retrieving data from Oracle Database and caching that data in Oracle TimesTen.
4. Execute a CREATE CACHE GROUP statement to define a group of related tables—in this case, only one table—to be cached in Oracle TimesTen.
Listing 2 shows all four of these steps.
Code Listing 2: Creating a read-only cache group
Command> connect dsn=ttdispatch; Connection successful: . . . Command> call ttCacheUidPwdSet('ttdispatch','ttdispatch'); Command> call ttCacheStart(); Command> Command> CREATE READONLY > CACHE GROUP phone_data > AUTOREFRESH > INTERVAL 5 MINUTES > FROM dispatch.phones ( > phone_num VARCHAR(8) NOT NULL PRIMARY KEY, > street_addr VARCHAR(20), > city VARCHAR(15) > ); Warning 5112: Cache table DISPATCH.PHONES contains VARCHAR column(s). Oracle VARCHAR comparison rule is different.
Note that the warning message at the end of Listing 2 is a reminder that Oracle TimesTen considers the empty string ('') to be non-null whereas Oracle Database treats the empty string as a null. You'll need to be careful about this difference in behavior when writing queries involving VARCHAR columns.
You now have a cache group named phone_data. Within that group is a single, empty table named PHONES. The cache group is set to automatically refresh, polling Oracle Database for data changes at five-minute intervals. However, that automatic refresh is initially created in a paused state. Issue the LOAD CACHE GROUP statement shown in Listing 3 to initialize the cache with current data from Oracle Database and to take the cache group out of pause. From this point forward, Oracle TimesTen will query Oracle Database every five minutes (you can specify larger or smaller intervals) for changes to the PHONES table. The triggers and support tables owned by the cache administrator (ttdispatch in this case) make such polling very efficient.
Code Listing 3: Performing the initial load
Command> SELECT * FROM phones; 0 rows found. Command> LOAD CACHE GROUP phone_data > COMMIT EVERY 100 ROWS; 6 rows affected. Command> select * from phones; < 555-1234, 100 W. Munising Ave, Munising > < 555-2345, 101 E. Varnum, Munising > < 555-3456, E2904 S. First, Trenary > < 555-4567, N3284 M-67, Limestone > < 555-5678, N7569 Spruce St., AuTrain > < 555-6789, 112 Colwell, Grand Marais > 6 rows found.
Creating a Write-through Cache
Whereas phone/address data comes from Oracle Database and goes into Oracle TimesTen, you want call log data to flow in the other direction. Dispatchers will log events pertaining to a 911 call into Oracle TimesTen. However, you want log data to propagate to Oracle Database for long-term storage. To that end, you can create an asynchronous write-through cache group. Listing 4 shows the Oracle TimesTen statement for doing that.
Code Listing 4: Creating a write-through cache
CREATE ASYNCHRONOUS WRITETHROUGH CACHE GROUP call_log_data FROM dispatch.call_log ( call_num BIGINT, event_time TIMESTAMP, event VARCHAR(80), PRIMARY KEY (call_num, event_time) ) UNIQUE HASH ON (call_num, event_time) PAGES=1000;
After creating the group, start the Oracle TimesTen replication service, by invoking the built-in ttRepStart procedure from ttIsql:
From this point forward, rows your dispatch application inserts into the Oracle TimesTen CALL_LOG table will be inserted into the CALL_LOG table in Oracle Database. Because the cache group is asynchronous, Oracle TimesTen will commit transactions involving inserts into CALL_LOG without waiting for the corresponding inserts to commit in Oracle Database. If the connection to Oracle Database is interrupted, Oracle TimesTen will keep track of newly inserted log entries and ship them to Oracle Database when the connection is reestablished.
An asynchronous write-through cache gives you the best performance. However, you can create a synchronous cache group if you want to ensure that when a transaction involving the CALL_LOG table is committed in Oracle TimesTen, any changes to the CALL_LOG table have also been committed in Oracle Database. The choice is entirely yours to make, depending on your business needs.
If you're not careful about your application design, you will run the risk that inserts into the CALL_LOG table that commit successfully in Oracle TimesTen will fail when pushed to Oracle Database. Any such replication errors are recorded in an asynchronous write-through error file, having the extension .awterr, in the same directory that contains the checkpoint files.
One way to avoid problems with replication is to think carefully about which database will "own," or control, a given table or set of related tables. In the dispatch scenario, all inserts into the CALL_LOG table are done in Oracle TimesTen. No data changes are allowed from the Oracle Database side. Thus, all inserts should propagate from Oracle TimesTen to Oracle Database without error.
Aging Out Old Records
A table such as CALL_LOG will grow forever. You can't keep all of a table like that in an in-memory database. You'll want to make provisions to purge older log records. One approach you might take is to retain only the current day's log entries in Oracle TimesTen, leaving the job of longer-term, archival storage to Oracle Database. To that end, you can execute the UNLOAD CACHE GROUP statement in Listing 5 on a daily basis. The statement will delete all but the current day's log entries from Oracle TimesTen while leaving the corresponding CALL_LOG table in Oracle Database untouched. Dispatchers can then access the current day's log entries from Oracle TimesTen. Anyone with a need to look at log entries from past days can query Oracle Database.
Code Listing 5: Aging of old call logs
UNLOAD CACHE GROUP call_log_data WHERE NOT( EXTRACT(YEAR FROM EVENT_TIME) = EXTRACT(YEAR FROM SYSDATE) AND EXTRACT(MONTH FROM EVENT_TIME) = EXTRACT(MONTH FROM SYSDATE) AND EXTRACT(DAY FROM EVENT_TIME) = EXTRACT(DAY FROM SYSDATE) );
Is Memory a Silver Bullet?
Because Oracle TimesTen derives its performance benefits from keeping an entire data store in memory, why not simply run Oracle Database and configure the buffer cache to be large enough to hold an entire database in memory? Wouldn't Oracle Database then perform just as well as Oracle TimesTen? This is a good and fair question, worthy of some attention.
Oracle TimesTen was designed from the ground up as an in-memory database. There are no logical I/Os in the sense that there are in Oracle Database. There are no database blocks. There is no buffer cache. Index entries, rather than containing logical row IDs, point directly to the memory locations where their target rows can be found. Going from an index entry to a row in Oracle TimesTen requires the simple dereferencing of a pointer. When using the TimesTen Data Manager 6.0 driver, your application has direct access to the memory holding the data; no costly context switches are needed.
However, memory is not a silver bullet. Much has been written over the past few years about the nonzero cost of logical I/Os in Oracle Database, and although Oracle TimesTen may get the cost of accessing in-memory data much closer to zero, there is still a cost. Good design is still important. Queuing theory still applies. Performance optimization to reduce unneeded work is still important.
Benefits of TimesTen
Oracle TimesTen is flexible in ways well beyond what you've seen here. In addition, with Oracle TimesTen
Combined with sound database and application design, the in-memory performance of Oracle TimesTen enables time- and mission-critical database-backed applications. When minimizing microseconds can save money or lives, Oracle TimesTen In-Memory Database can deliver.
Thanks to Sam Drake and Simon Law at Oracle for their patience in answering many questions for this article; to the Alger County, Michigan, Sheriff's Department for providing a detailed tour of its dispatch center; and to Alger County Emergency Medical Services for inspiring the example scenario used in this article.
Jonathan Gennick (www.gennick.com) is an experienced Oracle professional and member of the Oak Table Network. He wrote the best-selling SQL Pocket Guide and the Oracle SQL*Plus Pocket Reference, both from O'Reilly Media.