Configure Oracle Database 12c In-Memory Option

Configure Oracle Database 12c In-Memory Option :-

The In-Memory Column store:

• Database In-Memory uses an In-Memory column store, which is a new component of the System Global Area (SGA), called the In-Memory Area. Data in the IM column store does not reside in the traditional row format, instead it uses a new column format. The IM column store does not replace the buffer cache, but acts as a supplement, so that data can now be stored in memory in both a row and a column format
• The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0). The current size of the In-Memory area is visible in V$SGA

Enabling In-Memory option in Oracle database :

SQL> show parameter COMPATIBLE;

NAME       TYPE    VALUE

---------- ------- ------

compatible string  12.2.0

SQL> show parameter sga_target;

NAME       TYPE        VALUE

---------- ----------- ------

sga_target big integer  4G

SQL> show parameter INMEMORY_SIZE;

NAME          TYPE        VALUE

------------- ----------- ------

inmemory_size big integer  0

SQL> ALTER SYSTEM SET INMEMORY_SIZE=2G scope=spfile;

System altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

After startup  "In-Memory Area" has allocated.

Total System Global Area 4294967296 bytes

Fixed Size 8628936 bytes

Variable Size 603981112 bytes

Database Buffers 1526726656 bytes

Redo Buffers 8146944 bytes

In-Memory Area 2147483648 bytes

Database mounted.

Database opened.

SQL> SHOW PARAMETER INMEMORY

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

inmemory_adg_enabled                 boolean     TRUE

inmemory_clause_default              string

inmemory_expressions_usage           string      ENABLE

inmemory_force                       string      DEFAULT

inmemory_max_populate_servers        integer     1

inmemory_query                       string      ENABLE

inmemory_size                        big integer 2G

inmemory_trickle_repopulate_servers_ integer     1

percent

inmemory_virtual_columns             string      MANUAL

optimizer_inmemory_aware             boolean     TRUE

IM Column store sub divided into two pools:

1. 1 MB pool—Store the actual column formatted data
2. 64k Pool— Store the metadata about the objects.

We can see the amount memory available in each pool using below query

SQL> select * from v$inmemory_area;

POOL     ALLOC_BYTES    USED_BYTES   POPULATE_STATUS  CON_ID

-------- -------------  ----------   ---------------- ----------

1MB POOL 1710227456     0            DONE             0

64KB POOL 419430400     0            DONE             0

Disabling In-Memory option :

Alter system set inmemory_size=0 SCOPE=SPFILE;

or

Alter system reset inmemory_size  SCOPE=SPFILE;