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

本文对于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_N<div>本文来源gaodai.ma#com搞#代!码网_</div>AME--------------------------------------------------------------------------------     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左右,具体取决于数据

喜欢 (0)赏

[搞代码]

分享 (0)