oracle中估算表In-Memory需要記憶體大小

對於12.1.0.2的In-Memory特性很多朋友都已經知曉,現在可能有這個困惑我一張表啟用In-Memory大概需要多少記憶體呢？該如何估算這個值呢？這裡我告訴你通過dbms_compression可以完成你想做的事情
啟用In-Memory功能
[oracle@www.111cn.net 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還可以配置不同的壓縮層級實現不同的壓縮比例,其他壓縮比例請見下圖

原文來自:http://www.xifenfei.com/5627.html