We value your privacy

We use cookies to enhance your browsing experience, serve personalised content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies.

Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

  Master in Data Analyst | Batch Starting from 10 March 2025 | Register for Free Demo

Know More

IN-MEMORY In Oracle 12c

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 20 Sep, 2023
  • 0 Comments
  • 5 Mins Read

IN-MEMORY In Oracle 12c

              Oracle in-memory concept has been introduced in oracle 12c. This feature enables tables, partitions, materialized views be stored in memory using column format, which improves performance of SQL queries.           

            To understand Database In-Memory feature and its benefits we first need to understand the unique “dual format” architecture that enables Oracle Database tables to be simultaneously represented on disk and in memory, using a traditional row format and a new in-memory column format.

Row Format – Oracle has traditionally stored data in row format where each new record is represented as a new row in a table having multiple columns with each column representing a different attribute about that record. 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 a separate column-structure. So 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. 

How to check whether inmemory is enabled or not:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.

Note : If in-memory_size is set to ZERO, it means that in-memory is not enabled in databases.

How to enable the in-memory feature in DB:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
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
------------------------------------------------------------------------------------
 
8 rows selected.
Background process:
The background process imco (IN MEMORY COORDINATOR)  is responsible for loading the in-memory enabled objects to memory
Enable in-memory with PRIORITY CRITICAL
With this option, the respective tables will be loaded to memory upon database startup.
ALTER TABLE dbaclass.TEST3 INMEMORY PRIORITY CRITICAL;
 
 
SQL> select OWNER,SEGMENT_NAME,populate_status,INMEMORY_PRIORITY from v$im_segments;
 
OWNER SEGMENT_N POPULATE_ INMEMORY
-------- --------- --------- --------
DBACLASS TEST3 COMPLETED CRITICAL
DBACLASS TEST2 COMPLETED NONE
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 inmemory 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

Hope it helps!

How To Flush A Sql Statement From Shared Pool
Sec_case_sensitive_logon Parameter In Oracle