本文对于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 0pl/sql release 12.1.0.2.0 - production 0core 12.1.0.2.0 production 0tns for 64-bit windows: version 12.1.0.2.0 - production 0nlsrtl version 12.1.0.2.0 - production 0sql> show parameter inmemory;name type value------------------------------------ ----------- ------------------------------inmemory_clause_default stringinmemory_force string defaultinmemory_max_populate_servers integer 2inmemory_query string enableinmemory_size big integer 100minmemory_trickle_repopulate_servers_ integer 1percentoptimizer_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(*)---------- 91040sql> 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(*)---------- 91040sql> 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(*)---------- 91040sql> 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(*)---------- 91040sql> 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(*)---------- 91040sql> 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(*)---------- 91040sql> 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(*)---------- 91040sql> 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 memcompresssql> 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 memcompresssql> select count(*) from chf.t_inmemory1; count(*)---------- 182080sql> 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 memcompresssql> 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_named:\app\ffcheng\diag\rdbms\xff\xff\trace\xff_ora_7604.trcsql> select count(object_id) from chf.t_inmemory1;count(object_id)---------------- 364156sql> 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_inmemory1end of stmtparse #455134016:c=15600,e=3912,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=3154396630,tim=126773093620wait #455134016: nam='disk file operations i/o' ela= 154 fileoperation=8 fileno=0 filetype=8 obj#=-1 tim=126773093926exec #455134016:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3154396630,tim=126773094005wait #455134016: nam='sql*net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773094044fetch #455134016:c=0,e=13751,p=0,cr=3110,cu=1,mis=0,r=1,dep=0,og=1,plh=3154396630,tim=126773107829stat #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=126773108164fetch #455134016:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3154396630,tim=126773108215wait #455134016: nam='sql*net message to client' ela= 0 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108246sql> 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 memcompresssql> select count(*) from chf.t_inmemory1; count(*)---------- 364160sql> 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 memcompresssql> 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 memcompresssql> commit;提交完成。sql> select count(*) from chf.t_inmemory1; count(*)---------- 728320sql> 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 memcompresssql> 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)---------------- 1456624sql> 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 statsql> set lines 120sql> pages 1000sql> set pages 1000sql> 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 processed10046结果sql id: 1b61dgunxftdx plan hash: 3154396630select count(object_id) from chf.t_inmemory1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------parse 1 0.00 0.00 0 2 0 0execute 1 0.00 0.00 0 0 0 0fetch 2 1.26 4.14 16689 22446 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 1.26 4.14 16689 22448 0 1misses in library cache during parse: 1optimizer mode: all_rowsparsing user id: sysnumber of plan statistics captured: 1rows (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(*)---------- 91041sql> 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 memcompresssql> 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 processedsql> 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 statsql> 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 processedsql> set autot offsql> 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 lowsql> set autot traceonly exp statsql> 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 memcompresssql> alter table chf.t_inmemory2 inmemory;表已更改。sql> select count(*) from chf.t_inmemory2; count(*)---------- 91041sql> 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 memcompresssql> 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(*)---------- 91041sql> 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很多信息为0select max(id),min(id) from table优化执行计划中常见index访问方式oracle 12c partial global/local indexes for partitioned tables 原文地址：对于in memory option 部分细节测试—主要当inmemory_size不足之时, 感谢原作者分享。