Oracle Database In-Memory Base Level Feature in Oracle 19c

In-Memory database technology is already introduced in Oracle database system, however in Oracle 19c things changed ! The feature is now free with limited size of  16 GB. You can now explore “Base-Level” feature starting with Oracle 19.8 version where JULY 2020 RU (Release Update) is applied.

The new Base Level feature supports all Database In-Memory features except for the following:

  • Automatic In-Memory  (AIM)
  • Compression levels other than MEMCOMPRESS FOR QUERY LOW
  • Excluded columns (all columns of a table are populated)
  • The CELLMEMORY feature on Exadata

To enable In-Memory “Base-level” feature:

go to cd $ORACLE_HOME/dbs and edit your init.ora file by adding the following two parameters and “restart” your database

INMEMORY_FORCE=BASE_LEVEL

INMEMORY_SIZE=7G

SQL> create table dba.in_mem as select * from dba_objects;

Table created.

SQL> explain plan SET STATEMENT_ID=’EMO1′ for select count(*) from dba.in_mem;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’EMO1′,’ALL’));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3285478656

———————————————————————

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

———————————————————————

| 0 | SELECT STATEMENT | | 1 | 115 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| IN_MEM | 25010 | 115 (0)| 00:00:01 |

———————————————————————

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT

——————————————————————————–

————————————————————-

1 – SEL$1

2 – SEL$1 / IN_MEM@SEL$1

Column Projection Information (identified by operation id):

———————————————————–

1 – (#keys=0) COUNT(*)[22]

2 – (rowset=1019)

21 rows selected.

*** — Now i will load the table to Memory:

SQL> ALTER TABLE dba.in_mem INMEMORY PRIORITY HIGH;

Table altered.

— to check objects uploaded in memory you can query dictionary view V$IM_SEGMENTS:

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE from v$IM_SEGMENTS ;

OWNER SEGMENT_NAME SEGMENT_TYPE

——————————————————————————————————————————– ——————————————————————————————————————————– ——————

dba IN_MEM TABLE

SQL> explain plan SET STATEMENT_ID=’EMO2′ for select count(*) from dba.in_mem;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,’EMO2′,’ALL’));

PLAN_TABLE_OUTPUT

————————————————————————————————————————————————————————————————————————————————————————————————————

Plan hash value: 3285478656

——————————————————————————

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

——————————————————————————

| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS INMEMORY FULL| IN_MEM | 25010 | 5 (0)| 00:00:01 |

——————————————————————————

Query Block Name / Object Alias (identified by operation id):

————————————————————-

1 – SEL$1

2 – SEL$1 / IN_MEM@SEL$1

Column Projection Information (identified by operation id):

———————————————————–

1 – (#keys=0) COUNT(*)[22]

2 – (rowset=1019)

21 rows selected.

 

as shown above the explain plan shows full scan against database table stored in-memory.

** resources/refrences:

https://blogs.oracle.com/in-memory/base-level-198-ru

https://docs.oracle.com/en/database/oracle/oracle-database/20/ftnew/database-memory-base-level.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/INMEMORY_FORCE.html#GUID-1CAEDEBC-AE38-428D-B07E-6718A7225548

https://www.oracle.com/a/tech/docs/twp-oracle-database-in-memory-19c.pdf

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s