如何估算表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 - bit 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 503318 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

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 16
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