IMU and CR Reads
IMU and Private strands are interesting new entries in Oracle 10g. These to private buffer to a transaction will help to avoid many expensive latches both in buffer cache and log buffer. There are tons of data available in the net about these subjects and many experts spoke about that. In theory during a transaction the redo will place in private redo strand and undo change vector will be placed in IMU. I going to demonstrate, what will happen if any other session do a CR read when the transaction is in progress?
Typically any single block CR read will end up 3 consistent gets –
1) Touch the ‘CURRENT’ block
2) Read the undo
3) CR block
My test table is a single row – column table. From Session 1, did an UPDATE transaction
SQL> desc abc Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER SQL> update abc set c1=3; 1 row updated.
From session 2, fired a simple SELECT forcing a CR read and Oracle returned the results under 2 CR reads – not 3!
SQL> select * from abc where rowid='AAAP00AAFAAAR0VAAA'; C1 ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 1340935522 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| ABC | 1 | 3 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets => 2 CR gets 0 physical reads 0 redo size 520 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) rows processed
And after some time, when re-run the SELECT from the second session, Oracle used 3 consistent gets to return the results.
SQL> select * from abc where rowid='AAAP00AAFAAAR0VAAA' C1 ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 1340935522 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| ABC | 1 | 3 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets => 3 CR gets 0 physical reads 108 redo size 520 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
It is IMU helping Oracle to avoid undo block read for the first SELECT. There could be two possibilities for 2 consistent get; I am not sure what is the correct one.
- Oracle make a CR copy of the CURRENT block and use IMU to rollback the transaction
- The CURRENT mode block was never got the new update; rather it will get updated only when the IMU flush happen. So, there is no need to rollback the transaction, rather just copy the CURRENT block contents to the CR block.
V$MYSTAT tells me what Oracle is doing. During 2 consistent gets, Oracle is bypassing the undo block read and using IMU to build the CR block using either of the above method and recorded this statistics under “IMU CR rollbacks”.
SQL> select b.name, a.value from v$mystat a, v$statname b where a.statistic#=b.statistic# and b.name like '%IMU%' NAME VALUE ---------------------------------------------------------------- ---------- IMU commits 0 IMU Flushes 0 IMU contention 0 IMU recursive-transaction flush 0 IMU undo retention flush 0 IMU ktichg flush 0 IMU bind flushes 0 IMU mbu flush 0 IMU pool not allocated 0 IMU CR rollbacks 1 => IMU rollbacks IMU undo allocation size 0 IMU Redo allocation size 0 IMU- failed to get a private strand 0 13 rows selected.
And you can see an active IMU in buffer in x$ktifp table and the column KTIFPTXFLG tell us the status. If the status is 1, then the transaction is active in the IMU and if the status is 7, change vector for undo is already flushed out of IMU and clubbed with the private redo strands and placed in the public redo strand. As long as the IMU status is 1, Oracle was able to avoid UNDO block read for a CR block build and once the status is 7, Oracle need to read the UNDO blocks which will lead to 3 consistent gets. I believe, the incremental checkpoint or a time out forcing the IMU flush for an uncommited transaction.
SQL> select KTIFPTXFLG,ADDR,KTIFPNO, KTIFPSTA, KTIFPXCB xctaddr, 2 to_number(KTIFPUPE, 'XXXXXXXXXXXXXXXX')- 3 to_number(KTIFPUPB, 'XXXXXXXXXXXXXXXX') ubsize, 4 (to_number(KTIFPUPB, 'XXXXXXXXXXXXXXXX')- to_number(KTIFPUPC, 'XXXXXXXXXXXXXXXX'))*-1 ubusage, 5 to_number(KTIFPRPE, 'XXXXXXXXXXXXXXXX')- to_number(KTIFPRPB, 'XXXXXXXXXXXXXXXX') rbsize, 6 (to_number(KTIFPRPB, 'XXXXXXXXXXXXXXXX')-to_number(KTIFPRPC, 'XXXXXXXXXXXXXXXX'))*-1 rbusage, 7 KTIFPPSI,KTIFPRBS,KTIFPTCN 8* from x$ktifp KTIFPTXFLG ADDR KTIFPNO KTIFPSTA XCTADDR UBSIZE UBUSAGE RBSIZE RBUSAGE KTIFPPSI KTIFPRBS KTIFPTCN ---------- ---------------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 7 00007F1C37989BD0 0 0 00 64000 0 0 0 65535 2147483647 15287 1 00007F1C37989BD0 1 0 000000011BF88848 64000 1384 126464 1300 3 34 856 7 00007F1C37989BD0 2 0 00 64000 0 0 0 65535 2147483647 257 7 00007F1C37989BD0 3 0 00 64000 0 0 0 65535 2147483647 80 7 00007F1C37989BD0 4 0 00 64000 0 0 0 65535 2147483647 285 7 00007F1C37989BD0 5 0 00 64000 0 0 0 65535 2147483647 1140 7 00007F1C37989BD0 6 0 00 64000 0 0 0 65535 2147483647 733 7 00007F1C37989BD0 7 0 00 64000 0 0 0 65535 2147483647 20 6 00007F1C37989BD0 8 0 00 64000 0 0 0 65535 2147483647 126805 0 00007F1C37989BD0 9 0 00 64000 0 0 0 65535 2147483647 0 0 00007F1C37989BD0 10 0 00 64000 0 0 0 65535 2147483647 0
Either the background process or a reading session can trigger an IMU flush. Once the IMU flush is done, the status inx$ktifp will updated as 7 and further CR reads will use UNDO blocks even though the transaction is not committed.
KTIFPTXFLG ADDR KTIFPNO KTIFPSTA XCTADDR UBSIZE UBUSAGE RBSIZE RBUSAGE KTIFPPSI KTIFPRBS KTIFPTCN ---------- ---------------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 7 00007F1C37988B98 0 0 00 64000 0 0 0 65535 2147483647 15303 7 00007F1C37988B98 1 1 000000011BF88848 64000 1384 126464 1300 3 37 859 7 00007F1C37988B98 2 0 00 64000 0 0 0 65535 2147483647 257 7 00007F1C37988B98 3 0 00 64000 0 0 0 65535 2147483647 80 7 00007F1C37988B98 4 0 00 64000 0 0 0 65535 2147483647 285 7 00007F1C37988B98 5 0 00 64000 0 0 0 65535 2147483647 1140 7 00007F1C37988B98 6 0 00 64000 0 0 0 65535 2147483647 733 7 00007F1C37988B98 7 0 00 64000 0 0 0 65535 2147483647 20 6 00007F1C37988B98 8 0 00 64000 0 0 0 65535 2147483647 126960 0 00007F1C37988B98 9 0 00 64000 0 0 0 65535 2147483647 0 0 00007F1C37988B98 10 0 00 64000 0 0 0 65535 2147483647 0 0 00007F1C37988B98 11 0 00 64000 0 0 0 65535 2147483647 0 0 00007F1C37988B98 12 0 00 64000 0 0 0 65535 2147483647 0
At the end I did not get answer for a question. During the IMU CR read, Oracle worked normally.
SQL> select * from abc where rowid='AAAP00AAFAAAR0VAAA' ; C1 ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 1340935522 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| ABC | 1 | 3 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size => No redo 520 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
But, after flushing IMU, Oracle started generating 108 bytes redo for the CR read from the second session – not just once rather for every CR read.
SQL> select * from abc where rowid='AAAP00AAFAAAR0VAAA' C1 ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 1340935522 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY USER ROWID| ABC | 1 | 3 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 108 redo size => 108 bytes redo 520 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
May be, I need to ask this question to JL!
Thanks to Anita Gupta for helping me to research for this post.
Thomas Saviour's Blog 