RE: Question regarding CR blocks
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "bialekr@xxxxxxxxx" <bialekr@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Jun 2008 10:11:36 -0400
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@xxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Robert Bialek
Sent: Wednesday, June 25, 2008 7:50 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
