Oracle 12c In-Memory option

oracle昨天放出了12.1.0.2版本，这个版本中最大的亮点就是in-memory option了，针对现在数据的膨胀，库的大小都在TB级别，使用in-memory后可以大大提高全表扫速度，增快复杂SQL的处理，增快事务处理能力，而且对应用透明，只需在数据库层面操作即可。
关于in-memory详细介绍请参考Maclean的bolg:http://www.askmaclean.com/archives/12c-in-memory-option.html

官方文档：http://docs.oracle.com/database/121/nav/portal_17.htm#oracle_in-memory_database_cache

另外，需要注意如下2点：

(1)该特性不支持SYS用户下的对象,不支持SYSTEM、SYSAUX表空间下的对象
(2)Objects that are smaller than 64KB are not populated into memory
如果要支持sys用户和sys表空间里面的对象，需要修改隐含参数_inmemory_enable_sys为TRUE
enable in-memory on system tablespace with sys user

查看数据库中的IM组件和参数：
[code][oracle@ora12c oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 23 11:26:18 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL>
INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS
---------------- ---------------------------------------------------------------- ----------------- ------------------- ------------
orasql ora12c 12.1.0.2.0 2014-07-23 11:20:07 OPEN

SQL> show sga

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 524290800 bytes
Database Buffers 306184192 bytes
Redo Buffers 5455872 bytes


SQL> SHOW PARAMETER inmemory;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE


SQL> select parameter,value from v$option where parameter like 'In-Memory%';

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
In-Memory Column Store TRUE
In-Memory Aggregation TRUE[/code]
设置inmemory_size参数，并重启数据库
[code]SQL> alter system set inmemory_size=200M scope=spfile;


System altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 612371184 bytes
Database Buffers 8388608 bytes
Redo Buffers 5455872 bytes
In-Memory Area 209715200 bytes
Database mounted.
Database opened.

SQL> show sga

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 612371184 bytes
Database Buffers 8388608 bytes
Redo Buffers 5455872 bytes
In-Memory Area 209715200 bytes


SQL> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 1
inmemory_query string ENABLE
inmemory_size big integer 200M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE[/code]
创建测试表
[code]create table steven.im_test as select * from dab_objects tablespace EXAMPLE;

insert into steven.IM_TEST select * from steven.IM_TEST ;

insert into steven.IM_TEST select * from steven.IM_TEST ;

insert into steven.IM_TEST select * from steven.IM_TEST ;
commit;

SQL> select table_name,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE from dba_tables where table_name='IM_TEST';

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
IM_TEST

SQL> SELECT * FROM V$INMEMORY_AREA;

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 166723584 0 DONE 0
64KB POOL 25165824 0 DONE 0


SQL> set autotrace traceonly
SQL> select * from steven.im_test ;

274785 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3183909076

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 274K| 30M| 1277 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| IM_TEST | 274K| 30M| 1277 (1)| 00:00:01 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
131 recursive calls
0 db block gets
22772 consistent gets
4611 physical reads
0 redo size
36635866 bytes sent via SQL*Net to client
202050 bytes received via SQL*Net from client
18320 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
274785 rows processed[/code]
将表放到IM中：
[code]SQL> alter table steven.IM_TEST inmemory;

SQL> select table_name,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE from dba_tables where table_name='IM_TEST';

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
IM_TEST ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE


SQL> select owner,segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024 from dba_segments where segment_name=upper('IM_TEST') group by owner,segment_name,segment_type,tablespace_name

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------------- ------------------------------ ------------------ ------------------------------ --------------------
STEVEN IM_TEST TABLE EXAMPLE 296[/code]
查询表中数据，装载到IM内
[code]SQL> select count(*) from steven.im_test;
SQL> SELECT * FROM V$INMEMORY_AREA;

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 166723584 0 POPULATING 0
64KB POOL 25165824 0 POPULATING 0


SQL> /

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 166723584 39845888 DONE 0
64KB POOL 25165824 393216 DONE 0[/code]
装载完后296M的数据只占用了40M的空间
[code]SQL> set autotrace traceonly
SQL> select * from steven.im_test ;

2198280 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3183909076

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 274K| 30M| 61 (23)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| IM_TEST | 274K| 30M| 61 (23)| 00:00:01 |
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
120 recursive calls
6 db block gets
152 consistent gets
0 physical reads
0 redo size
121116812 bytes sent via SQL*Net to client
1612613 bytes received via SQL*Net from client
146553 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
2198280 rows processed[/code]
再次查询，发现执行计划中出现了TABLE ACCESS INMEMORY FULL，而物理读从原来的4611降到0，逻辑读从原来的22772降低到152，千百倍的提升。