对于IN Memory Option 部分细节测试—主要当inmemory_size不足之
Jun 07, 2016 pm 04:40 PM本文对于IMDB的几个特性进行了具体测试: 1. 压缩级别和压缩率(具体也需要具体测试),本实验仅提供参考 2. 对于IM空间不足已经存在的对象和加入新对象的现象 3. 对于PRIORITY级别进行了简单测试 数据库基本配置信息 SQL select * from v$version;BANNER CON_ID
本文对于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
特此声明:本文仅出自个人测试,得出结论,不可作为任何官方依据使用,具体环境需要具体测试
- 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不足之时, 感谢原作者分享。

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

What is the best graphics card for i7 3770?

What do you think of furmark? - How is furmark considered qualified?

CAMM2 for desktop PCs: MSI explains the benefits of the new RAM standard for gaming towers

Join a new Xianxia adventure! 'Zhu Xian 2' 'Wuwei Test' pre-download is now available

What are the differences between function testing and coverage in different languages?

The new king of domestic FPS! 'Operation Delta' Battlefield Exceeds Expectations

PHP Jenkins 101: The only way to get started with CI/CD
