As part our of continuing 12.2 New Feature Series we explore different areas of Oracle 12.2
In this blog we discuss the new In-Memory 12.2 features
An In-memory expression, or “hot” expression, enables frequently evaluated query expressions to be materialized in the In-Memory Column Store, for subsequent reuse. By default, the procedure DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS identifies and populates IM expressions.
Populating the materialized values of frequently used query expressions, into the In-Memory Column Store, greatly reduces the system resources required to execute queries, allowing for better scalability. The procedure, IME_CAPTURE_EXPRESSIONS, will capture and populate the 20 “hottest” expressions in the database for a specified time range.
In-Memory Virtual Columns
An IM virtual column, is a value derived by evaluating an expression. IM virtual columns improve query performance by avoiding repeated calculations. Also, the database can scan and filter IM virtual columns, using techniques such as SIMD vector processing.
Before 12.2, the columnar format was only available In-Memory, meaning that after a database restart, the In-Memory Column Store would have to be populated. This multiple step process, converted traditional, row formatted data into the compressed columnar format and placed in-memory.
Now, In-Memory Column Store optimizes the compressed columnar population of database objects (tables, partitions, and subpartitions) in the In-Memory column store. This process, significantly reduces the time required to re-populate In-Memory objects.
Use DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE procedure to enable a specific tablespace for FastStart
Automatic Data Optimization (ADO) Support for In-Memory Column Store
In 12.2, ADO now also manages the IM column store as a new data tier. When enabled, the Heat Map feature automatically tracks data access patterns; ADO uses this Heat Map data to implement user-defined policies at the database level. ADO manages the In-Memory Column Store, by moving objects (tables, partitions or subpartitions) in and out of the memory, based on Heat Map statistics.
Twelve Days of 12.2
Copyright © 2016 Viscosity North America, Inc. All rights reserved.
In-Memory Join Groups
IM column stores can use join groups, to optimize joins of populated IM tables. Join groups, eliminate the performance overhead of decompressing and hashing column values. Create join groups using the CREATE INMEMORY JOIN GROUP statement:
CREATE INMEMORY JOIN GROUP prodid_jg (mine.items(product_id),mine.product_line(product_id));
In-Memory Support on Oracle Active Data Guard
12.2, allows IM column store to be enabled on Oracle Active Data Guard environments, by setting the init.ora parameter INMEMORY_ADG_ENABLED to TRUE. Using the in-memory column store, on an Active Data Guard standby database, enables users to offload larger and heavier reporting workloads, onto Standby Databases. Moreover, 12.2 permits the Standby Database to populate a completely different set of data in the in-memory column store than the Primary Database, providing greater data access flexibility.
In-Memory Column Store Dynamic Resizing
You can now dynamically increase the size of the in-memory area, while the database is open, assuming that enough memory is available within the SGA. Thus, the in-memory column store can be resized without restarting the database, providing greater application availability.