Question regarding CR blocks

From: Robert Bialek <bialekr_at_gmail.com>
Date: Wed, 25 Jun 2008 13:50:20 +0200
Message-Id: <1214394620.20786.5.camel@ltbirt61.trivadis.com>

Hello *,

I'm a bit confused with CR block management in db cache...

Please have a look at the following test case:

---

Session 1

---

SQL> startup

#obj=53254, DBABLK=61458 -> table T

SQL> select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where DBABLK=61458 and obj=53254;

no rows selected

SQL> update t set p1=p1+1;

1 row updated.

SQL> select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where DBABLK=61458 and obj=53254;

CR_XID_USN CR_XID_SLT CR_XID_SQN OBJ TCH STATE ---------- ---------- ---------- ---------- ---------- ----------

         0          0          0      53254          1          1
         0          0          0      53254          1          3

After the UPDATE I see one XCUR and one CR block in cache. What is the reason to create the CR block without having a request for a consistent read from a select?

---

Session 2

---

Oracle could now reuse the existing CR block to satisfy the consistent read, but instead is creating additional CR blocks (5 seems to be a limit). Why?

SQL> select * from t;

        P1

---

         1

SQL> select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where DBABLK=61458 and obj=53254;

CR_XID_USN CR_XID_SLT CR_XID_SQN OBJ TCH STATE ---------- ---------- ---------- ---------- ---------- ----------

         5          0        532      53254          1          3
         5          0        532      53254          1          3
         0          0          0      53254          1          1
         0          0          0      53254          1          3

SQL> select * from t;

        P1

---

         1

SQL> select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where DBABLK=61458 and obj=53254;

CR_XID_USN CR_XID_SLT CR_XID_SQN OBJ TCH STATE ---------- ---------- ---------- ---------- ---------- ----------

         5          0        532      53254          1          3
         0          0          0      53254          1          1
         5          0        532      53254          1          3
         5          0        532      53254          1          3
         0          0          0      53254          1          3

SQL> select * from t;

        P1

---

         1

SQL> select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where DBABLK=61458 and obj=53254;

CR_XID_USN CR_XID_SLT CR_XID_SQN OBJ TCH STATE ---------- ---------- ---------- ---------- ---------- ----------

         5          0        532      53254          1          3
         5          0        532      53254          1          3
         0          0          0      53254          1          1
         5          0        532      53254          1          3
         5          0        532      53254          1          3
         0          0          0      53254          1          3

6 rows selected.

Thank you very much in advance,
Robert

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 25 2008 - 06:50:20 CDT