Oracle 12.2 In-memory New Features

I was able to attend the Oracle Open World 2016 event this past fall.

Last April, I posted a nice series on In-memory, material I had gathered from Oracle PM Andy Revenes (heck of a nice guy). Here are the Oracle12.2. new features for In-Memory from VP Tirthankar Lahiri, presented at OOW16. He clearly stated where you can currently get Oracle12.2: His slide simply said “Oracle is presenting features for Oracle database 12c Release 2 on Oracle cloud. Features and enhancements related to the on-premises versions of Oracle Database 12c Release 2 are not being announced at this time.”

As of this writing, the documentation on Oracle12.2 is available online but there still isn’t a Windows or Linux version of Oracle12.2 available yet. You can review the online documentation at this link: http://docs.oracle.com/database/122/

In-memory Performance Information

Mr. Lahiri said overall performance was much better and in the 3x range with mixed workloads (row and column store processing). He claimed 10x faster when using table joins. He said there is a new Join Group feature that is quite performant as well. He noted that JSON columns can now participate in a column store query with a 20 to 60 performance increase in access times. He also pointed out that in-memory features now support Active Data Guard (see further discussions and syntax below). There is also a column store Fast Start feature, again, discussed in detail below.

In-memory Join Group Syntax

CREATE INMEMORY JOIN GROUP v_deptno (DEPT(deptno), EMP (deptno));

Create the join group on common columns being joined between two tables such as the illustrated EMP and DEPT.

In-memory Expressions

CREATE TABLE CUSTOMER_SALES (
     CUST_NO  number,
     PRICE number,
     QTY number,
    TAX  number,
    TOTAL_SALE as (PRICE * QTY + TAX))
INMEMORY;

The column expressions must have a one-to-one relationship to the rows in the table or column store. You can also use DECODE, UPPER, LOWER, etc.

Expressions can be manually defined (as shown in the above syntax) or automatically created by Expressions Statistics Store (ESS) which monitors workloads. When it notices repeating SQL expressions, it will use the DBMS_INMEMORY.IME_CAPTURE to capture the repeating expressions and DBMS_INMEMORY.IME_POPULATE to create the in-memory virtual columns.

In-memory and Exadata

Exadata flash cache is managed by the keyword CELLMEMORY.

ALTER TABLE emp CELLMEMORY;
ALTER TABLE emp NO CELLMEMORY;
CREATE TABLE CUSTOMER_SALES (
     CUST_NO  number,
     PRICE number,
     QTY number,
    TAX  number,
    TOTAL_SALE as (PRICE * QTY + TAX))
CELLMEMORY
MEMCOMPRESS FOR QUERY;

You can use this feature on tables, partitions, sub partitions, and materialized views. The MEMCOMPRESS clause supports FOR QUERY LOW and FOR CAPACITY LOW. The NO PRIORITY clause will populate this into in-memory upon first request.

This feature also supports automatic data optimizations with this policy syntax:

• INMEMORY or NO INMEMORY
• Alter MEMCOMPRESS level
  • After <time> of no access
  • After <time> of creation
  • After <time> of no modification
  • On <user defined Boolean function>
• These policies can be run manually (as in a batch job perhaps)
  • DBMS_ILM.EXECUTE_ILM

Some examples include:

ALTER TABLE emp ILM ADD POLICY NO INMEMORY AFTER 6 months OF CREATION;
ALTER TABLE emp ILM ADD POLICY NO INMEMORY AFTER 10 days no access;
ALTER TABLE emp ILM ADD POLICY MEMCOMPRESS FOR QUERY after 5 days of creation;

In-memory Fast Start

Another feature that will help with database startup and the initialization of the in-memory column store is to save the column store from the prior operational system. You simply enable this feature and name the tablespace where the column store is to be saved when the database is shutdown.

In-memory Fast Start syntax:

Begin
    DBMS_INMEMORY_ADMIN.FastStart_ENABLE(‘<tablespace name>’);
End;

Tablespace listed should be 2x larger than the parameter INMEMORY_SIZE. Use the syntax SHOW SGA or SHOW PARAMETER INMEMORY_SIZE to see current in-memory total size assigned. The data is then check pointed and stored in the DBIMFS_LOGSEG$. The metadata about this feature is stored in the sysaux tablespace. The column store is then loaded from this area on database restart rather than rebuilt from scratch. This should save a considerable amount of time to startup the database that has larger in-memory column stores.

In-memory Dynamic Allocation

The in-memory size can now be adjusted dynamically!  You can increase the size of the allocation but you cannot decrease the size without recycling the database (shutdown/startup). Use the syntax ‘alter system set inmemory_size = 512M scope = both;’ to change this size both on the fly and for future recycles of the database. Again, use SHOW ALL or SHOW PARAMETER INMEMORY_SIZE to see the current then the new allocation (after executing the syntax).

There are some prerequisites to this, however:

1. The column store must be enabled
2. The database compatibility level must be set to 12.2.0 or higher
3. The database has to have been started using the SPFILE option
4. The new size of the in-memory size (INMEMORY_SIZE) must be 128M or greater than its current setting

In-memory and Active Data Guard

Oracle 12.2 also supports in-memory features for data guard/standby databases. There are three configuration options to consider:

1. Identical column stores in both primary and standby databases
2. Column store in just the standby database
3. Different column store configurations between the primary and standby databases

Identical column stores is where both the primary database and its standby database have the same column stores on the same tables. This will ensure the same level of query performance when connected to either environment. This setup is convenient when the standby database is used as a reporting database.

These settings need to be set:

1. INMEMORY_SIZE is set in both, set to the same size
2. INMEMORY_ADG_ENABLED is set to true on the standby instance
3. The INMEMORY clause is used on the same objects in both the active and standby instances

Standby database in-memory only features is where you are probably using the standby database as a reporting database and do not wish to use the in-memory features in the primary database.

These settings need to be set:

1. INMEMORY_SIZE is set in the standby instance and this is set to 0 in the primary database
2. INMEMORY_ADG_ENABLED is set to true on the standby instance
3. The INMEMORY and DISTRIBUTE FOR SERVICE clauses are used on the standby instance objects only

You can have a mix of the above…using in-memory features for both the primary and standby instances but on different objects. You might want the current quarter with in-memory features in the primary instance but maybe a different (prior) quarter of data in the standby instance.

These settings need to be set:

1. INMEMORY_SIZE is set in both instances but doesn’t necessarily need to be set to the same number.
2. INMEMORY_ADG_ENABLED is set to true on the standby instance
3. The INMEMORY clause is again used to tell the instance which object to load into the data store and DISTRIBUTE FOR SERVICE clause is used in the standby instance only.

You can review this level of detail in the Oracle12.2 In-memory Deployment guide using this link: http://docs.oracle.com/database/122/INMEM/deploying-im-column-store-with-adg.htm#INMEM-GUID-F5934C5A-34DE-46BA-ABD2-727E548B8D9F

Dan Hotka

Author/Instructor/Oracle Expert

…check out current promotions on my Video-on-Demand courses: www.DanHotka.com