RE: Question regarding CR blocks

Hi Robert,

For the first part, when you do:
Update t set p1=p1+1;

Even though there's no where clause, Oracle will still do a consistent read portion of the update, to identify a self-consistent set of rows that will be updated. This will happen using consistent mode gets, so, CR blocks will appear in the buffer cache.

I did a slightly different test:
Create table my_test nologging as select * from dba_objects where 1=0; Select object_id from dba_objects where object_type='TABLE' and object_name = 'MY_TEST'; Alter system flush buffer_cache;
select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where obj=<object_id_of_my_test>;

insert /*+ append */ into my_test select * from dba_objects; select CR_XID_USN,CR_XID_SLT,CR_XID_SQN,OBJ,TCH,STATE from x$bh where obj=<object_id_of_my_test>;

Here, you'll see only current mode blocks. That's because in the case of direct load insert, there are no consistent mode gets required to provide a consistent view of the rows to be updated. Since these are all new rows being loaded into brand new blocks, there is no consistent get operation for a direct load insert.

As to your second case, first, your observation that CR mode copies of a specific block is limited to 5 is correct, and is governed by ' _db_block_max_cr_dba', which defaults to 5 in recent versions of Oracle, and you probably don't want to mess with it.

Hope that helps,

-Mark

--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak_at_proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robert Bialek
Sent: Wednesday, June 25, 2008 7:50 AM
To: oracle-l_at_freelists.org
Subject: Question regarding CR blocks

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


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