12c in-Memory Oracle

Introduction

• Oracle Database has traditionally stored data in a row format.

• In a row format database, each new transaction or record stored in the database is represented as a new row in a table.

• That row is made up of multiple columns, with each column representing a different attribute about that record.

• A row format is ideal for online transaction systems, as it allows quick access to all of the columns in a record since all of the data for a given record are

• kept together in-memory and on-storage.

• The In-Memory Column Store (IM column store) is the headline feature of the 12.1.0.2 patch set.

• This features allows us to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the traditional row format.

• Extremely well for analytic queries found in business intelligence products.

• Up until now We have been forced to pick just one format and suffer the tradeoff of either

• sub-optimal OLTP or

More about In-Memory

• Oracle Database In-Memory (Database In-Memory) provides the best of both worlds by allowing data to be simultaneously

a. Populated in both an in-memory row format (the buffer cache) and

b. A new in-memory column format[In-memory Area]

• Less than a 20% overhead in terms of total memory requirements

• There remains a single copy of the table on storage,

• so there are no additional storage costs or synchronization issues.

• The database maintains full transactional consistency between the row and the columnar formats, just as it maintains consistency between tables and indexes.

• The Oracle Optimizer is fully aware of the column format: It automatically routes analytic queries to the column format and OLTP operations to the row format.

Implementation at the Database level

• The In-Memory column store is a new static pool of the SGA, sized using the

INMEMORY_SIZE initialization parameter [default 0].

• Database In-Memory uses an In-Memory column store (IM column store), which is a new

component of the Oracle Database System Global Area (SGA), called the In-Memory Area.

• The IM column store does not replace the buffer cache, but acts as a supplement

• We can choose to store specific groups of columns, whole tables, materialized views or table partitions in the store.

• Alternatively, we can enable IM column store at the tablespace level, so all tables and materialized views in the tablespace are automatically enabled for the IM column store.

• Of course, if our database is small enough, we can populate all of our tables into the IM column store.

Enable In-Memory Column Store

• So the SGA must be capable of containing the amount of memory we want to assign to the INMEMORY_SIZE parameter.

• In a multitenant environment, the INMEMORY_SIZE parameter must be set in the CDB if any of the PDBs need access to the IM column store.

ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE; ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE; SHUTDOWN IMMEDIATE;

STARTUP;

ORACLE instance started.

Total System Global Area 3221225472 bytes Fixed Size 2929552 bytes

Variable Size 419433584 bytes Database Buffers 637534208 bytes Redo Buffers 13844480 bytes

In-Memory Area 2147483648 bytes

Database mounted. Database opened.

In-Memory For the PDBs

• The INMEMORY_SIZE parameter setting is inherited by all PDBs

• Unless it is explicitly set at the PDB level.

• Changing the INMEMORY_SIZE parameter value at the PDB level does not require a restart of the instance or PDB.

CONN sys@pdb1 AS SYSDBA

-- Disable IM column store in the PDB

ALTER SYSTEM SET INMEMORY_SIZE=0; -- OR

ALTER SYSTEM RESETINMEMORY_SIZE;

-- Assign a PDB-specific size.

Disable the In-Memory

• There are several ways to disable the IM column store, depending on what We are trying to achieve. • Setting the INMEMORY_FORCE parameter to "OFF" means objects will not be maintained in the IM

column store.

• Switching it back to "DEFAULT" returns to the default behaviour. -- System level

ALTER SYSTEM SET INMEMORY_FORCE=OFF; ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;

• Setting the INMEMORY_QUERY parameter to "DISABLE" means the optimiser will not consider the IM column store to optimise queries. Switching it back to "ENABLE" reverts it to the default

functionality.

-- System level

ALTER SYSTEM SET INMEMORY_QUERY=DISABLE; ALTER SYSTEM SET INMEMORY_QUERY=ENABLE; -- Session level

ALTER SESSION SET INMEMORY_QUERY=DISABLE;

ALTER SESSION SET INMEMORY_QUERY=ENABLE;

• To disable the IM column store completely and release the memory, reset the INMEMORY_SIZE parameter.

ALTER SYSTEM RESET INMEMORY_SIZE SCOPE=SPFILE; SHUTDOWN IMMEDIATE;

Implementation at the Object level :

• Database In-Memory adds a new INMEMORY attribute for tables and materialized views. • Only objects with the INMEMORY attribute are populated into the IM column store.

• The following commands have been modified to include additional in-memory clauses.

• CREATE TABLE, ALTER TABLE, CREATE TABLESPACE, ALTER TABLESPACE, CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW

• 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;

• 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);

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

Pros and Cons :

Benificial for :

• Large scans that apply "=", "<", ">" and "IN" filters.

• Queries that return a small number of columns from a table with a large number of columns.

• Queries that join small tables to large tables.

• Queries that aggregate data.

Not effective for :

• Queries with complex predicates.

• Queries that return a large number of columns.

• Queries that return large numbers of rows.

• Queries with multiple large table joins.

• The important thing to remember here is *we* will be responsible for deciding which objects will benefit the most from inclusion in the IM column store.

• If We choose wisely we will see big improvements in performance.

Managing Tables : CREATE

CREATE TABLE im_tab ( id NUMBER

) INMEMORY;

CREATE TABLE noim_tab ( id NUMBER

) NO INMEMORY;

• Creating a table with the NO INMEMORY clause is the same as not specifying the clause at all.

SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate

FROM user_tables ORDER BY table_name;

TABLE_NAME INMEMORY INMEMO_P INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL --- --- --- -- ---- ---DEFAULT_TAB DISABLED

IM_TAB ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE

Managing Tables : ALTER

• The ALTER TABLE command can change the IM status of the objects. The following example flips the status.

ALTER TABLE IM_TAB NO INMEMORY;

ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FOR CAPACITY LOW; ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH;

SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate

FROM user_tables ORDER BY table_name;

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL --- --- --- -- ---- ---DEFAULT_TAB ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE IM_TAB DISABLED

Managing Columns : [ during ] CREATE

id NUMBER, col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER ) INMEMORY

INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2) INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3) NO INMEMORY (id, col4);

• The column settings are displayed using the V$IM_COLUMN_LEVEL view.

CONN sys@pdb1 AS SYSDBA

SELECT table_name, segment_column_id, column_name, inmemory_compression FROM v$im_column_level

WHERE owner = 'TEST' and table_name = 'IM_COL_TAB‘ ORDER BY segment_column_id;

TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION

--- --- --- ---IM_COL_TAB 1 ID NO INMEMORY

IM_COL_TAB 2 COL1 FOR QUERY HIGH IM_COL_TAB 3 COL2 FOR QUERY HIGH IM_COL_TAB 4 COL3 FOR CAPACITY HIGH

Managing Columns : [ using ] ALTER

ALTER TABLE im_col_tab

NO INMEMORY (col1, col2)

INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3) NO INMEMORY (id, col4);

SELECT table_name, segment_column_id, column_name, inmemory_compression FROM v$im_column_level

WHERE owner = 'TEST' and table_name = 'IM_COL_TAB' ORDER BY segment_column_id;

TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION --- --- --- ---IM_COL_TAB 1 ID NO INMEMORY

IM_COL_TAB 2 COL1 NO INMEMORY IM_COL_TAB 3 COL2 NO INMEMORY

IM_COL_TAB 4 COL3 FOR CAPACITY HIGH IM_COL_TAB 5 COL4 NO INMEMORY

Managing Tablespaces : CREATE

• Setting the default IM column store parameters for a tablespace means all tables and materialized views in that tablespace will use those setting unless explicitly overridden.

CONN sys@pdb1 AS SYSDBA CREATE TABLESPACE new_ts

DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf' SIZE 10M

DEFAULT INMEMORY; SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute, def_inmemory_compression, def_inmemory_duplicate FROM dba_tablespaces ORDER BY tablespace_name;

TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_ ---- --- --- -- ----

---NEW_TS ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE

SYSAUX DISABLED SYSTEM DISABLED TEMP DISABLED USERS DISABLED

Managing Tablespaces : ALTER

• The ALTER TABLESPACE command is used to change the IM column store parameters.

ALTER TABLESPACE new_ts

DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

SELECT tablespace_name, def_inmemory, def_inmemory_priority, def_inmemory_distribute, def_inmemory_compression, def_inmemory_duplicate FROM dba_tablespaces ORDER BY tablespace_name;

TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_ ---- --- --- -- ---- ---NEW_TS ENABLED NONE AUTO FOR CAPACITY HIGH NO DUPLICATE SYSAUX DISABLED

SYSTEM DISABLED TEMP DISABLED USERS DISABLED

Populating of Data in the IM-Area

• a 1MB pool [data pool] used to store the actual column formatted data populated into memory,

• and a 64K pool [metadata pool] used to store metadata about the objects that are populated into the IM column store.

• The amount of available memory in each pool is visible in the V$INMEMORY_AREA view

• The IM column store is populated by a set of background processes referred to as worker

processes(ora_w001_orcl).

• Each worker process is given a subset of database blocks from the object to populate into the IM column store.

• Just as a tablespace on disk is made up of multiple extents, the IM column store is made up of

multiple InMemory Compression Units (IMCUs).

• Each worker process allocates its own IMCU and populates its subset of database blocks in it.

• Population is streaming mechanism, simultaneously columnizing and compressing the data.

• With a pure in-memory database, the database cannot be accessed until all the data is

More about Populating of Data in IM…

• in a prioritized list immediately after the database is opened

• or after they are scanned (queried) for the first time.

• The order in which populating 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.

• ALTER TABLE customers INMEMORY PRIORITY CRITICAL;

PRIORITY DESCRIPTION

CRITICAL Object is populated immediately after the database is opened

HIGH Object is populated after all CRITICAL objects have been populated

MEDIUM Object is populated after all CRITICAL and HIGH objects have been populated

LOW Object is populated after all CRITICAL, HIGH, and MEDIUM objects have been populated NONE Objects only populated after they are scanned for the first time (Default)

• The population order can be superseded if an object without a PRIORITY is scanned.

In Memory -

Compression …

• Typically compression is considered only as a space-saving mechanism.

• However, data populated into the IM column store is compressed using a new set of compression algorithms that not only help save space but also improve query performance.

• The new Oracle In-Memory compression format allows queries to execute directly against the

compressed columns.

• This means all scanning and filtering operations will execute on a much smaller amount of data.

• Data is only decompressed when it is required for the result set.

• In-memory compression is specified using the keyword MEMCOMPRESS, a sub-clause of the INMEMORY attribute.

COMPRESSION LEVEL DESCRIPTION

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

CREATE TABLE employees( c1 NUMBER,c2 NUMBER, c3 VARCHAR2(10), c4 CLOB )

When the In-Memory can’t be used ..

Almost all objects in the database are eligible to be populated into the IM column but there are a small number of exceptions.

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

• Objects that are smaller than 64KB are not populated into memory, as they will waste a considerable amount of

• space inside the IM column store as memory is allocated in 1MB chunks.

• The IM column store cannot be used on an Active Data Guard standby instance in the current release.

• However it can be used in a Logical Standby instance and in an instance maintained using Oracle Golden Gate.

How the scan works…

• Analytic queries typically reference only a small subset of the columns in a table

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

• In-Memory Storage Index:

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

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

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

DML and the In-Memory column store :

• For the IM column store to be truly effective, it has to be able to handle both bulk data loads and online transaction processing.

• For the Bulk loads [direct] :

• Once the operation has been committed,

• the IM column store is instantly aware it does not have all of the data populated for the object.

• The size of the missing data will be visible in the BYTES_NOT_POPULATED column of the

V$IM_SEGMENTS view.

• If the object has a PRIORITY specified on it then the newly added data will be automatically populated into the IM column store.

• Otherwise the next time the object is queried, the background worker processes will be triggered to begin populating the missing data

• For Transaction Processing :

• Single row data change operations (DML) execute via the buffer cache (OLTP style changes), just as they do without Database In-Memory enabled. If the object in which the DML operations occurs is populated in the IM column store, then the changes are reflected in the IM column store as they occur.

• The buffer cache and the column store are kept transactionally consistent via the In-Memory Transaction Manager.

How the sync happens : 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 taking 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 (ora_w001_orcl)..

• The data is available at all times

• Any changes that occur to rows in the IMCU during repopulation are automatically recorded. [marked as stale for the next round]

Trickel Repopulate : IMCO

• In addition to the standard repopulation algorithm, there is another algorithm that attempts to clean all stale entries using a low priority background process, known as trickle repopulate • The IMCO (In-Memory Coordinator) background process carries out repopulation for any IMCU

that has some stale entries but does not currently meet the staleness threshold.

• The IMCO wakes up every two minutes and checks to see if any population tasks need to be completed.

• The IMCO will also check to see if there are any IMCUs with stale entries [ higher that threshold ].

• 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