联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对于12.1.0.2的In-Memory特性很多朋友都已经知晓,现在可能有这个困惑我一张表启用In-Memory大概需要多少内存呢?该如何估算这个值呢?这里我告诉你通过dbms_compression可以完成你想做的事情
启用In-Memory功能
[oracle@www.xifenfei.com u02]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 7 17:50:47 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_size=400M; alter system set inmemory_size=400M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> alter system set inmemory_size=400M scope=spfile; System altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO SQL> alter session set container=pdb1; Session altered. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 0 inmemory_query string ENABLE inmemory_size big integer 0 inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_size=200M; alter system set inmemory_size=200M * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> alter system set inmemory_size=200M scope=spfile; alter system set inmemory_size=200M scope=spfile * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option SQL> !oerr ora 2096 02096, 00000, "specified initialization parameter is not modifiable with this option" // *Cause: Though the initialization parameter is modifiable, it cannot be // modified using the specified command. // *Action: Check the DBA guide for information about under what scope // the parameter may be modified SQL> select 200*1024*1024 from dual; 200*1024*1024 ------------- 209715200 SQL> alter system set inmemory_size=209715200; alter system set inmemory_size=209715200 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-02095: specified initialization parameter cannot be modified SQL> shutdown immediate; Pluggable Database closed. SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 838860800 bytes Fixed Size 2929936 bytes Variable Size 360712944 bytes Database Buffers 50331648 bytes Redo Buffers 5455872 bytes In-Memory Area 419430400 bytes Database mounted. Database opened. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 400M inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter session set container=pdb1; Session altered. SQL> alter database open; Database altered. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 400M inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE SQL> alter system set inmemory_size=100M; System altered. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 100M inmemory_trickle_repopulate_servers_ integer 1 optimizer_inmemory_aware boolean TRUE
这里可以发现inmemory_query默认为true,但是inmemory_size默认为0,也就是说In-Memory Option默认是关闭的,如果启用直接设置inmemory_size值即可(该值最小值为100M),但是需要注意在cdb中需要重启后生效,pdb需要待cdb生效后才能够设置
估算表设置In-Memory默认需要内存空间
SQL> create user chf identified by xifenfei; User created. SQL> grant dba to chf; Grant succeeded. SQL> create table chf.t_xifenfei as select * from dba_objects; Table created. SQL> select sum(bytes), sum(blocks) from DBA_segments where segment_name = 'T_XIFENFEI'; SUM(BYTES) SUM(BLOCKS) ---------- ----------- 13631488 1664 SQL> set serveroutput on DECLARE l_blkcnt_cmp binary_integer; l_blkcnt_uncmp binary_integer; l_row_cmp binary_integer; l_row_uncmp binary_integer; l_cmp_ratio number; SQL> 2 3 4 5 6 7 l_comptype_str varchar2(100); 8 BEGIN 9 dbms_compression.get_compression_ratio( 10 scratchtbsname => upper('&ScratchTBS'), 11 ownname => upper('&ownername'), 12 objname => upper('&TableName'), 13 subobjname => NULL, 14 comptype => DBMS_COMPRESSION.COMP_INMEMORY_QUERY_LOW, 15 blkcnt_cmp => l_blkcnt_cmp, 16 blkcnt_uncmp => l_blkcnt_uncmp, 17 row_cmp => l_row_cmp, 18 row_uncmp => l_row_uncmp, 19 cmp_ratio => l_cmp_ratio, 20 comptype_str => l_comptype_str 21 ); 22 dbms_output.put_line('.'); 23 dbms_output.put_line('OUTPUT: '); 24 dbms_output.put_line('LINEORDER '||l_comptype_str||' ratio: '||to_char(l_cmp_ratio,'99.999')); 25 end; 26 / Enter value for scratchtbs: USERS old 10: scratchtbsname => upper('&ScratchTBS'), new 10: scratchtbsname => upper('USERS'), Enter value for ownername: CHF old 11: ownname => upper('&ownername'), new 11: ownname => upper('CHF'), Enter value for tablename: T_XIFENFEI old 12: objname => upper('&TableName'), new 12: objname => upper('T_XIFENFEI'), . OUTPUT: LINEORDER "In-memory Memcompress Query Low" ratio: 2.800 PL/SQL procedure successfully completed. SQL> SELECT 13631488/2.800 FROM DUAL; 13631488/2.800 -------------- 4868388.57 SQL> alter table CHF.T_XIFENFEI inmemory; Table altered. SQL> select COUNT(*) FROM CHF.T_XIFENFEI; COUNT(*) ---------- 90923 SQL> select inmemory_size from v$im_segments where segment_name = 'T_XIFENFEI'; INMEMORY_SIZE ------------- 4325376 SQL> select (4868388.57-4325376)/4325376 from dual; (4868388.57-4325376)/4325376 ---------------------------- .125541125
这里使用大家在ehcc中熟悉的dbms_compression.get_compression_ratio来估算In-memory需要的大概空间,例如本测试中,创建T_XIFENFEI表占用磁盘空间为13631488byte,使用dbms_compression估算在In-memory默认的压缩比例(Query Low)情况下,大概压缩比例为2.8,也就是通过合理估算,表启用In-memory cache之后,大概需要空间为13631488/2.800=4868388.57byte,最终通过实际测试需要空间为4325376byte,整体误差为(4868388.57-4325376)/4325376=12.5%左右
因此我们在使用In-memory cache一个表之时,如果不确定其需要内存大小,可以通过dbms_compression包来估算.另外In-memory还可以配置不同的压缩级别实现不同的压缩比例,其他压缩比例请见下图
详细link请见:https://docs.oracle.com/database/121/ARPLS/d_compress.htm#ARPLS65599
那接着就有一个问题了,如果随着数据的变化,当表的大小超过了inmemory_size设置的值得时候,表是会全部被置换出去还是部分列被置换出去?置换的算法和优先级是怎样的?