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

Oracle Database 12.1.0.2 has been released

Oracle has released Oracle database version 12.1.0.2 and it has many new features, and a major one every body is waiting for (in-memory).

The release is a “full instillation” so no need to install first 12.1.0.1

**other features in the new release are:

  • Improvements to Oracle Multi-tenant
  • Advanced Index Compression
  • Zone Maps
  • Approximate Count Distinct
  • Attribute Clustering
  • Full Database Caching
  • Rapid Home Provisioning

this new “Enterprise Edition” is certified on Linux and Solaris platforms so far.

a very good paper released by Oracle for the new in-memory feature can be found here:

http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

Oracle Blog Reference & instillation link reference:

https://blogs.oracle.com/UPGRADE/entry/oracle_database_12_1_0

 

as an Oracle DBA Excited  🙂

cheers !