Exploring Oracle database in-memory management

 

Typically, the hierarchy of exploring any enhancements in one’s own domain is to understand the concepts first, do a thorough test and apply it. The current Oracle 12c (12.1.0.2) release made head-lines and caught everyone’s attention for its in-memory mechanism. This paper is intended to peek at the important architecture of Oracle database in-memory feature, how the feature will help accelerating analytical queries performance in DSS and OLTP environments.

In-memory column store

In the previous Oracle releases, Oracle incorporated several significant enhancements in System Global Area (SGA) and memory sub-components functionality and mechanism. The latest Oracle 12c (12.1.0.2) patch set presents yet another SGA memory component, In-memory area, also referred as IM Column store, to stores the data in column format in contrast to traditional row format. This feature provides performance improvements for ad-hoc queries and analytics on live data in DSS or OLTP systems.

 

The following diagram outlines Oracle 12c memory components architecture:

 

 Image courtesy Oracle documentation

 

This feature (requires additional licensing) is turned-on by setting the INMEMORY_SIZE initialization parameter to a non-zero value and can be configured at column, table, MV, partition and tablespace levels. When the feature is enabled at a column, table, MV, partition and tablespace level, each column is stored as a separate structure. If the feature is enabled at a tablespace level, it applies to all tables and materialized views that are stored in the tablespace by default.

 

In-Memory area, a static pool in SGA, uses a 1MB and 64k pool to store actual column formatted data and object metadata respectively. Query the V$INMEMORY_AREA dynamic view to know the available memory in the different pools. Typically majority of memory is allocated to the 1MB pool.

 

               SQL> SELECT pool,alloc_bytes,used_bytes, FROM v$imemory_area;

 

Each RAC database instance will hold its private IM column store and it is recommended to have equal size across all instance. However, if any of the instance doesn’t require the feature, you can set the INMEMORY_SIZE parameter to zero on that instance to disable the feature.

 

Queries with the following type of operations can gain substantial performance improvements with the IM column store:

 

·        The predicate filtering operations with IN, =, <, > on large table scans

·        When joining a small table with a large table in the query

·        Selecting a few columns from a table or   MV with huge number of columns

·        A query that aggregate the data

 

The feature also can be defined at the time of table/MV/tablespace creation, and can alter any existing table/MV/tablespace. To determine the objects that IM column store features is attached, refer the new v$ dynamic view, V$IM_SEGMENTS.

 

SQL> SELECT owner,segment_name,inmemory_priority FROM v$im_segments;

 

Having said that, one may not achieve anticipated performance boost in the following circumstances:

 

·        Queries with complex predicates

·        While selecting large number of columns

·        When large number of rows are returned

·        Queries on multiple large table jobs

 

If you are managing very large databases (VLDBs), it is advised to use the memcompress option to compress the data to gain performance for the queries. Data in columns with IM Column store feature can be compressed like the typical columns data. The following are the different options for data compression on IM Column store:

 

·        MEMCOMPRESS FOR DML                             -              Optimized for DML operation on compressed tables

·        MEMCOMPRESS FOR QUERY LOW -             Best option for query performance

·        MEMCOMPRESS FOR QUERY HIGH -              Provides excellent query performance

 

You can define the priority level of the object when IM Column store feature is enabled to default for automatic control or it can be defined manually also. The following are the various priority levels:

 

·        PRIORITY NONE|LOW|MEDIUM|HIGH|CIRTICAL

 

Here is the list of initialization parameters introduced to control the configuration/settings of IM Column store:

·        INMMEORY_SIZE                               -              sets the size of the IM column store in the SGA

·        OPTIMIZER_INMEMORY_AWARE   -              this will control cost mode enhancements for in-memory

·        INMEMORY_MAX_POPULATE_SERVERS -    specifies the number of background process (servers)

·        INMEMORY_QUERY                          -              controls in-memory query executions settings

 

SQL> SHOW PARAMETER INMEMORY
 
NAME                                 TYPE        VALUE
---------------------------------- ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 500M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

 

Syntaxes

Here are the working syntaxes for enabling/disabling IM Column store feature:

 

·        The COMPATIBLE database parameter must be set to 12.1.0 or higher

·        A non-zero value for INMEMORY_SIZE, minimum 100MB (it’s a static parameter, can’t be modified dynamically)

 

When the parameter is set, the startup command displays the IM column store settings:

 

SQL> ALTER SYSTEM SET INMEMORY_SIZE=500M SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
 
ORACLE instance started.
……
In-Memory Area           536870912 bytes
……

 

-- table level IM feature configuration

CREATE TABLE inmem_emp(eno number(10),ename varchar2(30)) INMEMORY;

 

-- modifying existing table level configuration

ALTER  TABLE inmem_emp INMEMORY;

ALTER TABLE inmem_emp INMEMORY MEMCOMPRESS FOR QUERY HIGH(eno) PRIORITY HIGH;

 

-- defining the IM feature at tablespace level

 CREATE TABLESPACE inmem_tbs …. INMEMORY;

 

-- disable the configuration at table level

ALTER TABLE inmem_emp NO INMEMORY;

 

Putting the entire database in cache

We have all learned and experienced that data access from memory is much faster than accessing it from the underlying physical disk.  The Oracle 12.1.0.2 patch set allows you put the entire database data in the cache.  If you have a small sized database with huge memory resources available over the database server, you can test the putting the entire database in the cache to measure the performance for the queries.

 

You can also force to keep the entire database in the cache using the ALTER DATABASE FORCE FULL DATABASE CACHING statement, make sure the database is mounted, not OPENED. However, before you go with this option, ensure adequate memory resource is available to fit the database into the memory. To verify if the database is cached or not, run the following query at the database:

 

               SQL> SELECT force_full_db_caching FROM v$database;

 

With the FORCE FULL DATABASE CACHING, even the NOCACHE LOBs are also cached in the memory. Also, LOBs and SecureFiles LOB can be cached as well.

 

To disable the option, execute the following statement putting the database in MOUNT state:

 

               SQL> ALTER DATABASE NO FORCE FULL DATABASE CACHING;

 

Note: You should keep in mind that the in-memory area (IM column store) is not a buffer cache replacement, but act as a supplement to keep the data in column format.

 

Conclusion

This article explains how the IM column store features, can provide a significant query performance and the procedure and advantages of placing the entire database in the memory (cache). One should thoroughly experiment the features before decides to take it to the production environment.