Re: Question regarding CR blocks

Robert,

I don't have the research handy, but I specifically ran some experiments like this to identify contention for free buffers in a buffer cache. My scenario was that following a metadata update (metadata that's read by all application sessions frequently) that was not committed (so that the update could be verified for correctness first), there was huge contention for free buffers in the buffer cache. With about 600 concurrent sessions, what I saw was that each session was creating its own CR version of each changed block. I couldn't explain why Oracle thought that was the right thing to do, but it was clear that it was happening. So, your observed limit of 5 doesn't match with what I saw in those tests. Our buffer cache was 6Gb and the metadata change only modified about 50Mb worth of data. So, my rough estimate was that we might see up to 500Mb worth of blocks needed in the buffer cache to support current + CR versions. What we saw instead was huge amounts (upwards of 3Gb) and so much contention for free blocks (which caused lots of DBWR activity to make room) that the system essentially slowed to a crawl. As soon as the commit was entered to commit the metadata change, everything went back to normal and performance was restored to normal levels.

Took me a fair amount of effort to explain it since the metadata updates are made via a package and no one knew what really happened under the covers. Made sense once we read the package code.

Dan

Robert Bialek wrote:
> 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