In-Memory测试

测试要使用普通用户，不要用sysaux和system表空间

2.1 全字段查询

通过CTAS方式创建表TEST,普通行式扫描与开启inmemory特性分别测试，比对执行计划。

create table test as select * from dba_objects;

no inmemory查询测试

set timing on

set autotrace traceonly

set pagesize 200 linesize 200

col TABLE_NAME for a20

col INMEMORY_PRIORITY for a20

col INMEMORY_DISTRIBUTE for a20

col INMEMORY_COMPRESSION for a20

全字段查询，执行计划

SQL> select count(*) from test;

COUNT(*)

----------

74329

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 403 (1)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| TEST | 74329 | 403 (1)| 00:00:01 |

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

Statistics

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

0 recursive calls

5 db block gets

1460 consistent gets

0 physical reads

0 redo size

544 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

开启in memory查询测试

SQL> alter table test inmemory;

1

SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables;

TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION

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

TEST NONE AUTO FOR QUERY LOW

已开启inmemory

SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id

from V$INMEMORY_AREA;

POOL ALLOC_BYTES/1024/1024 USED_BYTES/1024/1024 POPULATE_STATUS CON_ID

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

1MB POOL 815 0 DONE 3

64KB POOL 192 0 DONE 3

--因为只是把该表设置了INMEMORY,但是未查询过,所以查询V$INMEMORY_AREA中未使用相关内存--

全字段查询，执行计划

第一次执行：

SQL> select count(*) from test;

COUNT(*)

----------

74329

Elapsed: 00:00:00.05

Execution Plan

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 403 (1)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS INMEMORY FULL| TEST | 74329 | 403 (1)| 00:00:01 |

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

Statistics

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

5 recursive calls

5 db block gets

1467 consistent gets

0 physical reads

0 redo size

544 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

第二次执行：

Elapsed: 00:00:00.03

Execution Plan

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 16 (7)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS INMEMORY FULL| TEST | 74329 | 16 (7)| 00:00:01 |

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

Statistics

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

0 recursive calls

3 db block gets

9 consistent gets

0 physical reads

0 redo size

544 bytes sent via SQL*Net to client

608 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

查询inmemory内存使用：

SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id

from V$INMEMORY_AREA;

POOL ALLOC_BYTES/1024/1024 USED_BYTES/1024/1024 POPULATE_STATUS CON_ID

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

1MB POOL 815 4 DONE 3

64KB POOL 192 .25 DONE 3

--再次查看，已经使用了分配的In-Memory中内存

性能比对：

sql plan no-inmemory inmemory

consistent gets 1460 9

physical reads 0 0

Cost 403 16

结果：

开启inmemory之后性能提升162倍

2.2 索引字段比较测试

在已开启IMO特性条件下，通过给表test增加列索引，比较IM与索引执行计划

1、在object_name上创建索引

SQL> create index idx_test_OBname on test(OBJECT_NAME);

1

2、查看表TEST是否加载到IM中

col owner for a20

col SEGMENT_NAME for a20

col POPULATE_STATUS for a20

col BYTES_NOT_POPULATED for 99999

SELECT OWNER, SEGMENT_NAME, POPULATE_STATUS, BYTES_NOT_POPULATED FROM V$IM_SEGMENTS;

OWNER SEGMENT_NAME POPULATE_STATUS BYTES_NOT_POPULATED

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

CS TEST COMPLETED 0

表已被加入到IM中

3、使用索引简单列查询

SQL> set autotrace traceonly

SQL> select count(*) from test where object_name='TEST';

Execution Plan

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

Plan hash value: 3197243274

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 35 | | |

|* 2 | INDEX RANGE SCAN| IDX_TEST_OBNAME | 10 | 350 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_NAME"='TEST')

Statistics

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

2 recursive calls

0 db block gets

6 consistent gets

2 physical reads

0 redo size

542 bytes sent via SQL*Net to client

607 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

逻辑读：6

物理：2

4、强制全表扫描查询

SQL> select /*+full(s)*/count(*) from test s where object_name='test';

Execution Plan

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 124 (1)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 35 | | |

|* 2 | TABLE ACCESS INMEMORY FULL| TEST | 9 | 315 | 124 (1)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - inmemory("OBJECT_NAME"='test')

filter("OBJECT_NAME"='test')

Statistics

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

1 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

541 bytes sent via SQL*Net to client

607 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

逻辑读：10

物理读：0

总结：在数据离散度较高，且通过索引条件过滤的扫描场景中，IM特性对性能并没有提升，传统的索引+行式存储的执行计划已经足够，

在默认情况下还是会根据查询索引返回rowid的方式查找数据。

2.3 批量update测试

1、创建测试表disk、emp，分别为im表以及rows表，总数相同

SQL> create table disk as select * from dba_objects;

SQL> insert into disk select * from disk;

74360 rows created.

SQL> /

148720 rows created.

SQL> /

297440 rows created.

SQL> /

594880 rows created.

SQL> /

1189760 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from disk;

COUNT(*)

----------

2379520

SQL> create table mem as select * from disk inmemory;

更新磁盘表

update disk set owner=‘tom’;

更新内存表

update mem set owner=‘tom’;

普通表：

时间：14.49

逻辑读：3202294+894128

物理读：5602

cost：403

IM表：

时间：18.94

逻辑读：3231179+254

物理读：92476

cost：483

总结：IM特性在update性能有所下降

2.4 大批量insert测试

从磁盘插到磁盘

insert into disk select * from cs;

从磁盘插到内存

insert into mem select * from cs;

普通表：

时间：0.13

逻辑读：9332+2903

物理读：2895

cost：404

IM表：

时间：0.17

逻辑读：3231179+254

物理读：92476

cost：483

2.5 大量delete测试

delete from disk where rownum <10000;

delete from mem where rownum <10000;

普通表：

时间：0.08

逻辑读：11470+202

物理读：253

cost：12986

IM表：

时间：0.07

逻辑读：11472+9

物理读：0

cost：493

2.6 压缩方式测试

压缩方式描述

NO MEMCOMPRESS IMO 中存储无压缩

MEMCOMPRESS FOR DML 最小化压缩，优化 DML 操作

MEMCOMPRESS FOR QUERY LOW 缺省方式：查询性能最优、空间压缩效果好于DML方式

MEMCOMPRESS FOR QUERY HIGH 查询性能次优(excellent)、空间压缩效果好于 QUERY LOW

MEMCOMPRESS FOR CAPACITY LOW 查询性能良好(good)、空间压缩效果好于 QUERY HIGH

MEMCOMPRESS FOR CAPACITY HIGH 缺省设置、空间压缩效果最优

查看压缩比

col owner for a5

col SEGMENT_NAME for a10

col POPULATE_STATUS for a10

col INMEMORY_COMPRESSION for a10

SQL> SELECT V.OWNER, V.SEGMENT_NAME,V.BYTES/1024/1024 ORIG_SIZE_MB,V.INMEMORY_SIZE/1024/1024 IN_MEM_SIZE_MB,BYTES_NOT_POPULATED,POPULATE_STATUS status,INMEMORY_COMPRESSION,V.BYTES/V.INMEMORY_SIZE COMP_RATIO FROM V$IM_SEGMENTS V WHERE SEGMENT_NAME = 'TEST';

OWNER SEGMENT_NAME ORIG_SIZE_MB IN_MEM_SIZE_MB BYTES_NOT_POPULATED STATUS INMEMORY_COMPRESS COMP_RATIO

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

CS TEST 1466.78125 110.625 0 COMPLETED FOR QUERY LOW 13.2590395

表TEST在磁盘上占用1466MB,采用默认压缩方式，内存中占用110.625MB，压缩比为13.26：1

压缩比测试：

表TEST大小1466.78125MB，数据行9514112

1、NO MEMCOMPRESS：

alter table test inmemory MEMCOMPRESS NO MEMCOMPRESS;

逻辑读：14

物理读：0

COST: 126

时间： 0.01

压缩比：1.19

2、MEMCOMPRESS FOR DML：

alter table test inmemory MEMCOMPRESS FOR DML;

逻辑读：14

物理读：0

COST: 126

时间：0.01

压缩比：1.22

3、MEMCOMPRESS FOR QUERY LOW：

alter table test inmemory MEMCOMPRESS FOR QUERY LOW;

逻辑读：14

物理读：0

COST:125

时间：0.01

压缩比：7.20

4、MEMCOMPRESS FOR QUERY HIGH：

alter table test inmemory MEMCOMPRESS FOR QUERY high;

逻辑读：14

物理读：0

COST: 125

时间：0.01

压缩比：9.67

5、MEMCOMPRESS FOR CAPACITY LOW：

alter table test inmemory MEMCOMPRESS FOR CAPACITY LOW;

逻辑读：14

物理读：0

COST: 125

时间：0.01

压缩比：12.91

6、MEMCOMPRESS FOR CAPACITY HIGH：

alter table test inmemory MEMCOMPRESS FOR CAPACITY HIGH;

逻辑读：14

物理读：0

COST: 25

时间：0.01

压缩比：19.14

测试结果汇总：

压缩方式压缩比

NO MEMCOMPRESS 1.19

MEMCOMPRESS FOR DML 1.22

MEMCOMPRESS FOR QUERY LOW 7.20

MEMCOMPRESS FOR QUERY HIGH 9.67

MEMCOMPRESS FOR CAPACITY LOW 12.91

MEMCOMPRESS FOR CAPACITY HIGH 19.14

总结：压缩方式不同，表加载到IM中的时间也会不一样。压缩比越大加载到内存中的时间越长。而对于select查询消耗时间不影响。

2.7 查询大量列性能测试

三、评估对象在IMO大小

DBMS_COMPRESSION.GET_COMPRESSION_RATIO

在对一张表使用COMPRESSION clause进行IM压缩级别设置之前，我们可以通过Oracle的COMPRESSION ADVISOR对表放入到IM中的大小进行提前计算。

SET SERVEROUTPUT ON

DECLARE

l_blkcnt_cmp PLS_INTEGER;

l_blkcnt_uncmp PLS_INTEGER;

l_row_cmp PLS_INTEGER;

l_row_uncmp PLS_INTEGER;

l_cmp_ratio PLS_INTEGER;

l_comptype_str VARCHAR2(100);

BEGIN

dbms_compression.get_compression_ratio (

-- Input parameters

scratchtbsname => 'CS',

ownname => 'CS',

objname => 'TEST',

subobjname => NULL,

comptype => dbms_compression.comp_inmemory_QUERY_LOW,

-- Output parameter

blkcnt_cmp => l_blkcnt_cmp,

blkcnt_uncmp => l_blkcnt_uncmp,

row_cmp => l_row_cmp,

row_uncmp => l_row_uncmp,

cmp_ratio => l_cmp_ratio,

comptype_str => l_comptype_str,

subset_numrows => dbms_compression.comp_ratio_allrows);

dbms_output.put_line('Comp. ratio (QUERY LOW):'||l_cmp_ratio);

END;

/

Comp. ratio (QUERY LOW):7

估计结果压缩比7，实际压缩比为7.20

#五、RAC环境测试

rac环境独有参数DUPLICATE clause、DISTRIBUTE clause：

DUPLICATE clause：

此参数为EXADATA一体机专用，在RAC环境中，每个节点拥有自己的IM Area。一个objects根据DUPLICATE clause的设置将一样的数据加载到多个IM Area中。

默认是NO DUPLICATE设置，表示在数据库的IM中对一个objects在所有节点中合起来只保存一份。举例说明，比如三节点的RAC中，对于分区表SALES来讲可能2012年份的数据在1节点，2013年份的数据在2节点，2014年份的数据在3节点，每个分区只保存在一个节点上。

为了提升可用性，也可以设置为 DUPLICAET ALL，在每个节点上都保存一份。举例说明，还是刚才那个SALES表的请款下，1，2，3三个节点各保存一份完整sales表数据到各自的IM中。在任意一个节点上都可以获取查询需要的数据。

在设置为DUPLICATE ALL的情况下

DISTRIBUTE clause：

如果一个objects因为太大无法被加载到一个IM Area中，还可以通过DISTRIBUTE clause的设置将它分成几个数据片分别加载到不同的节点中。

默认情况下 DISTRIBUTE clause 的默认值为AUTO-DISTRIBUTE，这时候是否将objects分布式分布在不同的节点上由Oracle内部算法决定。这个参数对于单实例没有影响，在RAC环境中，默认存在IM中的表会分布在各个节点之中。

RAC环境并不像单实例一样只需修改表的IM属性即可启用，如果要使用IMO，必须在系统层面修改“并行度策略”为自动。下面对参数“parallel_degree_policy”，分别测试2个场景

1、先查看要测试的表TEST是否已加载到IM STORE

col SEGMENT_NAME for a5

col POPULATE_STATUS for a10

col INMEMORY_DISTRIBUTE for a10

col INMEMORY_DUPLICATE for a20

SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments;

INST_ID SEGME INMEMORY_SIZE/1024/1024 BYTES/1024/1024 BYTES_NOT_POPULATED/1024/1024 INMEMORY_D INMEMORY_DUPLICATE POPULATE_S

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

2 TEST 9.125 192 123.53125 AUTO NO DUPLICATE COMPLETED

1 TEST 16.1875 192 66.9140625 AUTO NO DUPLICATE COMPLETED

1、开启AUTO DOP

alter system set parallel_degree_policy=AUTO sid='*';

alter system set parallel_force_local=false sid='*';

alter system flush buffer_cache;

alter system flush shared_pool;

2、查看执行计划

SQL> select /*+ parallel */count(*) from test;

COUNT(*)

----------

1455168

Execution Plan

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

Plan hash value: 2661943167

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

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

| 0 | SELECT STATEMENT | | 1 | 9 (0)| 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 | | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWP | |

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

Note

-----

- automatic DOP: Computed Degree of Parallelism is 2

- parallel scans affinitized for inmemory

Statistics

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

232 recursive calls

4 db block gets

163 consistent gets

15 physical reads

0 redo size

545 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

16 sorts (memory)

0 sorts (disk)

1 rows processed

逻辑读：163

物理读：15

、不使用并行查询查询

SQL> select count(*) from test;

COUNT(*)

----------

1455168

Execution Plan

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

Plan hash value: 1950795681

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

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

| 0 | SELECT STATEMENT | | 1 | 16 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 16 (0)| 00:00:01 |

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

Note

-----

- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation

Statistics

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

3 recursive calls

0 db block gets

9081 consistent gets

9077 physical reads

0 redo size

545 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

逻辑读：9081

物理读：9077

4、关闭AUTO DOP

alter system set parallel_degree_policy=MANUAL sid='*';

alter system set parallel_force_local=false sid='*';

alter system flush buffer_cache;

alter system flush shared_pool;

5、查看执行计划

SQL> select /*+ parallel */count(*) from test;

COUNT(*)

----------

1455168

Execution Plan

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

Plan hash value: 2661943167

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

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

| 0 | SELECT STATEMENT | | 1 | 9 (0)| 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 | | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWC | |

| 6 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWP | |

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

Note

-----

- automatic DOP: Computed Degree of Parallelism is 2

- parallel scans affinitized for inmemory

Statistics

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

209 recursive calls

4 db block gets

17909 consistent gets

17766 physical reads

0 redo size

545 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

16 sorts (memory)

0 sorts (disk)

1 rows processed

逻辑读：17909

物理读：17766

总结：

1、调整系统参数(parallel_degree_policy)之后，rac环境下的IM列查询大幅降低了逻辑读与物理读。

2、在多实例的并发查询中实例之间传输的并不是IMCU，而是每个节点都会对本节点的数据运行相同的sql语句，之后把自己的结果集发送给发起sql语句的实例，组成最终的结果返回给用户。

parallel_degree_policy AUTO MANUAL

逻辑读 163 17909

物理读 15 17766

备注：

在没有显式使用并行sql时，rac环境im全表扫描并没有使用并行。oracle的优化器会通过一系列的计算比较cost。

SQL> select count(*) from test;

执行计划Note

-----

automatic DOP:

Computed Degree of Parallelism is 1 because of no expensive parallel operation

In some cases, even with Auto DOP set correctly, the optimizer may calculate the cost of a serial access to be less than the

cost of a parallel access. This has been identified as bug 18960760, and will usually only happen when very smalltables in the

IM column store are involved in the query.

来自Oracle Blog

https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-i

四、参考文献

1、Oracle Database In-Memory on RAC - Part I

https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-i

2、Oracle 12c DB In-Memory入门实验手册

https://blog.csdn.net/badly9/article/details/49777993

3、Oracle12c IMO 测试

https://www.jianshu.com/p/966ee0182e1c

4、rac并行查询

http://blog.sina.com.cn/s/blog_74a7d3390102wegl.html

5、Oracle In-Memory白皮书

http://www.oracle.com/technetwork/cn/database/in-memory/overview/twp-oracle-database-in-memory-2245633-zhs.pdf

ITPub博客

In-Memory测试

最新文章