The Oracle Database 12c (12.1.0.2) In-Memory option

The Oracle Database 12.1.0.2 introduces the In-Memory option that has the capability to speed up real-time analytics by an order of magnitude. The faster analytics complements and enables real-time decision making. Long-running reports and ad-hoc analytical queries are expected to benefit the most. The feature can be implemented without any application changes and works transparently with no manual hindrance, thus resulting in improved productivity.

The challenge

Enterprise applications have been reported to have mixed workloads—that is, OLTP workloads and analytics processing. In the past, there have been a couple of approaches to segregating the workloads. Mixed workload production databases can run on the same system, but running both of them simultaneously would degrade the OLTP performance. Running workloads on separate server systems impacts the real-time decision making because data on the analytics server has to be refreshed from time to time.

The problem statement and Oracle Database 12c In-Memory

Oracle Database is a trusted relational database management system that stores data in a row format. For transactional databases, data stored in a row format is a mandate because transactions work on a record basis and require all the attributes of a table in a single fetch. On the other hand, data analytics and reports, which run on few columns of data while also spanning many rows, work well with the columnar format. Until now, enterprises were forced to choose either of the two formats.

Oracle Database 12c In-Memory allows the database to be represented in a row format as well as a columnar format, thus providing the flavor of a "dual-format" architecture within the database. A piece of data can be represented in a row format as well as columnar format. The transactions continue to follow the row format of the data while the analytics workload work with the columnar format. The analytics get more real-time as the columnar format accelerates it by an order of magnitude. The best-of-both-worlds strategy is enabled by switching on the In-Memory feature in the Oracle Database.

The In-Memory feature marks a memory area (known as the In-Memory Column Store) within the System Global Area (SGA). This memory space is used to hold the objects frequently referenced by the analytics queries and reports. It implies that enabling the In-Memory feature for a database doesn't require double memory requirements. However, databases may require some additional memory to accommodate their active objects in the In-Memory store.

Oracle Database 12c In-Memory option features

The Oracle Database 12c In-Memory feature was released as an option in the patchset release (12.1.0.2) of Oracle Database 12c Release 1. The following list of features includes some must-know information about this option:

• The In-Memory column store is not a replacement for the buffer cache; rather, it supplements it.
• In-Memory Column Store is a new static pool within the System Global Area (SGA). Being In-Memory, it is non-persistent and non-logging. It is not affected by Automatic Memory Management and the resident objects stay populated until they are manually flushed out.
• Administrators or users are authorized to identify those objects which when populated in the In-Memory column store, would yield the best performance.
• All objects except Index Organized Tables, Clustered tables, LONG columns, and Out-of-Line LOBs can be populated in the In-Memory column store.
• The Oracle Database optimizer is fully aware of the In-memory store; it decides which query would benefit from the buffer cache or in-memory columnar store.
• It is a licensed option, available starting from Oracle Database 12.1.0.2 Enterprise edition.

The Oracle Database 12c In-Memory Architecture

The System Global Area contains a new static pool, known as the In-Memory column store. The segments that are marked and populated in the In-Memory column store are oriented in the columnar format. Diving deep into the technical aspects of In-Memory column store, the static pool comprises of two pools: the IMCU (or 1MB) pool and the SMU (or 64KB) pool. The IMCU pool comprises In-memory compression units (IMCU) that hold the actual data in a columnar format. For each IMCU, there is a co-related SMU to store the IMCU's metadata and a transaction journal. The distribution of 1MB and 64KB pools are based completely on internal factors. The current allocation can be viewed under the V$INMEMORY_AREA dictionary view. The following figure shows the architecture of SGA and the In-Memory column store in Oracle Database 12c:

Controlling the In-Memory column store

The In-Memory Column Store can be configured through a new set of initialization parameters, introduced in Oracle 12c. These parameters control In-Memory dynamics such as sizing, the optimizer's behavior, and worker processes to be deployed for the population. Here is the list of initialization parameters:

• INMEMORY_SIZE (default 0): This configures the In-Memory store by setting this parameter for a minimum of 100MB. The database must be bounced for the changes to take effect.
• INMEMORY_QUERY (default ENABLE): This parameter controls whether the queries should be optimized using the In-Memory store.
• INMEMORY_MAX_POPULATE_SERVERS (default 0): Configures the number of worker processes (max) to be used for In-Memory column store populate operations.
• INMEMORY_CLAUSE_DEFAULT: This sets the default In-Memory clause or sub clause. By default, the value of the clause is NULL.
• INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT: This sets the percentage of worker processes that can perform trickle repopulation. The default value of the parameter is 1%.
• INMEMORY_FORCE (default DEFAULT): Setting this to OFF restricts the In-memory column store population.
• OPTIMIZER_INMEMORY_AWARE (default TRUE): This controls whether the optimizer should be aware or unaware of the In-Memory column store.

The INMEMORY clause

The objects required to be populated in the In-Memory column store can have the additional INMEMORY clause. The INMEMORY attribute can be specified for a table, columns, partition, materialized view, or a tablespace. In addition to the INMEMORY clause, there are other sub-clauses for some important aspects, such as population priority and compression.

The following In-Memory sub-clauses are applied by default along with the INMEMORY clause. To override the default behavior, you must specify the desired value.

• MEMCOMPRESS: The sub-clause determines the compression mode of the in-memory objects. The admissible compression modes are:
  • NO MEMCOMPRESS: No compression.
  • MEMCOMPRESS FOR DML: Compression for frequently transactional objects.
  • MEMCOMPRESS FOR QUERY LOW (default): Balanced compression mode to optimally compress and ensure space savings. Enhances the query performance.
  • MEMCOMPRESS FOR QUERY HIGH: Compression mode that focuses on query performance but checks the space savings too.
  • MEMCOMPRESS FOR CAPACITY LOW: Compression mode for optimal space savings.
  • MEMCOMPRESS FOR CAPACITY HIGH: Compression approach is the highest degree of space savings.
• PRIORITY: The PRIORITY sub-clause determines whether an object, which is marked as INMEMORY, can be populated automatically or manually. There are five possible values of PRIORITY clause:
  • CRITICAL: Critical priority objects are populated immediately after the database is opened or through the In-Memory Co-ordinator (IMCO) process's timely wake-up
  • HIGH: After the population of CRITICAL priority objects completes and the In-Memory column store has vacant space
  • MEDIUM: After the population of CRITICAL and HIGH priority objects completes and the In-memory column store has vacant space
  • LOW: After the population of CRITICAL, HIGH, and MEDIUM priority objects completes and the In-memory column store has vacant space
  • NONE (Default): The NONE priority segments are populated after the first full scan
• DISTRIBUTE: The DISTRIBUTE sub-clause is used in clustered environments (Oracle Database Real Application Cluster) to distribute the object data across the In-Memory Column Store on all the cluster nodes.
• DUPLICATE: The DUPLICATE sub-clause is exclusively for the members of the Oracle Engineered Systems family. It allows the duplication of the In-memory column store across selective or all nodes of the cluster for high availability.

Performance optimizations

Oracle Database In-Memory feature is designed for analytics performance. The optimizations that account for overall performance are as follows:

• Columnar Format and Vector Processing: The column format enables only the required column to be scanned, and not the complete record. The column format supports Single Instruction Multiple Data (SIMD) processing, which helps in processing multiple data values in each CPU instruction.
• Predicate evaluation and Join operations pushdown to the In-Memory column store: Predicates can be pushed down to the IM column store for evaluation. The In-Memory column store makes use of bloom filters to join multiple tables together.
• The In-Memory storage index provides min-max pruning that helps in preventing the IMCUs from scanning: Predicates can be checked against the IMCU header, which maintains information about minimum and maximum values. It helps in determining whether to scan or skip an IMCU.
• The evaluation of a query predicate can be minimized if the IMCU header satisfies the predicate: If the IMCU header fully or partially satisfies the predicate condition, the predicate evaluation can be prevented or reduced for the columnar units.

In-Memory Advisor

For large application databases, choosing the most suitable objects to be populated in the In-Memory Column Store can be a challenge. Oracle provides an In-Memory Advisor kit to recommend those objects whose in-memory format will yield the maximum benefits. The tool analyses the database workload through Automatic Workload Repository (AWR) and Active Session History (ASH) repositories, plan cardinalities, and parallel execution. Once the analysis is completed, it generates HTML advisory reports. The reports provide the list of objects that would benefit the most, when placed in the In-Memory column store.

The In-Memory Advisor is part of Oracle Tuning Pack and can be installed in Oracle Database 11.2.0.3 and above.

Oracle Database In-Memory benefits

Oracle Database 12c In-Memory offers a dual-format architecture to support mixed workloads. An object can be represented in row format as well as columnar format. The columnar format is read-consistent and transactional-consistent with the data on disk. The In-Memory feature is embedded natively in the Oracle Database. Therefore, it is supported on all Oracle Database-supported platforms. Also, it is compatible with all database technologies such as Real Application Clusters, Multitenant, High Availability, and Exadata Engineered Systems.