oracle12c R2 in memory_oracle in memory dml-CSDN博客

本文链接：https://blog.csdn.net/xqzhang8/article/details/73302555

查看参数设置

SQL> show parameter inmemory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled                   boolean     TRUE
inmemory_clause_default                string
inmemory_expressions_usage         string      ENABLE
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
inmemory_virtual_columns             string      MANUAL
optimizer_inmemory_aware             boolean     TRUE

参数inmemory_size,指定可以作为内存列存的内存

SQL> alter system set inmemory_size=800M cope=spfile;

inmemory_max_populate_servers 用于将数据加载到内存的后台进程数量

SQL> alter system set inmemory_max_populate_servers=2;

SQL> show sga

Total System Global Area 1543503872 bytes
Fixed Size                  8793112 bytes
Variable Size             570426344 bytes
Database Buffers          117440512 bytes
Redo Buffers                7983104 bytes
In-Memory Area            838860800 bytes

验证：

SQL> select * from zxq;

72633 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3272699641

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 72633 |  9433K|   396   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| ZXQ  | 72633 |  9433K|   396   (1)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          5  db block gets
       6176  consistent gets
          0  physical reads
          0  redo size
   11079730  bytes sent via SQL*Net to client
      53869  bytes received via SQL*Net from client
       4844  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72633  rows processed

内存话处理：

SQL> alter table zxq inmemory;

Table altered.

SQL>  select * from zxq；

72633 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3272699641

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      | 72633 |  9433K|    21  (29)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| ZXQ  | 72633 |  9433K|    21  (29)| 00:00:01 |
-----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
    4836448  bytes sent via SQL*Net to client
      53869  bytes received via SQL*Net from client
       4844  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72633  rows processed

查看内存区域的使用情况

SQL> select * from V$INMEMORY_AREA;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                     670040064    4194304 DONE                                5
64KB POOL                    150994944     262144 DONE                                5