Oracle12c IMO 测试

1.概述

2014 年 6 月，在 Oracle 12c 的 12.1.0.2 版本中，Oracle 正式发布和引入了基于内存和列式计算的 In-Memory Option 组件 （以下简称 IMO），IMO 组件主要包含 In-Memory Column Store
和 In-Memory Aggregation 两大特性。在 IMO 中，数据在 SGA 共享内存的独立区域中按照列式进行存储，数据是被压缩存放的，即数据库中同时存在相同数据的行式及列式存储，该特性对应用透明，可以极大的提升某些场景下的查询性能，本文档针对 Oracle 12.1.0.2 的 IMO 组件进行相关测试，主要包括如下两大方面：

1. IMO 基础功能测试
2. IMO 效果测试

数据库测试环境概要信息如下：

项目	内容	项目	内容
主机名(node1)	memory247	硬件厂商	IBM
Public IP	10.253.129.247	内存	504G
vip	10.253.129.242	CPU	64C
操作系统	Red hat 5.8	数据库版本	12.0.1.2
主机名(node2)	memory248	硬件厂商	IBM
Public IP	10.253.129.248	内存	504G
vip	10.253.129.243	CPU	64C
操作系统	Red hat 5.8	数据库版本	12.0.1.2

2.IMO 基础功能

2.1IMO 开启与关闭

IMO 的开启与关闭主要通过初始化参数 INMEMORY_SIZE 进行控制，当 INMEMORY_SIZE>0 时，IMO 开启，INMEMORY_SIZE=0 时 IMO 关闭，如果开启 IMO，该参数最小值设置为 100MB。同时该参数允许在 PDB 级别设置，否则将继承 root PDB 设置的参数值，在非 root PDB 级别修改INMEMORY_SIZE 参数无需重启实例或 PDB。

本测试环境在 root PDB 中设置 INMEMORY_SIZE 为 150GB 如下，所有 PDB 将继承该参数值：

SQL>alter system set inmemory_size=150G scope=spfile;

另外，在配置了 IMO 的情况下，也可以通过设置 INMEMORY_FORCE=OFF 的方式强制清空 SGA中 IN-MEMORY 内存区域中的对象，即强制关闭 IMO 特性，该参数为动态参数，同时可以在 PDB 级别设置。
此外，还可以通过在全局或会话级别设置 INMEMORY_QUERY=DISABLE 禁止 CBO 在查询时考虑使用 IMO 特性，该参数为动态参数，同时可以在 PDB 级别设置。

2.2IMO 初始化参数

SQL> select name,value,description from v$system_parameter where name like '%inmemory%';

NAME                                               VALUE    DESCRIPTION
------------------------------------------- -------------   ------------------------------------------------
inmemory_size                                   161061273600      size in bytes of in-memory area
inmemory_clause_default                     Default in-memory clause for new tables
inmemory_force                                  DEFAULT           Force tables to be in-memory or not       
inmemory_query                                  ENABLE            Specifies whether in-memory queries are allowed
inmemory_max_populate_servers                   32            maximum inmemory populate servers
inmemory_trickle_repopulate_servers_percent     1             inmemory trickle repopulate servers percent
optimizer_inmemory_aware                    TRUE              optimizer in-memory columnar awareness

以上小节描述了与 IMO 开启与关闭有关的三个参数，本节将描述另外 4 个与 IMO 相关的参数。

• inmemory_clause_default：在 IMO 开启的情况下，通过该参数设置新建表或物化视图赋予的 INMEMORY 属性，如：新建表或视图是否直接设置 INMEMORY、INMEMORY 优先级、压缩方法、RAC 环境中是否在所有节点实例中进行 duplicate 保存等。

注：duplicate 属性目前仅支持 Oracle Engineered System ，如 Oracle exadata。因此在非 Oracle Engineered System RAC 环境中使用 IMO 会受到一些制约，同时严重影响 IMO 效果。

• inmemory_max_populate_servers:表或物化视图载入内存允许启用的最大发布进程数。该参数缺省值为主机可用 cpu 数的一半与 PGA_AGGREGATE_TARGET/512M 比较,取较小值。
• inmemory_trickle_repopulate_servers_percen：当 IMCU 中脏数据达到一定阈值时触发 IMCU 向内存重启加载，重新加载过程中可以使用的最大发布进程数，该参数缺省值为 1。
• optimizer_inmemory_aware：用于控制 CBO 优化器是否考虑 IN-MEMORY 特性带来的执行计划改变，该参数默认为 true，在 SQL 执行过程中优化器会考虑 IN-MEMORY 特性。

2.3IMO 适用对象及优先级

IMO 可以基于如下对象维度启用：

• Table---全表级启用
• Column---表中部分列级启用
• Materialized view---物化视图级启用
• Tablespace---表空间级启用
• Partition---分区表分区级启用

注：SYS 用户及 SYSTEM 和 SYSAUX 表空间对象、IOT 表、cluster table 不支持 IMO 特性。

针对启用了 IMO 的对象，会按照一定的优先级进入 SGA 中配置好的 IN-MEMORY 区域，同时，在 IN-MEMORY 区域用满后，依次置换出优先级较低的对象。下表为关于 IMO 对象优先级说明：

优先级	描述
PRIORITY NONE	缺省级别；执行 SQL 引起对象扫描后，触发进入 IN-MEMORY
PRIORITY CRITICAL	最高优先级；数据库启动后立即进入 IN-MEMORY
PRIORITY HIGH	在具有 CRITICAL 优先级的对象之后进入 IN-MEMORY
PRIORITY MEDIUM	在具有 CRITICAL、HIGH 优先级的对象之后进入 IN-MEMORY
PRIORITY LOW	在具有 CRITICAL、HIGH、MEDIUM 优先级的对象之后进入 IN-MEMORY

IMO 适用对象测试

TABLE 级启用：可以通过如下初始建表或后续修改表 inmemory 属性的方式进行启用：

create table imo_t1 ( id number,name varchar2(100), type VARCHAR2(100)) inmemory;

或

alter table IMO_T2 inmemory;
SELECT table_name,inmemory FROM user_tables where table_name in ('IMO_T1','IMO_T2');

TABLE_NAME            INMEMORY
-------------------- --------
IMO_T2                ENABLED
IMO_T1                ENABLED

COLUMN 级启用：仅启用表中某列前，该表必须先设置为 inmemory 模式：

SELECT table_name,inmemory FROM user_tables where table_name in ('IMO_T1','IMO_T2');

TABLE_NAME           INMEMORY
-------------------- --------
IMO_T2              ENABLED
IMO_T1              ENABLED



alter table imo_t1 inmemory (id) no inmemory (name,type); alter table imo_t2 inmemory (name) no inmemory (id,type);

SELECT table_name, segment_column_id seg_col_id, column_name, inmemory_compression FROM v$im_column_level WHERE owner = 'IMOTEST' and table_name in ('IMO_T1','IMO_T2') ORDER BY 1,3;

                
TABLE_NAME SEG_COL_ID COLUMN_NAME   INMEMORY_COMPRESSION    
---------- ---------- --------------- -----------------------   
IMO_T1           1          ID          DEFAULT 
IMO_T1           2          NAME        NO INMEMORY 
IMO_T1           3          TYPE        NO INMEMORY 
IMO_T2           1          ID          NO INMEMORY 
IMO_T2           2          NAME        DEFAULT 
IMO_T2           3          TYPE        NO INMEMORY 

物化视图级启用：可以通过如下初始创建物化视图或后续修改物化视图 inmemory 属性的方式进行启用：

create materialized view imo_t1_mv inmemory as select * from imo_t1;

或

alter materialized view imo_t2_mv inmemory;

set linesize 1000
col table_name FORMAT A20 

SELECT table_name,inmemory FROM user_tables where table_name in ('IMO_T1_MV','IMO_T2_MV');

TABLE_NAME          INMEMORY
-------------------- --------
IMO_T2_MV           ENABLED
IMO_T1_MV           ENABLED

表空间级启用：可以通过如下初始创建表空间或后续修改表空间 inmemory 属性的方式进行启用，在属性为 inmemory 的表空间中创建的对象自动加载 inmemory 属性，除非显示设置对象为 no inmemory：

create tablespace imotest datafile '/u01/app/oracle/oradata/test12c/imotest01.dbf' size 100M default inmemory;

或

alter tablespace imotest default inmemory;
select tablespace_name, def_inmemory from dba_tablespaces where tablespace in ('IMOTEST');

TABLESPACE_NAME                 DEF_INMEMORY
------------------------------ ---------------
IMOTEST                         ENABLED


create table imo_t3 tablespace imotest as select * from imo_t1; create table imo_t4 tablespace imotest as select * from imo_t1;
SELECT table_name,
    inmemory
FROM user_tables
WHERE table_name IN (
        'IMO_T3',
        'IMO_T4'
        );

    TABLE_NAME          INMEMORY    
    -------------------- --------   
    IMO_T4              ENABLED 
    IMO_T3              ENABLED 
    
    
ALTER TABLE imo_t3 no inmemory;

SELECT table_name,
    inmemory
FROM user_tables
WHERE table_name IN (
        'IMO_T3',
        'IMO_T4'
        );


    
    TABLE_NAME      INMEMORY    
-------------------- --------   
    IMO_T4          ENABLED 
    IMO_T3          DISABLED    

分区表分区级启用：可以通过如下初始创建分区表或后续修改分区 inmemory 属性的方式进行启用：


CREATE TABLE imo_t5_part (
    id number NOT NULL,
    iddate DATE,
    scn number
    ) tablespace users PARTITION BY range (id) (
    PARTITION p_test_1 VALUES less than(20000) inmemory,
    PARTITION p_test_2 VALUES less than(40000),
    PARTITION p_test_3 VALUES less than(60000),
    PARTITION p_test_4 VALUES less than(80000),
    PARTITION p_test_5 VALUES less than(100000),
    PARTITION p_test_6 VALUES less than(maxvalue)
    );


或

alter table imo_t5_part modify partition P_TEST_1 inmemory;

SELECT table_name,
    partition_name,
    inmemory
FROM user_tab_partitions
WHERE table_name = 'IMO_T5_PRAT'
ORDER BY 2;


            
TABLE_NAME                  PARTITION_NAME  INMEMORY    
-------------------- -------------------- --------  
IMO_T5_PART                 P_TEST_1    ENABLED 
IMO_T5_PART                 P_TEST_2    DISABLED    
IMO_T5_PART                 P_TEST_3    DISABLED    
IMO_T5_PART                 P_TEST_4    DISABLED    
IMO_T5_PART                 P_TEST_5    DISABLED    
IMO_T5_PART                 P_TEST_6    DISABLED    

IMO 对象优先级测试

不指定优先级缺省默认级别为 none，同时需要通过执行 SQL 引起对象扫描后，触发进入 IN-MEMORY。

alter table imo_t1 inmemory;

select table_name,inmemory,inmemory_priority from user_tables;

TABLE_NAME          INMEMORY INMEMORY
-------------------- -------- --------
IMO_T1                  ENABLED NONE


select segment_name, partition_name, segment_type, inmemory_priority from v$im_segments;

no rows selected

select count(*) from imo_t1;


select segment_name, partition_name, segment_type, inmemory_priority from v$im_segments;

SEGMENT_NAME            PARTITION_NAME  SEGMENT_TYPE    INMEMORY_PRIORITY
-------------------- --------------- ------------------ ------------------
IMO_T1                                          TABLE   NONE

在 IN-MEMORY 空间充足的情况下，除 NONE 以外 CRITICAL、HIGH、MEDIUM、LOW 优先级会在数据库启动后或对象设置权限后自动进入 IN-MEMORY：

select segment_name, partition_name, segment_type, inmemory_priority from v$im_segments order by 1;

SEGMENT_NAME                PARTITION_NAME  SEGMENT_TYPE    INMEMORY
-------------------- -------------------- ------------------ --------
IMO_T1                                                  TABLE   NONE

alter table imo_t2 inmemory priority critical;
alter table imo_t5_part modify partition P_TEST_1 inmemory priority high;

alter table imo_t4 inmemory priority medium; alter table imo_t1_mv inmemory priority low;

select segment_name, partition_name, segment_type, inmemory_priority from v$im_segments order by 1;

SEGMENT_NAME            PARTITION_NAME  SEGMENT_TYPE    INMEMORY
--------------------    --------------- --------------- --------
IMO_T1                                          TABLE   NONE
IMO_T1_MV                                       TABLE   LOW
IMO_T2                                          TABLE   CRITICAL
IMO_T4                                          TABLE   MEDIUM
IMO_T5_PART P_TEST_1                            TABLE PARTITION HIGH

2.4IMO 压缩方式

Oracle 在 IMO 中的对象压缩，使用新的压缩算法，只有结果集需要的数据才会进行解压(decompress)。即考虑节约空间，同时考虑性能的提升。

针对 IMO 对象的压缩可以分为如下 6 种方式,实际压缩比取决于具体数据。

压缩方式	描述
NO MEMCOMPRESS	IMO 中存储无压缩
MEMCOMPRESS FOR DML	最小化压缩，优化 DML 操作
MEMCOMPRESS FOR QUERY LOW	缺省方式：查询性能最优(best)、空间压缩效果好于 DML 方式
MEMCOMPRESS FOR QUERY HIGH	查询性能次优(excellent)、空间压缩效果好于 QUERY LOW
MEMCOMPRESS FOR CAPACITY LOW	查询性能良好(good)、空间压缩效果好于 QUERY HIGH
MEMCOMPRESS FOR CAPACITY HIGH	CAPACITY 方式的缺省设置、空间压缩效果最优

根据表中具体数据的不同压缩比可以在 2X-20X 之间，如下 CUSTOMERS 表 12.6G 左右，不同压缩方式测试过程和结果如下：

alter table CUSTOMERS inmemory priority high NO MEMCOMPRESS;

INST_ID     SEGMENT_NAME    INGB    TBGB      NOGB      POPULATE_STATUS         INMEMORY_COMPRESSION INMEMORY_DISTRIBUTE
---------- ------------ ---------- ---------- ---------- ------------------ -------------------- --------------------
        1   CUSTOMERS   11.0810547  12.640625   0        COMPLETED                NO MEMCOMPRESS    AUTO


alter table CUSTOMERS inmemory priority high MEMCOMPRESS FOR DML;

INST_ID    SEGMENT_NAME INGB           TBGB    NOGB       POPULATE_STATUS   INMEMORY_COMPRESSION INMEMORY_DISTRIBUTE
---------- ------------ ---------- ---------- ---------- ------------------ -------------------- -------------------
1          CUSTOMERS    11.0830078  12.640625   0          COMPLETED            FOR DML           AUTO

alter table CUSTOMERS inmemory priority high;           
INST_ID    SEGMENT_NAME INGB      TBGB      NOGB      POPULATE_STATUS     INMEMORY_COMPRESSION INMEMORY_DISTRIBUTE
---------- ---------- --------- ---------- ---------- ------------------ -------------------- ---------------------
1           CUSTOMERS 7.58148193 12.640625  0           COMPLETED           FOR QUERY LOW      AUTO

alter table CUSTOMERS inmemory priority high MEMCOMPRESS FOR QUERY HIGH;    
INST_ID    SEGMENT_NAME INGB        TBGB        NOGB     POPULATE_STATUS    INMEMORY_COMPRESSION NMEMORY_DISTRIBUTE
---------- ----------- ----------- ---------- ---------- ------------------ -------------------- ------------------
1           CUSTOMERS   5.637146    12.640625   0          COMPLETED         FOR QUERY HIGH       AUTO


alter table CUSTOMERS inmemory priority high MEMCOMPRESS FOR CAPACITY LOW;  
INST_ID     SEGMENT_NAME    INGB    TBGB           NOGB   POPULATE_STATUS   INMEMORY_COMPRESSION INMEMORY_DISTRIBUTE
---------- ------------ ---------- ---------- ---------- ------------------ -------------------- -------------------
1           CUSTOMERS   4.07855225  12.640625   0               COMPLETED   FOR CAPACITY LOW    AUTO

压缩方式	压缩后大小
NO MEMCOMPRESS	11.08G
MEMCOMPRESS FOR DML	11.08G
MEMCOMPRESS FOR QUERY LOW	7.58G
MEMCOMPRESS FOR QUERY HIGH	5.63G
MEMCOMPRESS FOR CAPACITY LOW	4.07G
MEMCOMPRESS FOR CAPACITY HIGH	3.19G

3.IMO 效果测试

本次测试环境为两台 PC Server 构建的 RAC 环境，由于 RAC 环境中 IMO 的 duplicate 功能目前仅支持 Oracle Engineered System ，如 Oracle exadata，即使在非一体机环境设置了 duplicate
也无法生效。因此在 RAC 环境中整体 IMO 性能提升程度受到严重影响。

官方宣称的 IMO 适用场景如下：

1. 扫描大量数据行并通过=、<、>或 IN 谓词条件进行过滤的查询；
2. 在一个具有较多字段的表中，仅查询较少字段，如表中拥有 100 个字段，查询仅获取 5个字段；
3. 小表与大表的连接查询；
4. 聚合查询。

以下为以“聚合查询”场景为例，描述 RAC 环境中 IMO 对象跨节点发布及单节点发布的性能表现，其它三种适用场景通过测试表现基本一致。

3.1跨节点发布

非exadata RAC 环境中 IMO 对象的发布会按照一定的规则将数据拆分平均发布到不同节点的IN-MEMORY 中。以测试中使用的 CUSTOMERS 表举例如下：

INST_ID      SEGMENT_NAME INMEMORY_SIZE BYTES        BYTES_NOT_POPULATED INMEMORY_DISTRIBUTE POPULATE_STATUS
----------  ------------ ------------- ------------- ------------------- ------------------- ---------------
1              CUSTOMERS    4151181312  13572767744  6644211712                      AUTO   COMPLETED
2              CUSTOMERS    3989438464  13572767744  6907183104                      AUTO   COMPLETED

可以看到如上 CUSTOMERS 表拆分后被平均分布到两个节点的 IN-MEMORY 中。
INMEMORY_SIZE 表示压缩后列式存储在内存中的大小；
BYTES 字段表示 CUSTOMERS 表以传统行式存储的大小约 12.6G； BYTES_NOT_POPULATED 表示 CUSTOMERS 表在对应实例没有被发布到 IMO 中的部分。此时 CUSTOMERS 表已经完成 IMO 发布，执行如下 SQL 观察效果：

alter system flush buffer_cache;---清空 buffer    cache

select count(*) from    customers t;

Elapsed: 00:00:41.85

Execution Plan

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

Plan hash value: 296924608

---------------------------------------------------------------------------------------
| Id    | Operation                   | Name      | Rows    | Cost (%CPU)  | Time     |
---------------------------------------------------------------------------------------
|   0   | SELECT STATEMENT            |           | 1       |   229K(1)    | 00:00:09 |
|   1   |   SORT AGGREGATE            |           | 1       |              |          |
|   2   |   TABLE ACCESS INMEMORY FULL| CUSTOMERS | 100M    |   229K(1)    | 00:00:09 |
---------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
4       recursive calls
0       db block gets
811101  consistent gets
811092  physical reads
0       redo size
542     bytes sent via SQL*Net to client
552     bytes received via SQL*Net from client
2       SQL*Net roundtrips to/from client
0       sorts (memory)
0       sorts (disk)
1       rows processed

从以上执行结果可以看到：执行计划虽然已经变化 INMEMORY 中的全表扫描，但依然产生了大量的逻辑读(811101)及物理读(811092)，这种开销对于 IMO 内存扫描是不合理的。如下第二次执行该 SQL，我们看到物理读消失，但依然产生了与第一次执行近似的逻辑读(811093)，这同样是不合理的。

select count(*) from customers t;

Elapsed: 00:00:04.20

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
-------------------------------------------------------------------------------------
| Id    | Operation                   | Name      | Rows    | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0   | SELECT STATEMENT            |           | 1       |   229K  (1)| 00:00:09 |
|   1   |   SORT AGGREGATE            |           | 1       |            |          |
|   2   |   TABLE ACCESS INMEMORY FULL| CUSTOMERS | 100M    |   229K  (1)| 00:00:09 |
-------------------------------------------------------------------------------------

Statistics          
----------------------------------------------------------  
0       recursive calls     
0       db block gets       
811093  consistent gets     
0       physical reads  
0       redo size   
542     bytes sent via SQL*Net to client    
552     bytes received via SQL*Net from client  
2       SQL*Net roundtrips to/from client   
0       sorts (memory)
0       sorts (disk)
1       rows processed

进一步通过设置如下两个参数后，再次执行如上 SQL。

alter system set parallel_degree_policy=AUTO sid='*';-打开 oracle 自动并行设置 alter system set parallel_force_local=false sid='*';-设置并行进程不允许跨节点执行

alter system flush buffer_cache;---清空 buffer cache

select count(*) from    customers t;

Execution Plan

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

Plan hash value: 1221513835

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

TQ  |IN-OUT| PQ Distrib |

---------------------------------------------------------------------------------------------------------
|   0   | SELECT STATEMENT            |           | 1   | 25463 (1)| 00:00:01 |      |      |           |
|   1   |   SORT AGGREGATE            |           | 1   |          |          |      |      |           |
|   2   |   PX COORDINATOR            |           |     |          |          |      |      |           |
|   3   |   PX SEND QC (RANDOM)       | :TQ10000  | 1   |          |          |Q1,00 | P->S | QC (RAND) |                   
|   4   |   SORT AGGREGATE  |         | 1         |     |          |          |Q1,00 | PCWP |           |                   
|   5   |   PX BLOCK ITERATOR         |           | 100M| 25463 (1)| 00:00:01 |Q1,00 | PCWC |           |                   
|   6   |   TABLE ACCESS INMEMORY FULL| CUSTOMERS | 100M| 25463 (1)| 00:00:01 |Q1,00 | PCWP |           |                   
---------------------------------------------------------------------------------------------------------

Note
-----

-   automatic DOP: Computed Degree of Parallelism is 10
-   parallel scans affinitized for inmemory

Statistics

----------------------------------------------------------
30      recursive calls
0       db block gets
7155    consistent gets
0       physical reads
0       redo size
542     bytes sent via SQL*Net to client
552     bytes received via SQL*Net from client
2       SQL*Net roundtrips to/from client
0       sorts (memory)
0       sorts (disk)
1       rows processed

从以上执行结果可以看到：即使清空 buffer cache 的情况下也没有出现大量物理读，同时执行计划中启用了自动并行，并出现并行 inmemory 扫描，单次执行逻辑读从 811093 下降为 7155，呈几何级下降。

转载于:https://www.cnblogs.com/wangrongxin/p/5870098.html