ORACLE 12C in-memory : Part 4 (IMCU Stale entries)

“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).

Ref : https://docs.oracle.com/database/121/REFRN/refrn10358.htm#REFRN10358

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s