Oracle Open World 2016 – Day 3

On day 3 I visited various sessions about In-Memory and its many new features in 12gR2. Yesterday I already listed some new features of In-Memory. In this post I want to explain the new features more in detail:

In-Memory Join Groups:

Columns are specified, which are used to join tables:

CREATE INMEMORY JOIN GROUP V_name_jg (VEHICLES(NAME), SALES(NAME));


Those columns share the compression dictionary. My understanding on how this works is as follows: The joins occur on dictionary values rather than data. E.g. a dictionary with distinct values of car brands (column NAME in VEHICLES and SALES) may look as follows:

BMW 1
VW 2
Mercedes 3
Ford 4
Fiat 5
Dodge 6

The dictionary values are stored in the compression units instead of the real data:

1
1
2
3
3
3
4
5
5
6

Doing that for the 2 columns allows the joins to happen on the dictionary values rather than real values. Oracle talks about a speedup of a factor 2-3 when using Join Groups.

In-Memory expressions:

SQL expression results (NET in the example below) can now be stored as additional in-memory columns:

CREATE TABLE SALES (
PRICE NUMBER, TAX NUMBER, ...
NET AS (PRICE + PRICE * TAX )
)
INMEMORY;


All in-memory optimizations (e.g. vector processing or storage indexes) apply to the expression columns as well. When a SQL contains the expression then it can be taken from the column store without computing it:

SELCT SUM(NET) FROM SALES WHERE NET > 50;


Different types of expressions are possible:

• Arithmetic expression
• Logical expression (e.g. DECODE)
• Type conversion (e.g. UPPER, TO_CHAR)
• PL/SQL expressions

Two modes to define the expressions to populate (Manual and Auto) can be used:

Manual: Declare virtual columns for the desired expression (see example above). The parameter INMEMORY_VIRTUAL_COLUMNS defines if virtual columns are considered for inmemory:
INMEMORY_VIRTUAL_COLUMNS
= ENABLE: All user-defined virtual columns on the table or partition enabled for in-memory will be populated
= MANUAL: User-defined virtual columns must explicitly marked for INMEMORY (default)
= DISABLE: No user-defined column will ever be populated

Auto: Oracle detects frequent expressions automatically. I.e. the optimizer stores “hot” expressions (based on frequency and cost) in the expression statistics store (ESS). With the procedures IME_CAPTURE and IME_POPULATE in the package DBMS_INMEMORY the expressions are captured and declared as hidden in-memory virtual columns on the appropriate table. Expressions in the ESS can be checked by querying ALL|DBA|USER_EXPRESSION_STATISTICS.

In-Memory JSON

JSON columns and expressions on JSON columns can now be populated in-memory.

In-Memory on Active Data Guard

According Oracle, In-Memory on Active Data Guard was the most wanted feature from customers for In-Memory for 12gR2. I.e. the In-Memory column store can now be used on the Active Data Guard instance as well. The standby database can have different data (columns) stored in-memory than the primary. The decision on what columns to populate in-memory on the different instances is based on a service. I.e. on primary the following DDL may be used:

ALTER TABLE SALES INMEMORY DISTRIBUTE FOR SERVICE A;
ALTER TABLE SHIPMENTS INMEMORY DISTRIBUTE FOR SERVICE B;


Table SALES will be populated In-Memory on that instance, which has service A enabled and SHIPMENTS will be populated on the instance, which has service B enabled.

In-Memory Columnar Flash

On Exadata the In-Memory format can be used on the Flash Cache. I.e. an In-Flash Column store can be created. The advantage is that there is usually much more Flash than RAM and hence more data can be cached for In-Memory on Flash scans. To do that the CELLMEMORY segment attribute is available:

ALTER TABLE sales CELLMEMORY;
CREATE TABLE trades (...) CELLMEMORY MEMCOMPRESS FOR QUERY;


Restrictions in comparison to normal In-Memory:

• The MEMCOMPRESS sub-clause only supports “FOR QUERY LOW” and “FOR CAPACITY LOW”.
• There is no PRIORITY sub-clause.

Automatic Data Optimization (ADO) extended to In-Memory

The heatmap can now be used to define how long data should remain In-Memory or when In-Memory compression should be changed. I.e. in the maintenance window or on manual request (using the procedure DBMS_ILM.EXECUTE_ILM) Oracle checks if a policy has been met to e.g. flush data from the In-Memory column store.

ALTER TABLE sales ILM ADD POLICY ... NO INMEMORY AFTER 10 DAYS OF NO ACCESS;


That allows sliding windows of column store content based on access-time or time of creation. A customized policy function can also be created in PLSQL.

In-Memory Fast-Start

The In-Memory column store content can now be checkpointed to Securefile Lobs. When the DB restarts the population is faster (between 2-5x) as the data can immediately be loaded in memory without CPU-intensive compression activity. To activate In-Memory Fast-Start the procedure DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE with a tablespace-name as parameter has to be executed. The tablespace should be 2 times the size of the in-memory column store.