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

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
Symptoms
Changes
Cause
Solution


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.