1 Oracle TimesTen In-Memory Database and TimesTen Application-Tier Database Cache

Oracle TimesTen In-Memory Database (TimesTen) is a memory-optimized relational database that empowers applications with the responsiveness and high throughput required by today's real-time enterprises and industries such as telecom, capital markets and defense. TimesTen Application-Tier Database Cache (TimesTen Cache) uses the Oracle TimesTen In-Memory Database as its RDBMS engine. Deployed in the application tier as an embedded database, Oracle TimesTen In-Memory Database operates on databases that fit entirely in physical memory using standard SQL interfaces. High availability for the in-memory database is provided through real-time transactional replication.

TimesTen Application-Tier Database Cache (TimesTen Cache) is an Oracle Database product option ideal for caching performance-critical subsets of an Oracle database for improved response time in the application tier. Cache tables can be read-only or updatable. Applications read and update the cache tables using standard SQL, and data synchronization between the cache and the Oracle database is performed automatically. TimesTen Application-Tier Database Cache offers applications the full generality and functionality of a relational database, the transparent maintenance of cache consistency with the Oracle database, and the real-time performance of an in-memory database.

Oracle TimesTen In-Memory Database and TimesTen Cache deliver real-time performance by changing the assumptions about where data resides at run time. By managing data in memory and optimizing data structures and access algorithms accordingly, database operations execute with maximum efficiency, achieving dramatic gains in responsiveness and throughput, even compared with a fully cached, disk-based relational database management system (RDBMS). TimesTen and TimesTen Cache libraries are also embedded within applications, eliminating context switching and unnecessary network operations, further improving performance.

Following the standard relational data model, you can use SQL, JDBC, ODBC, PL/SQL, and Oracle Call Interface (OCI) to access TimesTen and TimesTen Cache databases. Using SQL to shield applications from system internals allows databases to be altered or extended without impacting existing applications. New services can be quickly added into a production environment simply by adding application modules, tables, and columns. As with any mainstream RDBMS, a cost-based optimizer automatically determines the fastest way to process queries and transactions. Any developer familiar with the Oracle database or SQL interfaces can be immediately productive developing real-time applications with TimesTen and TimesTen Cache.

Why is Oracle TimesTen In-Memory Database fast?

Much of the work that is done by a conventional, disk-optimized RDBMS is done under the assumption that data primarily resides on disk. Optimization algorithms, buffer pool management, and indexed retrieval techniques are designed based on this fundamental assumption.

Even when a disk-based RDBMS has been configured to hold all of its data in main memory, its performance is hobbled by assumptions of disk-based data residency. These assumptions cannot be easily reversed because they are hard-coded in processing logic, indexing schemes, and data access mechanisms.

TimesTen is designed with the knowledge that data resides in main memory and can take more direct routes to data, reducing the length of the code path and simplifying algorithms and structure.

When the assumption of disk-residency is removed, complexity is dramatically reduced. The number of machine instructions drops, buffer pool management disappears, extra data copies are not needed, index pages shrink, and their structure is simplified. The design becomes simple and more compact, and requests are executed faster. Figure 1-1 shows the simplicity of the TimesTen design.

Figure 1-1 Comparing a disk-based RDBMS to TimesTen

Description of Figure 1-1 follows
Description of "Figure 1-1 Comparing a disk-based RDBMS to TimesTen"

In a conventional disk-based RDBMS, client applications communicate with a database server process over some type of IPC connection, which adds performance overhead to all SQL operations. An application can link TimesTen directly into its address space to eliminate the IPC overhead and streamline query processing. This is accomplished through a direct connection to TimesTen. Traditional client/server access is also supported for functions such as reporting, or when a large number of application-tier platforms must share access to a common in-memory database. From an application's perspective, the TimesTen API is identical whether it is a direct connection or a client/server connection.

TimesTen and TimesTen Cache feature overview

TimesTen and TimesTen Cache have many familiar database features as well as some unique features. This section includes the following topics:

TimesTen API support

The run time architecture of TimesTen supports connectivity through the ODBC, JDBC, OCI, Pro*C/C++ Precompiler and ODP.NET APIs. TimesTen also provides built-in procedures, utilities and the TTClasses API (C++) that extend ODBC, JDBC and OCI functionality for TimesTen-specific operations, as well as supporting PL/SQL. API support is described in subsequent sections.

ODBC and JDBC interfaces

TimesTen and TimesTen Cache support ODBC and JDBC. Unlike many other database systems, where ODBC or JDBC API support may be much slower than the proprietary interface, ODBC and JDBC are native TimesTen interfaces that operate directly with the database engine. TimesTen supports versions of these APIs that are both fully compliant with the standards and tuned for maximum performance in the TimesTen environment.

For more information, see Oracle TimesTen In-Memory Database C Developer's Guide and Oracle TimesTen In-Memory Database Java Developer's Guide.

SQL and PL/SQL functionality

TimesTen and TimesTen Cache support extensive SQL functionality as well as SQL extensions to simplify the configuration and management of special features such as replication and TimesTen Cache.

TimesTen and TimesTen Cache support PL/SQL (Procedural Language Extension to SQL), a programming language that enables you to integrate procedural constructs with SQL for a TimesTen or TimesTen Cache database. You can execute PL/SQL from all supported APIs.

For more information, see Oracle TimesTen In-Memory Database SQL Reference and Oracle TimesTen In-Memory Database PL/SQL Developer's Guide

OCI and Pro*C/C++ Precompiler support

TimesTen and TimesTen Cache support the Oracle Call Interface (OCI) and the Pro*C/C++ Precompiler for TimesTen functionality.

TimesTen OCI support enables you to run many existing OCI applications with TimesTen in direct mode or client/server mode. TimesTen OCI also enables you to use other Oracle Database products that use OCI as a database interface. You can call PL/SQL from OCI applications.

For more information, see Oracle TimesTen In-Memory Database C Developer's Guide.

ODP.NET support

Oracle Data Provider for .NET (ODP.NET) is an implementation of the Microsoft ADO.NET interface. ODP.NET support for TimesTen and TimesTen Cache provides fast and efficient ADO.NET data access from .NET and C# client applications to TimesTen databases.

For more information, see Oracle Data Provider for .NET Oracle TimesTen In-Memory Database Support User's Guide.

Transaction Log API

TimesTen and TimesTen Cache have an API that allows applications to monitor update activities in order to generate actions outside the database. In TimesTen and TimesTen Cache, this capability is provided by the Transaction Log API (XLA), which allows applications to monitor update records as they are committed and take various actions based on the detected updates. For example, an XLA application can apply the detected updates to another database, which could be TimesTen or a disk-based RDBMS. Another type of XLA application can notify subscribers that an update of interest has taken place. This API is supported for C, Java (JMS/XLA) and C++ (TTClasses).

TimesTen and TimesTen Cache provide materialized views that can be used with XLA to enable notification of events described by SQL queries. The primary purpose of XLA is to be a high performance, asynchronous alternative to triggers.

For more information, see "Detect transaction modifications with the Transaction Log API" and Oracle TimesTen In-Memory Database C Developer's Guide.

TTClasses

TimesTen C++ Interface Classes (TTClasses) is more convenient than ODBC while maintaining fast performance. This C++ class library provides wrappers around the most common ODBC functionality. The TTClasses library is also intended to promote best practices when writing application software.

For more information, see Oracle TimesTen In-Memory Database TTClasses Guide.

Distributed Transaction Processing APIs

TimesTen implements the X/Open XA Specification and its Java derivative, the Java Transaction API (JTA).

The TimesTen implementation of the XA interfaces is intended for use by transaction managers in distributed transaction processing (DTP) environments. These interfaces can be used to write a new transaction manager or to adapt an existing transaction manager to operate with TimesTen resource managers.

The TimesTen implementation of the JTA interfaces is intended to enable Java applications, application servers, and transaction managers to use TimesTen resource managers in DTP environments.

For more information, see Oracle TimesTen In-Memory Database C Developer's Guide and Oracle TimesTen In-Memory Database Java Developer's Guide.

Access Control

TimesTen and TimesTen Cache are installed with access control to allow only users with specific privileges to access particular TimesTen features. TimesTen Access Control uses standard SQL operations to establish user accounts with specific privileges. TimesTen offers object-level access control as well as database-level access control.

For more information, see Oracle TimesTen In-Memory Database Operations Guide.

Database connectivity

TimesTen and TimesTen Cache support direct driver connections for higher performance, as well as connections through a driver manager. TimesTen also supports client/server connections.

These connection options allow users to choose the best tradeoff between performance and functionality for their applications. Direct driver connections are fastest. Client/server connections may provide more flexibility. Driver manager connections can provide support for ODBC applications written for a different ODBC version or for multiple RDBMS products with ODBC interfaces.

See "TimesTen connection options".

Durability

TimesTen and TimesTen Cache achieve durability through a combination of transaction logging and periodic refreshes of a disk-resident version of the database. Log records are written to disk asynchronously or synchronously to the completion of the transaction and controlled by the application at the transaction level. For systems where maximum throughput is paramount, such as non-monetary transactions within network systems, asynchronous logging allows extremely high throughput with minimal exposure. In cases where data integrity must be preserved, such as securities trading, TimesTen and TimesTen Cache ensure complete durability, with no loss of data.

TimesTen uses the transaction log in the following situations:

  • Recover transactions if the application or database fails

  • Undo transactions that are rolled back

  • Replicate changes to other TimesTen databases

  • Replicate TimesTen changes to Oracle Database tables

  • Enable applications to detect changes to tables (using the XLA API)

TimesTen and TimesTen Cache maintain the disk-resident version of the database with a checkpoint operation that takes place in the background and has very little impact on database applications. This operation is called a "fuzzy" checkpoint and is performed automatically. TimesTen and TimesTen Cache also have a blocking checkpoint that does not require transaction log files for recovery. Blocking checkpoints must be initiated by the application. TimesTen and TimesTen Cache maintain two checkpoint files in case a failure occurs mid-checkpoint. Checkpoint files should reside on disks separate from the transaction logs to minimize the impact of checkpointing on application activity.

See the following sections for more information:

Performance through query optimization

TimesTen and TimesTen Cache have a cost-based query optimizer that chooses the best query execution plan based on factors such as the presence of indexes, the cardinality of tables, and the presence of ORDER BY clauses in the query.

The method that the TimesTen and TimesTen Cache optimizer analyzes SQL statements to minimize performance costs is different than in traditional disk-based systems, because the cost structure of a main-memory system differs from that of disk-based systems in which disk access is a dominant cost factor. Because disk access is not a factor in TimesTen and TimesTen Cache, the optimization cost model includes factors not usually considered by optimizers for disk-based systems, such as the cost of evaluating predicates.

TimesTen and TimesTen Cache provide range, hash, and bitmap indexes. The query optimizer also uses two types of join methods (nested-loop and merge-join). The optimizer can create temporary indexes as needed. The optimizer accepts hints that provide applications the decision on whether to make tradeoffs between factors, such as temporary space usage and performance.

See Chapter 5, "Query Optimization" for more information about the query optimizer and indexing techniques.

Concurrency

TimesTen and TimesTen Cache provide full support for shared databases. Options are available so users can choose the optimum balance between response time, throughput and transaction semantics for an application.

Read-committed isolation provides nonblocking operations and is the default isolation level. For databases with extremely strict transaction semantics, serializable isolation is available. These isolation levels conform to the ODBC standard and are implemented with optimal performance in mind. As defined by the ODBC standard, a default isolation level can be set for a TimesTen or TimesTen Cache database, which can be dynamically modified for each connection at run time.

For more information about managing concurrent operations in TimesTen and TimesTen Cache, see Chapter 4, "Concurrent Operations".

Database character sets and globalization support

TimesTen and TimesTen Cache provide globalization support for storing, retrieving, and processing data in native languages. Over 50 different national, multinational, and vendor-specific character sets including the most popular single-byte and multibyte encodings, plus Unicode, are supported as the database storage character set. The TimesTen Cache supports some database character sets that are compatible with the Oracle Database. The connection character set can be defined to enable an application running in a different encoding to communicate to the TimesTen or TimesTen Cache database; character set conversion between the application and the database occurs automatically and transparently.

TimesTen and TimesTen Cache offer linguistic sorting capabilities that handle the complex sorting requirements of different languages and cultures. More than 80 linguistic sorts are provided. They can be extended to enable the application to perform case-insensitive and accent-insensitive sorting and searches.

For more information, see "Globalization Support" in Oracle TimesTen In-Memory Database Operations Guide.

In-memory columnar compression

TimesTen provides the ability to compress tables at the column level, thus storing the data more efficiently. This mechanism provides space reduction for tables by eliminating the redundant storage of duplicate values within columns and improves the performance of SQL queries that perform full table scans.

For more information, see "CREATE TABLE" in Oracle TimesTen In-Memory Database SQL Reference.

Data replication between servers

TimesTen and TimesTen Cache replication enable real-time data replication between servers for high availability and load sharing. Data replication configurations can be active-standby or active-active, using asynchronous or synchronous transmission, with conflict detection and resolution and automatic resynchronization after a failed server is restored.

See "Replication".

Cached data with the TimesTen Cache

The TimesTen Application-Tier Database Cache (TimesTen Cache) creates a real-time, updatable cache for the Oracle database data. It offloads computing cycles from Oracle databases and enables responsive and scalable real-time applications. TimesTen Cache loads a subset of the Oracle database tables into a cache database. It can be configured to propagate updates in both directions and to automate passthrough of SQL requests for uncached data. It automatically resynchronizes data after failures.

See Chapter 8, "TimesTen Cache".

Load data from an Oracle database into a TimesTen table

You can load the results of a SQL query from a back-end Oracle database into a single table on TimesTen. TimesTen provides tools that execute a user-provided SELECT statement on the Oracle database and load the result set into a table on TimesTen.

TimesTen provides two methods to accomplish these tasks:

  • The ttIsql utility, an interactive SQL utility, provides the createandloadfromoraquery command that, once provided the TimesTen table name and the SELECT statement, automatically creates the TimesTen table, executes the SELECT statement on the Oracle database, and loads the result set into the TimesTen table.

  • The ttTableSchemaFromOraQueryGet built-in procedure evaluates the user-provided SELECT statement to generate a CREATE TABLE statement that can be executed to create a table on TimesTen, which would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure executes the SELECT statement on the Oracle database and loads the result set into the TimesTen table.

Business intelligence and online analytical processing

TimesTen and TimesTen Cache provide analytic SQL functions and aggregate SQL functions for business intelligence and similar applications. Analytic functions enable analysts and decision makers to make comparisons and identify trends. They include ranking, cumulative, moving, centered and reporting functions.

The GROUP BY SQL clause includes online analytical processing (OLAP) operators such as GROUPING SETS, CUBE and ROLLUP.

For more information, see "Functions" and "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Large objects

TimesTen supports large objects (LOBs) in tables that are not cached in the TimesTen Cache. TimesTen supports the BLOB, CLOB, and NCLOB data types.

For more information, see "Data Types" in Oracle TimesTen In-Memory Database SQL Reference.

Automatic data aging

Data aging is an operation to remove data that is no longer needed. There are two general types of data aging: removing old data based on some time value or removing data that has been least recently used (LRU). For example, you can remove yesterday's price list, remove profiles and preferences of users who have logged out from the system, or remove detailed records that are more than 2 days old.

Two types of automatic data aging capability for TimesTen database tables and TimesTen Cache data are available:

  • Time-based data aging based on timestamp values

  • Usage-based data aging based on the LRU algorithm

For more information, see "Implementing aging in your tables" in Oracle TimesTen In-Memory Database Operations Guide and "Implementing aging in a cache group" in Oracle TimesTen Application-Tier Database Cache User's Guide.

System monitoring

Users have the following options for system monitoring and reporting.

  • The ttStats utility can monitor and display database performance metrics in real-time. It generates HTML reports from performance snapshots at user-specified intervals.

    Note:

    System statistics are collected and stored in the SYS.SYSTEMSTATS table; the information is used by various TimesTen utilities and monitoring facilities.

  • The Oracle Enterprise Manager System Monitoring Plug-In for Oracle TimesTen In-Memory Database provides a graphical user interface that can be used for monitoring database metrics and performance. You can also view reports on the collected metric information, which is useful in diagnosing and troubleshooting database performance issues.

  • TimesTen provides the ttStatsConfig, ttSQLCmdCacheInfo, and ttSQLExecutionTimeHistogram built-in procedures to measure and display execution time statistics for SQL operations to determine the performance of SQL statements.

Administration and utilities

TimesTen and TimesTen Cache support typical database utilities such as the following:

  • Interactive SQL (ttIsql).

  • Backup and restore (ttBackup and ttRestore).

  • Copy data between different database systems.

  • Migrate (ttMigrate) provides is a high speed copy for moving data between different versions of TimesTen or TimesTen Cache.

Many administrative activities are available by using SQL extensions. TimesTen and TimesTen Cache also use SQL extensions to set up replication, caching from an Oracle database, and materialized views.

TimesTen built-in procedures and C language functions enable programmatic control over TimesTen operations and settings. TimesTen command-line utilities allow users to monitor the status of connections, locks, replication, and so on. Status can also be obtained using SQL SELECT queries on the system tables in the TimesTen schema.

For more information on TimesTen administration, see Chapter 9, "TimesTen and TimesTen Cache Administration".