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
6.没有对表查询前还是未真正放到In-Memory内存中
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------- ----------- ---------- -------------------- ----------
1MB POOL 837812224 0 DONE 3
64KB POOL 201326592 0 DONE 3
SQL> select count(*) from bmw.t;
COUNT(*)
----------
90927
7.查询一次后可以看到USED_BYTES已经分配了内存
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------- ----------- ---------- -------------------- ----------
1MB POOL 837812224 4194304 DONE 3
64KB POOL 201326592 131072 DONE 3
8.查看一下执行计划
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| 20 (20)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| T | 90927 | 9M| 20 (20)| 00:00:01 |
-----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
4944325 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 INMEMORY FULL扫描，而且consistent gets为5.可以看到INMEMORY会给查询带来非常高的性能提升。
Oracle 12c In-Memory 初探
原文：http://blog.itpub.net/26390465/viewspace-1817305/
