Performance Benchmarks: TimesTen vs Oracle Database
- 26th May 2013
- Performance / Exalytics
- 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:
- Oracle Database - maintaining the back-end data warehouse or OLTP
- 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)
- 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
- 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
- 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.