Oracle 在12c版本中引入了内存与列式压缩选件In-Memory,In-Memory可以极大提升查询的性能。
  这里通过一个对比可以看到同样的查询中能提高多少性能。
  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;
Table altered.
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          NONE                     AUTO                                          FOR QUERY LOW