Oracle 21c New Feature - Automatic In-Memory

This blog post explores a significant enhancement in Oracle 21c related to the In-Memory Column Store.  It is called Automatic In-Memory, or sometimes "AIM" for short.

Automatic In-Memory (ref) automatically populates, evicts, and recompresses segments in the Oracle In-Memory Column Store (IMCS), without user intervention.  Recall that the IMCS can be smaller than the amount of data you want to load into it, and that only some "working set" amount of data can fit in the IMCS at any one time.  Throughout the day, the data you want to have in the working set could change.  Keeping up with this could be a full-time job for a DBA.  That's where Automatic In-Memory comes in.  It uses database statistics to determine which data should be in the active working set.

Automatic In-Memory is an enhancement to IMCS.  If you are not using IMCS, then Automatic In-Memory basically automates the entire process of configuring tables and managing the working set.  If are already using IMCS, then your settings are retained and Automatic In-Memory just manages the working set.

As background, IMCS is a columnar formatted memory space within the Oracle SGA intended to expedite analytics, reports, and other queries that typically perform full table scans to extract just a few columns of data. IMCS operates transparently to any application, and you do not need to write special queries or rewrite your existing queries to use IMCS.  The Oracle Optimizer will develop some execution plans that include IMCS and some that do not.  Queries may be redirected to IMCS if the query planner estimates the cost is lower.

To enable Automatic In-Memory, set the initialization parameter INMEMORY_AUTOMATIC_LEVEL=HIGH.  The default value is OFF.  Of course, I assume you have already enabled and configured the core features of In-Memory Column Store.  Failing to enable and configure IMCS will prevent Automatic In-Memory from working.

It is important to understand how setting INMEMORY_AUTOMATIC_LEVEL affects your system:

If set to HIGH, then two things will happen:

  • All segments with a previously configured INMEMORY setting will continue to use those settings but Oracle will try to auto-manage loading and evicting data from the IMCS working set.
  • All segments without a previously configured INMEMORY setting are automatically marked INMEMORY MEMCOMPRESS AUTO.  Partitioned tables will have their compression level set to MEMCOMPRESS AUTO for all existing and future partitions.

If not set to HIGH (the default), then two things will happen:

  • Oracle uses the settings you manually applied to database objects, but Oracle won't try to auto-manage them.
  • All objects for which you have not manually applied an INMEMORY setting are treated as if you set NO INMEMORY. 

There are actually four allowable values for this parameter: OFF, LOW, MEDIUM, and HIGH.  Please see the Oracle on-line documentation (ref) for details on this parameter.

Once the database initialization parameter has been set as discussed above, you can optionally alter a table's INMEMORY clause to specifically state automations should be used.  This isn't required, because as noted earlier setting database parameter INMEMORY_AUTOMATIC_LEVEL=HIGH tells Oracle to apply the clause INMEMORY MEMCOMPRESS AUTO to any segment that doesn't already have an INMEMORY clause.  So, you only need to do this manually for segments where you previously set a clause and now you want to add automation.  The following example is from the Oracle on-line documentation:

ALTER TABLE sh.countries INMEMORY MEMCOMPRESS AUTO;

There is some confusion regarding Automatic In-Memory and the ADO Heat Map.   Automatic In-Memory does not use the ADO Heat Map, but instead has its own similar feature.  Automatic In-Memory tracks the temperature of things and uses those statistics to determine what to work on next.  Therefore, you do not need to enable the ADO Heat Map to use Automatic In-Memory.

In terms of monitoring Automatic In-Memory, you can use the Oracle-supplied views DBA_INMEMORY_AIMTASKS and DBA_INMEMORY_AIMTASKDETAILS.

The Oracle In-Memory Column Store (IMCS) was first introduced in Oracle 12c R1 patchset 12.1.0.2.  It is an Enterprise Edition option that requires a license for the Oracle Database In-Memory Option. 

Automatic In-Memory was first introduced in Oracle 21c.  It is available to all Enterprise Edition users who have a license for the Oracle Database In-Memory Option.  Note that the first time I looked at this feature it required Oracle engineered hardware like Exadata, but this is apparently not the case now.


Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more