Introduction

Oracle Database In-Memory is a capability of Oracle Database to dramatically enhance eligible queries. Such queries can be accelerated hundreds of times by means of loading data into an In-Memory Columnar Store, which is part of the SGA. One of the reasons behind this striking query improvement is made possible by utilizing a means of vector CPU processing. On Intel, this CPU capability is called SIMD (Single Instruction Multiple Data). So even though database blocks maybe pinned into the Buffer Cache by means of ALTER TABLE .. KEEP command, still queries against the Buffer Cache is of no match to database blocks loaded into the In-Memory Columnar Store.

Oracle Database In-Memory was first introduced in Oracle Database 12c. Since then, there has been significant enhancements introduced to this great capability.
From another angle, Active Data Guard (ADG) is a capability of Oracle Database that enables queries to run on the standby database. This capability frees resources on the primary database by offloading queries to the standby site.
In Oracle Database version 12.2, it is possible to do columnar In-Memory queries on the standby database. This feature is only available with Exadata.

Configuration

Initialization parameters on the standby database:

  • inmemory_size = non-zero value that must be less than SGA_MAX_SIZE
  • inmemory_adg_enabled = TRUE

Create the necessary standby service on the primary database:

SQL> execute dbms_service.create_service (service_name => ‘standby_db’, network_name => ‘network_alias’);

For this example:

  • standby_db = any name for the service indicating the standby site.
  • network_alias = tnsnames.ora entry pointing to the standby database.

Start the service:

SQL> execute dbms_service.start_service (service_name => ‘standby_db’,  instance_name => ‘instance_name’);
  • instance_name = standby instance name.

Once the above steps are done, all what we need to do is to populate the tables in question onto the columnar store of the standby database.
This is done using the following command for each table on the primary database:

SQL> ALTER TABLE <schema.table> INMEMORY MEMCOMPRESS FOR <compression method> PRIORITY <loading priority> DISTRIBUTE FOR SERVICE standby_db;
  • INMEMORY = a flag to place the object into the In-Memory Columnar Store.
  • compression method = in-memory compression (QUERY LOW | QUERY IGH | CAPACITY LOW | CAPACITY HIGH) 
  • loading priority = whether this table should be loaded at instance startup, or defered to the first query on that table (NONE | LOW | MEDIUM | HIGH | CRITICAL)
  • DISTRIBUTE FOR SERVICE = a flag to place the object in the service standby_db.

For a full syntax of the ALTER TABLE .. INMEMORY …, please check: ALTER TABLE (oracle.com)

Note: It must be noted that if the tables are partitioned, then the above command will by default populate all the partitions belonging to that table.

To populate only a partition of a table, the following can be used:

SQL> ALTER TABLE <schema.table> MODIFY PARTITION <partition_name> INMEMORY MEMCOMPRESS <compression method > PRIORITY <loading priority> DISTRIBUTE FOR SERVICE standby_db;

After that, we need to ensure that the above DDLs are pushed to the standby:

SQL> alter system switch logfile; — repeat 2-3 times

At this moment, the dynamic performance view V$IM_SEGMENTS on the standby site must be queried till BYTES_NOT_POPULATED column is 0.
The below query can be used:

set pages 1000 lines 132
column name format a20
column owner format a15
column segment_name format a30
column populate_status format a20
column bytes_in_mem format 999,999,999,999,999
column bytes_not_populated format 999,999,999,999,999

SELECT
  v.owner, v.segment_name name, v.populate_status status,
  v.bytes bytes_in_mem, v.bytes_not_populated
FROM
  v$im_segments v;

References

Deploying an IM Column Store with Oracle Active Data Guard