This is the second in our series on new Database In-Memory features in Oracle Database 21c. The first one described the enhancements to Automatic In-Memory. In-Memory Deep Vectorization is a new framework that has been added to Database In-Memory. It is a SIMD-based (single instruction, multiple data) framework that supports vectorization for higher-level query operators in the query plan. The first feature that takes advantage of the In-Memory deep vectorization framework is In-Memory vectorized joins. The best part is that not only does it speed up in-memory joins, it is transparent to the user.

An In-Memory vectorized join is a run-time decision that uses SIMD vector processing to optimize aspects of hash joins such as hashing, building, probing, and gathering. This optimization can improve the performance of join processing by 100% or more. An In-Memory vectorized join pushes the hash table build/probe processing into the data scan using a SIMD-optimized hash table data structure. The following image shows a representation of how this works:

The use of an In-Memory vectorized join does not prevent the use of other Database In-Memory features like Bloom filters, Join Groups, IM dynamic scans, aggregation pushdowns and IM columnar compression formats.

Since it is a run-time decision on whether an In-Memory vectorized join will be used there is no direct information available in an execution plan, but a SQL Monitor active report can be used to determine if an In-Memory vectorized join was used for a particular query. Similar to how to determine if a Join Group was used, clicking on the binoculars icon for the hash join will bring up a window that will list two statistics, DeepVec Hash Joins and DeepVec Hash Join Flags, that determine whether an In-Memory vectorized join took place. For an example see the image below:

There is also an initialization parameter INMEMORY_DEEP_VECTORIZATION that allows the feature to be enabled by setting it to true, this is the default, or it can be disabled by setting it to false.

If you would like more details or to see a demo of the feature head over to our Ask TOM Office Hours page and check out the session we did on In-Memory vectorized joins.

 

Original publish date: 9/9/2021