Applies to:
Oracle database – 12.1.0.2
Author:
Mahesh Reddy M
Introduction:
With the introduction of Oracle Database 12c In-Memory, a single database can now efficiently support mixed workloads, delivering optimal performance for transactions while simultaneously supporting real-time analytics and reporting. In-Memory enables both datamarts and data warehouses to provide more ad-hoc analytics, giving end-users the ability to ask multiple business driving queries in the same time it takes to run just one now. This features allows you to store columns, tables, partitions and materialized views in memory in a columnar format, rather than the typical row format. The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patch set.
Oracle Database 12c In-Memory Architecture:
Traditionally data is stored in ROW format only, where as In-Memory database store data in columnar format only. So, oracle database 12c supports dual format architecture.
- Whenever data are requested for read/write operations (data manipulations), they are loaded into the traditional Row Store (Buffer Cache)
- Whenever data are requested for read-only operations, they are populated into a new In-Memory Column Store. This population, of course, includes a transformation from row to columnar format.
- Whenever a transaction that includes inserts, updates, or deletes is committed, the new data will immediately and simultaneously appear in both the row store and the in-memory column store. Therefore both stores are transactionally consistent
The In-Memory Column store:
Database In-Memory uses an In-Memory column store, which is a new component of the System Global Area (SGA), called the In-Memory Area. Data in the IM column store does not reside in the traditional row format, instead it uses a new column format. The IM column store does not replace the buffer cache, but acts as a supplement, so that data can now be stored in memory in both a row and a column format
The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0). The current size of the In-Memory area is visible in V$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.
We can see the amount memory available in each pool using below query
SQL> select * from v$inmemory_area;
| POOL |
ALLOC_BYTES |
USED_BYTES |
POPULATE_STATUS |
CON_I |
| 1MB POOL |
2.5767E+11 |
2.3569E+11 |
DONE |
1 |
| 64KB POOL |
6.4408E+10 |
876347392 |
DONE |
1 |
| 1MB POOL |
2.5767E+11 |
2.3569E+11 |
DONE |
2 |
| 64KB POOL |
6.4408E+10 |
876347392 |
DONE |
2 |
| 1MB POOL |
2.5767E+11 |
2.3569E+11 |
DONE |
3 |
| 64KB POOL |
6.4408E+10 |
876347392 |
DONE |
3 |
Table 1: Memory available in each pool
Enable IM Column store:
As a static pool, any changes to the INMEMORY_SIZE parameter will not take effect until the database instance is restarted. It is also not impacted or controlled by Automatic Memory Management (AMM). The In-Memory area must have a minimum size of 100MB.
sql> ALTER SYSTEM SET SGA_TARGET=500G SCOPE=SPFILE;
sql> ALTER SYSTEM SET INMEMORY_SIZE=300G SCOPE=SPFILE;
sql>SHUTDOWN IMMEDIATE;
sql>STARTUP;
ORACLE instance started.
Total System Global Area 4.2950E+11 bytes
Fixed Size 7677400 bytes
Variable Size 2.0938E+10 bytes
Database Buffers 8.5899E+10 bytes
Redo Buffers 529190912 bytes
In-Memory Area 3.2212E+11 bytes
Database mounted.
Database opened.
We can see the In-Memory settings using below query
SQL> SHOW PARAMETER INMEMORY
NAME TYPE VALUE
---------------------------------- ----------- -----
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 1
inmemory_query string ENABLE
inmemory_size biginteger 300G
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
We can set inmemory_size parameter at CDB and PDB level also. If you set this parameter at PDB level no need to restart instance or PDB. The sum of all PDBs value is less than on equal to CDB value.
We can enable and disable In-Memory option at PDB level shown below
Connect to PDB and then execute below command
Disable:
Alter system set inmemory_size=0; or
Alter system reset inmemory_size;
Enable:
Alter system set inmemory_size=20G;
In-Memory priority levels:
The IM column store should be populated with the most performance-critical data in the database. Less performance-critical data can reside on lower cost flash or disk. Of course, if your database is small enough, you can populate all of your tables into the IM column store. Database In-Memory adds a new INMEMORY attribute for tables and materialized views.
We can enable INMEMORY attribute at
Tablespace, Table, (sub) partition and materialized view.
If you enable this attribute at tablespace level, then all the tables and meterilized views in the tablesapce will be enabled for IMCOLUMN store by default.
alter tablespace quest INMEMORY;
if you enable this attribute at table level means all the columns of table should be populated into the IMCOLUMN store. However, it is possible to populate only subset of columns into IMCOLUMN store.
Alter table quest_tab INMEMORY NO Inmemory (EMP);
Similarly, for partitioned table
Alter table quest_tab INMEMORY MODIFY Partition quest_part_1 No Inmemory;
Background Processes:
IMCO: The IMCO background process initiates population (prepopulation) of in-memory enabled objects with priority LOW/MEDIUM/HIGH/CRITICAL.
SMCO: SMCO dynamically spawns secondary processes (Wnnn) to implement these tasks.
Wnnn: Wnnn processes execute in-memory populate and in-memory repopulate tasks for population or repopulation of in-memory enabled objects.
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
Different priority levels controlled by the PRIORITY sub clause of the INMEMORY clause
Priority Level 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)
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.
Ex: alter table quest inmemory priority critical;
In-Memory Compression Techniques:
The IM column store uses special compression formats optimized for access speed rather than storage reduction. The database increases speed in the following ways:
- The compression formats enable the database to decrease the amount of memory processed for each column. SQL executes directly on the compressed columns.
- The database uses SIMD vector (array) instructions to process an array of column values in a single CPU clock cycle. The database can store many values in a vector, which maximizes the performance benefits with SIMD vector processing.
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.
No Memcompress: Data is populated to In-Memory without compression.
Memcompress for DML: It is mainly for DML performance and minimal compression.
Memcompress for Query Low: Optimized for Query performance (Default).
Memcompress for Query High: Optimized for Query performance and also space saving
Memcompress for Capacity Low: More space saving compare to Query high and low
Memcompress for Capacity High: Optimized for space saving and little bit less performance.
Compression ratios can vary from 2X – 20X, depending on the compression option chosen, the data type, and the contents of the table.
Ex: alter table quest inmemory memcompress for query high;
In-Memory Column Store on RAC:
In Cluster environment each node has its own column store. Each node in the cluster should be maintain equal size of IM Column store. By default all objects populated into memory will be distributed across all of the IM column stores in the cluster.The distribution of objects across the IM column stores in a cluster is controlled by two additional subclauses to the INMEMORY attribute: DISTRIBUTE and DUPLICATE (Enineered systems only).
Distribute:
The objects are distributed across the cluster is controlled by distribute sub-clause. You can distribute the objects following ways
Distribute by rowed ranage—Distrubute the objects by rowed to different nodes
Distribute by Partition – distribute the partitions to nodes with in the cluster
Distribute by sub partition – distribute the sub partition to different nodes.
Ex: alter table quest inmemory distribute by partition;
Here, importatnt to say that Oracle In-Memory RAC is
shared-nothing architecture for queries. i.e if you raise a query againest inmemory data objects (assume objects are distrubuted 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) accross the instances in the cluster.
So, you can set
the degree of parallelism(DOP) to AUTO. The parallel query cordinator identify the other instances of IMCU' location. if you cann't set the DOP to AUTO, the parallel query co-ordinator won't use the other instances of IMCU's.
set the degree of parallel to auto
alter system set parallel_degree_policy=AUTO scope=both sid='*';
Duplicate:
To increase the availability, objects are copied into all instances. You can use the duplicate sub-clause to specify the inmemory table is stored in all instances with in the cluster. For non-engineered systems this option wont work.
Ex; alter table quest inmemory duplicate all;
quest table is stored in all instances.
Monitoring In-memory Objects:
For monitoring inmemory objects, oracle introducing two new V$views are
v$IM_SEGMENTS or v$IM_USER_SEGMENTS and v$IM_COLUMN_LEVEL.
Using these views we can find out the how many objects are currently populated into the IMCOLUMN store.
EX:
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;
SEGMENT_NAME ORIG GB IN-MEM GB % IN_MEM COMP RATIO
H_LINEITEM 317.27 68.2 88.77 4.13
H_PARTSUPP 35.17 21.04 100 1.67
EX:
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='QUEST'
ORDER BY inmemory, owner||'.'||table_name;
OBJECT INMEMORY PRIORITY DISTRIBUTE COMPRESSION DUPLICATE
H_NATION ENABLED CRITICAL AUTO FOR QUERY HIGH NO DUPLICATE
H_REGION ENABLED CRITICAL AUTO FOR QUERY HIGH NO DUPLICATE
H_CUSTOMER
H_SUPPLIER
In the example above you will notice that two of the tables – Customer and Supplier – don’t have a value for the INMEMORY column. The INMEMORY attribute is a segment level attribute. Both Customer and Supplier are partitioned tables and are therefore logical objects. The INMEMORY attribute for these tables will be recorded at the partition or sub-partition level in *_TAB_ (SUB) PARTITIONS.
Three additional columns – INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, and INMEMORY_COMPRESSION – have also been added to the *_TABLES views to indicate the current InMemory attributes for each table.