Introduction :- Oracle 12c has lots of characteristics  In-Memory is one of the most important features.  It enables tables, partitions, materialized views be stored in memory using column format, which delivers fast SQL processing for the Analytical purpose. Oracle Database In-Memory proffers an idiosyncratic dual-format architecture that legalizes tables to be concurrently represented in memory using standard row format and a new in-memory column format. The Oracle SQL Optimizer automatically routes analytic queries to the column format and OLTP queries to the row format, palpably delivering best-of-both-worlds performance. Oracle Database 12cautomatically maintains full transactional consistency between the row and the column formats, just as it maintains consistency between tables and indexes today. The new column format is a pure in-memory format and is not persistent on disk, so there are no additional storage costs or storage synchronization issues.

Row Format – Oracle has traditionally stored data in row format where each new record is represented as a new row in a table having numerous columns with each column representing a non-identical attribute about that record. This format is ideal for OLTP Env. as it allows expeditious access to all the columns in a record since all the columns of the record are kept intact in-memory and on storage.This is ideal for processing DMLs (Insert, Update, Delete)

Column Format – A column format database stores each of the attributes of a record in an autonomous column-structure. So obviously this is good for an OLAP Env, as it allows faster data retrieval when a large portion of data is selected but only for a few columns.

Oracle Database In-Memory feature enables data to be simultaneously populated in memory in both a row format (in the buffer cache) and a new in-memory column format. The In-Memory Column Store(IMCS) is a static pool in the Oracle System Global Area (SGA) associated with the
Oracle Database. It stores copies of objects in the memory in a columnar format. The IMCS does not replace the buffer cache but supplements it so
that both the memory areas can store data in different formats.The Oracle Database query optimizer is fully aware of the column format and thus it
automatically routes analytic queries to the column format and OLTP operations to the row format, ensuring outstanding performance and complete data consistency for all workloads without any application changes.
You can choose to store specific groups of columns, whole tables, materialized views or table partitions in the store. Alternatively, you can enable IM column store at the tablespace level, so all tables and materialized views in the tablespace are automatically enabled for the IM column store.

Method  to check whether in memory is enabled or not:

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  ---- > 0 Means inmemory not enabled

inmemory_trickle_repopulate_servers_ integer     1

percent

optimizer_inmemory_aware             boolean     TRUE

SQL>  select name,value from v$sga where NAME='In-Memory Area';

No rows selected.

Here inmemory_size is set to ZERO, shows that in-memory is not enabled in databases.

How to enable the in-memory feature in DB:

SQL> alter system set inmemory_size=5G scope=spfile;



System altered.



shutdown immediate;

startup



SQL> show parameter inmemory_size


NAME                                 TYPE        VALUE

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

inmemory_size                        big integer 3G

SQL>  select name,value from v$sga where NAME='In-Memory Area';

NAME                      VALUE

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

In-Memory Area       3221225472

You may have to resize the SGA, after allocating space to inmemory . Now in-memory feature is enabled in databases. Now let’s enable in-memory for a table.(DBACLASS.TEST2)

Enable in-memory for  a table

SELECT table_name,inmemory,inmemory_priority,

inmemory_distribute,inmemory_compression,

inmemory_duplicate

FROM   dba_tables

WHERE table_name='TEST2';


no rows selected

SQL>select owner, segment_name, populate_status from v$im_segments


no rows selected


SQL> alter table dbaclass.test2 inmemory;

Table altered.

Now check if it is populated in im_segment or not.

col owner for a12

col segment_name for a12

select owner, segment_name, populate_status from v$im_segments

no rows selected

After enabling in-memory,we need to query that table once, to load in memory.

select count(*) from dbaclass.test2;

Now check again:

col owner for a12

col segment_name for a12

select owner, segment_name, populate_status from v$im_segments


OWNER        SEGMENT_NAME POPULATE_STATUS

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

DBACLASS     TEST2        COMPLETED


set lines 299

col table_name for a12

SELECT table_name,inmemory,inmemory_priority,

inmemory_distribute,inmemory_compression,

inmemory_duplicate

FROM   dba_tables

WHERE table_name='TEST2';

TABLE_NAME   INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL

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

TEST2        ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE


Now the im_segment table is populated.

Now check the explain plan:

SQL> explain plan for select * from dbaclass.test2;



Explained.


SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT

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

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

Plan hash value: 3778028574



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

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT           |       | 77294 |  8001K|    29  (25)| 00:00:01 |

|   1 |  TABLE ACCESS INMEMORY FULL| TEST2 | 77294 |  8001K|    29  (25)| 00:00:01 | ---- >>> THIS ONE SHOWS THAT INMEMORY IS USED

Background process:

The background process imco (IN MEMORY COORDINATOR)  is responsible for loading the in-memory enabled objects to memory

oracle 22665 1 0 13:58:45 ? 0:01 ora_imco_SBDB

Enable in-memory for a tablespace:

If enabled at tablespace level, all the tables will enable for IM column store.

SQL>

select tablespace_name,DEF_INMEMORY,DEF_INMEMORY_PRIORITY,DEF_INMEMORY_COMPRESSION,DEF_INMEMORY_DISTRIBUTE,DEF_INMEMORY_DUPLICATE from dba_tablespaces where tablespace_name='USERS';


TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_COMP DEF_INMEMORY_DI DEF_INMEMORY_
------------------------------ -------- -------- ----------------- --------------- -------------
USERS DISABLED

SQL> ALTER TABLESPACE USERS DEFAULT INMEMORY;



Tablespace altered.

SQL> select tablespace_name,DEF_INMEMORY,DEF_INMEMORY_PRIORITY,DEF_INMEMORY_COMPRESSION,DEF_INMEMORY_DISTRIBUTE,DEF_INMEMORY_DUPLICATE from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME DEF_INME DEF_INME DEF_INMEMORY_COMP DEF_INMEMORY_DI DEF_INMEMORY_
------------------------------ -------- -------- ----------------- --------------- -------------
USERS ENABLED NONE FOR QUERY LOW AUTO NO DUPLICATE

Disable in-memory for the table:

ALTER TABLE DBACLASS.TEST2 NO INMEMORY;

USAGE:

V$INMEMORY_AREA stores the usage of the in-memory area.

set pagesize 200

set lines 200

select * from V$INMEMORY_AREA

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID

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

1MB POOL 2549088256 9437184 DONE 0
64KB POOL 654311424 1638400 DONE 0

1MB pool used to store the actual column-formatted data populated into memory
64K pool used to store metadata about the objects that are populated into the IM column store

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.