DBA Hub

📋Steps in this guide1/8

In-Memory Column Store in Oracle Database 12c Release 1 (12.1.0.2)

This article provides an overview of the In-Memory Column Store feature in Oracle 12c Release 1 (12.1.0.2).

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Introduction

The In-Memory column store is a new section of the SGA, sized using the initialization parameter. 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. The following commands have been modified to include additional in-memory clauses. Basic examples of their use will be shown below. The documentation claims the IM column store is good for the following. - Large scans that apply "=", "<", ">" and "IN" filters. - Queries that return a small number of columns from a table with a large number of columns. - Queries that join small tables to large tables. - Queries that aggregate data. It also states it is not designed for the following. - Queries with complex predicates. - Queries that return a large number of columns. - Queries that return large numbers of rows. - Queries with multiple large table joins. The important thing to remember here is *you* will be responsible for deciding which objects will benefit the most from inclusion in the IM column store. If you choose wisely you will see big improvements in performance. If you choose badly, you will waste a lot of memory that could be used by the buffer cache. Full lists of initialization parameters, compression methods and population options are available from the documentation. I'm not going to repeat them here. - Initialization Parameters Related to the IM Column Store - IM Column Store Compression Methods - IM Column Store Data Population Options

Code/Command (click line numbers to comment):

1
2
3
4
5
6
CREATE TABLE
ALTER TABLE
CREATE TABLESPACE
ALTER TABLESPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
2

Enable In-Memory Column Store

Remember, the IM column store is part of the SGA, so the SGA must be capable of containing the amount of memory you want to assign to the parameter. In a multitenant environment, the parameter must be set in the CDB if any of the PDBs need access to the IM column store. - If you are using AMM (MEMORY_TARGET), you will need to extend this to account for the parameter value. - If you are using ASMM (SGA_TARGET), you will need to extend this to account for the parameter value. Assuming the parameter is set to 12.1.0 or higher and there is enough room in the SGA to hold the IM column store, the following process will enable the IM column store. In this case I am setting the parameter to 2G. Notice the "In-Memory Area" line produced during the startup. The current IM settings are shown below. With the exception of the size, all others are default values. The parameter setting is inherited by all PDBs unless it is explicitly set at the PDB level. Changing the parameter value at the PDB level does not require a restart of the instance or PDB.

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
ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE;
ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

ORACLE instance started.

Total System Global Area 3221225472 bytes
Fixed Size                  2929552 bytes
Variable Size             419433584 bytes
Database Buffers          637534208 bytes
Redo Buffers               13844480 bytes
In-Memory Area           2147483648 bytes
Database mounted.
Database opened.
SQL>

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 2G
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
SQL>

CONN sys@pdb1 AS SYSDBA
-- Disable IM column store in the PDB
ALTER SYSTEM SET INMEMORY_SIZE=0;
-- OR
ALTER SYSTEM RESET INMEMORY_SIZE;

-- Assign a PDB-specific size.
ALTER SYSTEM SET INMEMORY_SIZE=1G;
3

Disable In-Memory Column Store

There are several ways to disable the IM column store, depending on what you are trying to achieve. Setting the parameter to "OFF" means objects will not be maintained in the IM column store. Switching it back to "DEFAULT" returns to the default behaviour. Setting the parameter to "DISABLE" means the optimiser will not consider the IM column store to optimise queries. Switching it back to "ENABLE" reverts it to the default functionality. To disable the IM column store completely and release the memory, reset the parameter. As described in the previous section, the PDB-specific settings can be altered without an instance or PDB restart.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- System level
ALTER SYSTEM SET INMEMORY_FORCE=OFF;
ALTER SYSTEM SET INMEMORY_FORCE=DEFAULT;

-- System level
ALTER SYSTEM SET INMEMORY_QUERY=DISABLE;
ALTER SYSTEM SET INMEMORY_QUERY=ENABLE;

-- Session level
ALTER SESSION SET INMEMORY_QUERY=DISABLE;
ALTER SESSION SET INMEMORY_QUERY=ENABLE;

ALTER SYSTEM RESET INMEMORY_SIZE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
4

Managing Tables

The and commands have been amended to allow you to determine if the table should be stored in the IM column store. Creating a table with the clause is the same as not specifying the clause at all. The example below shows the creation of three tables using three variations on the syntax. The views have been amended to include IM-related information. The command can change the IM status of the objects. The following example flips the status.

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
45
46
47
48
49
50
51
52
53
54
55
56
57
CONN test/test@pdb1

CREATE TABLE im_tab (
  id  NUMBER
) INMEMORY;

CREATE TABLE noim_tab (
  id  NUMBER
) NO INMEMORY;

CREATE TABLE default_tab (
  id  NUMBER
);

COLUMN table_name FORMAT A20

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
ORDER BY table_name;

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
DEFAULT_TAB          DISABLED
IM_TAB               ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE
NOIM_TAB             DISABLED

3 rows selected.

SQL>

ALTER TABLE IM_TAB NO INMEMORY;
ALTER TABLE NOIM_TAB INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
ORDER BY table_name;

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
DEFAULT_TAB          ENABLED  HIGH     AUTO            FOR QUERY LOW     NO DUPLICATE
IM_TAB               DISABLED
NOIM_TAB             ENABLED  NONE     AUTO            FOR CAPACITY LOW  NO DUPLICATE

3 rows selected.

SQL>
5

Managing Columns

The following example shows the syntax for including a subset of columns in the IM column store. The column settings are displayed using the view. The IM settings can be changed using the ALTER TABLE command.

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
CREATE TABLE im_col_tab (
  id   NUMBER,
  col1 NUMBER,
  col2 NUMBER,
  col3 NUMBER,
  col4 NUMBER
) INMEMORY
INMEMORY MEMCOMPRESS FOR QUERY HIGH (col1, col2)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
NO INMEMORY (id, col4);

CONN sys@pdb1 AS SYSDBA

SELECT table_name,
       segment_column_id,
       column_name,
       inmemory_compression
FROM   v$im_column_level
WHERE  owner = 'TEST'
and    table_name = 'IM_COL_TAB'
ORDER BY segment_column_id;

TABLE_NAME           SEGMENT_COLUMN_ID COLUMN_NAME                     INMEMORY_COMPRESSION
-------------------- ----------------- ------------------------------- --------------------------
IM_COL_TAB                           1 ID                              NO INMEMORY
IM_COL_TAB                           2 COL1                            FOR QUERY HIGH
IM_COL_TAB                           3 COL2                            FOR QUERY HIGH
IM_COL_TAB                           4 COL3                            FOR CAPACITY HIGH
IM_COL_TAB                           5 COL4                            NO INMEMORY

5 rows selected.

SQL>

CONN test/test@pdb1

ALTER TABLE im_col_tab 
NO INMEMORY (col1, col2)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (col3)
NO INMEMORY (id, col4);

CONN sys@pdb1 AS SYSDBA

SELECT table_name,
       segment_column_id,
       column_name,
       inmemory_compression
FROM   v$im_column_level
WHERE  owner = 'TEST'
and    table_name = 'IM_COL_TAB'
ORDER BY segment_column_id;

TABLE_NAME           SEGMENT_COLUMN_ID COLUMN_NAME                     INMEMORY_COMPRESSION
-------------------- ----------------- ------------------------------- --------------------------
IM_COL_TAB                           1 ID                              NO INMEMORY
IM_COL_TAB                           2 COL1                            NO INMEMORY
IM_COL_TAB                           3 COL2                            NO INMEMORY
IM_COL_TAB                           4 COL3                            FOR CAPACITY HIGH
IM_COL_TAB                           5 COL4                            NO INMEMORY

5 rows selected.

SQL>
6

Managing Materialized Views

The and commands work in a similar was to the and commands.

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
CONN test/test@pdb1

CREATE TABLE t1 AS
  SELECT * FROM all_objects;

CREATE MATERIALIZED VIEW t1_mv INMEMORY 
  AS SELECT * FROM t1;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
WHERE  table_name = 'T1_MV';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
T1_MV                ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE

1 row selected.

SQL>


ALTER MATERIALIZED VIEW t1_mv
  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY HIGH;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
WHERE  table_name = 'T1_MV';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
T1_MV                ENABLED  HIGH     AUTO            FOR CAPACITY HIGH NO DUPLICATE

1 row selected.

SQL>


ALTER MATERIALIZED VIEW t1_mv NO INMEMORY;

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   user_tables
WHERE  table_name = 'T1_MV';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
T1_MV                DISABLED

1 row selected.

SQL>
7

Managing Tablespaces

Setting the default IM column store parameters for a tablespace means all tables and materialized views in that tablespace will use those setting unless explicitly overridden. The following example shows how to set the IM column store parameters during a tablespace creation. The keyword is part of the IM clause. The default IM settings are displayed using the view. The command is used to change the IM column store parameters. The default settings can be overridden by specific object settings shown above.

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
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
CONN sys@pdb1 AS SYSDBA

CREATE TABLESPACE new_ts
   DATAFILE '/u01/app/oracle/oradata/CDB1/datafile/pdb1/pdb1_new_ts.dbf' SIZE 10M 
   DEFAULT INMEMORY;

SELECT tablespace_name, 
       def_inmemory,
       def_inmemory_priority,
       def_inmemory_distribute,
       def_inmemory_compression,
       def_inmemory_duplicate
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ -------- -------- --------------- ----------------- -------------
NEW_TS                         ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TEMP                           DISABLED
USERS                          DISABLED

5 rows selected.

SQL>

ALTER TABLESPACE new_ts
  DEFAULT INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

SELECT tablespace_name, 
       def_inmemory,
       def_inmemory_priority,
       def_inmemory_distribute,
       def_inmemory_compression,
       def_inmemory_duplicate
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ -------- -------- --------------- ----------------- -------------
NEW_TS                         ENABLED  NONE     AUTO            FOR CAPACITY HIGH NO DUPLICATE
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TEMP                           DISABLED
USERS                          DISABLED

SQL>


ALTER TABLESPACE new_ts
  DEFAULT NO INMEMORY;

SELECT tablespace_name, 
       def_inmemory,
       def_inmemory_priority,
       def_inmemory_distribute,
       def_inmemory_compression,
       def_inmemory_duplicate
FROM   dba_tablespaces
ORDER BY tablespace_name;

TABLESPACE_NAME                DEF_INME DEF_INME DEF_INMEMORY_DI DEF_INMEMORY_COMP DEF_INMEMORY_
------------------------------ -------- -------- --------------- ----------------- -------------
NEW_TS                         DISABLED
SYSAUX                         DISABLED
SYSTEM                         DISABLED
TEMP                           DISABLED
USERS                          DISABLED

5 rows selected.

SQL>
8

Views

We've already seen some of the existing views have been modified to include IM column store information. The following views have been added in to support the IM column store. - V$IM_COLUMN_LEVEL - V$IM_SEGMENTS - V$IM_USER_SEGMENTS For more information see: - Using the In-Memory Column Store Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!