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

New Features for Oracle Database 12cR2 In-Memory

Oracle 12cR2 release introduced many new features for the in-memory database technology, the following is some of the new features introduced:

Dynamically increase In-Memory area on the fly

Starting in 12.2, it is possible to increase the size of the In-Memory area on the fly, by increasing the INMEMORY_SIZE parameter using ALTER SYSTEM command. Also, worht mentioning is that The INMEMORY_SIZE parameter must be increased by 128MB or more in order for this change to take effect. However, It is not possible to shrink the size of the In-Memory area on the fly.

In-Memory FastStart

Enabling In-Memory FastStart (IM FastStart) the system will checkpoints the IMCUs from the IM column store to the FastStart area on disk. Consequnetly, when database restarts takes place, data is populated via the FastStart area rather than from the base tables.

Note that FAST_START_TBS is a dedicated tablespace configured for FastStart,is allowed for each PDB or non-CDB.the FastStart tablespace should be at least twice the size of the INMEMORY_SIZE setting.

CREATE TABLESPACE FAST_START_TBS DATAFILE
‘D:\APP\EMAD\VIRTUAL\ORADATA\ORACLE\FASTSTARTD01.DBF’ SIZE 400M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

SQL> BEGIN dbms_inmemory_admin.faststart_enable(‘FAST_START_TBS’); END;

So, basically The FastStart area defines what data is loaded when the database reopens. Population of data is still controlled by the “priority” settings, it won’t affect it.

SQL> SELECT * FROM V$INMEMORY_FASTSTART_AREA;

inmem1

In-Memory Expressions

In-Memory Expressions provide the ability to materialize commonly used expressions in the In-Memory column store. Materializing these expressions will improve the query performance by preventing the re-computation of the expression for every row. Also, it enables us to take advantage of all of the In-Memory query performance optimization when we access them.

* Analytic queries often contain complex expressions in the select list or where clause predicates that need to be evaluated for every row processed by the query

* The evaluation of these complex expressions can be very resource intensive and time consuming

* In-Memory Expressions greatly improve the performance of analytic queries that use computationally intensive expressions and access large data sets

* In-Memory expressions speed queries of large data sets by pre-computing computationally intensive expressions

In-Memory Virtual Columns

user-defined virtual columns can now be populated in the In-Memory column store. Virtual columns will be materialized as they are populated and since the expression is evaluated at population time it can be retrieved repeatedly without re-evaluation.

To illustrate:

SQL> alter system set inmemory_virtual_columns=ENABLE scope=spfile;

inmem2

CREATE TABLE HR.EMPLOYEE_SALES (
employee_id NUMBER(6) PRIMARY KEY ,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(8,2)
);

The target query against the table will be:

SQL> select employee_id,last_name,salary,(salary/12) as monthly_sal, 3*(salary+2000) as bonus from HR.EMPLOYEE_SALES order by bonus;

SQL> ALTER TABLE HR.EMPLOYEE_SALES ADD monthly_sal AS (salary/12);
SQL>ALTER TABLE HR.EMPLOYEE_SALES ADD bonus AS (3*(salary+2000));

SQL> ALTER TABLE HR.EMPLOYEE_SALES INMEMORY PRIORITY HIGH;

SQL>DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS(‘CURRENT’);
SQL> EXEC DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

SQL>SELECT OWNER, TABLE_NAME, COLUMN_NAME, SQL_EXPRESSION
FROM DBA_IM_EXPRESSIONS;