When do workloads benefit from #oracledatabase In-Memory? This is a question that we often receive from our customers and partners in discussions about workload performance. There is excellent material on the web when searching for Oracle Database In-Memory – you will find a selection at the end of the posting. However it’s too much or as we say here in Germany: “Sometimes you can’t see the forest for the trees!” 

In this posting we want to summarize the important statements and help to find workloads in using the In-Memory Advisor.

Markus Kissling, our Product Manager for Database In-Memory gives insights out of his perspective and my colleague Rob Watson contributes two videos to demonstrate the usage of the In-Memory Advisor.  

But first let’s define what Oracle Database In-Memory is about and when to use it.

Oracle Database In-Memory – A Definition

Oracle Database 12.1.0.2 introduced Oracle Database In-Memory (short: DBIM) allowing a single database to efficiently support mixed woorkloads – analytic and transactional – suited for the Cloud and on-premises because it requires no additional changes to the application.  An Oracle Database configured with Oracle Database In-Memory delivers optimal performance for transactions while simultaneously supporting real-time analytics and reporting. 

Take a quick look at the Oracle Database architecture you will find a new component of the system global area (SGA) called in-memory area that provides the functionality. The column store resides in this In-Memory Area, which is an optional portion of the SGA. The IM column store does not replace row-based storage or the database buffer cache, but supplements it. The database enables data to be in memory in both a row-based and columnar format, providing the best of both.

SGA

Image 1: Oracle Database SGA component

It’s very easy to set it up, basically only two steps are required: First you need to set the parameter inmemory_size (Note: SGA must be large enough to accommodate). Second you need to enable the required objects for population. The IM column store should be populated with the most performance-critical data in the database. Less performance-critical data can reside on lower cost flash or disk. Columnar format is supported for the following objects: Tables, Partitions, Sub-Partitions, and Materialized Views. Important to know is also that the data populated into the IM column store is compressed using a new set of compression algorithms that not only help save space but also improve query performance. Read the brief write-up from Markus how to start with Oracle Database In-Memory: Oracle Database In-Memory – Getting Started 

The development of Oracle Database In-Memory continued over the years, e.g. there is support for self-managing in-memory called Automatic In-Memory (AIM) which was introduced in Oracle Database 18c and has been enhanded in 21c. In 18c we also implemented the INMEMORY MEMCOMPRESS clause for External Tables that use the two legacy drivers (ORACLE_LOADER and ORACLE_DATAPUMP). In 19c finally we added support for the Big data drivers (ORACLE_HDFS and ORACLE_HIVE). Any time external data needs to be queried repeatedly in a relatively short space of time can now benefit from Oracle’s DBIM technology. 

Oracle Database In-Memory Innovations

Image 2: Oracle Database In-Memory Innovations

When to use In-Memory Database and when not

Most SQL statements that are analytical in nature will improve. By how much is highly dependent on how much time is being spent scanning, filtering, joining and aggregating data, and whether the Optimizer can further optimize the execution plan based on an in-memory access path.

Oracle Database In-Memory does not speed up DML (insert, update, delete), parsing, and PL/SQL. Futhermore in ETL processes where data is loaded and selected only once. You can find more information on this subject in the Paper: When to Use Oracle Database In-Memory.

You can find candidates based on knowledge of the application or with the help of the In-Memory Database Advisor.

In-Memory Database Advisor

In-Memory Advisor uses Automatic Workload Repository (AWR), Active Session History (ASH) and other meta-data to help with determining which objects will benefit from Database In-Memory. A license of Diagnostic and Tuning Pack is required. Since the In-Memory Advisor is available as a separate utility it can be used in database versions 11.2.0.3 or higher. More information and how to download can be obtained via My Oracle Support (MOS) Note 1965343.1 and the In-Memory Advisor technical white paper.

To get a guidance how to use the In-Memory Advisor you may have a look at following two videos. Each video takes about 15 minutes. In the first video we demonstrate how to install the Oracle Database In-Memory Advisor and how to extract the required data from an Oracle Database to enable us to run the advisor from a remote location.


The second video is a continuation of the first video in which we showed how to install the Oracle Database In-Memory advisor and how to extract the required data. In the second video we show how to load the AWR data and the augmented data in the remote database, run the In-Memory Advisor, analyze the data and review an HTML advisor output report.

 

 

Perhaps one remark why we run Oracle Database In-Memory Advisor on a remote database: Often customers cannot or are not allowed to use the production database for such purposes and therefore running it remotely is the only way to test their workload and run In-Memory Advisor.

Conclusion

The Oracle Database In-Memory Advisor analyzes your workload and makes specific recommendations regarding how to size Oracle Database In-Memory and which objects would render the greatest benefit to your system when placed in the In-Memory column store. In the two videos you learn how to extract the required data from your source database, install the In-Memory Advisor and load the data into a remote datatabase. Finally we demonstrate how to run the In-Memory Database Advisor and how to interpret the resulting output. 


If you have any questions, please let us know.

Markus Kissling: Markus.Kissling@oracle.com
Rob Watson: Rob.Watson@oracle.com
Ulrike Schwinn: Ulrike.Schwinn@oracle.com

Further readings 


Some selected papers