I thought it would be a good idea to remind everyone why In-Memory is an excellent complementary feature for Exadata users.
I again encountered this question in a recent meeting with a customer: "I have Exadata already, and it is really fast. Do I really need Database In-Memory? Will it still benefit me?"
The answer is really simple. The answer is "Yes".
The answer has always been "Yes" since the first release of Database In-Memory with Oracle Database 18.104.22.168. There are a number of additional reasons why this is an even bigger "YES" with the new Exadata specific features in Oracle Database 12cR2 and 18c.
1. Database In-Memory provides the fastest possible analytics on hot data since it leverages superfast local memory on Exadata Database Servers:
Memory that is local to the CPUs of the Compute Servers can be accessed with an order of magnitude higher bandwidth than accesses to Storage Servers. So for the super-critical, hottest data needing real-time analytics, you will benefit by populating that data into the In-Memory column store. This will allow you to process that data at the rate of billions of rows per second using SIMD vector instructions for near instant analytic response times.
The Exadata X7-2 has between 384GB to 1.5TB of memory per Database Server, and a total of up to 28.5TB of memory per rack; allowing for a very large in-memory column store.
2. Unique In-Memory Fault Tolerance on Exadata Database Servers:
On Exadata, a unique feature for the In-Memory column store is In-Memory fault tolerance: All in-memory sections (referred to as In-Memory Compression Units or IMCUs) for a table in the column store can be populated into the memory of two database instances on different database servers in the same RAC cluster. This means that if any instance fails, there is zero application impact from having to repopulate the lost IMCUs since each IMCU is safe on another instance. This mode is enabled by declaring the table to be "INMEMORY DUPLICATE".
3. Full In-Memory Duplication for Fault-Tolerance and Performance:
As a special case of the above capability, it is also possible to fully duplicate all the IMCUs of a table on all instances. This provides both availability (since the in-memory contents of the table continue to be available as long as there is at least one surviving instance in the RAC cluster) as well as performance (since all accesses to the IMCUs of the table are local to the instance from which the access is being issued). This mode of full duplication (enabled by declaring the table to be "INMEMORY DUPLICATE ALL") is ideal for smaller reference tables and dimension tables.
4. New from 12cR2 onwards: Database In-Memory Expands the column store to much larger Flash, for intermediate data
With the 12cR2 release of Exadata storage software , the same in-memory format that is available on the compute servers is used as the format for the columnar flash cache. This means, that an offloaded smart scan running on the storage server will process data using the same SIMD vector optimizations available on the database servers, leading to much faster smart scan performance. See Andy Rivenes' blog post for more details.
Since the flash cache capacity of a full rack Exadata X7-2 can be an astonishing 350TB, this means that it is possible to expand your in-memory column store to hundreds of Terabytes using Exadata Flash Cache combined with Database In-Memory. The format for in-memory on Flash is referred to as CELLMEMORY. By default, if the inmemory option is enabled, all HCC compressed tables in 12.2 will also be automatically declared to be in the CELLMEMORY format.
Starting with Oracle Database 18c, all tables (not just HCC compressed tables) will be marked with CELLMEMORY format if the in-memory option is enabled. No user intervention required!
5. New from 12cR2 onwards: In-Memory on Active Data Guard
A unique capability for Database In-Memory on Exadata, starting with Oracle Database 12cR2, is the ability to populate tables into the in-memory column store of an Active Data Guard standby database. This is a huge advantage for Exadata customers: if they run an Active Data Guard standby on Exadata, that standby can be used for real-time reporting on in-memory tables. This is a very powerful and flexible mechanism: the standby database can have a completely different set of tables populated into memory compared to the primary database, and if you are using multiple standby databases, each can have a completely independent in-memory column store.
Many different combinations of in-memory tables across primary and standby databases are possible; for instance, recent partitions on the primary, older partitions on the standby, or, smaller tables on the primary (where there is also memory demand from OLTP workloads), larger tables on the standby (which can be exclusively used for Analytics), etc.
Deciding where to populate the contents of a table or partition is done by the new "INMEMORY DISTRIBUTE FOR SERVICE" syntax; you can use this syntax to populate a table or partition on any database instance (primary or secondary) where the specified service is enabled.
6. New in 18c: Automatic In-Memory
In Oracle Database 18c, it is possible to automatically manage the contents of the column store on Exadata using the new Automatic In-Memory mechanism. Automatic In-Memory is useful when it is difficult to identify an exact set of Database segments to populate into the column store, and the total size of potential in-memory candidates exceeds the size of available memory. With Automatic In-Memory enabled, the system will automatically track accesses to the in-memory column store and make room for hotter objects by automatically evicting colder objects.
Exadata is the premier, flagship platfom for Oracle Database. Not only does Database In-Memory bring additional analytic workload performance to this amazing platform (just as a supercharger brings additional performance to a high end sports car), it also brings with it advanced and unique functionality specific to Exadata.
So the answer to the question: "Does Database In-Memory provide additional value if I already have Exadata?" is a resounding "Yes." Get the supercharger for your sports car (or for your more restrained looking sedan or SUV) and have fun at each stoplight!