联系：手机/微信(+86 17813235971) QQ(107644445)
标题：对于IN Memory Option 部分细节测试—主要当inmemory_size不足之时
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
本文对于IMDB的几个特性进行了具体测试:
1. 压缩级别和压缩率(具体也需要具体测试),本实验仅提供参考
2. 对于IM空间不足已经存在的对象和加入新对象的现象
3. 对于PRIORITY级别进行了简单测试
数据库基本配置信息
SQL> 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> 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
数据库版本12.1.0.2,inmemory_size配置为100M
准备测试环境
SQL> create tablespace inmemory datafile 'D:\APP\FFCHENG\ORADATA\XFF\PDB\in_memory01.dbf' 2 size 100m autoextend on next 4m maxsize 10g; 表空间已创建。 SQL> create user chf identified by xifenfei; 用户已创建。 SQL> grant dba to chf; 授权成功。 SQL> alter user chf default tablespace inmemory; 用户已更改。 SQL> create table chf.t_inmemory1 as select * from dba_objects; 表已创建。
创建测试表空间,用户,测试表
测试压缩级别
SQL> alter table chf.t_inmemory1 inmemory NO MEMCOMPRESS;
表已更改。
SQL> select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
2 from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
2 from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR DML
2 ;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY LOW;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY HIGH;
表已更改。
SQL> select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
2 from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY LOW;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY HIGH;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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左右,具体取决于数据
这里可以看出来,压缩效果确实如Oracle所描述,级别越高压缩效果越好.
测试inmemory_size大小不足之时
SQL> alter table chf.t_inmemory1 inmemory no MEMCOMPRESS;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
91040
SQL> 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> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 91040 行。
SQL> 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> commit;
提交完成。
SQL> 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> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
182080
SQL> 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> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 182080 行。
SQL> commit;
提交完成。
SQL> 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> oradebug setmypid
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;
会话已更改。
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> oradebug TRACEFILE_NAME
D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_7604.trc
SQL> SELECT COUNT(object_id) FROM chf.t_inmemory1;
COUNT(OBJECT_ID)
----------------
364156
SQL> 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> 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> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
364160
SQL> 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> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 364160 行。
SQL> 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> commit;
提交完成。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
COUNT(*)
----------
728320
SQL> 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> 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> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 728320 行。
SQL> commit;
提交完成。
SQL> select count(object_id) from chf.t_inmemory1;
COUNT(OBJECT_ID)
----------------
1456624
SQL> 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> alter system flush buffer_cache;
系统已更改。
SQL> 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> set autot trace exp stat
SQL> set lines 120
SQL> pages 1000
SQL> set pages 1000
SQL> 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> create table chf.t_inmemory2 as select * from dba_objects;
表已创建。
SQL> alter table chf.t_inmemory2 inmemory;
表已更改。
SQL> select count(*) from chf.t_inmemory2;
COUNT(*)
----------
91041
SQL> 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> 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> alter table chf.t_inmemory1 no inmemory;
表已更改。
SQL> select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
2 from v$im_segments where segment_name = 'T_INMEMORY2';
未选定行
SQL> set autot traceonly exp stat
SQL> 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> set autot off
SQL> 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> set autot traceonly exp stat
SQL> 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
上述测试几个结论:
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> 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> alter table chf.t_inmemory2 inmemory;
表已更改。
SQL> select count(*) from chf.t_inmemory2;
COUNT(*)
----------
91041
SQL> 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> alter table chf.t_inmemory1 inmemory no memcompress PRIORITY LOW;
表已更改。
SQL> select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
未选定行
SQL> select count(*) from chf.t_inmemory2;
COUNT(*)
----------
91041
SQL> 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
这里测试证明：
1. 指定PRIORITY不为none也需要访问对象后才能够放入IM中
2. 当IM不足时,PRIORITY级别高的会把级别低的对象刷出IM
特此声明:本文仅出自个人测试,得出结论,不可作为任何官方依据使用,具体环境需要具体测试
我测试IM这个功能似乎只能在用一时间内IM一个table，不知道这个合理么？还是由于内存不够呢？