My Oracle Support Banner

Execution Plan Shows that Query Uses the “In-Memory Option” but Query is not Executed “In-Memory“ (Doc ID 1950831.1)

Last updated on MARCH 03, 2022

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


 A query against a table that is configured to be stored in-memory is not using in-memory even though the execution plan shows that it is.


create table im_test(cola number, colb varchar2(10)) inmemory;
insert into im_test values(1, 'IMTEST');

select * from im_test;

select * from table(dbms_xplan.display_cursor);

SQL_ID  3a13xhtpdvztt, child number 1
select * from im_test

Plan hash value: 3183909076

| Id  | Operation                  | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT           |         |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS INMEMORY FULL| IM_TEST |     3 |    60 |     1   (0)| 00:00:01 |   <==  Note that the execution plan shows we are using in-memory

select segment_name,bytes Disk, inmemory_size,populate_status from v$im_segments;        <==  However no in memory segments are created, so it is not using in-memory

no rows selected





To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.