Oracle Database In-Memory Concept

Oracle Database In-Memory (Database In-Memory) is a suite of features, first introduced in Oracle Database 12c Release 1 (12.1.0.2), that greatly improves performance for real-time analytics and mixed workloads.

Traditionally, relational databases store data in either row or columnar formats. Memory and disk store data in the same format.

An Oracle database stores rows contiguously in data blocks. For example, in a table with three rows, an Oracle data block stores the first row, and then the second row, and then the third row. Each row contains all column values for the row. Data stored in row format is optimized for transaction processing. For example, updating all columns in a small number of rows may modify only a small number of blocks.

To address the problems relating to analytic queries, some database vendors have introduced a columnar format. A columnar database stores selected columns—not rows—contiguously. For example, in a large sales table, the sales IDs reside in one column, and sales regions reside in a different column.

Analytical workloads access few columns while scanning, but scan the entire data set. For this reason, the columnar format is the most efficient for analytics. Because columns are stored separately, an analytical query can access only required columns, and avoid reading inessential data. For example, a report on sales totals by region can rapidly process many rows while accessing only a few columns.

一般关系数据库要么使用行格式要么使用列格式来存储数据，内存与磁盘是以相同格式存储。行格式即一行一行的存储，列格式即一列一列的存储

Database vendors typically force customers to choose between a columnar and row-based format. For example, if the data format is columnar, then the database stores data in columnar format both in memory and on disk. Gaining the advantages of one format means losing the advantages of the alternate format. Applications either achieve rapid analytics or rapid transactions, but not both. The performance problems for mixed-use databases are not solved by storing data in a single format.

The demand for real-time analytics means that more analytic queries are being executed in a mixed-workload database. The traditional approach is not sustainable.

Fact table and Dimension table

Fact table事实表即包含事实数据的表，这些数据可用于汇总，如汇总具体时间段内一组商店的特定商品的销售情况。因此fact table通常包含大量行，需要汇总列会创建索引。一般来说，一个事实数据表都要和一个或多个维度表相关联，用户在利用事实数据表创建多维数据集时，可以使用一个或多个维度表。

Dimension table维度表，即是你从哪个角度去观察事实表数据，它包含了事实表的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据。如某地区商品的销量，是从地区这个角度观察商品销量的。事实表就是销量表，维度表就是地区表

在fact table与dimension table连接时默认会先将fact table相应数据放入PGA，再根据join条件过滤dimension table, 即此时fact table称为join的build side, dimension table称为join的probe side

（一）Dual-Format: Column and Row 双重格式：列式与行式

The IM column store maintains copies of tables, partitions, and individual columns in a special compressed columnar format that is optimized for rapid scans. The IM column store resides in the In-Memory Area, which is an optional portion of the system global area (SGA). The IM column store does not replace row-based storage or the database buffer cache, but supplements it.

是否启用In-Memory并不会影响buff cache的工作方式

The database buffer cache stores and processes data blocks in the same way whether the IM column store is enabled or disabled. Buffer I/O and buffer pools function the same.

In-Memory Area的数据不需要在buffer cache中缓存

Objects populated in the IM column store do not also need to be loaded into the buffer cache.

You can configure all or a subset of a database object's columns for population in the IM column store. Similarly, for a partitioned table or materialized view, you can configure all or a subset of the partitions for population.Use the INMEMORY clause in DDL statements to enable the IM column store at any of the following levels:

Column (nonvirtual or virtual)

Table, materialized view, or partition

Tablespace

The following figure shows a sample IM column store. The database stores the sh.sales table on disk in traditional row format. The SGA stores the data in columnar format in the IM column store, and in row format in the database buffer cache.

The columnar format does not affect the format of data stored in data files or in the buffer cache, nor does it affect undo data and online redo logging.

The database processes DML modifications in the same way, regardless of whether the IM column store is enabled, by updating the buffer cache, online redo log, and undo tablespace. However, the database uses an internal mechanism to track changes and ensure that the IM column store is consistent with the rest of the database. For example, if the sales table is populated in the IM column store, and if an application updates a row in sales, then the database automatically keeps the copy of the sales table in the IM column store transactionally consistent. A query that accesses the IM column store always returns the same results for a query that accesses the buffer cache.

（二）In-Memory Storage Units 内存存储结构

The IM column store manages both data and metadata in optimized storage units, not in traditional Oracle data blocks. 

Oracle Database maintains the storage units in the In-Memory Area.

In-Memory Area又细分为两个子池(均由oracle自动决定使用大小)：

1. The columnar data pool：由IMCUs和IMEUs组成，用于存储实际数据

The columnar pool of the In-Memory Area stores the actual data: IMCUs and IMEUs. 

V$INMEMORY_AREA的POOL列名为"1MB POOL" 即为columnar data pool

1. The metadata pool：由SMUs组成

This subpool stores metadata about the objects that reside in the IM column store.

V$INMEMORY_AREA的POOL列名为"64KB POOL"即为metadata pool

Figure 2-5 IM Column Store: Memory and Process Architecture

1. In-Memory Compression Units (IMCUs)

An In-Memory Compression Unit (IMCU) is a compressed, read-only storage unit that contains data for one or more columns. An IMCU is analogous to a tablespace extent.

可以不用解压直接对IMCU进行过滤，过滤后获取数据时再解压。

注意IMCU是只读结构，不能对数据进行更改

The columnar format enables queries to execute directly against the compressed columns.

Compression enables scanning and filtering operations to process a much smaller amount of data, which optimizes query performance. Oracle Database only decompresses data when it is required for the result set.

The compression applied in the IM column store is closely related to Hybrid Columnar Compression. Both technologies process column vectors. The primary difference is that the column vectors for the IM column store are optimized for SIMD vector processing, whereas the column vectors for Hybrid Columnar Compression are optimized for disk storage.

When you enable an object for population into the IM column store, you specify the type of compression in the INMEMORY clause: FOR DML, FOR QUERY (LOW or HIGH), FOR CAPACITY (LOW or HIGH), or NONE. 如：

ALTER TABLE sh.sales INMEMORY MEMCOMPRESS FOR QUERY LOW;

ALTER TABLE sh.sales INMEMORY MEMCOMPRESS FOR QUERY LOW NO INMEMORY (promo_id, quantity_sold, amount_sold);

同一IMCU只能存同一对象的列数据（类似表空间的extent概念），对象中指定INMEMORY的所有列同时存于相同的IMCU中。一个IMCU中存储多少行数据称为granule，它由Oracle自动决定，同一对象的所有IMCU的granule相同

The IM column store stores data for a single object (table, partition, materialized view) in a set of IMCUs. An IMCU stores columnar data for one and only one object.

Each IMCU contains all column values (including nulls) for a subset of rows in a table segment. A subset of rows is called a granule. All IMCUs for a given segment contain approximately the same number of rows. Oracle Database determines the size of a granule automatically depending on data type, data format, and compression type. A higher compression level results in more rows in the IMCU.

The number of rows in an IMCU dictates the amount of space an IMCU consumes. If the target number of rows causes an IMCU to grow beyond the amount of contiguous 1 MB extents available in the 1 MB pool, then the IMCU creates additional extents (pieces) to hold the remaining column CUs. An IMCU always allocates space in 1 MB increments.

IMCU与blocks是一对多的关系，即一个IMCU可以存储多个blocks数据，存于IMCU的数据并不会被排序（按读入顺序存储），另外IMCU中列顺序同记录顺序

A one-to-many mapping exists between an IMCU and a set of database blocks. Each IMCU stores the values for columns for a different set of blocks.The columns in an IMCU are not sorted. Oracle Database populates them in the order that they are read from disk.

Example 2-2 IMCUs and Row Subsets

In this simplified example, only the following 4 columns of the customers table have the INMEMORY attribute: cust_id, cust_first_name, cust_last_name, and cust_gender. Only 5 rows exist in the table, stored in 2 data blocks. Conceptually, the first data block stores its rows as follows:

82,Madeline,Li,F;37004,Abel,Embrey,M;1714,Hardy,Gentle,M

The second data block stores rows as follows:

100439,Uma,Campbell,F;3047,Lucia,Downey,F

Assume IMCU 1 stores the data for the first data block. In this case, the cust_id column values for the 3 rows in this data block stores are stored “vertically” within a CU as follows:

82

37004

1714

IMCU 2 stores the data from the second data block. The cust_id column values for these 2 rows are stored within a CU as follows:

100439

3047

Because the cust_id value is the first value for each row in the data block, the cust_id column is in the first position within the IMCU. Columns always occupy the same position, so Oracle Database can reconstruct the rows by reading the IMCUs for a segment.

An IMCU has two parts: a set of Column Compression Units (CUs), and a header that contains metadata such as the IM storage index.

1.1 Column Compression Units

CU用于存储IMCU中每列的数据，它又可分解为body and header.

A Column Compression Unit (CU) is contiguous storage for a single column in an IMCU. Every IMCU has one or more CUs. A CU is divided into a body and a header. The body of every CU stores the column values for the range of rows included in the IMCU. The header contains metadata about the values stored in the CU body, for example, the minimum and maximum value within the CU. It may also contain a local dictionary, which is a sorted list of the distinct values in that column and their corresponding dictionary codes.

The following figure shows an IMCU with 4 CUs for the sales table: prod_id, cust_id, time_id, and channel_id.

每个CU都是按rowid顺序存储的，即不同CU的相同位置数据为相同行记录

The CUs store values in rowid order. For this reason, the database can answer queries by “stitching” the rows back together. For example, an application issues the following query:

SELECT cust_id, time_id, channel_id

FROM   sales

WHERE  prod_id =5;

The database begins by scanning the prod_id column for entries with the value 5. Assume that the database finds 5 in position two in the prod_id column. The database now must find the corresponding cust_id, time_id, and channel_id for this row.

Because the CUs store data in rowid order, the database can find the corresponding cust_id, time_id, and channel_id values in position 2 in those columns. Thus, to answer the query, the database must extract the values from position 2 in the cust_id, time_id, and channel_id columns, and then stitch the row back together to return it to the end user.

In a CU, the local dictionary has a list of distinct values and their corresponding dictionary codes.

The local dictionary stores the symbol contained in the column. The following figure illustrates how a CU stores a name column in a vehicles table.

Figure 2-8 Local Dictionary

In the preceding figure, the CU contains only 7 rows. Every distinct value in this CU, such as Cadillac or Audi, is assigned a different dictionary code, such as 2 for Cadillac and 0 for Audi. The CU stores the dictionary code rather than the original value.

Note: When the database uses a common dictionary for a join group, the local dictionary contains references to the common dictionary rather than the symbols. For example, rather than storing the values Audi, BWM, and Cadillac for the vehicles.name column, the local dictionary stores dictionary codes such as 101, 220, and 66.

如果表列使用了common dictionary，它的dictionary code使用common dictionary的code

The CU header contains the minimum and maximum values for the column. In this example, the minimum value is Audi and the maximum value is Cadillac. The local dictionary stores the list of distinct values: Audi, BMW, and Cadillac. Their corresponding dictionary codes (0, 1, and 2) are implicit. The local dictionary for a CU in each IMCU is independent of the local dictionaries in other IMCUs.

If a query filters on Audi automobiles, then the database scans this IMCU for only 0 codes.

1.2 In-Memory Storage Indexes

用于维护In-Memory Storage Indexes ，它用于存储此IMCU中所有列各自的最大最小值

Every IMCU header automatically creates and manages In-Memory Storage Indexes (IM storage indexes) for its CUs. An IM storage index stores the minimum and maximum for all columns within the IMCU.

For example, sales is populated in the IM column store. Every IMCU for this table has all columns. The sales.prod_id column is stored in a separate CU within every IMCU. The IMCU header has the minimum and maximum values of each prod_id CU (and every other CU).

To eliminate unnecessary scans, the database can perform IMCU pruning based on SQL filter predicates. The database scans only the IMCUs that satisfy the query predicate, as shown in the WHERE prod_id > 14 AND prod_id < 29 example in the following graphic.

Figure 2-9 Storage Index for Columnar Data

1. Snapshot Metadata Units (SMUs)

快照元数据单元（SMU）包含相关IMCU的元数据和事务信息

A Snapshot Metadata Unit (SMU) contains metadata and transactional information for an associated IMCU.This figure shows IMCUs in the data pool, and SMUs in the metadata pool.

IMCU与SMU一一对应

Every IMCU maps to a separate SMU. Thus, if the columnar data pool contains 100 IMCUs, then the metadata pool contains 100 SMUs. The SMUs store several types of metadata for their associated IMCUs, including the following:

Object numbers

Column numbers

Mapping information for rows

如果buffer cache中修改了IM相关数据，会在SMU的事务日志中记录更改的rowid、DML操作的SCN，并标识出相应哪个IMCU已过期，如果有查询用到此IMCU会直接从buffer cache中取数据。详细population/repopulation过程见文档《Repopulation of the IM Column Store》

Every SMU contains a transaction journal. The database uses the transaction journal to keep the IMCU transactionally consistent.

The database uses the buffer cache to process DML, just as when the IM column store is not enabled. For example, an UPDATE statement might modify a row in an IMCU. In this case, the database adds the rowid for the modified row to the transaction journal and marks it stale as of the SCN of the DML statement. If a query needs to access the new version of the row, then the database obtains the row from the database buffer cache.

The database achieves read consistency by merging the contents of the column, transaction journal, and buffer cache. When the IMCU is refreshed during repopulation, queries can access the up-to-date row directly from the IMCU.

Figure 2-11 Transaction Journal

1. In-Memory Expression Units (IMEUs)

IMEU用于包含IM expression虚拟列及表虚拟列的数据，它与IMCU一一对应。

An In-Memory Expression Unit (IMEU) is a storage container for materialized In-Memory Expressions (IM expressions) and user-defined virtual columns.

1. 表的虚拟列是创建表时指定的计算表达式，如create table t1(id number, vir1 GENERATED ALWAYS AS (id*2) VIRTUAL);
2. IM expression是查询语句中使用到的计算表达式，Oracle通过ESS自动选出hot expression，并将它们生成名字前缀为SYS_IME的虚拟列。

Conceptually, an IMEU is a logical extension of its parent IMCU. Just as an IMCU can contain multiple columns, an IMEU can contain multiple virtual columns. Every IMEU maps to exactly one IMCU, mapping to the same row set. The IMEU contains expression results for the data contained in its associated IMCU. By default, the IMEU inherits the INMEMORY clause properties, including Oracle Real Application Clusters (Oracle RAC) properties such as DISTRIBUTE and DUPLICATE, from the base segment. You can selectively enable or disable virtual columns for storage in IMEUs. You can also specify compression levels for different columns.

在IMCU populate/repopulate同时会对IMEU数据populate/repopulate，但同时IMCU可以单独进行populate及repopulate，详见进程部分说明

When the IMCU is populated, the associated IMEU is also populated.

A typical IM expression involves one or more columns, possibly with constants, and has a one-to-one mapping with the rows in the table. For example, an IMCU for an employees table contains rows 1–1000 for the column weekly_salary. For the rows stored in this IMCU, the IMEU calculates the automatically detected IM expression weekly_salary*52, and the user-defined virtual column quarterly_salary defined as weekly_salary*12. The 3rd row down in the IMCU maps to the 3rd row down in the IMEU.

（三）Expression Statistics Store (ESS)  表达式统计信息

ESS即IM expression统计信息库，存于数据字典中，用于标识expression的权重，相关数据字典DBA_EXPRESSION_STATISTICS

The Expression Statistics Store (ESS) is a repository maintained by the optimizer to store statistics about expression evaluation. The ESS resides in the SGA and persists on disk.

When an IM column store is enabled, the database leverages the ESS for its In-Memory Expressions (IM expressions) feature. However, the ESS is independent of the IM column store. The ESS is a permanent component of the database and cannot be disabled.

The database uses the ESS to determine whether an expression is “hot” (frequently accessed), and thus a candidate for an IM expression. During a hard parse of a query, the ESS looks for active expressions in the SELECT list, WHERE clause, GROUP BY clause, and so on.

For each segment, the ESS maintains expression statistics such as the following:

Frequency of execution

Cost of evaluation

Timestamp evaluation

The optimizer assigns each expression a weighted score based on cost and the number of times it was evaluated. The values are approximate rather than exact. More active expressions have higher scores. The ESS maintains an internal list of the most frequently accessed expressions.

Control the behavior of IM expressions using the DBMS_INMEMORY_ADMIN package. For example, the IME_CAPTURE_EXPRESSIONS procedure prompts the database to identify and gradually populate the hottest expressions in the database. The IME_POPULATE_EXPRESSIONS procedure forces the database to populate the expressions immediately.

ESS information is stored in the data dictionary and exposed in the DBA_EXPRESSION_STATISTICS view. This view shows the metadata that the optimizer has collected in the ESS. IM expressions are exposed as system-generated virtual columns, prefixed by the string SYS_IME, in the DBA_IM_EXPRESSIONS view.

（四）In-Memory Process Architecture 相关进程

In response to queries and DML, server processes scan columnar data and update SMU metadata. Background processes populate row data from disk into the IM column store.

1. In-Memory Coordinator Process (IMCO)

IMCO进程主要用于管理population与repopulation作业。IMCO进程默认每2min唤醒一次对所有变动的IMCU进行repopulation，不论它是否达到阀值

The In-Memory Coordinator Process (IMCO) manages many tasks for the IM column store. Its primary task is to initiate background population and repopulation of columnar data.

1. Space Management Worker Processes (Wnnn)

Wnnn是IMCO实际的工作进程

Space Management Worker Processes (Wnnn) populate or repopulate data on behalf of IMCO.

During population, Wnnn processes are responsible for creating IMCUs, SMUs, and IMEUs.

The INMEMORY_MAX_POPULATE_SERVERS initialization parameter controls the maximum number of worker processes that can be started for population. The INMEMORY_TRICKLE_REPOPULATE_PERCENT initialization parameter controls the maximum percentage of time that worker processes can perform trickle repopulation.

（五）CPU Architecture: SIMD Vector Processing

For data that does need to be scanned in the IM column store, the database uses SIMD (single instruction, multiple data) vector processing.

The IM column store maximizes the number of column entries that the CPU can load into the vector registers(向量寄存器) and evaluate. Instead of evaluating each entry in the column one at a time, the database evaluates a set of column values in a single CPU instruction. SIMD vector processing enables the database to scan billions of rows per second.

For example, an application issues a query to find the total number of orders in the sales table that use the promo_id value of 9999. The sales table resides in the IM column store. The query begins by scanning only the sales.promo_id column, as shown in the following diagram:

Figure 2-12 SIMD Vector Processing

The CPU evaluates the data as follows:

1. Loads the first 8 values (the number varies depending on data type and compression mode) from the promo_id column into the SIMD register, and then compares them with the value 9999 in a single instruction
2. Discards the entries.
3. Loads another 8 values into the SIMD register, and then continues in this way until it has evaluated all entries.

For example, suppose a user executes the following ad hoc query:

SELECT cust_id, time_id, channel_id

FROM sales

WHERE prod_id BETWEEN 14 and 29

When using the buffer cache, the database would typically scan an index to find the product IDs, use the rowids to fetch the rows from disk into the buffer cache, and then discard the unwanted column values. Scanning data in row format in the buffer cache requires many CPU instructions, and can result in suboptimal CPU efficiency.

When using the IM column store, the database can scan only the requested sales columns, avoiding disk altogether. Scanning data in columnar format pipelines only necessary columns to the CPU, increasing efficiency. Each CPU core scans local in-memory columns using SIMD vector instructions.

注：SIMD(Single Instruction Multiple Data)为单指令多数据流，能够复制多个操作数，并把它们打包在大型寄存器的一组指令集。以加法指令为例，单指令单数据（SISD）的CPU对加法指令译码后，执行部件先访问内存，取得第一个操作数；之后再一次访问内存，取得第二个操作数；随后才能进行求和运算。而在SIMD型的CPU中，指令译码后几个执行部件同时访问内存，一次性获得所有操作数进行运算。这个特点使SIMD特别适合于多媒体应用等数据密集型运算