New Database In-Memory Features in Oracle Database 23ai

Oracle Database 23ai is here and Database In-Memory has many new features.

For more details on Oracle Database 23ai please follow this link: https://blogs.oracle.com/database/post/oracle-23ai-now-generally-available.

I have already blogged about many of the new features in Database In-Memory and there are also Ask TOM Office Hours sessions where I have demonstrated how many of the new features work in the 23ai Free – Developer Release.  There have been some changes in the latest version of Oracle Database 23ai Free so I will review the new features and what they do, and if you explored any of the features in the previous Developer Releases you should plan on taking another look. If you haven’t given them a try then hopefully this summary will motivate you to take a look.

The following are new or updated features for Database In-Memory in Oracle Database 23ai:

Automatic Enablement of In-Memory Features

This feature is an enhancement to Automatic In-Memory (AIM) and in 23ai AIM can automatically create Join Groups, enable In-Memory Optimized Arithmetic, vector optimization and Bloom filter optimizations based on an enhanced workload analysis algorithm. This new analysis also improves how AIM measures workload in mixed workload environments by accounting for DML overhead for in-memory populated objects.

Automatic In-Memory Sizing

This is a really exciting new feature. With Automatic In-Memory Sizing the IM column store can now be managed as part of Automatic Shared Memory Management (ASMM). This means that ASMM can grow or shrink the IM column store based on overall database workload requirements.

Multi-Level Joins and Aggregations

This feature is an addition to the In-Memory Vectorized Joins feature that was added in Oracle Database 21c. I wrote a blog post about the new additions for multi-level joins and aggregations in the 23c Developer release if you want to take a peek at what is new. If you want to also see some of the background on In-Memory Vectorized Joins you can look at this blog post from Oracle Database 21c.

Selective In-Memory Columns

This is a nice to have feature that enables the ability to exclude or include all columns  with an ALL keyword to avoid having to have long lists of columns when only populating a subset of columns in a table or materialize view.

In-Memory Optimized Dates

Another optimization to make date comparisons even faster in the IM column store by leveraging the In-Memory Expressions (IME) framework. When enabled, MONTH and YEAR will be extracted and placed in IMEs and comparisons on those portions of a date will be very fast. The following is an example of a SQL query that can leverage In-Memory Optimized Dates. Note the EXTRACT function:

SELECT d.d_year, p.p_brand1,SUM(lo_revenue) rev
FROM   lineorder l, date_dim d, part p, supplier s
WHERE  l.lo_orderdate = d.d_datekey AND l.lo_partkey = p.p_partkey
AND    l.lo_suppkey = s.s_suppkey AND p.p_category = 'MFGR#12';
AND    s.s_region   = 'AMERICA';
AND    EXTRACT(year from d.d_datekey) = 1997
GROUP  BY d.d_year, p.p_brand1;

New Database-native In-Memory Advisor

A new, database native In-Memory Advisor is now available in Oracle Database 23ai. We previously announced, and backported to 19c, the In-Memory Eligibility Test and we have now moved the In-Memory Advisor into the database as well. A big advantage with the new advisor is that there is no need to install additional code into the database to run the In-Memory Advisor. The new In-Memory Advisor uses Heat Map data, which no longer requires an additional license, to analyze analytic workload between two snapshots and then a report can be run to see the results.

Hybrid Exadata Scans

Hybrid Exadata Scans enables the ability to perform both an IM column store scan and an Exadata Smart Scan for partially populated in-memory tables. Prior to this feature if a table was only partially populated on Exadata then the data in the IM column store might not have been accessed. This feature corrects that, and this can be a big win when using AIM and the IM column store is under memory pressure.

In-Memory Columnar JSON

This feature enables the support of In-Memory JSON optimizations for CELLMEMORY. Exadata Smart Scan can automatically detect JSON columns and build Path/Value indexes as part of the Exadata Smart Flash Cache in-memory columnar rewrite (i.e. CELLMEMORY).

As you can see, there have been a lot of changes to Database In-Memory in Oracle Database 23ai, and this is on top of all the additions that were made in Oracle Database 21c. For customers upgrading from 19c there are lots of things to take a look at.