Tuesday Dec 20, 2011

Developing Applications using TimesTen

As we've discussed before, the TimesTen In-Memory Database is a fully featured relational database.  Because of its in-memory architecture it provides very fast response time, while providing standard APIs and interfaces as any relational database would. Due to these standard interfaces developing applications that run at in-memory speeds is easy with TimesTen, and leverages skills that developers already have.  


Like any relational database, data in TimesTen is stored in a schema consisting of tables.  Tables contain multiple rows of information; each row is made up of named columns.

The basic concepts used in TimesTen - schemas, users, tables, indexes, views, etc. - are compatible with Oracle Database.  

TimesTen is a fully transactional database ... work can be committed or rolled back as you expect.  TimesTen is also multi-user, supporting thousands of simultaneous connections to a single TimesTen database.  

Users are created and identified with passwords in ways that will be familiar to Oracle Database users, or users of other databases...and access to data is controlled by GRANT and REVOKE in standard ways.

The net is that TimesTen is a fully featured relational database, providing the same basic facilities as any other.  


TimesTen's SQL dialect is also very compatible with Oracle Database.  Tables are created with CREATE TABLE; industry-standard types such as CHAR and NCHAR are supported, as are Oracle's VARCHAR2 and NUMBER.  Sequences and views are created in the same way they would be in Oracle Database.  SELECT, INSERT, UPDATE, and DELETE are there just the way you expect them.  


Most users of Oracle Database are familiar with PL/SQL.  PL/SQL is a procedural language designed to be easy to use alongside SQL. TimesTen supports PL/SQL in many of the same ways that Oracle Database does.  TimesTen supports stored procedures, packages and functions written in PL/SQL, as well as anonymous blocks executed from application programs.  

TimesTen's dialect of PL/SQL is quite compatible with Oracle Database - in fact, TimesTen includes the standard PL/SQL compiler and runtime environment from Oracle Database unchanged.  


Application Program Interfaces (APIs) are used by application programs to execute SQL and PL/SQL.  JDBC is the standard API used to talk to databases from Java; ODBC and OCI are similar APIs for "C" and C++ languages.  TimesTen supports all three APIs.  It also supports the Pro*C preprocessor for applications that would prefer to use embedded SQL instead of a call-level interface.   And in Windows environments TimesTen supports ADO.NET via the Oracle Data Provider for .NET (ODP.NET).  

Tools and Environments

Because TimesTen supports standard SQL and standard APIs, it follows that TimesTen also works with a variety of tools built on top of them.  In the Java world, many standard object-relational mapping facilities such as Hibernate and EclipseLink work with TimesTen, as do application server environments such as Oracle Weblogic Server, GlassFish, Websphere, JBoss, and many others.  

SQL Developer is a fantastic tool that lets you explore and manage your tables, indexes and data in Oracle Databases and many others.  Out of the box SQL Developer fully supports TimesTen as well.  

And to manage your TimesTen installations with Oracle Enterprise Manager (EM), TimesTen provides a plugin which lets EM manage TimesTen databases.  


TimesTen supports the most commonly used concepts and facilities from Oracle Database.  Given the amazing capabilities of Oracle Database, not every feature in Oracle Database is supported in TimesTen.  But by providing the most commonly used interfaces from Oracle in a compatible manner, TimesTen is easy to learn and to use. Allowing you to re-use existing concepts, schemas and code can greatly accelerate the development of fast in-memory solutions for your business.  

In short, since the same tools, APIs and interfaces are used when developing for TimesTen as for Oracle Database, developing applications to run at in-memory speed with TimesTen is easy for database developers.  Give it a try!  


Details of TimesTen's SQL are in the TimesTen SQL Reference.

Manuals describing how to use TimesTen from C and Java, and with PL/SQL, are on the main TimesTen documentation page

Information on integrating TimesTen with application servers, SQL Developer, Oracle Enteprise Manager, and other environments is located on the main TimesTen page at OTN.  

Monday Dec 12, 2011

TimesTen and In-Memory Database Cache

Several recent posts I’ve written so far are intended to help explain the basics of what the Oracle TimesTen In-Memory Database is. If you’ve been reading along, you know that TimesTen is a very fast relational database that provides standard interfaces like SQL, PL/SQL, JDBC, ODBC and OCI to access data. TimesTen provides this very fast access to data in large part due to its “in-memory” architecture. TimesTen, unlike most databases, stores all data in RAM. (A copy is also maintained on disk.)

Because TimesTen stores data in RAM, the size of a TimesTen database is limited by the amount of physical RAM on a machine. On modern 64-bit systems this isn’t much of a limitation! Today (late 2011) systems can be purchased which have several Terabytes of RAM, and future hardware will naturally support even more.

But even though I really like TimesTen, I have to admit … some databases are bigger than that. J Traditional database systems, like Oracle Database itself, store data primarily on disk. Oracle Database is obviously a fantastic product, and one of the benefits it brings is the ability to support very large databases easily … ones larger than could be cost-effectively handled in memory in many cases.

So TimesTen is a fully featured database, and it can be used on its own to provide data storage for many applications. But in many cases what we really want is the best of both worlds … the fast memory-centric performance of TimesTen, combined with the large capacity and familiarity of the Oracle Database. If only you could have both!

Database Caching

Enter the “In-Memory Database Cache”. IMDB Cache is TimesTen … with a few extra tricks. It lets one or more TimesTen databases be used “in front” of a back-end Oracle Database to cache “hot” data.

There are a number of different ways that IMDB Cache can be used. Just to get across the basic concept, here’s one particular scenario. Suppose you run a “shopping” website, where customers log on, browse your catalog of goods, and place orders. All that data is stored in an Oracle Database. Your application runs in Java application servers in the mid-tier. Alongside the application server of your choice, you run TimesTen on the application server. TimesTen is configured to cache tables from the Oracle Database.

Let’s say user “Scott” logs on to your website. His first click on the site accesses his customer profile from the local TimeTen database. But Scott hasn’t been to your site for some time, so his data isn’t present in TimesTen. So under the covers, TimesTen automatically fetches Scott’s data from the Oracle Database, storing it in TimesTen for future reference. This initial load operation can load a number of items from a number of different tables. In this case, we’ll cache Scott’s preferences (such how he likes his web pages to be formatted) as well as his shopping cart contents and recent order history.

So once Scott has logged in to the site, everything about Scott has been loaded into TimesTen. His subsequent clicks on the site are all handled by TimesTen. Queries are handled by TimesTen exclusively; when items are changed – for example, if Scott adds an item to his shopping cart – those changes are made by the application in TimesTen, which automatically reflects them in Oracle Database as well.

When Scott logs off from the site, the copy of his data stored in the local TimesTen database can be removed, freeing up RAM to be used for other active users. And if Scott simply goes to lunch, when RAM is needed for other users then Scott’s data can be automatically reclaimed.

In this scenario, the bulk of database workload of your site is handled in RAM, by the TimesTen database. The TimesTen database automatically interacts with Oracle Database to cache information for active users of the site. Users to your site see the fastest possible response time, and workload on your Oracle Database is reduced.

Since TimesTen’s APIs and interfaces are very compatible with Oracle Database, the best part is that your application can manage data in a single relational schema, with a single set of tools and APIs (SQL, PL/SQL, JDBC, etc.). You can re-use code and schema used to manipulate data in the Oracle Database to implement a high performance in-memory cache … without needing to invent a new data model or data access library. The cache is itself a fully featured relational database … TimesTen.

As I said, there are many different examples of how TimesTen (er, the IMDB Cache) can be used to work with Oracle Database to provide scalable and high performance solutions. We’ll discuss this in more detail in future articles, I’m sure!


Oracle In-Memory Database Cache User’s Guide … Cache Concepts

White Paper: Using In-Memory Database Cache to Accelerate the Oracle Database:

Friday Dec 09, 2011

Direct And To The Point

In my previous post I explained how the TimesTen In-Memory Database is a very fast relational database. Like any relational database, TimesTen executes standard SQL via standard APIs Because of TimesTen’s “In-Memory” architecture, TimesTen can often use fewer CPU cycles than competing technologies to achieve the same result … yielding very fast response times.

In addition to its use of RAM, TimesTen provides another feature which helps it to provide very fast performance.

Relational databases, including TimesTen, let applications running on many machines access the database. Applications make use of a client library or driver which provides the database API. When the application calls that API – to execute SQL or fetch a row, for example – the client library formats the request from the application into messages, which it then transmits to the database server. Typically TCP/IP is used for communication.

On the database server, a process reads those messages from the network and processes them…accessing or modifying data in the database. Any results from the operation are then, in turn, packaged up into messages and sent back to the client from the database server.

This “client/server” design makes a lot of sense, and nearly all databases – including TimesTen – support it. However, TimesTen supports another alternative as well.

Direct Mode

TimesTen actually provides two different sets of libraries/drivers that applications can use. Both sets provide the same APIs – JDBC, ODBC, OCI, Pro*C. One set, the “client/server drivers”, work as explained above. The others, called “direct mode” drivers, work in a more streamlined manner.

The TimesTen database engine is included in its direct-mode drivers. When an application calls a direct mode driver to execute SQL or fetch rows the driver doesn’t package that request into messages. Instead, the driver in turn directly calls the TimesTen database engine, which executes the request directly. This is done without the overhead of packaging requests and responses into messages, and without the overhead of transmitting those messages on a network. In fact, since the database engine runs in the customer’s application process, there isn’t even any context switch or IPC (inter-process communication) overhead!

By using direct mode drivers instead of client/server drivers, applications can achieve dramatic performance results. SQL can easily be executed in microseconds (millionths of a second) instead of the typical milliseconds or more. When combined with the fast performance provided by TimesTen’s in-memory architecture, direct mode applications can achieve levels of performance that are hard to beat.


Since both direct mode and client/server mode drivers provide the same APIs and capabilities, applications don’t need to be modified to use direct mode. Both modes can be used simultaneously, so some very response time critical applications might use direct mode while other, less critical applications use client/server.

When applications run on the same machine that contains the TimesTen database, they can use either TimesTen’s client/server or direct mode drivers. If the application runs on a different machine than TimesTen then the client/server drivers must be used.

Even in client/server mode, TimesTen provides very fast performance. But by eliminating the overhead of network round-trips and context switches entirely, direct mode can provide applications with the fastest possible access to data, while still using standard SQL, PL/SQL, and APIs.


Using direct or client/server database connections from JDBC: http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13068/writing_app.htm#BABJIJHI

Using direct or client/server database connections from “C” applications: http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e13066/writing_app.htm#CEGGJGDG

Tuesday Dec 06, 2011

What is TimesTen?

Since this blog is about the Oracle TimesTen In-Memory Database, let’s start by explaining what it is. The product documentation contains an introduction, and the Oracle Technology Network has several overview presentations that get into the details. But here’s another quick version.

TimesTen is…

  • A relational database
  • Providing standard APIs and interfaces
  • That runs very fast through its memory-centric architecture
  • It can be used standalone
  • Or in conjunction with an existing Oracle Database
  • To provide extremely fast response time and high throughput

Let’s talk about each of those points in more detail.

A relational database

Like Oracle Database or MySQL, TimesTen is a relational database. It is used to store and retrieve data from applications. Applications use Structured Query Language (SQL) to access data in TimesTen databases. Transactions allow applications to safely implement complex operations in a multi-user environment, as in any other relational database. TimesTen databases are persistent … data stored in TimesTen is stored on disk and can be made highly available even in the event of disk or system failure.

Standard APIs and interfaces

Also like any relational database, TimesTen provides standard APIs like JDBC (for Java) and ODBC (for “C” / C++). Applications use these APIs to execute SQL statements against TimesTen databases to store and retrieve data.

The APIs and SQL that TimesTen provides are compatible with the facilities provided by the Oracle Database. Our SQL dialect is compatible with theirs … with datatypes like VARCHAR2, NUMBER and CLOB. TimesTen supports the PL/SQL language for writing stored procedures. And TimesTen also supports Oracle-specific APIs and tools such as OCI and Pro*C .

Memory Centric Architecture

TimesTen was written from the ground up around the idea that all the data in a TimesTen database resides in RAM – memory. It’s also stored on disk, for persistence … in case the power goes out, for example. But while most databases use RAM as a cache of recently-used data from disk, TimesTen stores all data in RAM all the time.

It turns out that this simple change in architecture can yield some big benefits … letting us simplify algorithms used under the covers for searching and storing data. That means that TimesTen can execute SQL with fewer instructions than many databases, resulting in faster response time.

Standalone Database…

Since TimesTen is a fully featured relational database, it can be used by itself to store data for applications. Even though it’s called an “in memory database”, data in TimesTen is also stored on disk. TimesTen also supports replication of data between TimesTen databases, which can be used to provide fully fault tolerant solutions for even the most demanding 24/7 applications.

…or used in conjunction with Oracle Database

TimesTen can also be used in conjunction with an Oracle Database. For example, you might use a TimesTen database residing on your application server to cache data from your Oracle Database. For example, as users log on to your website you might cache their customer profile, shopping cart and order history in the TimesTen database. TimesTen can automatically work with Oracle Database to move data back and forth as needed.

This gives your users the response time benefits of an in-memory solution with the ability to store many terabytes of data of Oracle Database. The best of both worlds!

Extremely fast response time and high throughput

By storing data in memory, and letting data be accessed at RAM speeds instead of network speeds, TimesTen can provide extremely fast response time… measured in microseconds (millionths of a second), rather than seconds or milliseconds (thousandths of a second).

Useful Links

If you’re just learning about TimesTen, this too-short introduction probably has left you with more questions than you had before. Check out the resources below for more details…and future posts here will dive deeper into some of the topics as well.

Monday Dec 05, 2011

Welcome to this new blog!

Hi, my name is Sam Drake; I am an Architect here at Oracle. I have worked for the past fifteen years on the Oracle TimesTen In-Memory Database. In this blog I’ll tell you a little bit about TimesTen … what it is, how it works, and how you can use it.

[Read More]

This blog covers the Oracle TimesTen In-Memory Database and the TimesTen Application-Tier Database Cache. The blog is maintained by Sam Drake. He has worked on the TimesTen product for more than 15 years, and is presently an Architect working in TimesTen development at Oracle.


« July 2016