转载

Oracle 12c In-Memory 初探

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会给查询带来非常高的性能提升。

正文到此结束

所属分类：编程技术

本文标签： db Select tab App 测试 sql Enterprise cat ACE Oracle Word tar dist client ip value
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二