对于IN Memory Option 部分细节测试—主要当inmemory

Home

Database

Mysql Tutorial

对于IN Memory Option 部分细节测试—主要当inmemory_size不足之

Jun 07, 2016 pm 04:40 PM

i memory option main test detail part

本文对于IMDB的几个特性进行了具体测试: 1. 压缩级别和压缩率(具体也需要具体测试),本实验仅提供参考 2. 对于IM空间不足已经存在的对象和加入新对象的现象 3. 对于PRIORITY级别进行了简单测试数据库基本配置信息 SQL select * from v$version;BANNER CON_ID

本文对于IMDB的几个特性进行了具体测试:
1. 压缩级别和压缩率(具体也需要具体测试),本实验仅提供参考
2. 对于IM空间不足已经存在的对象和加入新对象的现象
3. 对于PRIORITY级别进行了简单测试
数据库基本配置信息

SQL&gt; select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0
SQL&gt; show parameter inmemory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     2
inmemory_query                       string      ENABLE
inmemory_size                        big integer 100M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

Copy after login

数据库版本12.1.0.2,inmemory_size配置为100M

准备测试环境

SQL&gt; create tablespace inmemory datafile 'D:\APP\FFCHENG\ORADATA\XFF\PDB\in_memory01.dbf' 
   2  size 100m autoextend on next 4m maxsize 10g;
表空间已创建。
SQL&gt; create user chf identified by xifenfei;
用户已创建。
SQL&gt; grant dba to chf;
授权成功。
SQL&gt; alter user chf default tablespace inmemory;
用户已更改。
SQL&gt; create table chf.t_inmemory1 as select * from dba_objects;
表已创建。

Copy after login

创建测试表空间,用户,测试表

测试压缩级别

SQL&gt; alter table chf.t_inmemory1 inmemory NO MEMCOMPRESS;
表已更改。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     NO MEMCOMPRESS
--NO MEMCOMPRESS 压缩比例非常小，基本上不压缩
SQL&gt; alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL&gt;  alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR DML
  2  ;
表已更改。
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     FOR DML
--FOR DML 压缩比例非常小，基本上不压缩和NO MEMCOMPRESS在压缩效果上类似
SQL&gt; alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL&gt; alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY LOW;
表已更改。
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       4325376 NONE     FOR QUERY LOW
-- FOR QUERY LOW为默认压缩级别,这里看压缩比例在3:1左右,具体取决于数据
SQL&gt; alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL&gt; alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY HIGH;
表已更改。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       3276800 NONE     FOR QUERY HIGH
-- FOR QUERY HIGH,这里看压缩比例在4:1左右,具体取决于数据
SQL&gt; alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL&gt; alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY LOW;
表已更改。
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       2228224 NONE     FOR CAPACITY LOW
-- FOR CAPACITY LOW,这里看压缩比例在6:1左右,具体取决于数据
SQL&gt; alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL&gt; alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY HIGH;
表已更改。
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488        131072 NONE     FOR CAPACITY HIGH
-- FOR CAPACITY HIGH,这里看压缩比例在10:1左右,具体取决于数据

Copy after login

这里可以看出来,压缩效果确实如Oracle所描述,级别越高压缩效果越好.

测试inmemory_size大小不足之时

SQL&gt; alter table chf.t_inmemory1 inmemory no MEMCOMPRESS;
表已更改。
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     NO MEMCOMPRESS
--dml插入数据,不再次查询数据,v$im_segments.inmemory_size不发生改变(这个是bug还是设计考虑？？)
SQL&gt; insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 91040 行。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      10616832 NONE     NO MEMCOMPRESS
SQL&gt; commit;
提交完成。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      10616832 NONE     NO MEMCOMPRESS
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
    182080
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      22282240 NONE     NO MEMCOMPRESS
SQL&gt; insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 182080 行。
SQL&gt; commit;
提交完成。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      22282240 NONE     NO MEMCOMPRESS
--通过10046证明,虽然v$im_segments.inmemory_size值未及时更新,但是IMDB是生效的
SQL&gt; oradebug setmypid
已处理的语句
SQL&gt; alter session set db_file_multiblocK_read_count=1;
会话已更改。
SQL&gt; oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL&gt; oradebug TRACEFILE_NAME
D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_7604.trc
SQL&gt; SELECT COUNT(object_id) FROM chf.t_inmemory1;
COUNT(OBJECT_ID)
----------------
          364156
SQL&gt; oradebug EVENT 10046 trace name context off
已处理的语句
PARSING IN CURSOR #455134016 len=44 dep=0 uid=0 oct=3 lid=0 tim=126773093621 hv=1133975269 
 ad='7ff07339500' sqlid='5909ukj1tf5r5'
SELECT COUNT(object_id) FROM chf.t_inmemory1
END OF STMT
PARSE #455134016:c=15600,e=3912,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=3154396630,tim=126773093620
WAIT #455134016: nam='Disk file operations I/O' ela= 154 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=126773093926
EXEC #455134016:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3154396630,tim=126773094005
WAIT #455134016: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773094044
FETCH #455134016:c=0,e=13751,p=0,cr=3110,cu=1,mis=0,r=1,dep=0,og=1,plh=3154396630,tim=126773107829
STAT #455134016 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3110 pr=0 pw=0 time=13751 us)'
STAT #455134016 id=2 cnt=364160 pid=1 pos=1 obj=91914 op='TABLE ACCESS INMEMORY FULL T_INMEMORY1 (cr=3110 pr=0 
  pw=0 time=5386 us cost=17 size=455200 card=91040)'
WAIT #455134016: nam='SQL*Net message from client' ela= 116 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108164
FETCH #455134016:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3154396630,tim=126773108215
WAIT #455134016: nam='SQL*Net message to client' ela= 0 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108246
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      43384832 NONE     NO MEMCOMPRESS
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
    364160
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      43384832 NONE     NO MEMCOMPRESS
SQL&gt; insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 364160 行。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      43384832 NONE     NO MEMCOMPRESS
SQL&gt; commit;
提交完成。
SQL&gt; SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
    728320
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      43384832 NONE     NO MEMCOMPRESS
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      71892992 NONE     NO MEMCOMPRESS
--这里可以看出来INMEMORY_SIZE已经使用了71892992,再插入一次数据,一共100M的IM肯定不够使用
SQL&gt; insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 728320 行。
SQL&gt; commit;
提交完成。
SQL&gt; select count(object_id) from chf.t_inmemory1;
COUNT(OBJECT_ID)
----------------
         1456624
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS
--这里现在的INMEMORY_SIZE变为了63438848小于在插入数据之前的71892992,证明IM肯定出现问题,比如已经满了,
  v$im_segments显示值不准确
--测试刷新buffer_cache对IM的影响
SQL&gt; alter system flush buffer_cache;
系统已更改。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS
--结果证明无影响
autotrace结果
SQL&gt; set autot trace exp stat
SQL&gt; set lines 120
SQL&gt; pages 1000
SQL&gt; set pages 1000
SQL&gt; select count(*) from chf.t_inmemory1;
执行计划
----------------------------------------------------------
Plan hash value: 3154396630
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY1 | 91040 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      16693  consistent gets
      16690  physical reads
          0  redo size
        546  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
10046结果
SQL ID: 1b61dgunxftdx Plan Hash: 3154396630
select count(object_id) 
from
 chf.t_inmemory1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.26       4.14      16689      22446          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.26       4.14      16689      22448          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=22446 pr=16689 pw=0 time=4144536 us)
   1456640    1456640    1456640   TABLE ACCESS INMEMORY FULL T_INMEMORY1 (cr=22446 pr=16689 pw=0 
                                         time=2560999 us cost=17 size=455200 card=91040)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                     16689        0.03          3.05
  SQL*Net message from client                     2        5.40          5.40
--autotrace和10046都证明,当IM size不足之时,数据库未能够使用IM的特性,哪怕是部分也不能使用
--创建新对象存放IM中
SQL&gt; create table chf.t_inmemory2 as select * from dba_objects;
表已创建。
SQL&gt; alter table chf.t_inmemory2 inmemory;
表已更改。
SQL&gt; select count(*) from chf.t_inmemory2;
  COUNT(*)
----------
     91041
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS
SQL&gt; select count(*) from chf.t_inmemory2;
执行计划
----------------------------------------------------------
Plan hash value: 2042227318
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1532  consistent gets
       1530  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
SQL&gt; alter table chf.t_inmemory1 no inmemory;
表已更改。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
   2  from v$im_segments where segment_name = 'T_INMEMORY2';
未选定行
SQL&gt; set autot traceonly exp stat
SQL&gt; select count(*) from chf.t_inmemory2;
执行计划
----------------------------------------------------------
Plan hash value: 2042227318
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
         57  recursive calls
          0  db block gets
       1565  consistent gets
       1532  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
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL&gt; set autot off
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
   2  from v$im_segments where segment_name = 'T_INMEMORY2';
SEGMENT_NAME
-----------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY2
  13631488       4325376 NONE     FOR QUERY LOW
SQL&gt; set autot traceonly exp stat
SQL&gt; select count(*) from chf.t_inmemory2;
执行计划
----------------------------------------------------------
Plan hash value: 2042227318
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    53   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    53   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--当IM已经无空闲空间之时,创建新对象在PRIORITY未提升之前,即便是设置了IM和对对象进行了访问,也无法存入IM

Copy after login

上述测试几个结论:
1. 随着IM中对象的增加，当INMEMORY_SIZE不足之时,v$im_segments.INMEMORY_SIZE显示不准确
2. 随着IM中对象的增加，当INMEMORY_SIZE不足之时,当IM中的对象不能全部在IM中之时,对其对象操作,会转换成传统数据库操作,
不会使用部分的IM特性,但是执行计划依然提示使用INMEMORY
3. flush buffer_cache 不影响对象的IM
4. 当IM已经无空闲空间之时,创建新对象在PRIORITY未提升之前,即便是设置了IM和对对象进行了访问,也无法存入IM,
访问依然是传统方式,但是执行计划是INMEMORY

测试PRIORITY

 
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      57999360 NONE     NO MEMCOMPRESS
SQL&gt; alter table chf.t_inmemory2  inmemory;
表已更改。
SQL&gt; select count(*) from chf.t_inmemory2;
  COUNT(*)
----------
     91041
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      57999360 NONE     NO MEMCOMPRESS
SQL&gt; alter table chf.t_inmemory1  inmemory no memcompress PRIORITY LOW;
表已更改。
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
未选定行
SQL&gt; select count(*) from chf.t_inmemory2;
  COUNT(*)
----------
     91041
SQL&gt;  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY2
  13631488       4325376 NONE     FOR QUERY LOW

Copy after login

这里测试证明：
1. 指定PRIORITY不为none也需要访问对象后才能够放入IM中
2. 当IM不足时,PRIORITY级别高的会把级别低的对象刷出IM

特此声明:本文仅出自个人测试,得出结论,不可作为任何官方依据使用,具体环境需要具体测试

ORACLE 12C In-Memory功能性能测试
ORACLE 12C In-Memory组件初试
如何估算表In-Memory需要内存大小
In-Memory整体汇总
autotrace显示Statistics很多信息为0
select max(id),min(id) from table优化
执行计划中常见index访问方式
ORACLE 12C Partial Global/Local Indexes for Partitioned Tables

原文地址：对于IN Memory Option 部分细节测试—主要当inmemory_size不足之时, 感谢原作者分享。

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn