In-Memory Column (Oracle 12c)

In-Memory Column Store enables objects  (tables and partitions) be stored in memory using columnar format. This format enables scans, joins and aggregates faster than

traditional format (row format). This feature does not replace the buffer cache, it only maintains additional and consistent copy of the object.


The In-Memory Column Store is a new SGA component called In-Memory Area.
1st check is inmemory size has been defined ornot by default it will be 0.
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 0===================>>it's 0 mince you have to defaine the size as per your requirement
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL>
SQL> select name,value from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size              3011888
Variable Size         415238864
Database Buffers      125829120
Redo Buffers            5373952
==============================================================>>here no component it allocated to inmemory area mince IM is nt enable for this instance.
SQL>

SQL>ALTER SYSTEM SET inmemory_size = 500M scope=spfile; //set the memory parameter and bounce your db to get effect IM parameter.
SQL> show parameter inmemory                            //check the value of inmemory related parameter and inmemory is showing now 500M

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 500M====================>>now it's showing
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL>
SQL> select name,value from v$sga;  //after enabling this parameter new sga componant will be create as below it's showing.

NAME                      VALUE
-------------------- ----------
Fixed Size              3011888
Variable Size         415238864
Database Buffers      125829120
Redo Buffers            5373952
In-Memory Area        524288000  ===========================>>new componant has been allocated named as ...in-memory area with 500mb

SQL>

SQL>select v.owner,v.segment_name name,v.populate_status status from v$im_segments v;  //Verifying memory segments 0 row mince no segment in IM

no rows selected

SQL>
SQL> ALTER TABLE prd.emp INMEMORY;  //Changing a table to use in-memory

Table altered.

SQL> select v.owner,v.segment_name name,v.populate_status status from v$im_segments v;  //Verifying memory segments ..again no row mince you have enable the IM for emp

table but you have to populate into memory by running the select query as example given below,

no rows selected

SQL>
SQL>  SELECT id, Count(*)         //Populating memory and you also can run select * from emp;
FROM prd.emp
GROUP BY id;  2    3

        ID   COUNT(*)
---------- ----------
         1          1
         2      10000

SQL> select v.owner,v.segment_name name,v.populate_status status from v$im_segments v;  //Verifying memory segments.....now emp table has been populated into IM.

OWNER      NAME       STATUS
---------- ---------- ---------
PRD        EMP        COMPLETED

SQL>