Saturday, 15 October 2016

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 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.

ALTER TABLE test_part INMEMORY DISTRIBUTE BY PARTITION;
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.

ALTER TABLE test_part INMEMORY DUPLICATE ALL;

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:

CREATE TABLE Inmem_table (
     c_id     NUMBER,
     col1   VARCHAR2(10)) INMEMORY;

CREATE TABLE No_Inmem_table (
     c_id     NUMBER,
     col1   VARCHAR2(10)) NO INMEMORY;

CREATE TABLE test_table (
     c_id     NUMBER,
     col1   VARCHAR2(10),
     col2   VARCHAR2(10),
     col3   VARCHAR2(10));

SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION,INMEMORY_DUPLICATE FROM   USER_TABLES ;

TABLE_NAME INMEMORY INMEMORY
NO_INMEM_TABLE DISABLED 
INMEM_TABLE ENABLED NONE
TEST_TABLE DISABLED

INMEMORY_DISTRIINMEMORY_COMPRESSINMEMORY_DUPL


AUTOFOR QUERY LOW NO DUPLICATE




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

SQL> ALTER TABLE test_table INMEMORY;

SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION,INMEMORY_DUPLICATE FROM USER_TABLES ;

TABLE_NAMEINMEMORYINMEMORY
NO_INMEM_TABLEDISABLED 
INMEM_TABLEENABLEDNONE
TEST_TABLEENABLED  NONE

INMEMORY_DISTRIINMEMORY_COMPRESSINMEMORY_DUPL


AUTOFOR QUERY LOW NO DUPLICATE
AUTOFOR QUERY LOW NO DUPLICATE

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
SQL> ALTER TABLE test_table 
INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col2)
NO INMEMORY (col3);

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

SQL> SELECT table_name, segment_column_id, column_name, inmemory_compression
FROM   v$im_column_level
WHERE  owner = 'TEST' AND table_name = 'TEST_TABLE'
ORDER BY segment_column_id;

TABLE_NAME SEGMENT_
COLUMN_ID
COLUMN_NAME INMEMORY_COMPRESSION
TEST_TABLE 1 C_ID DEFAULT
TEST_TABLE COL1 FOR QUERY HIGH
TEST_TABLE COL2 FOR CAPACITY HIGH
TEST_TABLE COL3 NO INMEMORY

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:

DECLARE
 l_blkcnt_cmp       PLS_INTEGER;
 l_blkcnt_uncmp     PLS_INTEGER;
 l_row_cmp          PLS_INTEGER;
 l_row_uncmp        PLS_INTEGER;
 cmp_ratio          PLS_INTEGER;
 comptype_str       VARCHAR2(100);
 comp_ratio_allrows NUMBER := -1;
BEGIN
 DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
 scratchtbsname  => 'TEST_TS1',
 ownname         => 'TEST',
 objname         => 'TEST_TABLE',
 subobjname      => NULL,
 comptype        => dbms_compression.comp_inmemory_query_low,
 blkcnt_cmp => l_blkcnt_cmp,
 blkcnt_uncmp => l_blkcnt_uncmp,
 row_cmp => l_row_cmp,
 row_uncmp => l_row_uncmp,
 cmp_ratio => cmp_ratio,
 comptype_str => comptype_str,
 subset_numrows  => dbms_compression.comp_ratio_allrows);
 dbms_output.Put_line('The compression ratio : '|| cmp_ratio);
 dbms_output.Put_line('The compression Type  : '|| comptype_str);
END;
/

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.

CREATE TABLESPACE TEST_TS1 datafile '/oradata/TESTDB/TEST_TS101.dbf' size 100m DEFAULT INMEMORY;
CREATE TABLESPACE TEST_TS2 datafile '/oradata/TESTDB/TEST_TS101.dbf' size 100m DEFAULT INMEMORY;

SQL> SELECT TABLESPACE_NAME, DEF_INMEMORY,DEF_INMEMORY_PRIORITY,    DEF_INMEMORY_DISTRIBUTE,  DEF_INMEMORY_COMPRESSION 
FROM   DBA_TABLESPACES ORDER BY TABLESPACE_NAME;  

TABLESPACE_NAME   DEF_INMEMORY    DEF_INME   DEF_INMEMORY_DI      DEF_INMEMORY_COMP 
-----------------------------------------------------------------------------------
SYSAUX                      DISABLED
SYSTEM                      DISABLED
TEMP                           DISABLED
TEST_TS1                    ENABLED                NONE      AUTO     FOR QUERY LOW     
TEST_TS2                    DISABLED
UNDOTBS1                  DISABLED
USERS                         DISABLED

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
ALTER TABLESPACE test_ts2 DEFAULT INMEMORY;
ALTER TABLESPACE test_ts1 DEFAULT NO INMEMORY;

SQL> SELECT TABLESPACE_NAME, DEF_INMEMORY,DEF_INMEMORY_PRIORITY,    DEF_INMEMORY_DISTRIBUTE,  DEF_INMEMORY_COMPRESSION 
FROM   DBA_TABLESPACES ORDER BY TABLESPACE_NAME;  

TABLESPACE_NAME  DEF_INMEMORY   DEF_INME   DEF_INMEMORY_DI   DEF_INMEMORY_COMP 
----------------------------------------------------------------------------------------
SYSAUX                      DISABLED
SYSTEM                       DISABLED
TEMP                           DISABLED
TEST_TS1                    DISABLED    
TEST_TS2                    ENABLED               NONE       AUTO      FOR QUERY LOW     
UNDOTBS1                  DISABLED
USERS                         DISABLED