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.
CREATE TABLE CUSTOMER_SALES (
TOTAL_SALE as (PRICE * QTY + TAX))
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;
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:
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:
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:
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:
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:
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.
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.
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
…check out current promotions on my Video-on-Demand courses: www.DanHotka.com