New Features of Oracle 12c - In Memory

环境

Oracle Linux 7.x
Oracle 12c, 18c, 19c

理论

Alt text

In Memory列式存储。
IM特性最早是由12c开始引入。

前提条件

启用In Memory特性的前提条件：

compatible 初始化参数至少为12.2.0或更高
INMEMORY_SIZE最少100M

工作进程

Oracle从磁盘读取对象的数据，将行转换为列，压缩并填充到IMCU(in memory compression unit)中。

Wnnn是负责填充的进程

inmemory_max_populate_servers用于指定Wnnn的数量
默认是CPU_COUNT的一半

支持的对象

开启In Memory特性之后，需要显式的通过create或alter语句的inmomory子句来把IM对象放到内存中。

支持的对象：

列（非虚拟列，虚拟列）
表（内部表，外部表）
分区
物化视图
表空间

不支持的对象：

外部表的虚拟列
索引
索引组织表
hash cluster
sys用户的对象
存储在system和sysaux表空间的对象

注意：

对列的IM需要显式的禁止，见CASE 7

优先级

优先级适用于：

表
分区
子分区

但不适用于：

优先级等级由低~高如下：
priority none（默认）：默认情况下，只有当IM对象被全表扫描之后，IM对象才能被存储到内存中
priority low
priority medium
priority high
priority critical

注意：

重启后，oracle会根据IM对象的优先级自动的存放内存中

priority low到priority critical，即使不全表扫描，也会存到内存中

小于64K的IM对象不会被存到内存中

压缩

如想节省内存

1	for capacity high/low

如想高性能

1	for query high/low

压缩比由低~高，查询性能由高~低如下：
no memcompress
memcompress for DML
memcompress for query low（默认）
memcompress for query high
memcompress for capacity low（capacity默认）：capacity不指定low/high时，默认为low
memcompress for capacity high

参数

`inmemory_max_populate_servers`

inmemory_max_populate_servers用于指定Wnnn的数量

`inmemory_size`

INMEMORY_SIZE开于开启In Memory以及指定使用的内存的大小

注意1：

INMEMORY_SIZE最小100M

INMEMORY_SIZE可以动态调整，但只能调高；每次调高至少要128M；INMEMORY_SIZE增加的内存空间从SGA中获取

INMEMORY_SIZE重启后生效

注意2：

INMEMORY_SIZE也支持PDB级别设置

如果PDB没有设置INMEMORY_SIZE，INMEMORY_SIZE的值从CDB继承，也就是PDB可以用满所有的CDB的INMEMORY_SIZE

PDB的INMEMORY_SIZE可以比CDB的INMEMORY_SIZE大

所有PDB的INMEMORY_SIZE加起来可以ENABLE比CDB的INMEMORY_SIZE大

`inmemory_virtual_columns`

inmemory_virtual_columns用于指定虚拟列是否自动存储到内存中

MANUAL（默认）：如果表启用了IM，虚拟列不会自动存储到内存，除非虚拟列指定了inmemory
ENABLE：如果表启用了IM，虚拟列会自动存储到内存中，除非指定了no inmemory

语法

表空间

表空间开启IM的语法和表类似，除了要加一个关键字default

1	create tablespace tablespace_name ...... default inmemory ......

或

1	alter tablespace tablespace_name ...... default inmemory ......

动态视图

v$im_segments视图可以用来查询IM对象在磁盘和内存中的大小，以及计算的压缩比

注意：

但如果IM对象已经在磁盘上被压缩，那计算出来的压缩比不准

查询IM对象的状态

1	select segment_name,populate_status,inmemory_pirority from v$im_segments;

dba_tables也为IM添加了新的字段inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate

v$im_column_level用来查看列级别上的列存储

强制载入内存

默认情况下，Oracle会根据优先级和是否执行了全表扫描来决定是否将对象载入内存，但也可以通过dbms_inmemory.populate来强制执行，见CASE 11

见官方文档 PL/SQL Packages and Types Reference

语法如下：

DBMS_INMEMORY.POPULATE(
   schema_name      IN    VARCHAR2,
   table_name       IN    VARCHAR2,
   subobject_name   IN    VARCHAR2 DEFAULT NULL);    --分区名

实践

CASE 1：开启In Memory

INMEMORY_SIZE参数默认为0，开启In Memory，只要将其设为非0值

1	alter system set inmemory_size=100M scope=spfile;

开启之后，在SGA里面多出来一个In-Memory Area。

SYS@CDB$ROOT> show sga

Total System Global Area   3221223144 bytes      
Fixed Size                    9139944 bytes      
Variable Size               637534208 bytes      
Database Buffers           2449473536 bytes      
Redo Buffers                  7634944 bytes      
In-Memory Area              117440512 bytes

CASE 2：新建表时启用IM，将其存储到内存，并查看状态

在开启IM的情况下，创建测试表，优先级为priority low

1 2	conn test/test@pdb1 create table im_test inmemory priority low as select * from all_objects;

查看v$im_segments视图，populate_status字段显示载入内存的进度

SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST';

SEGMENT_NAME POPULATE_STATUS INMEMORY_PRIORITY INMEMORY_COMPRESS
------------ --------------- ----------------- -----------------
IM_TEST      COMPLETED       LOW               FOR QUERY LOW

CASE 3：更改IM对象的压缩级别

将压缩级别从query low改为query high

1 2	TEST@pdb1> alter table im_test inmemory memcompress for query high; Table altered.

再次查看IM对象的状态

注意：

如果更改压缩级别，Oracle会先将IM对象从内存中移除，再重新压缩存储内存

需要一定时间让Oracle将IM对象重新载入内存

SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST';
no rows selected
...
...
SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST';

SEGMENT_NAME POPULATE_STATUS INMEMORY_PRIORITY INMEMORY_COMPRESS
------------ --------------- ----------------- -----------------
IM_TEST      COMPLETED       LOW               FOR QUERY HIGH

CASE 4：使用Oracle压缩顾问预测压缩率

脚本见 Link
Doc ID 1589879.1
压缩种类参数表 Link

拿im_test表做例子预测压缩率

[oracle@ora192 scripts]$ sql test/test@pdb1

SQLcl: Release 19.2.1 Production on Sat Feb 01 18:29:33 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

Enter value for ScratchTBS: users
old  10: scratchtbsname => upper('&ScratchTBS'),
new  10: scratchtbsname => upper('users'),
Enter value for ownername: test
old  11: ownname => upper('&ownername'),
new  11: ownname => upper('test'),
Enter value for TableName: im_test
old  12: objname => upper('&TableName'),
new  12: objname => upper('im_test'),
Enter value for compression_type_number: 65536
old  14: comptype => &compression_type_number,
new  14: comptype => 65536,
Enter value for num_rows: 54882
old  15: subset_numrows=> &num_rows,
new  15: subset_numrows=> 54882,
.
OUTPUT: 
Estimated Compression Ratio: 3.8
Blocks used by compressed sample: 0
Blocks used by uncompressed sample: 1096
Rows in a block in compressed sample: 0
Rows in a block in uncompressed sample: 50


PL/SQL procedure successfully completed.

CASE 5：已存在的表启用和禁用IM

创建一个非IM表

TEST@pdb1> create table im_test as select * from all_objects;

Table created.

TEST@pdb1> select count(1) from im_test;

  COUNT(1)
----------
     54882

SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments;
no rows selected

将其启用IM

TEST@pdb1> alter table im_test inmemory;

Table altered.

TEST@pdb1> select count(1) from im_test;

  COUNT(1)
----------
     54882

SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST';

SEGMENT_NAME POPULATE_STATUS INMEMORY_PRIORITY INMEMORY_COMPRESS
------------ --------------- ----------------- -----------------
IM_TEST      COMPLETED       NONE              FOR QUERY LOW

对此表禁用IM

TEST@pdb1> alter table im_test no inmemory;

Table altered.

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where owner='TEST';

OWNER TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
----- ---------- -------- ----------------- --------------------
TEST  IM_TEST    DISABLED

CASE 6：外部表启用和禁用IM (TBD)

外部表见文档 Link

准备测试数据

set head off
set pages 0
set feedback off
set termout off
spool ext_dba_objects.sql
select object_id||','||owner||','||object_name||','||object_type from dba_objects;
spool off

创建目录并赋权限

create or replace directory test_data_dir as '/home/oracle/tmp/data';
create or replace directory test_log_dir as '/home/oracle/tmp/log';
create or replace directory test_bad_dir as '/home/oracle/tmp/bad';
grant read on directory test_data_dir to test;
grant write on directory test_log_dir to test;
grant write on directory test_bad_dir to test;

创建外部表并启用IM

create table ext_im_text
(
    object_id number,
    owner varchar2(128),
    object_name varchar2(128),
    object_type varchar2(23)
)    
organization EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY test_data_dir
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  BADFILE test_bad_dir:'ext_im_text.bad'
  LOGFILE test_log_dir:'ext_im_text.log'
  FIELDS TERMINATED BY ','
  MISSING FIELD VALUES ARE NULL
  (
    object_id,
    owner,
    object_name,
    object_type
  )
 )
 LOCATION ('ext_dba_objects.csv')
)
REJECT LIMIT UNLIMITED
INMEMORY
;

建立时报错
问题还没有解决

1
2
3

ERROR at line 1:
ORA-12754: Feature 'In-Memory External Tables' is disabled due to missing
capability 'Runtime Environment'.

查看dba_external_tables视图以确认IM的状态

填充数据到内存中

手动刷新新数据

CASE 7：列启用和禁用IM

先禁用`im_test`表上的IM

1	TEST@pdb1> alter table im_test no inmemory;

查看dba_tables，可以看到im_test表上禁用了IM

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where table_name='IM_TEST';

OWNER  TABLE_NAME INMEMORY INMEMORY INMEMORY_COMPRESS
------ ---------- -------- -------- -----------------
TEST   IM_TEST    DISABLED

查看v$im_column_level视图，可以看到没有一列启用了IM

1 2	SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST'; no rows selected

手动禁用`object_id`列上的IM

1	TEST@pdb1> alter table im_test no inmemory (object_id);

查看dba_tables，可以看到im_test表上IM依然禁用

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where table_name='IM_TEST';

OWNER  TABLE_NAME INMEMORY INMEMORY INMEMORY_COMPRESS
------ ---------- -------- -------- -----------------
TEST   IM_TEST    DISABLED

再次查看v$im_column_level视图，发现除了被禁用的object_id列，其余列都启用了IM

SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST';

TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION      
---------- --------------- --------------------------
IM_TEST    OWNER           DEFAULT                   
IM_TEST    OBJECT_NAME     DEFAULT                   
IM_TEST    SUBOBJECT_NAME  DEFAULT                   
IM_TEST    OBJECT_ID       NO INMEMORY               
IM_TEST    DATA_OBJECT_ID  DEFAULT   
...
...

在`im_test`表上启用IM

1	TEST@pdb1> alter table im_test inmemory;

查看dba_tables，可以看到这时im_test表上IM已经启用

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where table_name='IM_TEST';
OWNER  TABLE_NAME INMEMORY INMEMORY INMEMORY_COMPRESS
------ ---------- -------- -------- -----------------
TEST   IM_TEST    ENABLED  NONE     FOR QUERY LOW

查看v$im_column_level视图，此时和之前一样，object_id列的IM依然被禁用

SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST';

TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION      
---------- --------------- --------------------------
IM_TEST    OWNER           DEFAULT                   
IM_TEST    OBJECT_NAME     DEFAULT                   
IM_TEST    SUBOBJECT_NAME  DEFAULT                   
IM_TEST    OBJECT_ID       NO INMEMORY               
IM_TEST    DATA_OBJECT_ID  DEFAULT   
...
...

手动更改其他列的IM压缩属性

1	TEST@pdb1> alter table im_test inmemory memcompress for capacity high (object_name) inmemory memcompress for capacity low (object_type);

查看v$im_column_level视图，object_name和object_type这两个字段的IM压缩属性被修改

SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST';

TABLE_NAME COLUMN_NAME     INMEMORY_COMPRESSION      
---------- --------------- --------------------------
IM_TEST    OWNER           DEFAULT                   
IM_TEST    OBJECT_NAME     FOR CAPACITY HIGH         
IM_TEST    SUBOBJECT_NAME  DEFAULT                   
IM_TEST    OBJECT_ID       NO INMEMORY               
IM_TEST    DATA_OBJECT_ID  DEFAULT                   
IM_TEST    OBJECT_TYPE     FOR CAPACITY LOW   
...
...

在`im_test`表上禁用IM

1	TEST@pdb1> alter table im_test no inmemory;

所有的列都已禁用IM

1 2	SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST'; no rows selected

CASE 8：虚拟列启用和禁用IM（TBD）

当`inmemory_virtual_columns`的值为默认值`manual`时

增加一个虚拟列

1	TEST@pdb1> alter table im_test add(delete_date as (timestamp+365));

查看初始化参数inmemory_virtual_columns的值
a

SYS@pdb1> show parameter inmemory_virtual_columns
NAME                     TYPE   VALUE  
------------------------ ------ ------ 
inmemory_virtual_columns string MANUAL

对整个表启用IM

1	TEST@pdb1> alter table im_test inmemory priority low;

先查看整个表是否已载入内存，确认确实已载入

SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST';

SEGMENT_NA POPULATE_STATUS INMEMORY_PRIORITY INMEMORY_COMPRESSION
---------- --------------- ----------------- --------------------
IM_TEST    COMPLETED       LOW               FOR QUERY LOW

查看单个字段的压缩，UNSPECIFIED说明没有指定压缩方式，也就是虚拟列的压缩和所在表的压缩方式一样

SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST';

TABLE_NAME COLUMN_NAME        INMEMORY_COMPRESSION      
---------- ------------------ --------------------------
IM_TEST    DELETE_DATE        UNSPECIFIED               
IM_TEST    OWNER              DEFAULT                   
IM_TEST    OBJECT_NAME        DEFAULT 
...
...

可以单独修改虚拟列的压缩级别

1	TEST@pdb1> alter table im_test inmemory memcompress for query low (delete_date);

再次查看delete_date字段的压缩

SYS@pdb1> select table_name,column_name,inmemory_compression from v$im_column_level where table_name='IM_TEST';

TABLE_NAME COLUMN_NAME        INMEMORY_COMPRESSION
---------- ------------------ --------------------
IM_TEST    DELETE_DATE        FOR QUERY LOW       
IM_TEST    OWNER              DEFAULT             
IM_TEST    OBJECT_NAME        DEFAULT    
...
...

当`inmemory_virtual_columns`的值为`enable`时

修改inmemory_virtual_columns的值为enable

1	SYS@pdb1> alter system set inmemory_virtual_columns='enable';

重置im_test表的IM

TEST@pdb1> alter table im_test no inmemory;
Table altered.

TEST@pdb1> alter table im_test inmemory priority low;
Table altered.

explain plan一下可以看到全表扫描的时候用到了IM

TEST@pdb1> explain plan for select delete_date from im_test;
Explained.


TEST@pdb1> select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 3183909076                                                     
                                                                                
--------------------------------------------------------------------------------
------                                                                          

| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time
     |                                                                          

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


PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |         | 54882 |   696K|    12   (0)| 00:0
0:01 |                                                                          

|   1 |  TABLE ACCESS INMEMORY FULL| IM_TEST | 54882 |   696K|    12   (0)| 00:0
0:01 |                                                                          

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


8 rows selected.

CASE 9：分区启用和禁用IM

CASE 10：表空间启用和禁用IM

创建一个表空间im_tbs并启用IM

1	SYS@pdb1> create tablespace im_tbs datafile '/u01/app/oracle/oradata/CDB1/pdb1/im_tbs.dbf' size 10M default inmemory;

在该表空间上新建一个表im_test

1	TEST@pdb1> create table im_test tablespace im_tbs as select * from all_objects;

在该表空间上的所有表都会启用IM

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where table_name='IM_TEST';

OWNER TABLE_NAME INMEMORY INMEMORY_PRIORITY  INMEMORY_COMPRESSION
----- ---------- -------- ------------------ --------------------
TEST  IM_TEST    ENABLED  NONE               FOR QUERY LOW

修改表空间的IM属性

1 2	SYS@pdb1> alter tablespace im_tbs default inmemory priority low memcompress for query high; Tablespace altered.

改变表空间的IM属性并不会影响已有的表，只会影响新建的表

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where table_name='IM_TEST';

OWNER TABLE_NAME INMEMORY INMEMORY_PRIORITY  INMEMORY_COMPRESSION
----- ---------- -------- ------------------ --------------------
TEST  IM_TEST    ENABLED  NONE               FOR QUERY LOW

创建一个新表，再次查看

SYS@pdb1> select owner,table_name,inmemory,inmemory_priority,inmemory_compression from dba_tables where table_name like '%IM_TEST%';
OWNER TABLE_NAME INMEMORY INMEMORY_PRIORITY  INMEMORY_COMPRESSION
----- ---------- -------- ------------------ --------------------
TEST  IM_TEST    ENABLED  NONE               FOR QUERY LOW       
TEST  IM_TEST2   ENABLED  LOW                FOR QUERY HIGH

CASE 11：手动强制载入内存

创建一个新表

1	TEST@pdb1> create table im_test inmemory as select * from all_objects;

这个表默认并没有载入内存

1 2	SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST'; no rows selected

手动载入内存

1	TEST@pdb1> exec dbms_inmemory.populate('TEST','IM_TEST');

再次查看，确实已被载入内存

SYS@pdb1> select segment_name,populate_status,inmemory_priority,inmemory_compression from v$im_segments where segment_name='IM_TEST';

SEGMENT_NAME POPULATE_STATUS INMEMORY_PRIORITY INMEMORY_COMPRESSION
------------ --------------- ----------------- --------------------
IM_TEST      COMPLETED       NONE              FOR QUERY LOW

版本差异

12c

12.1

引入In Memory特性

12.2

inmemory_size动态调整
对某些列启用列式存储
18c
19c
并行应用开始支持In Memory列式存储

History:

v1.0，2020.01.22~2020.02.05，初始版本

附件：

Document 1903683.1
Oracle 18c必须掌握的新特性管理于实践，戴明明、臧强磊编著，2019年7月第1版