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

Last updated on AUGUST 26, 2016

Applies to:

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

Symptoms

 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');
commit;

select * from im_test;


select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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

 

Changes

 Nil

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms