oracle12c性能测试,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 组件进行相关测试，主要包括如下两大方面：

IMO 基础功能测试

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 适用场景如下：

扫描大量数据行并通过=、或 IN 谓词条件进行过滤的查询；

在一个具有较多字段的表中，仅查询较少字段，如表中拥有 100 个字段，查询仅获取 5个字段；

小表与大表的连接查询；

聚合查询。

以下为以“聚合查询”场景为例，描述 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，呈几何级下降。

未完待续