Tuesday Apr 28, 2015

Back to Basics - Multiple Connections in ttIsql

A user recently asked if ttIsql can support multiple concurrent connections to a TimesTen database. We thought the answer might be of interest to other users as well, and decided to include it in this blog.

ttIsql is an interactive SQL command line utility for working with TimesTen databases. In addition to supporting SQL command execution, ttIsql provides a rich set of functionality to enable the users to connect to the databases, execute built-in procedures and utilities, support flexible command editing, and the ability to invoke host OS commands, all within the same ttIsql session.

One of the scenarios where it’s convenient to have multiple connections within the same ttIsql session is when you want to execute commands that require different user credentials.  For example, there are database operations that require the Admin privilege, and other operations that only require table owner or general database access privileges. 

If you plan to use multiple connections, it’s useful to name the connections so that ttIsql can help you clearly identify which connection you are executing. 

In the example below, we will create two connections to the sample database, sampledb – first connection as the application user (appuser) and the second connection as the Admin user (adm). We will use the CONNECT command and specify a connection name for each:

$ ttIsq

Command> CONNECT “DSN=sampledb;uid=appuser;pwd=appuser;ConnectionName=app_conn”;

Command> SELECT count (*) from mytable;

< 1000000 >
1 row found.

Command> CONNECT "DSN=sampledb; uid=adm; pwd=adm; ConnectionName=adm_conn";

adm_conn: Command>

In ttIsql, the last connection made is the current active connection, and the ttIsql prompt displays the connection name currently in use. As shown above, adm_conn is the connection currently in use.

adm_conn: Command> CALL ttRamPolicySet (‘manual’);

The ttRamPolicySet built-in procedure requires the ADMIN privilege; instead of exiting the current ttIsql session or starting another session, we conveniently use the multiple connection functionality here. As shown above, we executed a command to set the RAM policy to MANUAL using the Admin connection.

To switch to a different connection, you simply execute the USE command:

adm_conn: Command> USE app_conn;

app_conn: Command> CALL ttVersion();

< 11, 2, 2, 8, 0 >
1 row found.

Notice that after the USE command, the ttisql prompt changed to the app_conn to indicate the current connection context. ttVersion is a built-in procedure that returns the current TimesTen version, and it can be called by any user.

To disconnect a connection, use the DISCONNECT command:

app_conn: Command> DISCONNECT adm_conn;

Another useful feature in ttIsql is the ability to execute host OS commands. You can execute any OS commands that you are entitled to. As a simple example below, we executed the MORE command to view the content in myconfig.ini file:

app_conn: Command> HOST more /home/appuser/myconfig.ini

This is myconfig.ini file
Line 1
Line 2
Line 3

app_conn: Command> exit


There are many other features in ttIsql you can use to help automate your day-to-day operations. Refer to the TimesTen Database Reference product documentation for more details.

Wednesday Apr 01, 2015

New TimesTen Plug-in Release for Oracle Enterprise Manager

Last Friday, Oracle released a new version of TimesTen Plug-in for Oracle Enterprise Manager 12c Cloud Control. This is a landmark release with many new features for database administrators in the enterprise.

Besides database performance and availability monitoring; the new plug-in offers administrators the ability to manage and administer their TimesTen instances and databases, such as starting and stopping TimesTen services, loading and unloading databases to and from memory, and scheduling backups and restoring databases. Additionally, users can monitor database and replication activities, memory and disk usages, workload performance statistics, and identify longest running and most executed SQL statements.

International users will be pleased to know that the new TimesTen plug-in was globalized with its user interface translated to nine different languages, the same languages as available in Oracle Enterprise Manager.  

TimesTen plug-in is downloadable through Enterprise Manager Self-Update.

For more information, visit the TimesTen product center on the Oracle Technology Network.

Blog contributor: SimonLaw-oracle 

Thursday Mar 12, 2015

Product News on SUSE 12 and HP-UX Itanium

Thursday – March 12, 2015

Recently, we added support for two new platforms with the latest release of Oracle TimesTen In-Memory Database:

  • TimesTen is certified for use with SUSE Linux Enterprise Server 12 (download the same binary for Linux X86-64 bit)
  • TimesTen is certified for the HP-UX Itanium platform (v11.31)
The software is available for download from the Oracle Support site http://support.oracle.com

Tuesday Feb 24, 2015

Product News – TimesTen

In case you missed it, the latest TimesTen release, version, was released at the end of January for all supported platforms.  I want to call out a couple new features which provide significant performance improvement.

1.  Dramatic Increase in Parallel Replication Throughput

For users who have OLTP application workloads running at very high transaction rates and are using TimesTen Replication for high availability, the new replication feature in this release will be of interest to you.

If your Replication configuration is currently using the single threaded Replication mode, you may want to consider upgrading your Replication scheme to take advantage of the Automatic Parallel Replication (APR) feature, which was made available in the 11.2.2.x releases. APR provides a significant boost for replication throughput – the rate of transactions replicated from one TimesTen database to another TimesTen database on a remote host. The improvement varies depending on your workload; we have seen a factor of 2 to 3 times increase in the rate of replicated transactions. Automatic Parallel Replication supports both Synchronous 2-Safe and Asynchronous replication topologies using the Active-Standby Pair replication schemes.

When automatic parallel replication is enabled, transaction dependencies are tracked automatically and the commit ordering is honored when applying the transactions on the remote standby database. There are no application changes required to enable parallel replication, and the setting of replication parallelism is a user-specified configuration. The attribute name is ReplicationParallelism, you set it to a value greater than one (1) to enable parallel replication.

Many of the OLTP applications have transactions that are independent of each other. For example, deducting the balance of your account is independent of deducting the balance of my account; updating location of your mobile device is independent of the location of my device; for such applications, it is typically not necessary to apply these transactions in the same commit order when using replication for high availability.

In the release, users may enable a new Parallel Replication feature to relax enforcing commit ordering on the receiving hosts, when TimesTen determines the transactions have no dependencies on each other. This feature provides even more parallelism when applying the replicated transactions on the Standby and Subscriber databases. Depending on your transaction workload characteristics, we have seen close to 80% improvement in replication throughput after relaxing the commit ordering on the standby database. To relax the commit ordering on the receiving host, set the attribute ReplicationApplyOrdering to a value of 2 when configuring the Active-Standby Pair replication scheme.

In a replicated environment, the ability to replicate the rate of changes is important because if you have a failover, the rate of changes being replicated determines how close your Standby is synchronized with the Active. Ideally, they should be the same, or as close as possible if you choose to use asynchronous replication. 

Refer to the TimesTen Replication documentation for more details.

2.  Reduce database restart time using parallel read operations from database checkpoint file

In the release, database restart time can be significantly faster by enabling parallel threads to read the TimesTen database checkpoint files; this is especially useful when the checkpoint files are relatively large and reside on Solid State or Flash storage.

The parallel checkpoint reads feature is enabled by setting the new CkptReadThreads attribute. CkptReadThreads is a First Connection attribute and should be set prior to loading the database. The attribute value specifies the number of threads that TimesTen uses to read checkpoint files when loading the database to memory.

The default value of CkptReadThreads is set to 1 (generally intended for hard disk storage). When using Solid State Drives or Flash storage, users can set the CkptReadThreads attribute to a value from 2 to 8. The overall read rate from the SSD/Flash is best achieved by setting the attribute to a value between 4 and 8; actual performance may vary depending on device models.

Using the current generation of SSD and Flash storage with 8 parallel checkpoint read threads, it’s possible to achieve 2 GB/sec read rate using a single SSD device or a PCIe Flash card, and to achieve 3.4 GB/sec sustained read rate when using two SSD devices/Flash cards (via disk striping). This means, a TimesTen database of one terabyte in size can be loaded to memory in about 5 minutes.

To improve database restart time, consider using SSD/Flash devices for your TimesTen Checkpoint files, and enable the parallel checkpoint reads feature.

TimesTen software is available on the TimesTen Product Center on the Oracle Technology Network.

Wednesday Oct 08, 2014

TimesTen client support for OS X 10.9 (Mavericks)

A new TimesTen 11.2.2 client for OS X Mavericks is available for download from OTN.  

This client driver allows applications running on OS X to access TimesTen databases that reside on other supported operating systems.  Developers using applications such as SQL Developer may find this very valuable! 

Download the new TimesTen 11.2.2 client for OS X today at:


Also ... thanks to everyone who came to see us at Oracle OpenWorld last week!

Friday Sep 19, 2014

TimesTen at Oracle OpenWorld 2014

Coming to Oracle OpenWorld 2014 in San Francisco?  It's almost here!

It's also a great opportunity to learn more about the Oracle TimesTen In-Memory Database.   We have some exciting presentations, and will be available in the DemoGrounds to answer your questions!  

Check out the schedule below.  We hope to see you there! 

TimesTen In-Memory Database
Oracle OpenWorld 2014 Sessions
The Oracle OpenWorld session schedule and locations may change.
Please refer to the Content Catalog for the latest information.









Speed Up Your Applications with an Application-Tier In-Memory Database

Conference Session

Moscone North 131


Tips and Tricks for Using Oracle TimesTen In-Memory Database in the Application Tier

Meet the Experts Session

Moscone South 305



Application-Tier In-Memory Analytics: Best Practices and Use Cases

Conference Session

Moscone North 131

Visit us at the Oracle Database DEMOgrounds in Moscone South, Left
Oracle TimesTen In-Memory Database booth SLD-135
Oracle TimesTen Application-Tier Database Cache booth SLD-136

Thursday Dec 12, 2013

Oracle TimesTen In-Memory Database release is available for download

This release provides new capabilities that further improve performance and scalability for applications with extremely fast response time and very high throughput requirements, specifically for Online Transaction Processing (OLTP) workloads and Business Intelligence (BI) in-memory analytics workloads for the Oracle Exalytics In-Memory Machine. The release also contains bug fixes as detailed in the release notes.

[Read More]

Thursday Sep 26, 2013

Oracle TimesTen Software Update Release Availability

Oracle TimesTen In-Memory Database release is available for download. This release provides new capabilities that further improve performance and scalability for applications with extremely fast response time and very high throughput requirements, specifically for Online Transaction Processing (OLTP) workloads and Business Intelligence (BI) in-memory analytics workloads for Oracle Exalytics In-Memory Machine.

Feature highlights for Business Intelligence In-Memory Analytics workloads:

Performance and Scalability for In-Memory Analytics

The SQL Optimizer for in-memory analytics added a number of optimizations targeted for common star-join query patterns. These enhancements resulted in the optimizer choosing better query plans and speeding up overall query response time:

  • Enables up to 3-10x query response time improvements for BI workloads and selected Oracle BI Applications;
  • Enhanced in-memory query processing for star schemas
  • Full Hash Index support. Prior to this release, hash indexes were supported only for primary key columns. Hash indexes may now be created on any set of columns to help speed up complex queries with equi-joins;
  • Improved Index Advisor recommendations provide optimal indexes based on a specific query set.

Improvements to reduce data loading time

Data loading has been enhanced with the ability to support parallel, concurrent writes into a large compressed table. This feature significantly reduces the overall data loading time from the Data Warehouse to the Oracle TimesTen database in the Oracle Exalytics machine.

  • Enables up to 3x reduction in data loading time for compressed tables;
  • Improved data loading from the Oracle Database to TimesTen using the (ttLoadFromOracle) built-in function, with added support for parallel inserts to compressed tables;
  • New utility (ttImportFromOracle) to analyze the data from an Oracle Database; the utility generates DDLs for tables and indexes with optimal Oracle TimesTen native data types and recommendation for columnar compression.

Feature highlights for Online Transaction Processing (OLTP) applications:

Optimistic Concurrent B+ Tree index for write scalability

A new B+Tree indexing capability featuring optimistic concurrency control provides increased scalability for write-intensive applications. This B+Tree index feature provides better throughput with higher numbers of concurrent writers, and is ideal for applications with very high volumes of insert and update transactions running in modern computing architectures with many processing cores.

  • Greater concurrency support and higher transaction throughput for write intensive applications
  • Enables up to 5x throughput improvement compared to existing B-tree indexes

Full Hash Index support for read scalability

The Hash index is a unique feature in Oracle TimesTen. A Hash index offers better performance for equality look up of Primary Key and is commonly used by OLTP application running on Oracle TimesTen databases. This release added the ability to create hash indexes on all columns in the table.

  • Hash indexes can now be created for any single column and multiple-column indexes
  • Enables up to 7x throughput improvement for read-intensive applications

Improved Scalability for NUMA systems

Modern computers are designed with high number of cores per server with large quantity of DRAM available. Such systems commonly exhibit Non Uniform Memory Access (NUMA) variations in memory latency, for example, when accessing memory outside of the local processor (socket). TimesTen as an in-memory database runs at memory (DRAM) speed and is more sensitive to the NUMA effect than a conventional disk-based relational database, where data access is optimized at the disk storage level. Improving TimesTen scalability on NUMA systems is an on-going effort in every release. A number of NUMA locality enhancements and code path optimizations were added in the release, resulting in improved read and write scaling, especially on systems with very high number of processor cores such as the Oracle T5-8 servers, which have 128 cores and 1024 processing threads per server.

  • 59.9 Million reads per second on Oracle T5-8 machine
  • 1.03 Million update transactions per second on Oracle T5-8 machine

New Manageability Capabilities

New in this release is a utility for real-time monitoring and capturing snapshots of monitored metrics. This utility, ttStats, provides HTML reports of TimesTen database performance (similar to the Automatic Workload Repository (AWR) facility for the Oracle Database.) Also included in this new monitoring facility is a set of PL/SQL procedures for custom development. ttStats is a standalone TimesTen utility, invoked at the command line, and requires no additional software. ttStats can be used for real-time monitoring, snapshots captures, and HTML reports.

In addition to database level performance, the ability to time individual SQL statements is available in this release allowing the administrator to quickly identify long running operations.

Oracle TimesTen Quick Start Guide

A user-friendly, self-guided HTML Guide is available to get started with Oracle TimesTen. The Quick Start Guide uses the “How to” approach to provide easy to follow instructions for all components of the product, from database configurations, setups, to programming APIs, to configuring replication and cache grid, the instructions are provided step by step with screen shots and explanations for new and existing TimesTen users. The Quick Start Guide also added many new Best Practices for Oracle TimesTen database and OS settings, Replication, Caching from the Oracle Database, and Monitoring.

Download Oracle TimesTen now!

Oracle TimesTen release supports the following licensed products in the TimesTen product family: Oracle TimesTen In-Memory Database; Oracle TimesTen In-Memory Database for Exalytics; Oracle In-Memory Database Cache; and In-Memory Database Cache for Oracle Applications.

Friday Sep 28, 2012

ROracle support for TimesTen In-Memory Database

Today's guest post comes from Jason Feldhaus, a Consulting Member of Technical Staff in the TimesTen Database organization at Oracle.  He shares with us a sample session using ROracle with the TimesTen In-Memory database. 

Beginning in version 1.1-4, ROracle includes support for the Oracle Times Ten In-Memory Database, version 11.2.2. TimesTen is a relational database providing very fast and high throughput through its memory-centric architecture.  TimesTen is designed for low latency, high-volume data, and event and transaction management. A TimesTen database resides entirely in memory, so no disk I/O is required for transactions and query operations. TimesTen is used in applications requiring very fast and predictable response time, such as real-time financial services trading applications and large web applications. TimesTen can be used as the database of record or as a relational cache database to Oracle Database.

ROracle provides an interface between R and the database, providing the rich functionality of the R statistical programming environment using the SQL query language. ROracle uses the OCI libraries to handle database connections, providing much better performance than standard ODBC.

The latest ROracle enhancements include:

  • Support for Oracle TimesTen In-Memory Database
  • Support for Date-Time using R's POSIXct/POSIXlt data types
  • RAW, BLOB and BFILE data type support
  • Option to specify number of rows per fetch operation
  • Option to prefetch LOB data
  • Break support using Ctrl-C
  • Statement caching support

Times Ten 11.2.2 contains enhanced support for analytics workloads and complex queries:

  • Analytic clauses: OVER PARTITION BY and OVER ORDER BY
  • Multidimensional grouping operators:
  • Grouping functions: GROUP, GROUPING_ID, GROUP_ID
  • WITH clause, which allows repeated references to a named subquery block
  • Aggregate expressions over DISTINCT expressions
  • General expressions that return a character string in the source or a pattern within the LIKE predicate
  • Ability to order nulls first or last in a sort result (NULLS FIRST or NULLS LAST in the ORDER BY clause)

Note: Some functionality is only available with Oracle Exalytics, refer to the TimesTen product licensing document for details.

Connecting to TimesTen is easy with ROracle.  Simply install and load the ROracle package and load 
the driver.
   > install.packages("ROracle")
   > library(ROracle)
    Loading required package: DBI
   > drv <- dbDriver("Oracle")

Once the ROracle package is installed, create a database connection object and connect to a 
TimesTen direct driver DSN as the OS user.

   > conn <- dbConnect(drv, username ="", password="",
                   dbname = "localhost/SampleDb_1122:timesten_direct")

You have the option to report the server type - Oracle or TimesTen?

   > print (paste ("Server type =", dbGetInfo (conn)$serverType))
   [1] "Server type = TimesTen IMDB"

To create tables in the database using R data frame objects, use the function dbWriteTable.  
In the following example we write the built-in iris data frame to TimesTen. The iris data set is 
a small example data set containing 150 rows and 5 columns. We include it here not to highlight 
performance, but so users can easily run this example in their R session.

   > dbWriteTable (conn, "IRIS", iris, overwrite=TRUE, ora.number=FALSE)
   [1] TRUE

Verify that the newly created IRIS table is available in the database. To list the available tables and 
table columns in the database, use dbListTables and dbListFields, respectively.

   > dbListTables (conn)
   [1] "IRIS"
   > dbListFields (conn, "IRIS")

To retrieve a summary of the data from the database we need to save the results to a local object. The 
following call saves the results of the query as a local R object, iris.summary. The ROracle function 
dbGetQuery is used to execute an arbitrary SQL statement against the database. When connected to 
TimesTen, the SQL statement is processed completely within main memory for the fastest response 
   > iris.summary <- dbGetQuery(conn, 'SELECT
                                        AVG ("SEPAL.LENGTH") AS AVG_SLENGTH,
                                        AVG ("SEPAL.WIDTH") AS AVG_SWIDTH,
                                        AVG ("PETAL.LENGTH") AS AVG_PLENGTH,
                                        AVG ("PETAL.WIDTH") AS AVG_PWIDTH
                                       FROM IRIS
                                       GROUP BY ROLLUP (SPECIES)')

   > iris.summary
   1     setosa    5.006000   3.428000       1.462   0.246000
   2 versicolor    5.936000   2.770000       4.260   1.326000
   3  virginica    6.588000   2.974000       5.552   2.026000
   4       <NA>    5.843333   3.057333       3.758   1.199333

Finally, disconnect from the TimesTen Database.

   > dbCommit (conn)
   [1] TRUE
   > dbDisconnect (conn)
   [1] TRUE

We encourage you download Oracle software for evaluation from the Oracle Technology Network. See these links for our software: Times Ten In-Memory DatabaseROracle.  As always, we welcome comments and questions on the TimesTen and  Oracle R technical forums.

Thursday Jan 12, 2012

Announcing Oracle TimesTen In-Memory Database 11g Release 2!

Big news!  The new major release of the Oracle TimesTen In-Memory Database is out!  

TimesTen 11g Release 2 (aka TimesTen 11.2.2) is now available for download from oracle.com.  

Since it first started shipping to customers 14 years ago, TimesTen has been used for performance-critical applications in real-time online transaction processing (OLTP) applications.  The newest release continues that tradition, providing significant scalability and throughput improvements that will enable even faster OLTP applications in the future.  

These enhancements to TimesTen's already-blindingly-fast transaction processing capabilities are a perfect match for the Oracle Communications Billing and Revenue Management (BRM) product.  BRM provides advanced billing services for telecommunications service providers ... an area where TimesTen has been used since the beginning.  Oracle BRM now uses TimesTen 11.2.2 ... enabling BRM to provide significantly faster response time and higher throughput ... while simultaneously handling more subscribers than previous releases.  

This release of TimesTen also includes significant new features that make it easy to use the performance of TimesTen in business intelligence and analytics applications as well.  Features such as analytic SQL functions and advanced columnar compression are key to TimesTen's role in the new Oracle Exalytics In-Memory Machine.  Exalytics, running Oracle Business Intelligence Foundation Suite and TimesTen, enables profound response time improvements of up to 20x ... enabling "speed of thought" visualization not possible before.

Our team has worked hard on TimesTen 11.2.2.  We hope you like it!   


Press Release



Oracle Communications Billing and Revenue Management (BRM)

Oracle Exalytics In-Memory Machine

Oracle Business Intelligence Foundation Suite

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.


« February 2017