ORACLE IN-MEMORY transaction journal or buffer cache ?

Oracle in-memory white paper stated that “When a DML statement changes a row in an object that is populated into the IM column store, the corresponding entries for that row is marked stale in the IMCU and a copy of the new version of the row is added to the in-memory transaction journal.”

“When a query with a newer SCN is executed against the object, it will read all of the entries for the columns in the IMCU except the stale entries. The stale entries will be retrieved either from the transaction journal or from the base table (buffer cache).

UPDATE 30/03/2017 : It’s seem to be a documentation bug the new doc state : “The stale entries will be retrieved from the base table (i.e. row store).”

Lets try some example and observe :

Test Environment :

  • ORACLE database 12.1.0.2
  • ORACLE EL 6.5

1. Create a table and load it in the IN-MEMORY store :

create table read_consis inmemory no memcompress as select * from dba_objects;

2. Check the status of the table :

Capture2

3. Run some query and observe :

select count(*) from read_consis where object_name=’test’;

4. From another session run snapper :

Capture3

5. Use systemtap and the script developed by Luca Canali to trace consistent reads http://externaltable.blogspot.com/2014/09/systemtap-into-oracle-for-fun-and-profit.html :

Capture4

6. Dump the data blocks :

Capture5

Observation : There is no stale entries , all the data was read from IN-MEMORY the 3 consistent gets was for the segment header.

7. Let’s now disable IN-MEMORY re-population  and do some updates (Update,Deletes) to invalidated some rows (stales)

Capture7

Observation : The running session allocated space on the private journal (IM space private journal bytes allocated) before the commit and then deallocated it (IM space private journal bytes freed) .We can also see that we have two stale rows now on the IN-MEMORY store.There is also a shared journal (Stats : IM space shared journal segments freed,etc) but it’s not used in our case.

8. Let’s now run a query and check from where the two rows will be located (transaction journal or the buffer cache)

  select count(*) from read_consis  where object_name=’test’;

9. Run snapper from another session :

Capture10

10. Trace consistent read from another session :

Capture11

11. Dump the new block :

Capture15

Observation : 3 of  4 consistent reads observed was for the segment header and the later was for a data block.Also the stats “IM scan rows cache” indicate that two rows was read from the cache from a single block “IM scan blocks cache”.

Conclusion : After doing different tests (Different size of IM store,size of rows,etc) none of them used the transaction journal to access the stale rows and all the stale rows was accessed from the buffer cache.( I have also used different trace events like  alter session set events ‘trace [IM_journal] disk=medium’)

Maybe i missed some things but until now i cannot figure out when the transaction table is used for finding the stale rows. If you know when please share 😀

2 thoughts on “ORACLE IN-MEMORY transaction journal or buffer cache ?

  1. Updated data wasn’t flushed out from buffer cache. Would you like to give a try to this :-

    1-> Update some good amount of data from table
    2-> Flush the SGA (don’t commit yet). Oracle will be forced to maintain the transaction entries
    3-> Commit

    And then if you execute query with higher SCN, I believe transaction journal might be read.

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