?这里通过一个对比可以看到同样的查询中能提高多少性能。

1.先给In-Memory分配内存
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

?2.INMEMORY_SIZE定义了in-memory的大小
SQL> alter system set inmemory_size=1000m scope=spfile;
?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> shutdown immediate
?Database closed.
?Database dismounted.
?ORACLE instance shut down.

?SQL> startup
?ORACLE instance started.

?Total System Global Area 5016387584 bytes
?Fixed Size? ? ? ? ? ? ? ? ? 3721128 bytes
?Variable Size? ? ? ? ? ? 1056966744 bytes
?Database Buffers? ? ? ? 3724541952 bytes
?Redo Buffers? ? ? ? ? ? ? 13053952 bytes
?In-Memory Area? ? ? ? ? ? 218103808 bytes
?Database mounted.
?Database opened.

?

?3.建立一张测试表
SQL> create table bmw.t as select * from dba_objects;

?Table created.

?SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from dba_tables where table_name='T';

?TABLE_NAME INMEMORY_PRIORITY? ? ? ? INMEMORY_DISTRIBUTE? ? ? ? ? ? ? ? ? ? ? ? ? INMEMORY_COMPRESSION
?---------- ------------------------ --------------------------------------------- ---------------------------------------------------
?T

可以看到并未真正的分配USED_BYTES
?SQL> l
? 1* SELECT * FROM V$INMEMORY_AREA
?SQL> /

?POOL? ? ? ? ? ? ? ? ALLOC_BYTES USED_BYTES POPULATE_STATUS? ? ? ? ? CON_ID
?-------------------- ----------- ---------- -------------------- ----------
?1MB POOL? ? ? ? ? ? ? 837812224? ? ? ? ? 0 DONE? ? ? ? ? ? ? ? ? ? ? ? ? 3
?64KB POOL? ? ? ? ? ? ? 201326592? ? ? ? ? 0 DONE? ? ? ? ? ? ? ? ? ? ? ? ? 3

?

?4.看一下未使用In-Memory时的查询
SQL> set autot trace
?SQL> SELECT * FROM bmw.t;
?90927 rows selected.
?Execution Plan
?----------------------------------------------------------
?Plan hash value: 1601196873

?--------------------------------------------------------------------------
?| Id? | Operation? ? ? ? | Name | Rows? | Bytes | Cost (%CPU)| Time? ? |
?--------------------------------------------------------------------------
?|? 0 | SELECT STATEMENT? |? ? ? | 90927 |? ? 9M|? 416? (1)| 00:00:01 |
?|? 1 |? TABLE ACCESS FULL| T? ? | 90927 |? ? 9M|? 416? (1)| 00:00:01 |
?--------------------------------------------------------------------------
?Statistics
?----------------------------------------------------------
? ? ? ? ? 5? recursive calls
? ? ? ? ? 0? db block gets
? ? ? ? 7487? consistent gets
? ? ? ? 1525? physical reads
? ? ? ? ? 0? redo size
? ? 12128303? bytes sent via SQL*Net to client
? ? ? 67223? bytes received via SQL*Net from client
? ? ? ? 6063? SQL*Net roundtrips to/from client
? ? ? ? ? 0? sorts (memory)
? ? ? ? ? 0? sorts (disk)
? ? ? 90927? rows processed

执行计划中也可以看到使用TABLE ACCESS FULL扫描，consistent gets也达到了7487。

5.将表放到In-Memory中
SQL> set autot off
?SQL> alter table bmw.t inmemory;