How to collect data from INMEMORY partition?

Question

I'm tuning Oracle 12c DB and trying to load some tables to INMEMORY partition. So, I'm altered 2 tables with CRITICAL PRIORITY. But, when I'm trying to SELECT some data from table in memory, it collects from general patition. I've got it from Autotrace.

What I'm doing wrong?

Answer 1

The IMCO(Inmemory Coordinator) process wakes up every two minutes and checks to see if any population tasks needs to be completed. So, querying a table immediately after enabling it for inmemory doesn't ensure that query to be served from inmemory.

Objects are populated into the IM column store either in a prioritized list immediately after the database is opened or after the tables are scanned (queried) for the first time.

For example, if we enable table tab1, tab2, tab3 for inmemory:

alter table tab1 inmemory priority medium;
alter table tab3 inmemory priority high;
alter table tab2 inmemory priority critical;

These tables get into inmemory when:

1. IMCO process picks up these tables and loads them into inmemory area (in the order of priority higest to lowest: tab2, tab3 and tab1)
2. If we perform select query on any of the tables (example: select * from tab1) before the IMCO process kicks in (in every 2 mins)

To know if a table/partition is loaded completely into inmemory you can query v$im_segments view like following:

select owner, segment_name, partition_name, segment_type, bytes, 
bytes_not_populated, populate_status from v$im_segments;

So, to answer your question:

1. Ensure the table is loaded into inmemory by querying v$im_segments
2. If the table is not loaded perform select query on it to make it loaded into inmemory area
3. Get query plan for the select query on the table, it should show INMEMORY as part of the plan

Check this whitepaper for more details.