TimesTen In-Memory Database General Feature Overview

TimesTen API and Language Support

TimesTen supports:

Industry standard language for RDBMS: SQL.
Industry standard APIs: ODBC, JDBC, and ODP.NET.
Oracle APIs: PL/SQL, Oracle Call Interface (OCI), and ProC/C++.
TTClasses and built-in procedures provided by TimesTen that extend ODBC functionality for TimesTen-specific operations.
Open source programming languages, such as Python and Node.js.

The runtime architecture of TimesTen supports connectivity through the ODBC, JDBC, OCI, Pro*C/C++ Precompiler and ODP.NET APIs.

SQL and PL/SQL Functionality

TimesTen supports extensive SQL functionality to simplify the configuration and management of special features. Using SQL to shield applications from system internals enables a database 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. Any developer familiar with the Oracle database or SQL interfaces can be immediately productive developing applications with TimesTen. See SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference.

TimesTen supports Oracle PL/SQL (Procedural Language Extension to SQL), a programming language that enables you to integrate procedural constructs with SQL for a TimesTen in-memory database. You can run PL/SQL from all supported APIs. See Introduction to PL/SQL in TimesTen in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

OCI, ODBC and JDBC Interfaces

TimesTen supports the Oracle Call Interface (OCI) 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.

TimesTen also supports the industry standard ODBC and JDBC API interfaces. TimesTen supports versions of these APIs that are both fully compliant with the standards and tuned for maximum performance in the TimesTen environment.

Unlike many other database systems, where the ODBC API support may be much slower than the proprietary interface, ODBC is a native TimesTen interface that operates directly with the database engine.

See TimesTen Support for OCI and Working with TimesTen Databases in ODBC in the Oracle TimesTen In-Memory Database C Developer's Guide and Working with TimesTen Databases in JDBC in the Oracle TimesTen In-Memory Database Java Developer's Guide.

Pro*C/C++ Precompiler Support

TimesTen supports the Oracle Pro*C/C++ Precompiler for C and C++ applications. You can use the precompiler with embedded SQL and PL/SQL applications that access a TimesTen database. Further, you can use your C or C++ program host variables in your embedded SQL or PL/SQL. See TimesTen Support for Pro*C/C++ in the 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 provides fast and efficient ADO.NET data access from .NET and C# client applications to a TimesTen in-memory database.

See Initial Considerations for ODP.NET in a TimesTen Environment in the Oracle Data Provider for .NET Oracle TimesTen In-Memory Database Support User's Guide.

Open Source Programming Languages

TimesTen supports open source programming languages, Python and Node.js, to access any TimesTen database through the Oracle Database ODPI-C driver.

ODPI-C is an open source library from Oracle Corporation designed to simplify access to Oracle databases from a variety of programming languages. It is a layer on top of the Oracle Call Interface.

See Getting Started in the Oracle TimesTen In-Memory Database Open Source Languages Support Guide for more details.

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.

See TTClasses Development Environment in the Oracle TimesTen In-Memory Database TTClasses Guide.

Transactional ACID Compliance

TimesTen supports transactions that provide atomic, consistent, isolated and durable (ACID) access to data. All operations on a TimesTen database, even those that do not modify or access application data, run within a transaction. See Transaction Isolation, Durability in this guide and Transaction Management in the Oracle TimesTen In-Memory Database Operations Guide.

Durability

A database in TimesTen is persistent across power failures and crashes. TimesTen accomplishes durability by periodically writing to the file system:

Changes made by transactions through transaction log files.

Transaction log records are written to the file system 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 enables extremely high throughput with minimal exposure. In cases where data integrity must be preserved, such as securities trading, TimesTen ensures complete durability, with no loss of data. See Transaction Logging and When Are Transaction Log Files Deleted?.
All data through checkpoint files.

TimesTen maintains the file system 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 performed automatically. TimesTen maintains two checkpoint files in case a failure occurs mid-checkpoint. Checkpoint files should reside on a file system separate from the transaction logs files to minimize the impact of checkpointing on application activity. See Checkpointing.

Durability for TimesTen Classic

TimesTen Classic 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)

See Durable Options for Logging Transactions in the Oracle TimesTen In-Memory Database Operations Guide.

Durability for TimesTen Scaleout

In TimesTen Scaleout, the data in your database is distributed into elements. Each element keeps its own checkpoint and transaction log files. As a result, the data stored in each element is independently durable.

Each instance in a grid manages one element of a database. In the event of a failure, an instance can automatically recover the data stored in its element from the checkpoint and transaction logs files while the remaining instances continue to service applications.

TimesTen Scaleout also enables you to keep multiple copies of your data to increase durability and fault tolerance.

You can change the durability settings of a database according to your performance and data durability needs. For example, you may choose if data is flushed to the file system with every commit or periodically in batches in order to operate at a higher performance level.

See Durability Settings in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Performance Through Query Optimization

As with any mainstream RDBMS, a cost-based optimizer automatically determines the fastest way to process queries and transactions. TimesTen has 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 optimizer analyzes SQL statements to minimize performance costs is different than in traditional file system-based systems, because the cost structure of a main-memory system differs from that of file systems in which access is a dominant cost factor. Because access to the file system is not a factor in TimesTen, the optimization cost model includes factors not usually considered by optimizers for file systems, such as the cost of evaluating predicates.

TimesTen provides range and hash 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 Query Optimization for more information about the query optimizer and indexing techniques.

Concurrency

TimesTen provides 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 database, which can be dynamically modified for each connection at runtime.

See Concurrent Operations for more information about managing concurrent operations in TimesTen.

Database Character Sets and Globalization Support

TimesTen provides 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. Cache operations support 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 a TimesTen database; character set conversion between the application and the database occurs automatically and transparently.

TimesTen offers 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.

See Globalization Support in Oracle TimesTen In-Memory Database Operations Guide.

Database Connectivity

TimesTen supports direct and client/server connection options so that users can choose the best tradeoff between performance and functionality for their applications.

A direct connection connects an application to a database where both exist on the same system.
A client/server connection accommodates connections from remote client machines to databases across a network.

From an application's perspective, the TimesTen API is identical whether it is a direct connection or a client/server connection.

See TimesTen Connection Options for overall information on connection options. See Database Connections and Connecting to a Database in the Oracle TimesTen In-Memory Database Scaleout User's Guide for details specific to TimesTen Scaleout.

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 run a user-provided SELECT statement on the Oracle database and load the result set into a table on TimesTen.

TimesTen provides the following methods to accomplish these tasks:

Use cache operations, which provide the connection and transfer of data between an Oracle database and a TimesTen database. You can cache Oracle Database data in a TimesTen database within cache groups. A cache group in a TimesTen database can cache a single Oracle Database table or a group of related Oracle Database tables.
The ttIsql utility is an interactive SQL utility. The ttIsql createandloadfromoraquery command, once provided the TimesTen table name and the SELECT statement, automatically creates the TimesTen table, runs 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 you can run to create a table on TimesTen. This table would be appropriate to receive the result set from the SELECT statement. The ttLoadFromOracle built-in procedure runs the SELECT statement on the Oracle database and loads the result set into the TimesTen table.

System Monitoring

You can use system tables, system views, and the ttStats utility for system monitoring and reporting for both TimesTen Classic and TimesTen Scaleout. The following sections describe system monitoring and reporting tools:

System Monitoring Through System Tables and Views

TimesTen stores metadata (information about the contents of your database) in system tables in your database. TimesTen also provides system views that allow you to use SQL to query information.

Use the ttIsql alltables to display a list of all system and user tables. Use the ttIsql allviews command to display a list of all system and user views. See ttIsql in the Oracle TimesTen In-Memory Database Reference.

See System Tables and Views in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

System Monitoring for TimesTen Classic

Users have the following options for system monitoring and reporting.

You can use the ttStats utility to monitor and display database performance metrics. 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.
You can use the Oracle Enterprise Manager System Monitoring Plug-In for TimesTen In-Memory Database that provides a graphical user interface to monitor database metrics and performance. You can also view reports on the collected metric information, which is useful in diagnosing and troubleshooting database performance issues.
You can use the ttStatsConfig, ttSQLCmdCacheInfo, and ttSQLExecutionTimeHistogram built-in procedures to measure and display runtime statistics for SQL operations to determine the performance of SQL statements.

System Monitoring for TimesTen Scaleout

There are options for system monitoring and reporting for your grid and database.

Note:

See Monitoring TimesTen Scaleout in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

You can use the ttStats utility enables you to monitor database metrics (statistics, states, and other information) or take and compare snapshots of metrics. It generates HTML reports from performance snapshots at user-specified intervals. See Using the ttStats Utility in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Note:

System statistics are collected and stored in the SYS.SYSTEMSTATS table; the information is used by various TimesTen utilities and monitoring facilities.
You can collect various logs from every host that is part of your grid. These logs are useful for troubleshooting errors that you may encounter while using your grid or database. See Collecting Grid Logs in the Oracle TimesTen In-Memory Database Scaleout User's Guide.
You can monitor the management instances. See Monitoring the Management Instances in the Oracle TimesTen In-Memory Database Scaleout User's Guide.
You can use SQL Developer to create, manage, and explore a grid and its components. Additionally, you can also browse, create, edit and drop particular database objects; run SQL statements and scripts; manipulate and export data; view and create reports; and view database metrics. See Using SQL Developer to Work With TimesTen Scaleout in the Oracle TimesTen In-Memory Database Scaleout User's Guide.

Administration and Utilities

Utility programs are explicitly invoked by users, scripts, or applications to perform services such as interactive SQL, bulk copy, backup and restore, database migration and system monitoring.

TimesTen supports typical database utilities such as the following:

Interactive SQL (ttIsql).
Migrate (ttMigrate) provides a way to quickly move data between different major versions of TimesTen.
Many administrative activities are available through SQL statements and system views. TimesTen also uses SQL statements to set up materialized views.

TimesTen Scaleout provides database management through the ttGridAdmin utility.

TimesTen Classic also supports typical database utilities such as the following:

Backup and restore (ttBackup and ttRestore).
Copy data between different database systems.
Using SQL statements to set up replication and caching from an Oracle database.

TimesTen built-in procedures and C language functions enable programmatic control over TimesTen operations and settings. TimesTen command-line utilities enable 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.

See TimesTen Administration.