Oracle in memory column store

Oracle in memory column store | IM column store

In-Memory Column Store (IM column store):

The IM Column Store stores the data by column rather than row.
The columnar format exists only in memory.
It is an optional STATIC component of the SGA.
IM Column store sub divided into two pools:
- 1 MB pool—Store the actual column formatted data
- 64k Pool--- Store the metadata about the objects.
Objects that are smaller than 64KB are not populated into memory.
No application changes are required.
Stores copies of tables and partitions in a special columnar format optimized for rapid scans.
IM column store is made up of multiple In-Memory Compression Units (IMCUs).
It is not a replacement of buffer cache, but acts as a supplement so that both memory areas store the same data in different formats.
The database keeps the columnar data transitionally consistent with the buffer cache.
By default, only objects specified as INMEMORY using DDL are candidates to be populated in the IM column store.

IM column store is useful for:

Performing fast full scans of large tables.
Evaluating predicate filters that use operators such as =, <, >, and IN
Querying a subset of columns in a table, for example, selecting 5 of 100 columns
Accelerating joins by converting predicates on small dimension tables into filters on a large fact table

Benefits:

Enables the database to perform scans, joins, and aggregates much faster than when it uses the on-disk format exclusively.
Business applications, ad-hoc analytic queries, and data warehouse workloads benefit most. Pure OLTP databases that perform short transactions using index lookups benefit less.
Compression is optimized for query performance. These compression techniques increase the effective memory bandwidth by enabling sessions to read more data into memory.
Fewer indexes, materialized views, and OLAP cubes are required. - The reduction in the number of pre-built objects results in reduced storage space and significantly less processing overhead.

Check IM column store is enabled or not:-

SQL> show parameter inmemory_size

When it is not enabled, it should display value 0, as follows

NAME TYPE VALUE
-----------------------------------------------------------------
inmemory_size big integer 0

If IM column store is enabled then you should see value for the parameter inmemory_size.

Before implementing IM column store:-

Inmemory_sizes is a component of SGA, means the amount of memory allocated to inmemory_size, will reduce SGA, if SGA is not increased.
The minimum size for inmemory_size is 100 MB.
In RAC environment inmemory_size parameter need to set same for all instances.
It is also possible to have the same objects appear in the IM column store on every node (Engineered Systems only).
Database must be restarted to enable IM column store.
Starting in 12.2, it is possible to increase the size of the In-Memory area on the fly, by increasing the INMEMORY_SIZE parameter via an ALTER SYSTEM command, assuming there is spare memory within the SGA. The INMEMORY_SIZE parameter must be increased by 128MB or more in order for this change to take effect.
It is not possible to shrink the size of the In-Memory area on the fly. A reduction in the size of the INMEMORY_SIZE parameter will not take effect until the database instance is restarted.
The In-Memory area is not impacted or controlled by Oracle Automatic Memory Management (AMM).
If inmemory_sizes is set at container each pluggable database inherits the value set at container level.
For pluggable databases it can be set for each pluggable database.
Sum of inmemory_size for all pluggable database do not need to be equal to inmemory_size of container. Sum of inmemory_size of all pluggable database could be more than the inmemory_size of container.

The following database objects cannot be populated in the IM column store:

Any object owned by the SYS user and stored in the SYSTEM or SYSAUX tablespace
Index Organized Tables (IOTs)
Clustered Tables

The following data types are also not supported in the IM column store:

LONGS (deprecated since Oracle Database 8i)
Out of line LOBS

How to configure IM column store:-

ALTER SYSTEM SET inmemory_size = 100G scope=spfile;

Bounce the database so these parameter changes can take effect.

SQL> show parameter inmemory_size

When it is not enabled, it should display value 0, as follows

NAME TYPE VALUE
-----------------------------------------------------------------
inmemory_size big integer 100G

How to implement IM column store:-

DDL statements specify the tablespaces, tables, partitions, or columns to be read into the IM column store. Only objects with the INMEMORY attribute are populated into the IM column store.

From White Paper: The INMEMORY attribute can be specified on a

tablespace,
table,
partition,
subpartition, or
materialized view.

From MOS 1903683.1: Q. If you want to be able to define at the partition level what columns will or won't be in the column store, how can this be done? For example, column1 in partition 1 might be INMEMORY, but column1 might not be INMEMORY for partition 2.

A.The inmemory_column_clause is a table-level clause and is not applicable to specific partitions; the view V$IM_COLUMN_LEVEL also specifies only the table name. Allowing this at the partition level would mean partitions couldn't be pruned at compile time, and would carry some other adverse side-effects; there are no plans to implement such a feature. You can add or remove columns from the IM column store at the table level by using syntax such as this:

Example:

ALTER TABLE <table_name> INMEMORY NO INMEMORY(<column_name>); -- puts the table INMEMORY for all partitions, but excludes the named column from the IM column store for all partitions.

Work around: ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NO INMEMORY

INMEMMORY for Table Space:-

If it is enabled at the tablespace level, then all new tables and materialized views in the tablespace will be enabled for the IM column store by default.

ALTER TABLESPACE ts_data DEFAULT INMEMORY;

Excludes column from INMEMORY:

The following statement sets the In-Memory attribute on the table SALES, in the SH sample schema, but it excludes the column PROD_ID.

ALTER TABLE sales INMEMORY NO INMEMORY(prod_id)

Remove object from INMEMORY:

To indicate an object is no longer a candidate, and to instantly remove it from the IM column store, simply specify the NO INMEMORY clause.

ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NO INMEMORY

Populate objects INMEMORY:

Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after they are scanned (queried) for the first time.

The order in which objects are populated is controlled by the keyword PRIORITY, which has five levels. The default PRIORITY is NONE, which means an object is populated only after it is scanned for the first time.

All objects at a given priority level must be fully populated before the population of any objects at a lower priority level can commence.

However, the population order can be superseded if an object without a PRIORITY is scanned, triggering its population into IM column store.

ALTER TABLE customers INMEMORY PRIORITY CRITICAL

PRIORITY:

CRITICAL - Object is populated immediately after the database is opened
HIGH - Object is populated after all CRITICAL objects have been populated, if space remains available in the IM column store
MEDIUM - Object is populated after all CRITICAL and HIGH objects have been populated, and space remains available in the IM column store
LOW - Object is populated after all CRITICAL, HIGH, and MEDIUM objects have been populated, if space remains available in the IM column store
NONE - Objects only populated after they are scanned for the first time (Default), if space is available in the IM column store

SQL execution plan depicts IN MEMORY:

To determine if a SQL statement is scanning data in the IM column store execution plan shows a new set of keywords "IN MEMORY".

In -Memory Storage Index:

In-Memory Storage Indexes that are automatically created and maintained on each of the columns in the IM column store. A further reduction in the amount of data accessed is possible because of In-Memory storage index.

An In-Memory Storage Index keeps track of minimum and maximum values for each column in an IMCU.

Storage Indexes allow data pruning to occur based on the filter predicates supplied in a SQL statement.

When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column is examined to determine if any entries with the specified column value exist in each IMCU by comparing the specified value(s) to the minimum and maximum values maintained in the Storage Index.

If the column value is outside the minimum and maximum range for an IMCU, the scan of that IMCU is avoided.

INMEMORY Compression:

In-memory compression is specified using the keyword MEMCOMPRESS, a sub-clause of the INMEMORY attribute. There are six levels, each of which provides a different level of compression and performance.

In-memory COMPRESSION LEVEL

NO MEMCOMPRESS - Data is populated without any compression
MEMCOMPRESS FOR DML - Minimal compression optimized for DML performance
MEMCOMPRESS FOR QUERY LOW - Optimized for query performance (default)
MEMCOMPRESS FOR QUERY HIGH - Optimized for query performance as well as space saving
MEMCOMPRESS FOR CAPACITY LOW - Balanced with a greater bias towards space saving
MEMCOMPRESS FOR CAPACITY HIGH - Optimized for space saving

By default, data is compressed using the FOR QUERY LOW option, which provides the best performance for queries.

This option utilizes common compression techniques such as Dictionary Encoding, Run Length Encoding and Bit-Packing.

The FOR CAPACITY options apply an additional compression technique on top of FOR QUERY compression, which can have a significant impact on performance as each entry must be decompressed before the WHERE clause predicates can be applied.

The FOR CAPACITY LOW option applies a proprietary compression technique called OZIP that offers extremely fast decompression that is tuned specifically for Oracle Database. The FOR CAPACITY HIGH option applies a heavier-weight compression algorithm with a larger penalty on decompression in order to provide higher compression.

Compression ratios can vary from 2X – 20X, depending on the compression option chosen, the datatype, and the contents of the table.

The compression technique used can vary across columns, or partitions within a single table.

For example, you might optimize some columns in a table for scan speed, and others for space saving.

CREATE TABLE employees
(
c1 NUMBER,
c2 NUMBER,
c3 VARCHAR2(10),
c4 CLOB
) INMEMORY MEMCOMPRESS FOR QUERY

NO INMEMORY(c4) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH(c2)

INMEMORY and EXADATA:

In 12.2, it is possible to store data in the In-Memory columnar format in the flash cache in an Exadata environment.

This enables all of the In-Memory optimizations (accessing only the compressed columns required, SIMD (Single Instruction processing Multiple Data values) vector processing, storage indexes, etc.) to be used on a potentially much larger amount of data.

When the INMEMORY_SIZE parameter is set to a non-zero value, objects compressed using Hybrid Columnar Compression (HCC) brought into Exadata flash cache will be automatically converted into the In-Memory columnar format.

For INMEMORY compression levels in EXADATA

MEMCOMPRESS FOR QUERY LOW and
MEMCOMPRESS FOR CAPACITY LOW (default)

ALTER TABLE <table_name> CELLMEMORY MEMCOMPRESS FOR QUERY LOW

The PRIORTY sub-clause is also not available, as there are no background worker processes active on the Exadata storage cells (where the flash cache resides) to do the population on startup. Therefore, only on demand population is possible.

Note: Only objects that are compressed using Hybrid Columnar Compression on disk are eligible to be populated into the flash cache in the In-Memory columnar format.
This restriction is to ensure that objects populated into flash in the In-Memory columnar format are unlikely to be changing rapidly and therefore the overhead to maintain the In-Memory columnar format in flash will be minimal.

IN MEMORY Column Store and RAC:

It is highly recommended that the IM RAC node. Any RAC node that does not require an IM column store should have the INMEMORY_SIZE parameter set to 0.

It is also possible to have the same objects appear in the IM column store on every node (Engineered Systems only).

By default all objects populated into memory will be distributed across all of the IM column stores in the cluster.
By default, Oracle decides the best way to distribute the object across the cluster given the type of partitioning used (if any).

The distribution of objects across the IM column stores in a cluster is controlled by two additional sub-clauses to the INMEMORY attribute: DISTRIBUTE and DUPLICATE.

ALTER TABLE <TABLE_NAME> INMEMORY DISTRIBUTE BY PARTITION

You can specify

DISTRIBUTE BY ROWID RANGE to distribute by rowid range
DISTRIBUTE BY PARTITION to distribute partitions to different nodes, or
DISTRIBUTE BY SUBPARTITION to distribute sub-partitions to different nodes.

If the tables are partitioned or sub-partitioned by HASH and a partition-wise join plan is expected, DISTRIBUTE BY PARTITION or SUBPARTITION is recommended

For non-partitioned tables or for partitioned tables where DISTRIBUTE BY PARTITION would lead to data skew DISTRIBUTE BY ROWID RANGE can be used.

If the object is very small (consists of just 1 IMCU), it will be populated into the IM column store on just one node in the cluster.

In RAC, the IMCUs are *distributed* among the IM column stores on ALL nodes as long as PARALLEL_INSTANCE_GROUP is not set. That means that data that is in the IM column store on one node will not also be in the IM column store on another node (see exception for DUPLICATE parameter below*). If PARALLEL_INSTANCE_GROUP is set, then the IMCUs and parallelism are restricted to the IM column stores on the nodes pointed to by the service name defined in this parameter. You can control the type of distribution of the IMCUs across nodes with the DISTRIBUTION parameter.

We recommend you apply a patch for unpublished Bug 22013607 - "COST OF SERIAL IM FULL TABLE SCAN DOESN'T ACCOUNT FOR NECESSARY IO ON RAC," which addresses some cases where queries ran serially instead of in parallel, and thus could not access data in other nodes' IM column stores.

It is possible to have completely different objects populated on every node, or to have larger objects distributed across all of the IM column stores in the cluster. It is also possible to have the same objects appear in the IM column store on every node (restricted to only engineered systems with InfiniBand (IB), since IMCUs are not shipped over the interconnect). The distribution of objects across the IM column stores in a cluster is controlled by two additional sub-clauses to the INMEMORY attribute; DISTRIBUTE and DUPLICATE (restricted).

On an Engineered System it is possible to mirror the data populated into the IM column store by specifying the DUPLICATE sub-clause of the INMEMORY attribute.
This means that each IMCU populated into the IM column store will have a mirrored copy placed on one of the other nodes in the RAC cluster.
Mirroring the IMCUs provides in-memory fault tolerance as it ensures data is still accessible via the IM column store even if a node goes down.

It also improves performance, as queries can access both the primary and the backup copy of the IMCU at any time.

Caution: Normally, you will not have PARALLEL_INSTANCE_GROUP set unless you are trying to restrict both parallelism and the IM column store to a subset of nodes. Setting PARALLEL_INSTANCE_GROUP to an invalid value will cause your IM column store to not load anything. See note "RAC: How to control parallel execution across instances in 11g? (Doc ID 1207190.1)," which applies to 11g+, or Part 2 of the blog mentioned above.

Note: In 12.2, we recommend using the DISTRIBUTE ... FOR SERVICE ... clause instead of the PARALLEL_INSTANCE_GROUP. See Configuring an In-Memory Service for a Subset of Nodes: Example in the Oracle documentation for a good example.

Transaction Processing:

Single row data change operations (DML) execute via the row store (OLTP style changes), just as they do without Database In-Memory enabled.

If the object in which the DML operations occur is populated in the IM column store, then the changes are reflected in the IM column store as they occur.

The row store and the column store are always kept transactionally consistent, similarly to the way indexes are kept consistent.

All serialization and logging is done on the base table just as it was before. No additional locks or logging are needed for the In-Memory Column store.

For each IMCU in the IM column store, a Snapshot Metadata Unit (SMU) is automatically created and maintained. When a DML statement changes a row in an object that is populated into the IM column store, the corresponding column entries for that row are marked stale in the IMCU and the rowid is added to the metadata in the SMU.

The original entries in the IMCU are not immediately replaced in order to provide read consistency and maintain data compression.

Any transaction executing against the object in the IM column store, that started before the DML occurred, can still see the original version of the entries in the IMCU.

Read consistency in the IM column store is managed via System Change Numbers (SCNs) just as it is without Database In-Memory enabled.

When a query with a newer SCN is executed against the object, it will read all of the entries for the columns in the IMCU except the stale entries. The stale entries will be retrieved from the base table (i.e. the row store).

Repopulation:

The more stale entries there are in an IMCU, the slower the scan of the IMCU will become.

Therefore Oracle Database will repopulate an IMCU when the number of stale entries in an IMCU reaches a staleness threshold.
The staleness threshold is determined by heuristics that take into account the frequency of IMCU access and the number of stale rows in the IMCU.
Repopulation is more frequent for IMCUs that are accessed frequently or have a higher percentage of stale rows.
The repopulation of an IMCU is an online operation executed by the background worker processes.
The data is available at all times and any changes that occur to rows in the IMCU during repopulation are automatically recorded.
In addition to the standard repopulation algorithm, there is another algorithm that attempts to clean all stale entries using a low priority background process.

The IMCO (In-Memory Coordinator) background process may also instigate trickle repopulation for any IMCU in the IM column store that has some stale entries but does not currently meet the staleness threshold.

Trickle repopulate is a constant background activity.

The IMCO wakes up every two minutes and checks to see if any population tasks need to be completed. For example, the INMEMORY attribute has just been specified with a PRIORITY sub-clause on a new object. The IMCO will also check to see if there are any IMCUs with stale entries in the IM column store. If it finds some it will trigger the worker processes to repopulate them. The number of IMCUs repopulated via trickle repopulate in a given 2 minute window is limited by the new initialization parameter INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT. This parameter controls the maximum percentage of time that worker processes can participate in trickle repopulation activities. The more worker processes that participate, the more IMCUs that can be trickle repopulated. However, the more worker processes that participate the higher the CPU consumption. You can disable trickle repopulation altogether by setting INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT to 0.

Parameters related with IM column store:-

inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ percent integer 1
optimizer_inmemory_aware boolean TRUE

To temporarily disable all in-memory queries for the entire database, set this to DISABLE.

Inmemory_size:

Parameter type Big integer
Syntax - INMEMORY_SIZE = integer [K | M | G]
Default value – 0
Modifiable - ALTER SYSTEM
Modifiable in a PDB – Yes
Range of values - 0 to the amount of memory left in the SGA after other allocations
Oracle RAC - All instances should use the same value.

INMEMORY_SIZE sets the size of the In-Memory Column Store (IM column store) on a database instance.

The default value is 0, which means that the IM column store is not used.
The database must be restarted after setting this parameter to enable the IM column store.
The minimum size to which this parameter can be set is 100 MB.
Typically this parameter should be set to at least the size needed to accommodate all the tables that will use the IM column store. It can be set higher to allow for growth of those tables or to accommodate other tables that will use the IM column store in the future.

This parameter can also be set per PDB to limit the maximum size of the IM column store for that PDB. Note that the sum of the PDB values do not have to equal the CDB value, and the sum of the PDB values may even be greater than the CDB value.
Unless this parameter is specifically set on a PDB, each PDB inherits the CDB value, which means they can use all of the available IM column store.

inmemory_query:

INMEMORY_QUERY is used to enable or disable in-memory queries for the entire database at the session or system level. This parameter is helpful when you want to test workloads with and without the use of the In-Memory Column Store (IM column store).

This parameter enables in-memory queries for the entire database by default when the INMEMORY_SIZE parameter is specified.
Set this parameter to DISABLE if you want to disable in-memory queries.
Default value – ENABLE
Modifiable in a PDB – Yes
Oracle RAC - All instances should use the same value.

inmemory_clause_default:

The INMEMORY_CLAUSE_DEFAULT parameter allows you to specify a default mode for in-memory tables by specifying a valid set of values for all of the INMEMORY sub-clauses, (such as PRIORITY LOW, PRIORYT HIGH, PRIORITY CRITICAL) and not explicitly specified in the syntax.

The default value is an empty string, which means that only explicitly specified tables are populated into the IM column store.

ALTER SYSTEM SET inmemory_clause_default = 'INMEMORY PRIORITY LOW'

The parameter value is parsed in the same way as the INMEMORY clause, with the same defaults if one of the sub-clauses is not is specified. Any table explicitly specified for in-memory will inherit any unspecified values from this parameter.

inmemory_force:

The default value is DEFAULT. When this value is in effect, the IM column store is populated only with tables and materialized views specified as INMEMORY

INMEMORY_FORCE allows you to specify whether tables and materialized view that are specified as INMEMORY are populated into the In-Memory Column Store (IM column store) or not.

If OFF is specified, then even if the IM column store is configured on this instance, no tables or materialized are populated in memory.

inmemory_max_populate_servers:

Modifiable in a PDB – No
Oracle RAC - All instances should use the same value.
Default value - Half the effective CPU thread count or the PGA_AGGREGATE_TARGET value divided by 512M, whichever is less
Range of values - 0 to a value based on the number of cores in the system.

INMEMORY_MAX_POPULATE_SERVERS specifies the maximum number of background populate servers to use for In-Memory Column Store (IM column store) population, so that these servers do not overload the rest of the system.

This parameter has meaning only if the INMEMORY_SIZE parameter is also set to a positive value.

The value to use for this parameter depends on the number of cores in the system. A certain percentage of CPU should be allocated for in-memory background population, and this parameter should be set accordingly.

You can also set this parameter to 0 to temporarily disable populate tasks on the system from executing.

inmemory_trickle_repopulate_servers_ percent:

Default value – 1
Modifiable in a PDB – No
Range of values - 0 to 50
Oracle RAC - All instances should use the same value.

Limits the maximum number of background populate servers used for In-Memory Column Store (IM column store) repopulation, as trickle repopulation is designed to use only a small percentage of the populate servers.

The value for this parameter is a percentage of the INMEMORY_MAX_POPULATE_SERVERS initialization parameter value.

For example, if this parameter is set to 5 and INMEMORY_MAX_POPULATE_SERVERS is set to 10, then on average half of a core is used for trickle repopulation.

The default value of 1 is good in most cases. In some cases, if you want to disable trickle repopulate, this parameter can be set to 0. If you want to keep the system more aggressively up to date (at the expense of more background CPU), you can set the parameter to higher values such as 5 or 10.

A value of greater than 50 is not allowed, so that at least half of the populate servers are available for other (re)populate tasks. On some systems, a value of less than 50 can be problematic, depending on other workload.

This parameter has meaning only if the INMEMORY_SIZE parameter is also set to a positive value.

optimizer_inmemory_aware:

Default value – true
Modifiable - ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB – Yes
Range of values - true | false
Oracle RAC - All instances should use the same value

OPTIMIZER_INMEMORY_AWARE enables or disables all of the optimizer cost model enhancements for in-memory. Setting the parameter to false causes the optimizer to ignore the in-memory property of tables during the optimization of SQL statements. This behavior can also be achieved by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to values lower than 12.1.0.2.

OPTIMIZER_FEATURES_ENABLE:

Default value - 12.1.0.2
Modifiable - ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB - Yes

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number. For example, if you upgrade your database from release 11.1 to release 12.1, but you want to keep the release 11.1 optimizer behavior, you can do so by setting this parameter to 11.1.0.7. At a later time, you can try the enhancements introduced in releases up to and including release 12.1 by setting the parameter to 12.1.0.2.

INMEMORY_EXPRESSIONS_USAGE: For 12c R2

Controls which In-Memory Expressions (IM expressions) are populated into the In-Memory Column Store (IM column store) and are available for queries.

INMEMORY_EXPRESSIONS_USAGE = { STATIC_ONLY | DYNAMIC_ONLY | ENABLE | DISABLE }
Default value – ENABLE
Modifiable in a PDB - Yes

Oracle RAC - The same value must be used on all instances.

STATIC_ONLY: Tables enabled for in-memory and containing certain data types such as Oracle numbers or JSON will have these columns populated in the IM column store using a more efficient representation. Note that this setting will increase the in-memory footprint for some tables. A static configuration enables the IM column store to cache OSON (binary JSON) columns, which are marked with an IS_JSON check constraint. Internally, an OSON column is a hidden virtual column named SYS_IME_OSON. In contrast, a dynamic configuration automatically creates and populates frequently used expressions.

DYNAMIC_ONLY: IM expressions will be automatically created and populated into the IM column store, if used in conjunction with PL/SQL procedure DBMS_INMEMORY.IME_CAPTURE_EXPRESSIONS. Note that setting this value will increase the in-memory footprint for some tables. This value should not be used in an Oracle Cloud environment.

ENABLE: Both static and dynamic IM expressions will be populated into the IM column store and available to be used by queries. Note that setting this value will increase the in-memory footprint for some tables. This is the default value. This value should not be used in an Oracle Cloud environment.

DISABLE: No IM expressions of any kind will be populated into the IM column store.

Changing the mode of a parameter does not have an immediate effect on existing in-memory data. For example, if the inmemory_expressions_usage clause is changed from DYNAMIC_ONLY to DISABLE, the stored IM expressions are not immediately removed. The next repopulation will not bring back the expressions in memory, thereby effectively removing the expressions.

In-Memory FastStart:

In-Memory population is a CPU bound operation, involving reformatting data into a columnar format and compressing that data before placing it in memory. New in 12.2, it is possible to checkpoint IMCUs to disk to relieve the CPU overhead of population, at the cost of additional disk space and IO bandwidth.

The FastStart area is a designated tablespace where In-Memory objects are stored and managed. The IM FastStart service is database specific, such that only one FastStart area is permitted for each database or Pluggable Database (PDB) in a Container Database (CDB) environment and is automatically enabled for all In-Memory objects except for objects compressed with “NO MEMCOMPRESS”, “MEMCOMPRESS FOR DML” or with Join Groups defined on them.

When In-Memory FastStart (IM FastStart) is enabled, the system checkpoints the IMCUs from the IM column store to the FastStart area on disk. On subsequent database restarts, data is populated via the FastStart area rather than from the base tables.

Enable FastStart:

BEGIN dbms_inmemory_admin.faststart_enable('FS_TBS'); END;

In-Memory and amount of data:

Analytic queries typically reference only a small subset of the columns in a table. Oracle Database In-Memory accesses only the columns needed by a query, and applies any WHERE clause filter predicates to these columns directly without having to decompress them first. This greatly reduces the amount of data that needs to be accessed and processed.

SIMD (Single Instruction processing Multiple Data Value) Vector processing:

For the data that does need to be scanned in the IM column store, Database In-Memory uses SIMD vector processing (Single Instruction processing Multiple Data values). Instead of evaluating each entry in the column one at a time, SIMD vector processing allows a set of column values to be evaluated together in a single CPU instruction.

In-Memory Virtual Columns:

New in 12.2, user-defined virtual columns can now be populated in the IM column store. Virtual columns will be materialized as they are populated and since the expression is evaluated at population time it can be retrieved repeatedly without re-evaluation. The initialization parameter INMEMORY_VIRTUAL_COLUMNS must be set to ENABLE or MANUAL to create user-defined In-Memory virtual columns. When set to ENABLE all user-defined virtual columns on a table with the INMEMORY attribute, will be populated into the IM column store. However, it is possible to have just a subset of virtual columns be populated.

Manually Create Virtual Column:

Create virtual columns for the two expressions

ALTER TABLE lineorder ADD sum_disc_price AS (lo_extendedprice * (1 - lo_discount))

ALTER TABLE lineorder ADD sum_charge AS (lo_extendedprice * (1 - lo_discount) * (1 + lo_tax))

Enable the INMMEORY attribute on the Lineorder table

ALTER TABLE lineorder INMEMORY PRIORITY HIGH

In-Memory Expressions:

Analytic queries often contain complex expressions in the select list or where clause predicates that need to be evaluated for every row processed by the query. The evaluation of these complex expressions can be very resource intensive and time consuming.

New in 12.2, In-Memory Expressions provide the ability to materialize commonly used expressions in the IM column store. Materializing these expressions not only improves the query performance by preventing the re-computation of the expression for every row but it also enables us to take advantage of all of the In-Memory query performance optimizations when we access them.

An In-Memory Expression can be a combination of one or more values, operators, and SQL or PL/SQL functions (deterministic only) that resolve to a value. They must be derived only from the table they are associated with, which means that they cannot access column values in a different table. In-Memory Expressions can be created either manually via virtual columns or automatically via the Expression Statistics Store (ESS).

Automatically Detected In-Memory Expressions:

In-Memory Expressions can also be automatically detected using the ESS (Expression Statistics Store) and the new procedure in the DBMS_INMEMORY_ADMIN package. When you execute the IME_CAPTURE_EXPRESSIONS procedure, the 20 most frequently executed expressions, as determined by the Optimizer, are captured from the ESS and populated automatically into the IM column store. Automatically added expressions are created as hidden virtual columns and a full list of the expressions captured can be found in the view USER_IM_EXPRESSIONS.

Populate the 20 most frequently executed expressions from the ESS into the IM column store:

Capture the expressions for ESS:

BEGIN dbms_inmemory_admin.ime_capture_expressions('CURRENT'); END;

Check what expressions were captured:

SELECT * FROM user_im_expressions

Populate the captured expression in the IM column store

BEGIN dbms_inmemory_admin.ime_populate_expressions; END;

This feature also requires the setting of the initialization parameter INMEMORY_EXPRESSIONS_USAGE, to determine what type of In-Memory Expressions are eligible to be populated. Currently in 12.2, In-Memory Expressions and In-Memory Virtual Columns are not candidates to be check-pointed to disk using In-Memory FastStart. Only the user defined columns of a table are written to the FastStart area.

IN MEMORY Column Store and DOP:

Oracle In-Memory RAC is shared-nothing architecture for queries. i.e if you raise a query against inmemory data objects (assume objects are distributed in Two IM-Column stores),can access the data resides in the affinity node only, means do not share the IMCU's (Inmemory compression units) across the instances in the cluster.

So, you can set the degree of parallelism (DOP) to AUTO. The parallel query coordinator identify the other instances of IMCU' location.

If you can’t set the DOP to AUTO, the parallel query coordinator won't use the other instances of IMCU's.

Monitoring inmemory objects:

v$IM_SEGMENTS or v$IM_USER_SEGMENTS and
v$IM_COLUMN_LEVEL

set linesize 256
set pagesize 999
select segment_name,ROUND(SUM(BYTES)/1024/1024/1024,2) "DATA GB", ROUND(SUM(INMEMORY_SIZE)/1024/1024/1024,2) "IN-MEM GB",
ROUND(SUM(BYTES-BYTES_NOT_POPULATED)*100/SUM(BYTES),2) "% IN_MEM",ROUND(SUM(BYTES-BYTES_NOT_POPULATED)/SUM(INMEMORY_SIZE),2) "COMP RATIO"
from V$IM_SEGMENTS group by owner,segment_name order by SUM(bytes) desc;

set linesize 256
set pagesize 999
set verify off
col OBJECT format a30

SELECT owner||'.'||table_name OBJECT,inmemory INMEMORY,inmemory_priority PRIORITY,
inmemory_distribute DISTRIBUTE,inmemory_compression COMPRESSION,
inmemory_duplicate DUPLICATE FROM all_tables where owner='<owner_name>'

Known Bugs:

Bug 19192570 ORA-600 [too-many-ref] during IM (inmemory) populate.
Bug 19192600 - ORA-8103 from IM (inmemory) populate (Doc ID 19192600.8)
ORA 600 [25027] on INMEMORY Table (Doc ID 2280246.1)
Bug 21378561 shared pool latch contention when populating inmemory store
Incorrect results or ORA-1841 when index partitions/sub-partitions are unusable or INMEMORY (Doc ID 2055117.1)
Bug 23738553 - group by is not pushed down to table level for inmemory tables (3) (Doc ID 23738553.8)
Bug 19192570 - ORA-600 [too-many-ref] during IM (inmemory) populate (Doc ID 19192570.8)
Execution Plan Shows that Query Uses the “In-Memory Option” but Query is not Executed “In-Memory“ (Doc ID 1950831.1)
Oracle Database In-Memory Option (DBIM) Basics and Interaction with Data Warehousing Features (Doc ID 1903683.1)
The current In-Memory bug numbers associated with parallel execution are:
18960760 – AUTODOP ON RAC WITH IMC IN DISTRIBUTE MODE SHOULD BE ROUNDED TO # OF INSTANCES
19913129 – CHECK FOR EXPENSIVE PARALLEL OPERATIONS NEEDS TO BE OVERRIDDEN FOR IMC

New Features:

Document 2242973.1 12.2 New Feature: Database In-Memory (IM) Column Store Expressions
Document 2242985.1 12.2 New Feature: Database In-Memory (IM) FastStart
Document 2242995.1 12.2 New Feature: Database In-Memory (IM) Column Store Join Groups
Document 2211831.1 Automatic Data Optimization (ADO) Supports Database In-Memory Column Store in 12.2>)
Dynamic Resizing of INMEMORY_SIZE (see question 3. Will objects age out of the IM column store, and can I increase the size of the IM column store dynamically?)
Database In-Memory Guide: Deploying an IM Column Store with Oracle Active DataGuard

FAQ:

01. How INMEMMORY column store data in memory?
A-01. INMEMMORY column consists of multiple IMCU - In memory compression unit, like extents for tablespace.

02. What background process populate INMEMORY column data?
A-02 Worker processes such as ora_w001_<db_name>

03. How INMEMORY column differ from pure in memory database?
A-03. Database for pure in memory is accessible only when all of the data is populated in memory, while for INMEMORY column database is accessible while it is populating data into memory.

04. How can I estimate how much memory I need to allocate for each object I want to put in the IM column store?
A-04 Use DBMS_COMPRESSION.GET_COMPRESSION_RATIO package and procedures, refer - MOS - Doc ID 1903683.1

05. Is INMEMORUY Column store compression the same as Advanced Compression?
A-05. The IM column store has its own compression algorithms, which are completely different from those of Advanced Compression. Advanced Compression focuses on saving space;

The IM column store uses compression to improve query performance, as well as save space. The default, MEMCOMPRESS FOR QUERY LOW, gives the best performance of all the compression methods.

06. Is there an Advisor for the IM column store?
A-06. The In-Memory Advisor, which is licensed as part of the Database Tuning Pack, analyzes your workload and gives advice on how to configure the IM column store.

See Document 1965343.1 Oracle Database In-Memory Advisor for information about this new Advisor

07. What happens if data on disk is compressed with either Basic or Advanced Compression?
A-07 If data on disk is compressed with either Basic or Advance Compression, it is read into memory uncompressed, pivoted 90 degrees, then recompressed per the INMEMORY compression format that you chose (or defaulted).

08. Will objects be imported with the INMEMORY attribute?
You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:y option of the impdp command. With this option, Data Pump keeps the IM column store clause for all objects that have one. When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops the IM column store clause from all objects that have one.

You can also use the TRANSFORM=INMEMORY_CLAUSE:string option to override the IM column store clause for a database object in the dump file during import. For example, you can use this option to change the IM column store compression for a database object being imported.

Example (note the \ characters are necessary to "escape" the double quotes so they are not stripped by the OS):

transform=inmemory_clause:\"INMEMORY MEMCOMPRESS FOR DML PRIORITY CRITICAL\"

09. Can I put my materialized view logs in the IM column store?

You should put your materialized view log in the IM column store ONLY if (1) you need the refreshes to be very fast AND (2) the volume of DML isn't too high. If the underlying table has a lot of DML going on, having the materialized view log in the IM column store may actual slow the DML down as we will have to maintain the log both in the IM column store and on disk.

-- this would put the materialized view log for scott.emp in the IM column store

alter table scott.mlog$_emp inmemory;

Ref.:

https://docs.oracle.com/database/121/REFRN/GUID-B5BEB6BF-5308-485F-920D-CBB584DDDE8F.htm#REFRN10348

https://blogs.oracle.com/in-memory/getting-started-with-oracle-database-in-memory-part-i-installing-enabling

http://en.community.dell.com/techcenter/enterprise-solutions/w/oracle_solutions/11379.how-do-i-configure-oracle-database-12c-in-memory-option

http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.pdf

https://en.wikipedia.org/wiki/List_of_in-memory_databases

http://www.oracle.com/technetwork/issue-archive/2015/15-jan/o15asktom-2398997.html