consistent reads and unique key updates

From: Ls Cheng <exriscer_at_gmail.com>
Date: Wed, 25 Apr 2012 00:06:01 +0200
Message-ID: <CAJ2-Qb_gmFpROrcabvD-iifdpQ_Gm8v=Y92-p8+3OezeULJE9w_at_mail.gmail.com>

Hi
While doing some tests to see if there are any anormal behaviour in a batch process I noticed something in my test case which I cannot explain regarding consistent read and undo applying.

I have an EMP table with this block distribution:

select

    dbms_rowid.rowid_to_absolute_fno(rowid, 'LSC', 'EMP') ABS_FNO,
    dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
    dbms_rowid.rowid_block_number(rowid) BLOCKNO,
    dbms_rowid.rowid_row_number(rowid) ROWNO,

    empno, ename, deptno
from emp
order by deptno, dbms_rowid.rowid_block_number(rowid), dbms_rowid.rowid_row_number(rowid)

   ABS_FNO REL_FNO BLOCKNO ROWNO EMPNO ENAME DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ----------

         5          5      45044          2       7839 KING               10
         5          5      45046          1       7934 MILLER             10
         5          5      45045          1       7876 ADAMS              20
         5          5      45046          0       7902 FORD               20
         5          5      45047          0       7369 SMITH              20
         5          5      45048          0       7566 JONES              20
         5          5      45044          0       7782 CLARK              30
         5          5      45044          1       7788 SCOTT              30
         5          5      45045          0       7844 TURNER             30
         5          5      45045          2       7900 JAMES              30
         5          5      45047          1       7499 ALLEN              30
         5          5      45047          2       7521 WARD               30
         5          5      45048          1       7654 MARTIN             30
         5          5      45048          2       7698 BLAKE              30

So the data is scattered in blocks 45044 to 45048. There is a PK on empno.

In session 1, I run these queries:

*Session 1:*
select *

   from emp
  where empno = 7934 -- data block 45046  order by empno;

select name, value
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name in ('data blocks consistent reads - undo records applied', 'consistent gets')
order by name;

and got this output

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

consistent gets                                                          23
data blocks consistent reads - undo records applied                       0

Then in session 2 I update empno 7902 whose row is in same data block as empno 7934, after the update I was expecting undo record applied.

*Session 2:*
update emp
set sal = 2000
where empno = 7902 -- data block 45046
;

I ran following again in session 1:

select *

   from emp
  where empno = 7934
 order by empno;

select name, value
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name in ('data blocks consistent reads - undo records applied', 'consistent gets')
order by name;

NAME                                                                  VALUE


---------------------------------------------------------------- ----------

consistent gets                                                          25
data blocks consistent reads - undo records applied                       0

---> cero

data blocks consistent reads - undo records applied is 0, I was expecting 1 since the data block 45046 has been modified, if I force the query to do FTS the undo applied stat gets increased by 1.

I think I probably have some problem with my consistent reads/multiversioning understanding. Anyone can throw some lights?

Thanks

--

http://www.freelists.org/webpage/oracle-l Received on Tue Apr 24 2012 - 17:06:01 CDT