资深Oracle数据库专家 OCM认证大师 10年数据库相关服务及开发经验 各类数据库相关方案的编写，管理及实施 数据中心数据库日常运维、大型项目割接、性能优化等方面有丰富的实战经验 客户包括： 电信，银行，保险，航空,国网，汽车，烟草等 想要一起学习探讨数据安全技术的请加qq群 256041954
全部博文（163）
分类： Oracle
2016-04-20 21:22:55
Oracle 12c in memory选件通过在SGA中分配独立的内存区域(In Memory Area),对数据使用列式压缩存储来提高查询性能.
In Memory区的大小由参数inmemory_size控制, 该参数是一个静态参数, 修改后需要重启数据库方可生效.
In Memory内存区可分为两个子池，分别为1M pool和64K pool. 1M pool用于存储列式数据. 64K pool用于存储对象的元数据(metadata)和事务日志(transaction journal). 1M pool 中分配的内存块的大小为1M的整数倍，64K pool中分配的内存块大小均为64K。
v$inmemory_area视图有每个pool大小的详细信息:
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 854589440 10485760 DONE 0
64KB POOL 201326592 393216 DONE 0
In Memory区的数据装载(populating)
相对于磁盘或者闪存,内存资源毕竟成本更高,更为有限,所以需要将有限的资源分配给更需要的对象. Oracle新增了一个INMEMORY属性用于控制对象是否会被装载到In Memory区, 该属性可以定义在表 空间,表,分区甚至列级. Oracle In Memory白皮书中给出了一些设置对象IMMEMORY属性的具体用例, 摘录于此, 以备将来参考:
ALTER TABLESPACE ts_data INMEMORY;
ALTER TABLE sales INMEMORY NO INMEMORY(prod_id);
ALTER TABLE sales MODIFY PARTITION SALES_Q1_1998 NO INMEMORY;
此外,为了控制对象的装载顺序,Oracle定义了5种优先级来实现精细控制,通过priority子句来选择.详细信息如下:
根据自己的需求,可以选择以上几种优先级中的一种来定义该对象的装载顺序.若未指定priority子句，则其默认值为NONE.
IM对象的装载是由后台进程ora_w00*_完成的.参数inmemory_max_populate_servers控制IM后台装载进程的个数, 其默认值为cpu_count的一半. 进程数越多,装载速度越快,但消耗的资源也更多.
注：实际观察发现inmemory_max_populate_servers参数并不是对后台装载进程ora_w00*_的硬性限制，它更像是一个最小值的设定，因为实际中发现ora_w00*_进程数是可以动态增加的。
在对象载入In Memory区后, 可使用no inmemory操作将其从IM区清除
alter table TEST_IM no inmemory;
此外,要注意的是,改变原有的priority(或者memcompress)属性,也会导致该对象被清除出IM区.比如:
alter table TEST_IM inmemory priority critical; --原值为none
这里还要注意的一点是,由于存储数据块的大小为1M的整数倍,也就是内存分配的最小单位是1M,为了避免空间浪费,Oracle不会将<=64K大小的对象载入In Memory区.这里做个简单测试来验证这一点:
create table test_im(v varchar2(100)) inmemory pctused 1 pctfree 99 tablespace users;
insert into test_im select rpad('a',100,'a') from dual connect by level <=5;
commit;
SQL> select bytes/1024 from dba_segments where segment_name='TEST_IM';
BYTES/1024
----------
64
SQL> select * from TEST_IM;
……
SQL> select * from v$im_segments where segment_name='TEST_IM';
no rows selected
查询v$im_segments可发现表TEST_IM并不在IM区.
SQL> insert into test_im select rpad('a',100,'a') from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select bytes/1024 from dba_segments where segment_name='TEST_IM';
BYTES/1024
----------
128
SQL> select * from TEST_IM;
……
SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments where segment_name='TEST_IM';
SEGMENT_NAME INMEMORY_SIZE BYTES POPULATE_STATUS
-------------------- ------------- ---------- --------------------
TEST_IM 1179648 131072 COMPLETED
而当表TEST_IM的大小大于64K时,Oracle才会将其装载入In Memory区.
In Memory压缩
Oracle为IM中的对象提供了六种级别的压缩选项, 可通过MEMCOMPRESS子句来指定, 详细信息如下:
通过下例来观察In Memory列压缩功能:
SQL> create table test_im_comp tablespace users as select * from dba_objects;
Table created.
SQL> insert into test_im_comp select * from test_im_comp;
92203 rows created.
SQL> /
……
SQL> commit;
Commit complete.
SQL> alter table TEST_IM_COMP inmemory memcompress for query low;
SQL> select count(*) from test_im_comp;
COUNT(*)
----------
2950496
之后可以看到开始向In Memory区装载TEST_IM_COMP表:
select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments;
SEGMENT_NAME INMEMORY_SIZE BYTES POPULATE_
------------------------------ ------------- ---------- ---------
TEST_IM_COMP 6553600 411041792 STARTED
等待一会, TEST_IM_COMP表装载完毕，从压缩前后的大小比对可看出，这里采用“memcompress for query low”压缩算法后TEST_IM_COMP表的压缩比大约为6：1。
SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments;
SEGMENT_NAME INMEMORY_SIZE BYTES POPULATE_
------------------------------ ------------- ---------- ---------
TEST_IM_COMP 63373312 411041792 COMPLETED
通过使用dbms_compression包,也可以预估数据库对象在采用不同压缩级别后载入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);
comp_ratio_allrows NUMBER := -1;
BEGIN
dbms_compression.get_compression_ratio (
scratchtbsname => 'USERS',
ownname => 'SYS',
objname => 'TEST_IM_COMP',
subobjname => NULL,
comptype => dbms_compression.comp_inmemory_query_low, --该参数控制压缩级别
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('The IM compression ratio is '|| l_cmp_ratio);
END;
/
The IM compression ratio is 6
上述脚本中的comptype选项可从dbmscomp.sql脚本中获得,如下:
grep -i comp_inmemory $ORACLE_HOME/rdbms/admin/dbmscomp.sql
COMP_INMEMORY_NOCOMPRESS CONSTANT NUMBER := 8192;
COMP_INMEMORY_DML CONSTANT NUMBER := 16384;
COMP_INMEMORY_QUERY_LOW CONSTANT NUMBER := 32768;
COMP_INMEMORY_QUERY_HIGH CONSTANT NUMBER := 65536;
COMP_INMEMORY_CAPACITY_LOW CONSTANT NUMBER := 131072;
COMP_INMEMORY_CAPACITY_HIGH CONSTANT NUMBER := 262144;
在以上Oracle提供的5种压缩算法中，FOR QUERY LOW选项压缩后的数据查询性能最佳。它使用的是一些常规的压缩技术，如“Dictionary Encoding, Run Length Encoding and Bit-Packing”.
其他压缩技术的压缩比例更高，但随之带来的读取时解压成本也更高。
In Memory区数据的访问
当访问In Memory区中的对象时,执行计划中会出现新的INMEMORY关键字:
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 312 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| TEST_IM | 6 | 312 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
但这里要注意的是, 当出现INMEMORY关键字时并不表示数据一定是在IM区获得的. 这里的INMEMORY只能说明TEST_IM表的INMEMORY属性已被enable,对该表数据的访问有可能是从IM区得到的.这里使用前面的例子来说明这一点:
create table test_im(v varchar2(100)) inmemory pctused 1 pctfree 99 tablespace users;
insert into test_im select rpad('a',100,'a') from dual connect by level <=5;
commit;
SQL> select bytes/1024 from dba_segments where segment_name='TEST_IM';
BYTES/1024
----------
64
SQL> select * from TEST_IM;
……
SQL> select * from v$im_segments where segment_name='TEST_IM';
no rows selected
SQL> set autotrace traceonly
SQL> select * from test_im;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2761107969
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 260 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| TEST_IM | 5 | 260 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
这里可以看到,TEST_IM表并不在IM区,但执行计划显示得是TABLE ACCESS INMEMORY FULL,说明执行计划并不能作为判断是否是从IM区获取数据的依据.
随着IM功能的引入,Oracle增加了一些新的统计信息用于IM相关的统计,这里可以使用IM scan rows来统计IM访问的情况,继续上面的例子:
SQL> select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'IM scan rows';
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 6
SQL> select * from test_im;
V
----------------------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SQL> select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'IM scan rows';
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 6
IM scan rows统计值前后没有变化,可见这里的确没有IM访问.
SQL> insert into test_im select rpad('a',100,'a') from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from TEST_IM;
……
SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments where segment_name='TEST_IM';
SEGMENT_NAME INMEMORY_SIZE BYTES POPULATE_STATUS
-------------------- ------------- ---------- --------------------
TEST_IM 1179648 131072 COMPLETED
SQL> set autotrace traceonly
SQL> select * from test_im;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2761107969
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 312 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| TEST_IM | 6 | 312 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
805 bytes sent via SQL*Net to client
550 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select name,value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'IM scan rows';
NAME VALUE
---------------------------------------------------------------- ----------
IM scan rows 12
IM scan rows值增加了6, 说明这里的数据是从IM区获得的, 并且行数也完全吻合.
此外,观察autotrace的结果可发现,在使用了IM访问后,consistent gets也从之前的7降到了3。
IMCU(In Memory Compression Units)
IMCU类似于表空间中extent的概念，是列数据在IM区中内存分配块的大小。后台进程ora_w00*在装载数据时，会分配自己的IMCU，并将分配给该进程的数据加载到该IMCU中。此后，当访问IM区中的列数据时，我们在统计信息中看到的consistent gets值也就是统计所访问IMCU的个数和所需访问metadata块的个数之和。特定对象所分配IMCU的详细信息，可从视图V_$IM_HEADER中查询。而其metadata块的信息可从视图V_$IM_SMU_CHUNK和V_$IM_SMU_HEAD查询。
在In Memory内部，以IMCU为单位，Oracle维护了一个In Memory Storage Index,记录IMCU单元中该列的最大值，最小值。此外，Oracle也会在metadata区为每个IMCU建立相应的metadata dictionary, metadata信息中会有一些列的统计信息。视图V_$IM_COL_CU可以帮助查询这些metadata dictionary信息。
与IM相关的视图：
SQL> SELECT VIEW_NAME FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'V_$IM%';
VIEW_NAME
------------------------------------------------------------------------------------
V_$IM_SEGMENTS_DETAIL --记录IM段对象的详细存储属性
V_$IM_SEGMENTS --记录IM段对象的存储属性
V_$IM_USER_SEGMENTS --记录当前用户下IM段对象的存储属性
V_$IM_TBS_EXT_MAP --记录IM区1M子池对象的区间映射关系
V_$IM_SEG_EXT_MAP --记录IM区对象的所有区间映射关系
V_$IM_HEADER --记录IM区对象所分配IMCU的详细信息
V_$IM_COL_CU --记录IMCU中基于列的统计信息
V_$IM_SMU_HEAD
V_$IM_SMU_CHUNK
V_$IM_COLUMN_LEVEL --记录IMCU中对象的列级压缩属性，若未在列级定义，则改视图为空
10 rows selected.
--以上对各VIEW的注释来自测试中的观察，描述有可能欠准确。
与IM相关的等待事件：
SQL> select name from v$event_name where name like '%IM %';
NAME
----------------------------------------------------------------
IM buffer busy
enq: IM - contention for blr
IM CU busy
latch: IM area scb latch
latch: IM area sb latch
latch: IM seg hdr latch
latch: IM emb latch
enq: SY - IM populate by other session
IM populate completion
9 rows selected
与IM相关的统计信息：
SQL> select name from v$statname where name like 'IM %' order by 1;
NAME
----------------------------------------------------------------
IM fetches by rowid from IMCU
IM fetches by rowid from disk
IM fetches by rowid from fetch list
IM fetches by rowid from journal
IM fetches by rowid row invalid in IMCU
IM populate (faststart) CUs accumulated write time (ms)
IM populate (faststart) CUs bytes read
要了解In Memory区数据的访问方式，首先来看看In Memory区数据的组织结构。当enable某个表的inmemory属性后，该表会在首次访问或者数据库启动后加载到In Memory区(取决于priority属性的设置)。数据在IM区使用压缩列式存储，存放在每个IMCU中。在IMCU内部，Oracle为每个IMCU维护着一个对应的In Memory Storage Index, 用于记录IMCU单元列中的最大值，最小值等信息。对于每个IMCU，还会有相应的metadata dictionary信息，存储在metadata块中，也就是IM中的64K pool中，块的大小固定为64K。metadata dictionary记录对象信息，列信息等。
In Memory Storage Index的作用
In Memory Storage Index通过data pruning的机制来帮助提高查询性能：由于In Memory Storage Index中记录了该列的最小值，最大值信息，当查询语句的where条件中指定了某一列的值或范围时，则根据该索引的信息即可预判哪些IMCU需要继续访问，哪些可以直接跳过。
以下测试用来帮助理解In Memory Storage Index的使用：
SQL> create table test_im_access tablespace users as select rownum id,systimestamp time from dual connect by level <=10000000;SQL> alter table test_im_access inmemory MEMCOMPRESS FOR QUERY LOW;SQL> select count(*) from test_im_access;SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments where segment_name='TEST_IM_ACCESS';SEGMENT_NAME INMEMORY_SIZE BYTES POPULATE_------------------------------ ------------- ---------- ---------TEST_IM_ACCESS 149028864 285212672 COMPLETED视图V_$IM_HEADER记录了每个IMCU的内存地址，分配大小等信息：
SQL> select IMCU_ADDR,ALLOCATED_LEN,USED_LEN from V_$IM_HEADER where OBJD=(select object_id from dba_objects where object_name='TEST_IM_ACCESS') order by IMCU_ADDR;IMCU_ADDR ALLOCATED_LEN USED_LEN---------------- ------------- ----------00000003D81FFF88 7340032 119985200000003D88FFF88 7340032 679752800000003DBFFFFA0 7340032 663215700000003DC6FFFA0 8388608 799729600000003DCEFFFA0 8388608 799729600000003DD6FFFA0 8388608 799603000000003DDEFFFA0 8388608 799730400000003DE6FFFA0 8388608 799729600000003DEEFFFA0 8388608 799737600000003DF6FFFA0 8388608 799732000000003EC000000 8388608 803547500000003EC800000 8388608 771100800000003ED000000 8388608 787612000000003ED800000 8388608 788748100000003EE000000 8388608 799729600000003EE800000 8388608 799730400000003EF000000 8388608 799735200000003EF800000 8388608 799730418 rows selected.视图V_$IM_COL_CU记录了每个IMCU的记录条数，最小值，最大值等信息。（将V_$IM_HEADER和V_$IM_COL_CU视图中的结果比对可发现似乎有一个IMCU分配了空间，但并没有载入数据，原因未知，测试了一些其他的压缩方式，没有重现该现象，这里没有深究。）
SQL> select HEAD_PIECE_ADDRESS IMCU_ADDR,COLUMN_NUMBER,DICTIONARY_ENTRIES,UTL_RAW.CAST_TO_NUMBER(MINIMUM_VALUE) MINIMUM_VALUE,UTL_RAW.CAST_TO_NUMBER(MAXIMUM_VALUE) MAXIMUM_VALUEfrom V_$IM_COL_CUwhere OBJD=(select object_id from dba_objects where object_name='TEST_IM_ACCESS') and COLUMN_NUMBER=1 order by 1;IMCU_ADDR COLUMN_NUMBER DICTIONARY_ENTRIES MINIMUM_VALUE MAXIMUM_VALUE---------------- ------------- ------------------ -------------- --------------00000003D81FFF88 1 591600 4736151 562355000000003DBFFFFA0 1 495190 9468851 1000000000000003DC6FFFA0 1 591600 8877251 976465000000003DCEFFFA0 1 591600 8285651 917305000000003DD6FFFA0 1 591500 7694151 858145000000003DDEFFFA0 1 591600 7102551 798995000000003DE6FFFA0 1 591600 6510951 739835000000003DEEFFFA0 1 591600 5919351 680675000000003DF6FFFA0 1 591600 5327751 621515000000003EC000000 1 642753 1 64275300000003EC800000 1 596867 642754 123962000000003ED000000 1 582624 1239621 182224400000003ED800000 1 583466 1822245 266555000000003EE000000 1 591600 2369751 325715000000003EE800000 1 591600 2961351 384875000000003EF000000 1 591600 3552951 444035000000003EF800000 1 591600 4144551 503195017 rows selected.接下来，针对已载入IM区的test_im_access表作id=3的条件查询，并记录相关统计信息：
select name,value from v$mystat, v$statnamewhere v$mystat.statistic# = v$statname.statistic#and v$statname.name in('CPU used by this session','IM scan rows','IM scan rows valid','IM scan CUs memcompress for query low','IM scan CUs pruned');NAME VALUE---------------------------------------------------------------- ----------CPU used by this session 2IM scan CUs memcompress for query low 0IM scan rows 0IM scan rows valid 0IM scan CUs pruned 0select * from test_im_access where id=3;--上述语句的执行计划如下：---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 387 | 13545 | 438 (11)| 00:00:01 ||* 1 | TABLE ACCESS INMEMORY FULL| TEST_IM_ACCESS | 387 | 13545 | 438 (11)| 00:00:01 |-----------------------------------------------------------------------------------------------check stats again:NAME VALUE---------------------------------------------------------------- ----------CPU used by this session 12IM scan CUs memcompress for query low 17IM scan rows 10000000IM scan rows valid 642753IM scan CUs pruned 16统计结果如下：
|
Stat name |
Value |
|
CPU used by this session |
10 |
|
IM scan CUs memcompress for query low |
17 |
|
IM scan rows |
10000000 |
|
IM scan rows valid |
642753 |
|
IM scan CUs pruned |
16 |
从统计结果可以看到，虽然test_im_access表采用’memcompress for query low’压缩列存储后占用的内存IMCU个数为17，但这里实际发生了完全扫描的IMCU个数只有一个（IM scan CUs memcompress for query low - IM scan CUs pruned），其中的16个块都发生了pruning。也就是只要访问这16个IMCU中的In Memory Storage Index, 即可确定我们要查询的记录不在这些IMCU中，从而跳过他们，而不用完全扫描整个IMCU。此外，该表有1千万条记录，由于IMCU data pruning的作用，我们实际只需要扫描其中一个IMCU的642753条记录。从该例也可以看出IM scan rows统计的并不是真正访问了的行数，它更像是一个估计的行数。
针对in-memory storage indexes，oracle提供了INMEMORY_PRUNING和NO_INMEMORY_PRUNING两个hint来控制这种索引的使用。继续上面的例子:
select /*+ NO_INMEMORY_PRUNING */* from test_im_access where id=3;
|
Stat name |
Value |
|
CPU used by this session |
40 |
|
IM scan CUs memcompress for query low |
17 |
|
IM scan rows |
10000000 |
|
IM scan rows valid |
10000000 |
|
IM scan CUs pruned |
0 |
可以看到，在使用NO_INMEMORY_PRUNING hint来禁用in-memory storage indexes的使用后，发生了pruning的CU个数为0，同样的查询需要访问IM中所有的17个IMCU,全部1千万条数据，CPU time也增加到了40。
实际上，Storage Index并不是12C In Memory Option中才有的新技术，了解过Exadata的应该知道这是在Exadata中就已经引入的技术了。Exadata的存储索引中除了标识最大值和最小值之外，还有一个标识用来表示在该存储单元中是否包含空值(null)，从而使得寻找空值的查询效率更高。那么In Memory Storage Index是否也标识了空值呢？从已公开的资料我没有查询到这一点，还是用测试来验证一下：
insert into test_im_access select null,systimestamp time from dual connect by level <=10000;
commit;
等待片刻，待oracle将新的数据载入IM区后，执行以下语句并记录统计结果：
select count(*) from test_im_access where id is null;
|
Stat name |
Value |
|
CPU used by this session |
7 |
|
IM scan CUs memcompress for query low |
17 |
|
IM scan rows |
10010000 |
|
IM scan rows valid |
1096690 |
|
IM scan CUs pruned |
15 |
结果表明，在我们作空值查询时，In Memory Storage Index仍旧发挥了作用，其中的15个IMCU发生了data pruning,说明了In Memory Storage Index同样有对空值的标识。
关于data pruning, Oracle In Memory白皮书中还提到了通过metadata dictionary可以实现另一个级别的data pruning。但在测试中并没有观察到该技术的使用。
SIMD Vector Processing
从前面的测试统计信息来看，无论是否使用了data pruning技术，从IM区访问数据所占用的CPU time都是很低的。这是如何实现的呢？
Oracle在IM option中引入了SIMD(Single Instruction processing Multiple Data values)向量处理技术用于提高CPU效率，简单来说就是通过单条CPU指令作批量数据比对。Oracle给出了下图用于简单说明该技术的原理，即每次load一批数据到CPU上的SIMD寄存器(register)上,通过单条CPU指令来比较整批数据，将匹配的结果记录后，再接着比较下一批数据…
In Memory Join操作
按Oracle的说法，IM区数据列式存储比较适用于用bloom filter方法来提升连接操作的效率。继续用测试来观察这一点:
创建测试表：
create table small_table tablespace users as select rownum id,systimestamp time from dual connect by level <=1000000;
create table big_table (id number, TIME TIMESTAMP) tablespace users;
Begin
for i in 1..50 loop
insert into big_table select rownum+1000000*(i-1) id,systimestamp time from dual connect by level <=1000000;
commit;
end loop;
end;
/
BEGIN
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname => 'BIG_TABLE',
degree=>8,
method_opt => 'for all columns size auto',
cascade => TRUE);
END;
/
BEGIN
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname => 'SMALL_TABLE',
degree=>8,
method_opt => 'for all columns size auto',
cascade => TRUE);
END;
/
将测试表载入IM区和keep buffer cache中，用于比较访问IM区和buffer cache时连接查询的不同：
alter table big_table inmemory;
alter table small_table inmemory;
alter table big_table storage (buffer_pool keep);
alter table small_table storage (buffer_pool keep);
select count(*) from big_table;
select count(*) from small_table;
SQL> select SEGMENT_NAME,INMEMORY_SIZE,BYTES,POPULATE_STATUS from v$im_segments;
SEGMENT_NAME INMEMORY_SIZE BYTES POPULATE_
---------------------------------------- ------------- ---------- ---------
BIG_TABLE 792788992 1342177280 COMPLETED
SMALL_TABLE 13828096 28311552 COMPLETED
--数据已加载到IM中
SQL> select b.object_name,sum(NUM_BUF) from X$KCBOQH a, dba_objects b where a.OBJ#=b.object_id and b.object_name in ('BIG_TABLE','SMALL_TABLE') group by b.object_name;
OBJECT_NAME SUM(NUM_BUF)
------------------------------ ------------
SMALL_TABLE 3322
BIG_TABLE 158863
--数据已加载到cache中
对测试表作连接查询，观察执行时间，执行计划和consistent gets的变化：
--这里取第二次执行时作观察，因为第一次执行会有一些硬解析的成本
SQL> set autotrace on
SQL> select count(*)
from big_table a,small_table b
where a.id=b.id and to_char(b.time,'yyyymmdd')='20140912';
COUNT(*)
----------
1000000
Elapsed: 00:00:09.35
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2632 (35)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | HASH JOIN | | 10012 | 234K| 2632 (35)| 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 10000 | 175K| 114 (54)| 00:00:01 |
|* 4 | TABLE ACCESS INMEMORY FULL| SMALL_TABLE | 10000 | 175K| 114 (54)| 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 50M| 286M| 2115 (21)| 00:00:01 |
|* 6 | TABLE ACCESS INMEMORY FULL| BIG_TABLE | 50M| 286M| 2115 (21)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
4 - inmemory(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
filter(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
6 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"A"."ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"."ID"))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
542 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
这里，测试语句的执行时间为9.35秒，consistent gets的个数为17，从执行计划可看到启用了bloom filter计算方法.
若是禁用bloom filter会有什么变化呢？
禁用bloom filter:
alter session set "_bloom_filter_enabled"=FALSE;
SQL> select count(*)
from big_table a,small_table b
where a.id=b.id and to_char(b.time,'yyyymmdd')='20140912';
COUNT(*)
----------
1000000
Elapsed: 00:00:45.18
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2632 (35)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | HASH JOIN | | 10012 | 234K| 2632 (35)| 00:00:01 |
|* 3 | TABLE ACCESS INMEMORY FULL| SMALL_TABLE | 10000 | 175K| 114 (54)| 00:00:01 |
| 4 | TABLE ACCESS INMEMORY FULL| BIG_TABLE | 50M| 286M| 2115 (21)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
3 - inmemory(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
filter(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
542 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
禁用bloom filter后，虽然仍是访问IM区，consistent gets的个数仍为17，但语句的执行时间已增长为45.18秒.
如果使用hint来禁用IM访问，强制连接在传统的buffer cache中执行又会是什么结果呢：
SQL> select /*+ NO_INMEMORY */ count(*)
from big_table a,small_table b
where a.id=b.id and to_char(b.time,'yyyymmdd')='20140912';
COUNT(*)
----------
1000000
Elapsed: 00:00:52.11
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | | 90645 (2)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 24 | | | |
|* 2 | HASH JOIN | | 977K| 22M| 27M| 90645 (2)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| SMALL_TABLE | 976K| 16M| | 978 (7)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIG_TABLE | 50M| 286M| | 44899 (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ID"="B"."ID")
3 - filter(TO_CHAR(INTERNAL_FUNCTION("B"."TIME"),'yyyymmdd')='20140912')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
162214 consistent gets
0 physical reads
0 redo size
542 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
虽然都是在内存执行的，但这里的consistent gets急剧增长为162214，语句执行时间也增加到了52.11秒.
以上测试结果总结如下表:
|
Join Method |
Consistent Gets |
Elapsed Time |
|
1.Join In IM, with BF enabled |
17 |
9.35 |
|
2.Join In IM, with BF disabled |
17 |
45.18 |
|
2.Join In Buffer Cache |
162214 |
52.11 |
从测试结果可看到，Oracle IM option通过将数据作列式压缩存储在内存中，并启用bloom filter后，连接查询的效率得到了极大的提升。
关于Bloom Filter
Bloom filter 是由 Howard Bloom 在 1970 年提出的一种计算方法，用于检测一个元素是不是集合中的一个成员。Oracle从10g开始引入该算法。其基本思想就是用一个或多个hash函数对数据集A中的每个成员做hash计算，计算结果映射到一个位向量（bit vector）中。位向量所有位初始值都为0，根据hash结果将位向量中相应位置1。对集合A中的所有成员的hash计算完成后，就得到了该数据集的位向量。当需要判断集合B中的元素是否属于该数据集时，也用相同的hash函数对其映射得到它的位向量，然后将其位向量上所有为1的位与数据集位向量上相应位比较，如果发现数据集的位向量上某个位为0的话，可以判断这个元素不属于该数据集，从而将这些元素排除出去。而如果所有相应位都为1的话，那么该元素可能属于这个数据集A，也可能不属于。也就是说Bloom Filter计算后的结果还不是最终的结果，它能帮助快速排除那些不符合条件的记录，接下来还需要使用其他连接方法来保证最终结果的正确性。
以前面测试结果中的执行计划为例，它首先访问表small_table,建立bloom filter :BF0000,接下来使用该filter对big_table作过滤操作，由于Bloom Filter算法自身的限制，最终还需要使用hash join来保证执行结果的正确性。
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2632 (35)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
|* 2 | HASH JOIN | | 10012 | 234K| 2632 (35)| 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 10000 | 175K| 114 (54)| 00:00:01 |
|* 4 | TABLE ACCESS INMEMORY FULL| SMALL_TABLE | 10000 | 175K| 114 (54)| 00:00:01 |
| 5 | JOIN FILTER USE | :BF0000 | 50M| 286M| 2115 (21)| 00:00:01 |
|* 6 | TABLE ACCESS INMEMORY FULL| BIG_TABLE | 50M| 286M| 2115 (21)| 00:00:01 |
---------------------------------------------------------------------------------------------
In Memory 聚合(aggregation)查询：Vector Group By
Oracle在12.1.0.2版本中引入了一种新的查询转换技术，称为Vector Group By, 据称这种算法可以更高效的使用CPU资源。这种转换多应用于数据仓库类型应用的分析查询中，这里就以数据仓库应用中的查询场景来介绍这种查询转换技术的实现：
这种查询转换可分为两个阶段：
Phase 1
1． 扫描“维度表”（dimension tables），也就是小表，根据扫描结果在连接列上建立key vectors，也就是一个一维阵列；
2． 根据key vectors的结果，以及原有的维度表扫描结果，创建一个新的称为IM Accumulator的数据结构。IM Accumulator是一个多维阵列，存放在PGA中。其作用是使得在扫描“事实表”(fact table)期间就可以作聚合或group by计算，而不用等到所有的结果都返回以后；
3． 将“维度表”中涉及到的select列的结果存放在一个临时表中。
Phase 2
4． 扫描“事实表”和前面生成的key vectors，利用key vectors来过滤“事实表”中的记录，将匹配连接条件的结果放到前面的IM Accumulator中。如果该值已存在，则其对应的统计值就会被更新；
5． 最后，对“事实表”的扫描结果会与第3步中的临时表作join，生成最终的结果。
既然说这种算法能够更有效地使用CPU资源，这里就通过测试观察数据库中的CPU used by this session统计指标的变化来验证这一说法。
Oracle在Sample Schema示例模式中有类似于数据仓库应用的测试数据。这里的测试思路如下：
在SH.SALES等表上作如下查询，该查询用以检索出从1999年12月至2000年2月间Florida州所有城市直销形式的每月销售额。
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = 'FL' AND ch.channel_desc = 'Direct Sales' AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
GROUP BY c.cust_city, t.calendar_quarter_desc;
测试的场景分别为：
1. 常规执行；
2. 使用hint /*+ VECTOR_TRANSFORM */来启用Vector Group By转换；
3. 将查询表载入In Memory区，并用hint /*+ VECTOR_TRANSFORM */启用Vector Group By转换
他们的执行计划分别为：
1．
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 607 | 490 (2)|
| 1 | HASH GROUP BY | | 607 | 490 (2)|
| 2 | NESTED LOOPS | | 812 | 489 (2)|
| 3 | NESTED LOOPS | | 14975 | 489 (2)|
| 4 | VIEW | VW_GBC_13 | 14975 | 487 (1)|
| 5 | HASH GROUP BY | | 14975 | 487 (1)|
| 6 | NESTED LOOPS | | 43094 | 484 (1)|
| 7 | NESTED LOOPS | | 43094 | 484 (1)|
| 8 | MERGE JOIN CARTESIAN | | 274 | 21 (0)|
|* 9 | TABLE ACCESS FULL | CHANNELS | 1 | 3 (0)|
| 10 | BUFFER SORT | | 274 | 18 (0)|
|* 11 | TABLE ACCESS FULL | TIMES | 274 | 18 (0)|
| 12 | PARTITION RANGE ITERATOR | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | | |
| 14 | BITMAP AND | | | |
|* 15 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | | |
|* 16 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | | |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 157 | 484 (1)|
|* 18 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 0 (0)|
|* 19 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 0 (0)|
-----------------------------------------------------------------------------------------
2．
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6130 | 972 (4)|
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6677_381357 | | |
| 3 | VECTOR GROUP BY | | 20 | 19 (6)|
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | |
|* 5 | TABLE ACCESS FULL | TIMES | 274 | 18 (0)|
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6678_381357 | | |
| 7 | VECTOR GROUP BY | | 613 | 426 (1)|
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 2734 | 425 (1)|
| 10 | LOAD AS SELECT | SYS_TEMP_0FD9D6679_381357 | | |
| 11 | VECTOR GROUP BY | | 1 | 4 (25)|
| 12 | HASH GROUP BY | | 1 | 4 (25)|
| 13 | KEY VECTOR CREATE BUFFERED | :KV0002 | | |
|* 14 | TABLE ACCESS FULL | CHANNELS | 1 | 3 (0)|
| 15 | HASH GROUP BY | | 6130 | 523 (5)|
|* 16 | HASH JOIN | | 6130 | 522 (5)|
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6678_381357 | 613 | 3 (0)|
|* 18 | HASH JOIN | | 6130 | 518 (5)|
| 19 | MERGE JOIN CARTESIAN | | 20 | 4 (0)|
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6679_381357 | 1 | 2 (0)|
| 21 | BUFFER SORT | | 20 | 2 (0)|
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6677_381357 | 20 | 2 (0)|
| 23 | VIEW | VW_VT_0737CF93 | 6130 | 514 (5)|
| 24 | VECTOR GROUP BY | | 6130 | 514 (5)|
| 25 | HASH GROUP BY | | 6130 | 514 (5)|
| 26 | KEY VECTOR USE | :KV0001 | 16697 | 514 (5)|
| 27 | KEY VECTOR USE | :KV0002 | 43110 | 514 (5)|
| 28 | KEY VECTOR USE | :KV0000 | 172K| 514 (5)|
| 29 | PARTITION RANGE SUBQUERY| | 918K| 513 (5)|
|* 30 | TABLE ACCESS FULL | SALES | 918K| 513 (5)|
--------------------------------------------------------------------------------------------
3．
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6130 | 313 (14)|
| 1 | TEMP TABLE TRANSFORMATION | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D667A_381357 | | |
| 3 | VECTOR GROUP BY | | 20 | 5 (20)|
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | | |
|* 5 | TABLE ACCESS INMEMORY FULL | TIMES | 274 | 4 (0)|
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D667B_381357 | | |
| 7 | VECTOR GROUP BY | | 613 | 30 (10)|
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | | |
|* 9 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 2734 | 29 (7)|
| 10 | LOAD AS SELECT | SYS_TEMP_0FD9D667C_381357 | | |
| 11 | VECTOR GROUP BY | | 1 | 2 (50)|
| 12 | HASH GROUP BY | | 1 | 2 (50)|
| 13 | KEY VECTOR CREATE BUFFERED | :KV0002 | | |
|* 14 | TABLE ACCESS INMEMORY FULL | CHANNELS | 1 | 1 (0)|
| 15 | HASH GROUP BY | | 6130 | 277 (13)|
|* 16 | HASH JOIN | | 6130 | 275 (13)|
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667B_381357 | 613 | 3 (0)|
|* 18 | HASH JOIN | | 6130 | 272 (13)|
| 19 | MERGE JOIN CARTESIAN | | 20 | 4 (0)|
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667C_381357 | 1 | 2 (0)|
| 21 | BUFFER SORT | | 20 | 2 (0)|
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D667A_381357 | 20 | 2 (0)|
| 23 | VIEW | VW_VT_0737CF93 | 6130 | 268 (13)|
| 24 | VECTOR GROUP BY | | 6130 | 268 (13)|
| 25 | HASH GROUP BY | | 6130 | 268 (13)|
| 26 | KEY VECTOR USE | :KV0001 | 16697 | 268 (13)|
| 27 | KEY VECTOR USE | :KV0002 | 43110 | 268 (13)|
| 28 | KEY VECTOR USE | :KV0000 | 172K| 268 (13)|
| 29 | PARTITION RANGE SUBQUERY | | 918K| 267 (13)|
|* 30 | TABLE ACCESS INMEMORY FULL| SALES | 918K| 267 (13)|
-----------------------------------------------------------------------------------------------
以上执行计划中，蓝色部分即是前面描述的Vector Group By转换中的Phase 1，红色部分即为Phase 2.
测试结果如下表，可以看到，将数据载入IM区并启用Vector Group By转换后，CPU资源使用量急剧下降，可见这种Vector Group By转换查询尤其适用于IM列式存储的数据。统计结果中还有一个有趣的发现，那就是启用Vector Group By后，DB Block Gets和Physical Reads统计指标都是非0值，跟踪发现Physical Reads是来自于对临时文件的读取，因为Phase 1的结果是要写到临时文件中的。而DB Block Gets则猜测是来自于对Key Vector的current mode读取。
|
测试场景 |
CPU Time |
Consistent Gets |
DB Block Gets |
Physical Reads |
|
常规执行 |
118 |
11884 |
0 |
0 |
|
启用Vector Group By转换 |
38 |
1859 |
24 |
3 |
|
In Memory，并启用Vector Group By转换 |
9 |
31 |
24 |
3 |