oracle 12c新功能之inmemory option

oracle 12c inmemory功能,不仅仅是将数据放入内存,而且采用了列存储和特殊的查询方式(SIMD vector instructions),可以在短时间内快速进行大量数据的是扫描,因此对于大表的全表扫描速度得到了很大的提升;对于开发者内存数据的访问、更新完全是透明,不需要再应用程序做任何处理的,只需要在数据库中配置哪些表启用inmemory功能。

1、修改数据库参数,以启用数据库的inmemory功能
inmemory使用的是SGA的内存空间,本次测试中inmemory占用内存设置为1G,因此将SGA_MAX_SIZE、SGA_TARGET也扩大了1G

SQL> alter system set sga_max_size = 7g scope=spfile;

System altered.

SQL> alter system set sga_target = 5g scope=spfile;

System altered.

SQL> alter system set inmemory_size=1g scope=spfile;

System altered.

2、重启数据库

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

SQL> startup
ORACLE instance started.

Total System Global Area 7516192768 bytes
Fixed Size                  6087600 bytes
Variable Size            3774876752 bytes
Database Buffers         2650800128 bytes
Redo Buffers               10686464 bytes
In-Memory Area           1073741824 bytes
Database mounted.
Database opened.

3、查看inmemory的参数是否生效

SQL> show parameter inmemory

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

4、测试表的行数和占用空间(表记录340万,占用2.3G空间)

CREATE TABLE mara AS SELECT * FROM mara@dl_ods_dev;

SQL> SELECT COUNT(*) FROM mara;
  COUNT(*)
----------
   3419561
SQL> select bytes/1024/1024 as size_mb from user_segments where segment_name = 'MARA';

   SIZE_MB
----------
      2304

5、测试从磁盘读取和内存读取的逻辑读差异
5.1 未使用inmemory选项的时候,全表扫描的逻辑读为292417

SQL> set autotrace traceonly;

SQL> select count(*) from mara;
Execution Plan
----------------------------------------------------------
Plan hash value: 781138304
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 79487   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MARA |  3419K| 79487   (1)| 00:00:04 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     292417  consistent gets
     292410  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

5.2 修改表定义,启用inmemory功能

SQL> alter table mara inmemory priority high;
Table altered.

5.3 查看表的inmemory特性的状态,(正在向内存中同步数据,状态为STARTED)

SQL> col owner format a30
SQL> col segment_name format a30
SQL> select owner, segment_name, populate_status status from v$im_segments;

OWNER                          SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ---------
ADAM                           MARA                           STARTED

5.4 内存同步完成后,重新查看表inmemory特性的状态

SQL> select owner, segment_name, t.inmemory_size/1024/1024 memory_size, t.bytes/1024/1024 table_size, populate_status status from v$im_segments t;

OWNER           SEGMENT_NAME    MEMORY_SIZE TABLE_SIZE STATUS
--------------- --------------- ----------- ---------- ---------
ADAM            MARA               183.6875       2304 COMPLETED

5.5 重新运行查询语句,记录逻辑读(23)

SQL> select count(*) from mara;
Execution Plan
----------------------------------------------------------
Plan hash value: 781138304
----------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |  3253   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| MARA |  3419K|  3253   (4)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

6. 总结
原表记录数3419561,占用空间2304M,全表扫描一次逻辑读次数292417,花费时间1.83秒
使用inmemory特性后,占用空间184M,占原表大小约8%,内存中全表扫描一次逻辑读次数23,是直接扫描表方式的万分之一,花费时间0.11秒
根据inmemory的特点,比较适合用于从大量数据进行筛选、过滤,返回结果较少场景,可以节省因创建大量不同查询条件索引占用的空间,提升查询效率和响应时间

参考资料:
Ask Tom:On Oracle Database In-Memory
How do i configure Oracle database 12c In-memory Option
Getting started with Oracle Database In-Memory Part I – Installing & Enabling
Getting started with Oracle Database In-Memory Part II – In-Memory Population
Getting started with Oracle Database In-Memory Part III – Querying The IM Column Store
Getting started with Oracle Database In-Memory Part IV – Joins In The IM Column Store
Getting started with Oracle Database In-Memory Part V – Controlling Access

Comments are closed.