IN-MEMORY In Oracle 12c

IN-MEMORY In Oracle 12c
=======================
How to check whether inmemory 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.

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

Enable in-memory for  a table

SELECT table_name,inmemory,inmemory_priority,
inmemory_distribute,inmemory_compression,
inmemory_duplicate
FROM   dba_tables
WHERE table_name=’test123′;

no rows selected

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

no rows selected

SELECT table_name,inmemory,inmemory_priority,
inmemory_distribute,inmemory_compression,
inmemory_duplicate
FROM   dba_tables
WHERE table_name=’test123′;

no rows selected

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

no rows selected

SQL> alter table dbaadmin.test123 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 dbaadmin.test123;

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

OWNER        SEGMENT_NAME POPULATE_STATUS
———— ———— —————
dbaadmin     test123        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=’test123′;

TABLE_NAME   INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
———— ——– ——– ————— —————– ————-
test123        ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE

Now check the explain plan:

SQL> explain plan for select * from dbaadmin.test123;

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| test123 | 77294 |  8001K|    29  (25)| 00:00:01 | —- >>> THIS ONE SHOWS THAT INMEMORY IS USED
————————————————————————————

8 rows selected.

Enable in-memory with PRIORITY CRITICAL

ALTER TABLE dbaadmin.TEST3 INMEMORY PRIORITY CRITICAL;

SQL> select OWNER,SEGMENT_NAME,populate_status,INMEMORY_PRIORITY from v$im_segments;

OWNER    SEGMENT_N POPULATE_ INMEMORY
——– ——— ——— ——–
dbaadmin test124     COMPLETED CRITICAL
dbaadmin test123     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 dbaadmin.test123 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

  • February 22, 2019 | 15 views
  • Comments