X

TimesTen In-Memory Database
for Extreme Performance

How do you develop apps for Oracle TimesTen Scaleout?

Doug Hood
TimesTen Cloud Product Manager

Summary

Oracle TimesTen Scaleout is a scale out, shared nothing, OLTP, SQL RDBMS which is similar but different than the Oracle RDBMS. This article gives a brief overview of how to develop applications on TimesTen Scaleout and makes comparisons with the Oracle RDBMS.

 

Short Answer

The way that you design, build, test and deploy applications for Velocity Scale is almost exactly the same to how that you would do it for an Oracle database. For most OLTP applications, the SQL APIs, table definitions and SQL statements can be exactly the same.

Languages and SQL Drivers

In fact, often a program with the exact same source code can run on both TimesTen Scaleout and the Oracle database:

  • An example JDBC program that runs the TPTBM OLTP workload on either Oracle or TimesTen Scaleout.
  • An example OCI [Oracle Call Interface] program that runs the TPTBM OLTP workload on either Oracle or TimesTen Scaleout.
  • The Oracle 8.1 OCI cdemo81.c sample program [with improved error handling] that works on either Oracle or TimesTen Scaleout.
  • An Oracle 8.1 Pro*C sample program [sample.pc] with improved security and error handling that works on either Oracle or TimesTen Scaleout.
  • An Oracle 8.1 Pro*C sample program for dynamic ANSI SQL that works on either Oracle or TimesTen Scaleout.

When these OCI or Pro*C programs are compiled and linked, the same executable can run against either Oracle or TimesTen Scaleout. This JDBC program dynamically loads the JDBC driver, so it can also use the same Java Class to run against either Oracle or TimesTen Scaleout.

The Open Source SQL APIs [Node.js, Python, Ruby, Go and Oracle R] that are OCI based and were created for the Oracle database also work for TimesTen Scaleout due TimesTen's support for OCI.

 

Long Answer

This rest of this article looks at the edge cases where TimesTen Scaleout and Oracle can be different for applications.

Database Column Types

For OLTP applications, Oracle TimesTen Scaleout uses the common database columns types that the Oracle database uses.

TimesTen Scaleout and Oracle Data Types

This above venn diagram, shows that the intersection covers the common OLTP database column types.

The column types on the right are not supported in TimesTen.  TimesTen supports 4 MB varchar2 / CLOB columns and 16 MB varbinary / BLOB columns. This means that TimesTen Scaleout can use the simpler and faster varchar2/varbinary types rather than the Oracle LONG, LONG RAW and BFILE types.

The column types on the left are not supported by the Oracle Database.  These types which start with 'TT_' tend to use less space and be faster than the equivalent Oracle column types.

 

Table Storage Clauses (transparent optimizations)

The Oracle Create/Alter Table syntax is very feature rich.  Much of the create table syntax occurs 'outside' the table column definitions for what I am calling the storage clauses.  The various Oracle database storage clauses are focused on the data structures and disk storage configurations aimed to optimize performance mainly by minimizing disk IOs. These 'storage clauses' are transparent to an application [and developer] and when applied correctly they can significantly increase the performance and scalability of an Oracle Database.

Storage Clauses

The Oracle TimesTen TimesTen Scaleout database also uses 'storage clauses' to increase performance and scalability. Oracle TimesTen TimesTen Scaleout is a scale out, shared nothing, In-Memory RDBMS. TimesTen Scaleout processes all of this SQL data in-memory so optimizing for disk IO [needed for persistence, rollback and recovery] is tends to be less of an issue than for an Oracle database.

Rather than optimizing disk IOs, TimesTen Scaleout is focused on optimizing network messages. While network messages can be faster than disk IOs, the fewer the network messages the faster and more scalable the TimesTen Scaleout application will be.

By default tables distribute their rows across TimesTen Scaleout machines by a consistent hash of the Primary Key.  If the primary and foreign key rows are on the same machine, any SQL joins between these rows will avoid network messages. Some tables that are commonly joined to and 'read mostly' can be duplicated across all machines. Joins with tables that are duplicated will always avoid network messages.  

Velocity Scale Distribute by Hash

Velocity Scale Distribute By Reference

Velocity Scale Duplicate table distribution

The distribution of tables in TimesTen Scaleout is transparent to an application and developer.  SQL applications will get the same results independent of the table distributions, but 'optimal' distributions will perform and scale much better. Developers can also use Materialized Views to enable optimal plans for multiple different columns access patterns.

 

SQL Analytic Functions

Although TimesTen TimesTen Scaleout is focused OLTP workloads, it also supports the following subset of the Oracle analytic SQL functions:

Analytics SQL functions

A benefit of TimesTen Scaleout's scale out architecture is that all SQL statements execute in parallel. This means that most SQL analytic functions will tend to execute faster as the number of TimesTen Scaleout nodes increase. This speedup will tend to be linear (more nodes means faster SQL). Due to Amdahl's Law, the time to execute a SQL statement will never be zero no matter how many machines are used.

Every aspect of TimesTen Scaleout application development and performance tuning will be covered in more detail in future articles.

 

Disclaimer: these are my personal thoughts and do not represent Oracle's official viewpoint in any way, shape, or form.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.