Why is TABLE not POPULATING in V$IM_SEGMENTS even after scanning?

Question

So I have added a table to inmemory and have scanned the table after that. But it is still not appearing in V$IM_SEGMENTS. In EXPLAIN PLAN it is showing INMEMORY ACCESS FULL. So not sure if it is using the column store.

Did these:

ALTER TABLE INMEMORY; SELECT * FROM ;

SELECT * FROM V$IM_SEGMENTS;

no rows

Answer 1

To start with inmemory_size should be around 100M. Following command should show appropriate size value for parameter inmemory_size:

show parameter inmemory_size

Loading of table segments into inmemory area kicks when there is a full scan on the table or inmemory priority clause is other than none, so we need to be sure the select query you had done went through table access full path. So, one more way to initiate full table scan is to do select count(*) from table.

Or you can use populate procedure from dbms_inmemory package to load the table manually into inmemory area.

Example usage (for user inmem_user, table t1):

exec dbms_inmemory.populate('INMEM_USER','T1');

One more thing to consider here with respect to querying v$im_segments is; bytes_not_populated and populate_status columns also to be queried for correctness. When v$im_segments returns rows, bytes_not_populated should be 0 and populate_status should be COMPLETED.

More information about inmemory population can be foune here