Performance Benchmarks: TimesTen vs Oracle Database

User Rating: / 11
PoorBest 

by Antony Heljula

Oracle TimesTen and Oracle Database are both extremely fast...

but they excel at different types of analytical workload

In this article, we're going to look at how Oracle TimesTen compares to the Oracle Database both from a performance and optimization perspective.

It is important to understand the differences between the database engines because, since the release of Exalytics, customers will now commonaly have an Oracle BI architecture consisting of two database layers:

  1. Oracle Database - maintaining the back-end data warehouse or OLTP
  2. Oracle TimesTen - used as a middleware "in-memory" cache


This architecture leads developers to raise a number of questions, such as:

  • Is TimesTen always faster than the Oracle Database?
  • When should you store data in TimesTen and when should you leave it in the database?


In order to answer these questions we conducted a series of performance tests to compare Oracle TimesTen with the Oracle Database Enterprise Edition on exactly the same hardware and containing exactly the same data (a 10 million record fact table and 3 dimension tables).

The tests involved running different types of analytical queries against the tables and adjusting the filters so that gradually more and more fact records were scanned (starting with 1000 rows and increasing all the way up to 10 million rows).

As TimesTen is an in-memory database, we had to make the tests fair by making sure all data and indexes on the Oracle Database were 100% cached - so that no queries ever needed to go to disk.   This put both databases on an equal footing as CPU and Memory were the only possible bottlenecks.   For TimesTen, we enabled Columnar Compression to maximise its performance.

The results were very interesting.

All our tests experienced the same performance pattern: TimesTen was the fastest at first, but as the size of the query grew the Oracle database caught up and then became fastest for the larger analytical queries.

The chart below shows how performance typically compares between the two databases:


Let's discuss the 3 key points highlighted on the chart:

TimesTen can be up to 9x faster for small-med workloads, especially with Columnar Compression (gains of at least 40% on every query)
  1. TimesTen is the fastest at responding to smaller queries - in our tests TimesTen was up to 9x faster at the smaller analytical workloads, especially with Columnar Compression enabled
  2. The Oracle DB is slower to react at first, but after a while it will alter its query plan to improve efficiency with larger queries e.g. hash joins, parallel query
  3. Oracle DB eventually becomes optimal for the larger analytical queries


You can see clearly in this chart that the two databases have very different optimization alogrithms.

The optimizer on the Oracle Database is more complex, this is perhaps why it is slowest to react for the smaller queries - but its sophisticated decision making ability makes it better at handling larger analytical queries (as it introduces parallel query and hash joins etc).

TimesTen appears to have a much simpler optimization strategy - and this is one of the reasons why TimesTen is able to respond so rapidly to the smaller analytical queries.  The downside however is that the TimesTen optimization appears to be very "straight line", it uses the same query plan for small queries as it does for large queries.  It is important to note however that we found that with TimesTen the size of the fact table does not really matter - it seems to be more about how many rows are analysed within it.

The key question is: at what point does the Oracle Database become the fastest?

A precise answer is not so straight forward as it depends on a number of factors such as:

  • The speed of your Oracle Database, and how much data is cached in memory
  • The number of parallel threads your Oracle Database can consume (as TimesTen does not use parallel query)
  • Your TimesTen columnar compression ratio (our tests suggest that the more you can compress the data, the faster your TimesTen queries will be)


So what does this all mean?  Here are some general guidelines:

  • TimesTen is great for achieving rapid response times with "dense" dashboard visualisations that initiate many concurrent SQL queries
  • The Oracle Database is best for the larger analytical queries which will benefit from a high degree of parallelism
  • With TimesTen, the size of the fact table does not matter - it is more about the data volumes being scanned within the table
  • Always try to use of TimesTen columnar compression - it can make a big difference to response times


Please add a comment below to let us know your thoughts.

 

Comments  

 
#1 Lamin Jobe 2013-05-27 18:01
Good comparison and great insights!

Thanks Antony!
Quote
 
 
#2 Harald Erb 2013-09-02 05:23
Hi Antony,
very good summary to a very popular question in our Exalytics PoC's.
Best regards, Harald
Quote
 
 
#3 Thorsten Schulz 2013-09-27 08:20
Hi Antony,
nice to see your blog.
This behaviour of TT regarding larger datasets is mentioned in several blogs.
But what if you use the TT index advisor to tune TT, can it handle larger analytical queries ?
Best regards, Thorsten
Quote
 
 
#4 THeljula 2013-09-29 22:05
Hi Thorsten,

Thanks for the comment. The Index Advisor certainly does help - and Oracle showed me recently one of my queries that was 40x faster after using the Index Advisor.

TimesTen is capable of handling some large analytical queries, for example: At Oracle OpenWorld last week there were demonstrations on TimesTen where queries scanning a billion rows ran in less than a second - all because of the correct indexing strategy.

But we will always look for further improvements! The big features coming in TimesTen during 2014 are Parallel Query and Columnar Storage. These should massively increase the performance of TimesTen even further - in theory you won't even need indexes.

Regards
Tony
Quote
 
 
#5 Le Thi Bich Thuan 2013-11-22 06:57
The diagram doesn't make so much informative. Can you explain more detail? I mean If using Oracle Database and Oracle TimesTen in the same environment (such as the number of threads is 1 - 2 - 4 - 8 or 16), so how many records can queried per second in selecting, updating, inserting or deleting?
Quote
 
 
#6 Tony H 2013-11-22 10:20
Hi, thanks for your comment and question about more precise benchmark figures. Unfortunately, as an Oracle Partner, we are not allowed to publish this type of benchmark data without approval from Oracle. Apologies for that.
Regards
Tony
Quote
 

View Office Addresses
UK:
Ireland:
+44 1246 550058
+353 1 871  9419
+44 1246 550058
+353 1 533 4280
enquiries@peakindicators.com
Twitter: @Peakindicators
LinkedIn



Latest Tweets