Oracle Exalytics Week : Exalytics & TimesTen – Under the Covers

February 29th, 2012 by

So in the first two postings in our Exalytics week series, we looked at why Oracle created Exalytics, and what Exalytics actually comprises of. If you missed the first two postings in the series, here’s the week’s agenda including links that I’ll complete once all of the series goes online:

So how does Exalytics actually work, and where does the special engineering and in-memory features come in? Exalytics brings together different technologies and places them in a high-performance server, and aims to harmonize and optimize their use for this particular hardware platform. The three engineering features that make Exalytics possible are:

  • An In-Memory Adaptive Data Mart
  • An In-Memory Intelligent Results Cache, and
  • In-Memory MOLAP cubes

Unlike tools such as SAP HANA that require you to re-engineer your system to work with a new database technology, or in-memory desktop tools such as Qlikview or Microsoft PowerPivot, Exalytics applications need no re-engineering and are designed to scale across the whole enterprise. So how do these features work, and in what sort of situations might they improve query performance?

Oracle Exalytics uses a specially-enhanced version of Oracle TimesTen, Oracle’s in-memory database, to cache commonly-used aggregates used in dashboards, analyses and other BI objects. By using an in-memory store to hold these aggregates, results are returned to the user with sub-second response times, making it possible to create dashboards that remove the Apply and Reset buttons on prompts to instantaneously respond to parameter changes. Because it’s a cache, and only aggregates are loaded in, this feature lends itself better to data warehouse data sources with a regular ETL process, and to BI systems that aggregate and analyze data. To work out what aggregates to create, usage tracking has been enhanced and now also includes a summary statistics table that tracks not only the logical SQL generated by queries, but also the physical SQL, query response time and query granularity, making it possible to now use a new Oracle BI Administration tool feature, the Summary Advisor, to recommend and create aggregates to place into TimesTen.

As well as the in-memory database cache, you also still have the results cache that OBIEE’s always have, stored in files on the filesystem. This still exists with Exalytics, and should be part of any caching and performance improvement strategy. With the high amount of RAM that’s on an Exalytics server, typically these cache files will end-up being held in memory whilst the OBIEE server is being used, or you can specifically place them on a RAM disk if required. Typically, that last step isn’t needed for the results cache files, which are written once and then read many times, but this approach might be useful for the working area for the BI and Presentation Servers, which typically write lots of data to disk as queries are merged and analysis views constructed.

Essbase, also, has had some in-memory optimizations and additional support for up to 128 CPUs, some of which will be available generally for all users, and some of which will be Exalytics-only. With Essbase applications, the idea is that Exalytics will hold the Essbase database, whilst you’d host the applications – Planning, Financial Reporting, Financial Management etc – on separate servers, some of which might need to be Windows-only. Check back on the blog once this release is out, for more details on what’s involved.

So what is Oracle TimesTen, and how does it work internally? TimesTen is a product that Oracle acquired several years ago, and is an in-memory database with data being persisted in the background to disk, so you don’t lose everything if the plug is pulled on the server. TimesTen loads the whole database into memory, and allocates disk as “perm” space, which is where your data gets stored, and “temp” space, which is working space for TimesTen. Generally, for larger deployments of TimesTen, the recommendation is to allocate the same amount of temp space as perm space, which if you consider that with the 1TB of RAM in Exalytics you might want to allocate, say, 800GB of that to TimesTen, then only half of that (400GB) would be useable space for the TimesTen databases (you can of course vary all of this depending on your particular workload). Overall not bad, but you’re not going to be able to take a 1TB data warehouse and load that all into memory, if that’s what you were thinking. The diagram below shows TimesTen as relating to OBIEE and how it uses disk-based checkpoint and other logging to ensure data isn’t lost if memory is powered-off.

TimesTen was originally created for the OLTP market, and was used in applications such as market trading, airline bookings and other scenarios where low-latency, fast single-row read and writes were required. In its previous form it was actually a pretty poor fit for OBIEE, as whilst it connected via ODBC, analytic and aggregation queries were actually slower than with regular disk-stored database, as it was optimized for these single-row reads and writes. With Exalytics though, extensions have been added to TimesTen which makes it more suited to the type of analytics, aggregated queries issued by the BI Server including:

  • Columnar compression
  • Support for the AVG, SUM, COUNT, MAX, MIN, DENSE_RANK, RANK, ROW_NUMBER, FIRST_VALUE and LAST_VALUE analytic functions
  • Support for the OVER PARTITION BY and OVER ORDER BY analytic clauses
  • Support for GROUP BY CUBE, GROUP BY ROLLUP, GROUP BY GROUPING SETS grouping clauses
  • Support for the GROUP, GROUPING_ID, GROUP_ID grouping functions
  • Support for the WITH clause (though in this initial release it’s best used only for reading data, not writing to it
  • and support for aggregate expressions over DISTINCT expression

So the idea here is that TimesTen should be able to support the same type of SQL functions and clauses as regular on-disk databases, but with the aggregates that are stored within it held in memory, and column-compressed. The Oracle BI Server will then use it’s internal aggregate navigation feature to refer to the TimesTen in-memory aggregates when queries request that level of aggregation, switching back to the regular data source when more detailed queries are requested. Unfortunately, these new TimesTen features can only be used in conjunction with Exalytics, which is a shame as it’d be nice to try them as a general-purpose in-memory analytic database, but that’s Oracle’s call I guess.

So how does data get into the TimesTen in-memory database, and how do you know what data to cache in there? We’ll see tomorrow, when we look in detail at the new Summary Advisor feature within the Oracle BI Administration tool.

Comments

  1. arivas Says:

    Hi Mark, excellent information as always.

    I’m confused about something. I thought that timesten was updated on version 11.2.2 to use all the features you mention (like collumnar compression, aggregate functions and so on…) and those features were available now even when you use it outside an exalytics environment.

    Are you positive that all the enhances of timesten 11.2.2 are only available on the exalytics version of the software?

    Thanks a lot for your advise, much appreciated.

    Antonio.-

  2. Mark Rittman Says:

    Hi Arivas

    Positive on that. The features that TimesTen for Exalytics uses are only available if licensed along with the Exalytics appliance, and can’t even be used if you’ve licensed them with Exalytics, but want to use it on other hardware or applications. Checked it out with the various PMs during the pre-launch activity.

    Mark

  3. Venkat Says:

    Hello Mark,

    Delighted to read your exposition on Exalytics. Interesting to note that support for columnar compression associated with Exadata has been extended to TimesTen as well. Any additional skills that a conventional OBIEE developer might need to pick up to work on Exalytics.

    Thanks and Regards

    Venkat

  4. VENKAT G Says:

    Hi Mark,
    It would be great if you can write some articles on SAP’s SAP HANA in memory database, and how it can be used with OBIEE 11.1.1.6. I am not sure if OBIEE 11.1.1.6 can be used with SAP HANA.

    Venkat

  5. Daniel Jacinto Says:

    Hi Mark

    Great Blog with good information, I am not familiar with ORACLE products as I come from the SAP world, so pardon my ignorance.

    From my understanding and your article, TimesTen will only hold the aggregates in memory. What happens if additional data is written to the database, such as in a transactional system, your aggregates will contain incorrect information or incorrect rollups, won’t there be data latency, between the time it takes the TimesTen server to rebuild the aggregates and load it into memory ?

    Kind Regards
    Daniel

  6. Mark Rittman Says:

    Daniel,

    Yes, like any aggregates, you’d need to refresh them to ensure that they accurately reflected the state of your base data. You can do this by re-running the script that the Summary Advisor creates for you, which first drops, and then recreates, the aggregates reading the most current data from your underlying database.

    Typically, you’d run this process every time you update the underlying data warehouse. As you say, these aggregates will take a while to create and come online, so there may be a period whilst this is happening that you will not benefit from the in-memory feature. This is the same though for any system that uses aggregate tables to speed up queries – the key is to get the refresh of the aggs as fast as possible, something we’ll be looking into once we get our Exalytics box in for testing.

    Mark

  7. Mark Rittman Says:

    Hi Venkat

    Just to be clear – the columnar compression that TimesTen uses isn’t the same as the Hybrid Columnar Compression that Exadata uses. The end-goal is the same (take advantage of the particular compression opportunities that denormalized dimension tables have) but the underlying implementation is different.

    Mark

  8. Mark Rittman Says:

    Venkat

    I’m afraid I’ve not had any exposure to SAP HANA, though the technology looks interesting.

    Mark

  9. Gennady Sigalaev Says:

    Hi Mark,

    Great blog! You were right as always. I found this information about TimesTen licensing(http://www.oracle.com/technetwork/database/timesten/documentation/licensing-info-1478940.pdf)

    Best regards,
    Gena

  10. Patrick Says:

    Hi, can you push a physical dimensional model on a traditional Oracle RDBMS into times-ten directly? or is the summary advisor the only means to get data into memory for this solution?

  11. Tim Vincent Says:

    Great blog Mark,

    Just to be clear. The TT features only licenced for the Exalytics box are:
    – In-Memory Columnar Compression
    – OLAP Grouping Operators: Cube, Grouping Set, Rollup

    Everything else AVG, SUM, COUNT, MAX, MIN, DENSE_RANK, RANK etc can be used outside the Exalytics box.

    Tim

  12. Mark Rittman Says:

    Thanks Tim. Are you one of the PMs for Timesten?

    Mark

  13. Tim Vincent Says:

    Hi Mark,

    Yes, please feel free to contact me for any TT related information. Keep up the good work!

    Tim

Website Design & Build: tymedia.co.uk