Home > Oracle, Performance > IMU and CR Reads

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.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: