“The more stale entries in an IMCU,the slower the scan of IMCU will become” (Oracle in-memory white papers)
In fact the stale row will be read from the transaction journal (if it’s there) or from the buffer cache.
The question is how much stale entries do we have ? How to reduce them if required ?
For the first question there is an interesting not yet documented v$ view that we can use: (V$IM_SMU_HEAD)
Here is an example query to check for the percentage of stale rows per segment :
select OBJD,sum(TOTAL_ROWS),sum(INVALID_ROWS),sum(INVALID_BLOCKS),(sum(INVALID_ROWS)/sum(TOTAL_ROWS))*100 pct_invalid from V$IM_SMU_HEAD group by OBJD order by 5 desc;
If we want to keep our system more up to date and reduce the number of stale rows we can increase the value of (trickle repopulation) “INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT” (at the expense of more background CPU).