Estimating table In-memory in Oracle requires memory size

For the in-memory characteristics of 12.1.0.2 Many friends already know that there may be this confusion now I have a table to enable in-memory how much memory? How do you estimate this value? Here, I'm telling you, you can do what you want with dbms_compression.
Enable In-memory Features
[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 to 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 to 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 isn't modifiable with this
Option

sql>!oerr Ora 2096
02096, 00000, "specified initialization parameter is isn't modifiable with" option "
*cause:though The initialization parameter is modifiable, it cannot to be
Modified using the specified command.
*action:check The DBA Guide for information about under what scope
The parameter may is 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 to 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
Here you can find that inmemory_query defaults to true, but inmemory_size defaults to 0, which means that in-memory option is turned off by default, and if you enable direct setting inmemory_size value (the minimum value is 100M) , but you need to be aware that the PDB needs to be CDB to take effect after the CDB is in effect before it can be set

Estimate table set in-memory default requires memory space
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 (
Scratchtbsname => Upper (' &scratchtbs '),
One ownname => Upper (' &ownername '),
objname => Upper (' &tablename '),
Subobjname => NULL,
Comptype => Dbms_compression.comp_inmemory_query_low,
BLKCNT_CMP => l_blkcnt_cmp,
BLKCNT_UNCMP => l_blkcnt_uncmp,
ROW_CMP => l_row_cmp,
ROW_UNCMP => l_row_uncmp,
Cmp_ratio => L_cmp_ratio,
Comptype_str => L_comptype_str
21);
Dbms_output.put_line ('. ');
Dbms_output.put_line (' output: ');
Dbms_output.put_line (' Lineorder ' | | l_comptype_str| | ' ratio: ' | | To_char (L_cmp_ratio, ' 99.999 '));
The 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
Here we use the familiar dbms_compression.get_compression_ratio in EHCC to estimate the approximate space in-memory needs, for example, in this test, create T_ The Xifenfei table takes up 13631488byte of disk space and uses dbms_compression to estimate that the compression ratio is 2.8 in the case of In-memory default compression (Query low), that is, by reasonable estimation, Table Enable In-memory cache, probably need space for 13631488/2.800=4868388.57byte, finally through the actual test needs space for 4325376byte, the overall error is (4868388.57-4325376)/ 4325376=12.5% Around
So when we use in-memory cache a table, if we are unsure of the size of memory, we can estimate it by dbms_compression package. In addition In-memory can also configure different compression levels to achieve different compression ratio, the other compression ratio, see the figure below

Original from: http://www.xifenfei.com/5627.html