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://www.oracle.com/a/tech/docs/twp-oracle-database-in-memory-19c.pdf
Geodata Master 