This is the second blog post about the new in-memory feature of oracle database 12c.For the preceding post Part 1
UPDATE 10/11/2014 : Flushing the buffer cache before testing to check for physical reads
In this post i will try to demonstrate how read consistency is maintained in the in-memory column store and determine how much work is done on behalf. Please read the white paper about in-memory before (you can find a link in the bottom of the page).
Let’s begin by creating our testing table and loading it on the in-memory column store :
| create table read_consis inmemory no memcompress as select * from dba_objects; select count(*) from read_consis ; 91612 rows (1572 before the high water mark) |
check if the table is populated :
| hatem@ORCL> select * from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID ————————– ———– ———- ————————– ———- 1MB POOL 82837504 10485760 DONE 0 64KB POOL 16777216 131072 DONE 0hatem@ORCL> SELECT v.*,v.bytes / v.inmemory_size comp_ratio FROM v$im_segments v ; OWNER SEGMENT_NAME |
After that i will disable population, repopulation and trickle repopulation to avoid cleaning of stale entry from the in-memory column store by setting this parameter : (there is other way to do this like playing with undocumented parameter to change the thresholds ” _inmemory_repopulate_threshold_rows_percent” ” _inmemory_repopulate_threshold_blocks_percent” etc)
| alter system set inmemory_max_populate_servers=0; |
Open three sessions : (session 3 is for stats collections using snapper tool thanks to Tanel Poder)
Session 1)
| set transaction read only; select object_name from read_consis where object_name=’TEST_INMEMORY3′; |
(Will return 1 rows by the way)
check stats
Session 2)
Update 3 rows and commit. If we don’t commit then the rows in the column store are not staled.(the stats collected won’t change)
The rows updated are in 2 different data blocks.
Flush the buffer cache and then fill it with random data to avoid prefetching of data.
Session 1)
| select object_name from read_consis where object_name=’TEST_INMEMORY3′; |
check stats
Session 3)
Compare stats
First execution :
| ————————————————————————– STATISTIC , DELTA, ————————————————————————– Requests to/from client , 3, opened cursors cumulative , 2, user calls , 4, session logical reads , 1541, DB time , 1, non-idle wait count , 4, session uga memory , -136, physical read total IO requests , 1, physical read total bytes , 8192, cell physical IO interconnect bytes , 8192, consistent gets , 3, consistent gets from cache , 3, consistent gets pin , 3, consistent gets pin (fastpath) , 2, logical read bytes from cache , 24576, physical reads , 1, physical reads cache , 1, physical read IO requests , 1, physical read bytes , 8192, free buffer requested , 1, shared hash latch upgrades – no wait , 1, calls to kcmgcs , 5, file io wait time , 23, table scans (short tables) , 1, table scans (IM) , 1, table scan rows gotten , 91612, IM scan CUs memcompress for query low , 1, session logical reads – IM , 1538, IM scan bytes in-memory , 3409651, IM scan bytes uncompressed , 10570758, IM scan CUs columns accessed , 1, IM scan CUs columns theoretical max , 18, IM scan rows , 91612, IM scan rows valid , 91612, IM scan rows projected , 1, IM scan CUs split pieces , 1, IM scan CUs predicates received , 1, IM scan CUs predicates applied , 1, IM scan segments minmax eligible , 1, session cursor cache hits , 2, parse count (total) , 2, execute count , 2, |
Second execution :
| ————————————————————————– STATISTIC , DELTA, ————————————————————————– Requests to/from client , 3, opened cursors cumulative , 2, user calls , 4, session logical reads , 1543, non-idle wait count , 7, enqueue requests , 1, enqueue releases , 1, physical read total IO requests , 3, physical read total bytes , 57344, cell physical IO interconnect bytes , 57344, consistent gets , 7, consistent gets from cache , 7, consistent gets pin , 5, consistent gets pin (fastpath) , 3, consistent gets examination , 2, logical read bytes from cache , 57344, physical reads , 7, physical reads cache , 7, physical read IO requests , 3, physical read bytes , 57344, consistent changes , 2, free buffer requested , 9, CR blocks created , 2, physical reads cache prefetch , 4, shared hash latch upgrades – no wait , 3, calls to kcmgcs , 5, file io wait time , 49, data blocks consistent reads – undo records applied , 2, rollbacks only – consistent read gets , 2, table scans (short tables) , 1, table scans (IM) , 1, table scan rows gotten , 91758, table scan blocks gotten , 2, IM scan CUs memcompress for query low , 1, session logical reads – IM , 1536, IM scan bytes in-memory , 3409651, IM scan bytes uncompressed , 10570758, IM scan CUs columns accessed , 1, IM scan CUs columns theoretical max , 18, IM scan rows , 91612, IM scan rows valid , 91609, IM scan rows excluded , 3, IM scan rows projected , 1, IM scan rows cache , 3, IM scan blocks cache , 2, IM scan CUs split pieces , 1, IM scan CUs predicates received , 1, IM scan CUs predicates applied , 1, IM scan segments minmax eligible , 1, session cursor cache hits , 2, parse count (total) , 2, execute count , 2, bytes sent via SQL*Net to client , 565, bytes received via SQL*Net from client , 552, SQL*Net roundtrips to/from client , 3, |
Let’s interpret this stats : We have 91612 rows,3 of them are staled and read form the cache into 2 different block(CR blocks created).We have 6 additional physical reads 2 data blocks (of our updated rows),1 undo blocks,1 undo header and some prefetched block (This is only an assumption, i should have used tracing 10046 and 10298 events to check for the correct type of block reads but i didn’t )
So for every DML change in a table that is populated in the columns store the corresponding row is marked stale. There is others parameters that control staleness of the an IMCU (In-Memory Compression Unity) like :
| _inmemory_pct_inv_blocks_invalidate_imcu 100 _inmemory_pct_inv_rows_invalidate_imcu 50 |
Oracle in-memory white paper state that “The stale entries will be retrieved either from the transaction journal or from the base table (buffer cache).”
I think that i must dig deeper to check when the transaction journal is used because in my case data is read from the buffer cache every time they are stalled.(May be it’s only used to speed repopulation) UPDATE 10/04/2015 : For more info check my new blog post https://mahmoudhatem.wordpress.com/2015/04/08/oracle-in-memory-transaction-journal-or-buffer-cache/
Also Oracle in-memory white paper state “The original entries in the IMCU are not immediately replaced in order to provide read consistency and maintain data compression. Any transaction executing against this object in the IM column store that started before the DML occurred, needs to see the original version of the entries” but in my test case although the transaction started before the DML we are not using the stale row (May be i am wrong but this what my test shows)
The outcome is other question without answers !! (May be in the next post)
Feel free to leave advice and some help 😀
Ref : http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html
Ref: http://jonathanlewis.wordpress.com/2014/08/27/in-memory-consistency
Hatem Mahmoud's blog 
[…] For other parts : Part1 , Part2 […]