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 (184.108.40.206) 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 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 (220.127.116.11) 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_force string DEFAULT
inmemory_max_populate_servers integer 1
inmemory_query string ENABLE
inmemory_size big integer 500M
inmemory_trickle_repopulate_servers_ integer 1
optimizer_inmemory_aware boolean TRUE
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;
-- 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;
We have all learned and experienced that data access from memory is much faster than accessing it from the underlying physical disk. The Oracle 18.104.22.168 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.
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.