Oracle In-Memory使用

• 设置In-Memory内存使用

1. 评估IM使用内存大小

IM使用大小由相关数据大小及压缩级别决定

The amount of memory required by the IM column store depends on the database objects stored in it and the compression method applied on each object. When choosing a compression method for the INMEMORY objects, balance the performance benefits against the amount of available memory:

1. To make the greatest reduction in memory size, choose the FOR CAPACITY HIGH or FOR CAPACITY LOW compression methods. However, these options require additional CPU during query execution to decompress the data.
2. To get the best query performance, choose the FOR QUERY HIGH or FOR QUERY LOW compression methods. However, these options consume more memory.

在设置IM大小时你要按以下步骤进行大小评估

When sizing the IM column store, consider the following guidelines:

1. For every object to be populated into the IM column store, estimate the amount of memory it consumes.
2. 可以使用Oracle Compression Advisor来预估IN-MEMORY表压缩率，使用方法：

set serveroutput on

declare

blkcnt_cmp NUMBER;

blkcnt_uncmp NUMBER;

row_cmp NUMBER;

row_uncmp NUMBER;

cmp_ratio NUMBER;

comptype_str VARCHAR2(32);

begin

DBMS_COMPRESSION.GET_COMPRESSION_RATIO (scratchtbsname=>'USERS', ownname=>'SYS',  objname=>'T1', subobjname=>'T1_P1',comptype=>2, blkcnt_cmp=>blkcnt_cmp, blkcnt_uncmp=>blkcnt_uncmp, row_cmp=>row_cmp, row_uncmp=>row_uncmp, cmp_ratio=>cmp_ratio, comptype_str=>comptype_str);

dbms_output.put_line('压缩类型: ' || comptype_str);

dbms_output.put_line('压缩后占用blocks: ' || blkcnt_cmp);

dbms_output.put_line('未压缩占用blocks: ' || blkcnt_uncmp);

dbms_output.put_line('压缩后单blocks包含行数: ' || row_cmp);

dbms_output.put_line('未压缩单blocks包含行数: ' || row_uncmp);

dbms_output.put_line('压缩率(压缩后占用blocks/未压缩占用blocks): ' || cmp_ratio);

end;

/

关于comptype:

COMP_INMEMORY_NOCOMPRESS(8192):In-Memory with no compression

COMP_INMEMORY_DML(16384):In-Memory compression level for DML

COMP_INMEMORY_QUERY_LOW( 32768):In-Memory compression level optimized for query performance

COMP_INMEMORY_QUERY_HIGH (65536):In-Memory compression level optimized on query performance as well as space saving

COMP_INMEMORY_CAPACITY_LOW(131072): In-Memory low compression level optimizing for capacity

COMP_INMEMORY_CAPACITY_HIGH(262144) In-Memory high compression level optimizing for capacity

1. Add the individual amounts to together.

Note: After population, V$IM_SEGMENTS shows the actual size of the objects on disk and their size in the IM column store. You can use this information to calculate the compression ratio for the populated objects. However, if the objects were compressed on disk, then this query does not show the correct compression ratio.

可以先确定哪些列放入IM之后查看V$SIM_SEGMENTS大小，再选择压缩比例进行内存使用评估

1. Add additional space to account for the growth of database objects, and to store updated versions of rows after DML operations.
2. The minimum amount for dynamic resizing is 128 MB.

1. 启用、关闭IM特性及设置IM内存使用

条件:The COMPATIBLE initialization parameter is set to 12.1.0 or higher.

In-Memory Area由参数INMEMORY_SIZE设置大小，默认值为0即关闭

In-Memory Area不受automatic memory management管理，它的大小是静态的，如设置SGA_TARGET为10g，INMEMORY_SIZE为4G，则SGA其余组件还剩余6G可用

操作步骤

1. ALTER SYSTEM SET INMEMORY_SIZE = 10G SCOPE=SPFILE;
2. Shut down the database, and then reopen it. 启用时必须要重启数据库
3. After the IM column store is enabled, you can increase its size dynamically without reopening the database. 可以使用ALTER SYSTEM动态增加INMEMORY_SIZE，但如果减小大小还要用SCOPE=SPFILE再重启数据库

另外在满足以下条件IM Area才会动态增加空间：

1. SGA有空闲空间
2. INMEMORY_SIZE至少比当前增加了128MB

1. 查看In-Memory Area使用V$INMEMORY_AREA , V$SGA 

（二）对象级In-Memory设置

可以在表空间及表级设置IM Column，即CREATE/ALTER TABLESPACE、CREATE/ALTER TABLE以及CREATE/ALTER MATERIALIZED VIEW，表级别设置会覆盖表空间设置

The following objects are not eligible for population in the IM column store:

Indexes

Index-organized tables

Hash clusters

Objects owned by the SYS user and stored in the SYSTEM or SYSAUX tablespace

If you enable a table for the IM column store and it contains any of the following types of columns, then these columns will not be populated in the IM column store:

Out-of-line columns (varrays, nested table columns, and out-of-line LOBs)

Columns that use the LONG or LONG RAW data types

Extended data type columns

1. In-Memory Syntax

以下如果是在CREATE/ALTER TABLESPACE中设置默认属性加default即可，如：

ALTER TABLESPAE USERS DEFAULT INMEMORY FOR QUERY LOW PRIORITY LOW;  --表空间

ALTER TABLE T1 INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY LOW;  --表级

ALTER TABLE T1 INMEMORY (ID) ;   --列级

ALTER TABLE T1 INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY LOW NO INMEMORY (ID);  --表级列级同时设置

ALTER TABLE T1 INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY LOW INMEMORY MEMCOMPRESS FOR QUERY HIGH (ID);  --表级列级同时设置

ALTER MATERIALIZED VIEW prod_info_mv INMEMORY PRIORITY HIGH;  -物化视图

注：表级别设置INMEMORY/NO INMEMORY可能会对列产生影响，所以设置后要查询V$IM_COLUMN_LEVEL确认设置正确：

CREATE TABLE T1(ID NUMBER, NAME VARCHAR2(10));

ALTER TALBE T1 NO INMEMORY(NAME);   ---id为inmemory, name为no inmemory

ALTER TALBE T1 INMEMORY;            --name仍为no inmemory

ALTER TALBE T1 NO INMEMORY;          ---name与id均为no inmemory

inmemory_table_clause::=

inmemory_attributes::=

inmemory_memcompress::=

Note: the MEMCOMPRESS keyword must be preceded by the INMEMORY keyword.

inmemory_priority::=

inmemory_distribute::=

inmemory_duplicate::=

inmemory_column_clause::=

1. In-Memory Population Priority

只能对整个表、分区、子分区设置population priority，不能对列级别设置。它只影响population的先后顺序而不是population的速度.

The INMEMORY PRIORITY subclause controls the priority of population, but not the speed of population. The priority level setting applies to an entire table, partition, or subpartition, not to different column subsets.

Note: If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated.

Oracle Database manages prioritization as follows:

1. On-demand population

默认优先级为NONE，即如果对象不被全表扫描不会被population

By default, the INMEMORY PRIORITY parameter is set to NONE. In this case, the database only populates the object when it is accessed through a full table scan. If the object is never accessed, or if it is accessed only through an index scan or fetch by rowid, then population never occurs.

1. Priority-based population

设置了优先级会自动population不用全表扫描，但如果内存不够只会population优先级高的

When PRIORITY is set to a value other than NONE, Oracle database automatically populates the objects using an internally managed priority queue. In this case, a full scan is not a necessary condition for population.

The database does the following:

1. Populates columnar data in the IM column store automatically after the database instance restarts
2. Queues population of INMEMORY objects based on the specified priority level

包括以下级别：PRIORITY NONE|LOW|MEDIUM|HIGH|CRITICAL

If the IM column store has insufficient space, then Oracle Database does not populate additional objects until space is available.

1. For a database object with a priority level other than NONE, an ALTER TABLE or ALTER MATERIALIZED VIEW DDL statement involving the database object does not return until the DDL changes are recorded in the IM column store.

segment被载入IM Area后被drop、remove或更改为NO NOMEMORY属性，亦或是更改了除priority外的其它INMEMORY属性均会把它逐出IM内存，是否需要再次repopulation看priority设置

After a segment is populated in the IM column store, the database only evicts it when the segment is dropped or moved, or the segment is updated with the NO INMEMORY attribute.

If a table is currently populated in the IM column store, and if you change any INMEMORY attribute of the table other than PRIORITY, then the database evicts the table from the IM column store. The repopulation behavior depends on the PRIORITY setting.

相关参数INMEMORY_MAX_POPULATE_SERVERS 

INMEMORY_MAX_POPULATE_SERVERS specifies the maximum number of worker processes to use for IM column store population. （即Wnnn进程数）By default, the setting is one half of CPU_COUNT. If INMEMORY_MAX_POPULATE_SERVERS is set to 0, then population is disabled.

Example 4-1 Population of an Object in the IM Column Store

ALTER TABLE sh.customers INMEMORY;

The preceding statement uses the default priority of NONE. A full scan is required to populate objects with no priority.

COL SEGMENT_NAME FORMAT a20

SELECT SEGMENT_NAME, POPULATE_STATUS

FROM   V$IM_SEGMENTS

WHERE  SEGMENT_NAME = 'CUSTOMERS';

In this case, no segments are populated in the IM column store because the sh.customers table has not yet been scanned.

SELECT cust_id, cust_last_name, cust_first_name

FROM   sh.customers

WHERE  cust_city = 'Hyderabad'

AND    cust_income_level LIKE 'C%'

AND    cust_year_of_birth > 1960;

Querying the cursor shows that the database performed a full scan and accessed the IM column store:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));

Query V$IM_SEGMENTS again (sample output included):

COL SEGMENT_NAME FORMAT a20

SELECT SEGMENT_NAME, POPULATE_STATUS

FROM   V$IM_SEGMENTS

WHERE  SEGMENT_NAME = 'CUSTOMERS';

SEGMENT_NAME         POPULATE_STATUS

-------------------- ---------------

CUSTOMERS            COMPLETED

The value COMPLETED in POPULATE_STATUS means that the table is populated in the IM column store. The DBA_FEATURE_USAGE_STATISTICS view confirms that the database used the IM column store to retrieve the results:

COL NAME FORMAT a25

SELECT ul.NAME, ul.DETECTED_USAGES

FROM   DBA_FEATURE_USAGE_STATISTICS ul

WHERE  ul.VERSION= (SELECT MAX(u2.VERSION)

                    FROM   DBA_FEATURE_USAGE_STATISTICS u2

                    WHERE  u2.NAME = ul.NAME

                    AND    ul.NAME LIKE '%Column Store%');

NAME                      DETECTED_USAGES

------------------------- ---------------

In-Memory Column Store        1

1. IM Column Store Compression Methods

Typically, compression is a space-saving mechanism. However, the IM column store can compress data using a new set of algorithms that also improve query performance. If the columnar data is compressed using the FOR DML or FOR QUERY options, then SQL queries execute directly on the compressed data. Thus, scanning and filtering operations execute on a much smaller amount of data. The database only decompresses data when it is required for the result set.

The V$IM_SEGMENTS and V$IM_COLUMN_LEVEL views indicate the current compression level. The following table summarizes the data compression methods supported in the IM column store.

CREATE/ALTER Syntax	Description
NO MEMCOMPRESS	The data is not compressed.
MEMCOMPRESS FOR DML	This method results in the best DML performance. This method compresses IM column store data the least, with the exception of NO MEMCOMPRESS. Note: This compression method is not supported for CELLMEMORY storage on Exadata flash cache.
MEMCOMPRESS FOR QUERY LOW	This method results in the best query performance. This method compresses IM column store data more than MEMCOMPRESS FOR DML but less than MEMCOMPRESS FOR QUERY HIGH. This method is the default when the INMEMORY clause is specified without a compression method in a CREATE or ALTER SQL statement or when MEMCOMPRESS FOR QUERY is specified without including either LOW or HIGH.
MEMCOMPRESS FOR QUERY HIGH	This method results in good query performance, and saves space. This method compresses IM column store data more than MEMCOMPRESS FOR QUERY LOW but less than MEMCOMPRESS FOR CAPACITY LOW.
MEMCOMPRESS FOR CAPACITY LOW	This method balances space saving and query performance, with a bias toward space saving. This method compresses IM column store data more than MEMCOMPRESS FOR QUERY HIGH but less than MEMCOMPRESS FOR CAPACITY HIGH. This method applies a proprietary compression technique called Oracle Zip (OZIP) that offers extremely fast decompression that is tuned specifically for Oracle Database. That data must be decompressed before it can be scanned. This method is the default when MEMCOMPRESS FOR CAPACITY is specified without including either LOW or HIGH.
MEMCOMPRESS FOR CAPACITY HIGH	This method results in the best space saving. This method compresses IM column store data the most.

1. About IM Virtual Columns

表的虚拟列也可放在IM中，它可以避免重复计算提高查询性能

Both nonvirtual columns and In-Memory virtual columns (IM virtual columns) are eligible for population into the IM column store. Storing the precalculated IM virtual column values in the IM column store can improve query performance. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions (DETERMINISTIC only). You cannot explicitly write to an IM virtual column.

Note: A virtual column or IM expression counts toward the limit of 1000 columns per populated object.

使用内存虚拟列需要以下前提：

To enable IM virtual columns, the following conditions must be true:

1. The IM column store is enabled for the database.
2. The table that contains the virtual columns is enabled for the IM column store.
3. The INMEMORY_VIRTUAL_COLUMNS initialization parameter is not set to DISABLE.

此参数可用值：

MANUAL (default): If a table is enabled for the IM column store, then no IM virtual columns defined on this table are eligible for population, unless they are explicitly set as INMEMORY.

ENABLE: If a table is enabled for the IM column store, then all IM virtual columns defined on this table are eligible for population, unless they are explicitly set as NO INMEMORY.

By default, the compression level of the column in the IM column store is the same as the table or partition in which it is stored. However, when a different compression level is specified for the IM virtual column, it is populated at the specified compression level.

1. The value for the initialization parameter COMPATIBLE is set to 12.1.0 or higher.

示例：

SQL> SHOW PARAMETER INMEMORY_SIZE

SQL> SHOW PARAMETER INMEMORY_VIRTUAL_COLUMNS

SQL> ALTER TABLE hr.employees ADD (weekly_sal AS (ROUND(salary*12/52,2)));

SQL> ALTER TABLE hr.employees INMEMORY;

SQL> ALTER SYSTEM SET INMEMORY_VIRTUAL_COLUMNS=ENABLE SCOPE=SPFILE;