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:
This architecture leads developers to raise a number of questions, such as:
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:
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:
So what does this all mean? Here are some general guidelines:
Please add a comment below to let us know your thoughts.