Explore Oracle Database In-Memory – Part 2

Oracle In-Memory is fully compatible with Oracle multitenant database architecture, allowing us to take advantage of fast In-Memory and low-cost storage. This multitenant architecture was also introduced in Oracle 12c, featuring multiple pluggable databases (PDBs) within a container database (CDB). This makes it easy to consolidate databases onto the cloud, deliver a high density of schema-based consolidation without making changes…

Oracle In-Memory is fully compatible with Oracle multitenant database architecture, allowing us to take advantage of fast In-Memory and low-cost storage.

This multitenant architecture was also introduced in Oracle 12c, featuring multiple pluggable databases (PDBs) within a container database (CDB). This makes it easy to consolidate databases onto the cloud, deliver a high density of schema-based consolidation without making changes to application, increase the utilization of resources, and allows for the rapid provision and cloning of databases for various purposes by lowering infrastructure and administrative costs.

The pluggable databases share the system global area (SGA) and background processes of a common container database, therefore PDBs also share a single IM column store. The size of the IM column store is controlled by the INMEMORY_SIZE parameter in the CDB.

Unless the INMEMORY_SIZE is set for a PDB, it inherits the CDB value. That means each PDB can see the entire IM Column store and has the potential to fully use it, and also that it’s possible for one PDB to starve another PDB space in the IM store due to oversubscription. For this reason, you should ensure that the INMEMORY_SIZE value should not oversubscribe for an extended period. Note that the CDB value is the maximum amount of memory available in the IM column store for CDB and PDBs including, root.

Not all PDBs in a given CDB need to use In-Memory column store, so if you do not want to enable In-Memory column store for a PDB you can set INMEMORY_SIZE parameter to “0”. If you set INMEMORY_SIZE at the PDB level you don’t need to restart the instance or PDB.

In-Memory Column Store on RAC

In a Real Application Cluster (RAC) environment, each node has its own IM Column store and it’s recommended that you size equally on each node. The objects populated into the memory are distributed across all IM column stores in the cluster. The object distribution in a cluster environment is controlled by DISTRIBUTE and DUPLICATE classes.

You can use one of the following sub-classes to distribute partition in cluster environment.

  • DISTRIBUTE BY PARTITION – To distribute partitions to different nodes
  • DISTRIBUTE BY SUBPARTITION – To distribute sub partitions to different nodes
  • DISTRIBUTE BY ROWID RANGE – To distribute by rowed range.

The following command distributes the TEST_PART table across the IM column stores by partition in the cluster.

For any RAC node that does not need IM Column store, set the INMEMORY_SIZE parameters to 0 (zero).

The DUPLICATE sub-class provides In-Memory fault tolerance – this is only applicable on an Oracle Engineered System. The DUPLICATE sub class can be used to mirror the data populated into the IM column store, meaning that you will have mirrored copy placed on one of the other nodes in the cluster.

By using DUPLICATE ALL sub-class with INMEMEORY attribute it is possible to populate an object into the IM column store on each node in the RAC cluster. This will provide highest level of redundancy and linear scalability.

How to manage In-Memory Tables

The CREATE TABLE/ALTER TABLE commands can be used with In-Memory clause to enable or disable a table for IM Column store. To enable a table for the IM column store, use the INMEMORY clause. To disable a table use the NO INMEMORY clause in a CREATE TABLE/ALTER TABLE statement.

In the following example we will create three tables:

In next example we will use ALTER TABLE to change the IM status:

At object level, the INMEMORY attribute can be used on Tables, materialized views and partitions. By using compression we can save space.

IM Column store Compression Methods:

Compression is considered to be a space saving method. The Oracle IM Column store compression uses a new set of compression algorithms which also improve performance. User queries run directly against the compressed column, which means that filtering and scanning will run against a smaller amount of data. When it is required for the result set, the data is decompressed. The compression is specified using the MEMCOMPRESS keyword.

There are different compression methods and each provides different level of performance and space saving.

Compression Method Description
NO MEMCOMPRESS Data is not compressed
MEMCOMPRESS FOR DML Optimized for DML with minimum compression
MEMCOMPRESS FOR QUERY LOW This is default value and optimized for best query performance
MEMCOMPRESS FOR QUERY HIGH Optimized for excellent performance and low space saving
MEMCOMPRESS FOR CAPACITY LOW Optimized for better space saving and good query performance
MEMCOMPRESS FOR CAPACITY HIGH Optimized for best space saving and fair query performance

The objects are populated using prioritized list or after the first query. The PRIORITY keyword will control the objects order in which they are populated. There are five priority levels: LOW|MEDIUM|HIGH|CRITICAL|NONE.

How to manage the In-Memory Column

You can apply different IM column store compression methods to different columns.

In the below example,

  • col1 enabled with MEMCOMPRESS FOR QUERY HIGH
  • col2 enabled with MEMCOMPRESS FOR CAPACITY HIGH
  • col3 not enabled for the IM column store

To view selective column compression levels for any database object, query V$IM_COLUMN_LEVEL view:

Oracle Compression Advisor has been enhanced to support In-Memory compression. This helps to provide an estimate of the compression ratio based upon the use of MEMCOMPRESS. This feature is available in Oracle Database 12.1.0.2 or later environments.

The DBMS_COMPRESSION package is used by Compression Advisor. This package has two main subprograms:

  • GET_COMPRESSION_RATIO – This function estimates the impact of different levels of compression on a table or partition.
  • GET_COMPRESSION_TYPE – This function returns the compression type for a specified row in a table.

By using the following example, we can get Compression Ratio and Compression type for table TEST_TABLE:

How to manage In-Memory Tablespace

If we enable INMEMORY at tablespace level then all tables and materialized views in that tablespace are enabled for IM column store unless explicitly overridden. By default, all columns in an object will be populated into the IM column store when we use the INMEMORY attribute.

We can enable/disable INMEMORY at the tablespace level.

In the following example:

  • We have disabled INMEMORY for tablespace TEST_TS1 using “NO INMEMORY” attribute
  • We have enabled INMEMORY for tablespace TEST_TS2 using “INMEMORY” attribute

Conclusion

Oracle In-Memory offers orders of magnitude performance improvements for analytics workloads, as well as substantial improvements for mixed-workload OLTP applications. Using this option, businesses can benefit from better decisions made in real-time, improved productivity, increased competitiveness and lowered costs.

References: