DBA Hub

📋Steps in this guide1/10

In-memory in oracle 12c DBACLASS

Oracle in-memory has been introduced in oracle 12c. It stores the data in column format, also know as IM column store. it will occupy some space from SGA...

oracle clusteringintermediate
by OracleDba
15 views
1

Introduction:

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 delivers fast SQL processing for the Analytical purpose. 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. This format is ideal for OLTP Env. as it allows quick 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 a separate 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. Reference –  https://www.linkedin.com/pulse/in-memory-column-store-oracle-database-12c-venkateswaran-ramanathan > NOTE – The compatible parameter should be set to 12.1.0.2 or later NOTE – The compatible parameter should be set to 12.1.0.2 or later
2

How to check whether inmemory is enabled or not:

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

Code/Command (click line numbers to comment):

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.
3

How to enable the in-memory feature in DB:

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)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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
4

Enable in-memory for  a table

Now check if it is populated in im_segment or not. After enabling in-memory,we need to query that table once, to load in memory. Now check again: Now the im_segment table is populated.

Code/Command (click line numbers to comment):

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
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.

col owner for a12
col segment_name for a12
select owner, segment_name, populate_status from v$im_segments

no rows selected

select count(*) from dbaclass.test2;

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
5

Now check the explain plan:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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.
6

Background process:

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

Code/Command (click line numbers to comment):

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

Enable in-memory with PRIORITY CRITICAL

With this option, the respective tables will be loaded to memory upon database startup.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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
8

Enable in-memory for a tablespace:

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
9

Disable in-memory for the table:

Code/Command (click line numbers to comment):

1
ALTER TABLE DBACLASS.TEST2 NO INMEMORY;
10

USAGE:

V$INMEMORY_AREA stores the usage of inmemory area. 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!