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
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 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 SQLNet to client
608 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 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 SQLNet to client
608 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 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 SQLNet to client
608 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 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 SQLNet to client
607 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 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 SQLNet to client
607 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 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 SQLNet to client
551 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 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 SQLNet to client
551 bytes received via SQLNet 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
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
| 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 SQLNet to client
551 bytes received via SQLNet 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
