CR image fabrication - bug? excessive?

I have encountered a very weird situation today... we can easily replicate this... looks like it makes the buffer cache really inefficient especially for short tables that are modified and selected a lot.. eg: tables that contain shares...it goes both ways.
- if the transactions are not committed in time.

• if the selects against the table is very huge...

create table test(roll number primary key, name varchar2(20)); insert into test select rownum, dbms_random.string(0,20) from dual connect by level < 1000;
commit;
_at_gatherstats test

Session A:
update test set name='VISHNU', roll = 10001 where roll = 1; don't commit;

Session B:

select * from test where roll = 10;
select * from test where roll = 20;
select * from test where roll = 30;
select * from test where roll = 40;

session C:
select * from test where roll = 50;
select * from test where roll = 60;

if we see the the number of CR blocks generated: OBJECT_NAME FILE_ID BLOCK STATE DIR COUNT(*)

-------------------- ---------- ---------- ---------- --- ----------
TEST      7        367 xcur       YES   1
SYS_C007633      7        371 xcur       NO   1
SYS_C007633      7        372 xcur       YES   1
SYS_C007633      7        372 cr      NO   4
SYS_C007633      7        373 xcur       YES   1
TEST      7        367 cr      NO   4

OBJECT_NAME FILE_ID      BLOCK STATE      DIR   COUNT(*)
-------------------- ---------- ---------- ---------- --- ----------
TEST      7        367 xcur       YES   1
SYS_C007633      7        371 xcur       NO   1
SYS_C007633      7        372 xcur       YES   1
SYS_C007633      7        372 cr      NO   6
SYS_C007633      7        373 xcur       YES   1
TEST      7        367 cr      NO   6

we end up generating 6 CR blocks for each index block and table blocks: if we see the SCN of each individual CR block... they are different indicating the SCN at the time SQL statement is issued which is supposed to be considering the read committed isolation level....

       OBJ DBABLK CR_SCN_BAS CR_SCN_WRP TCH STATE ---------- ---------- ---------- ---------- ---------- ----------

40  372       0  0     1 xcur
     74516  367 2130034  0     1 cr
     74516  367 2130033  0     1 cr
     74516  367 2130017  0     1 cr
     74516  367 2130015  0     1 cr
     74516  367 2130013  0     1 cr
     74516  367 2130011  0     1 cr
     74516  367       0  0     4 xcur
     74517  372 2130034  0     1 cr
     74517  372 2130033  0     1 cr
     74517  372 2130017  0     1 cr
     74517  372 2130015  0     1 cr
     74517  372 2130013  0     1 cr
     74517  372 2130011  0     1 cr
     74517  372       0  0     4 xcur

From a certain aspect things may appear proper as the SCNs are maintained at the block level and not at the row level... Once the ITL slot is open it goes to the transaction table to see if the transaction is still open... just in case.. as this can happen also due to delayed commit clean up and finds whether the transaction is still open, if it is still open, it clones the block and rollbacks the changes using the information from the undo tablespace.... and the block is a CR block... the SCN is advanced for the CR block to the select SCN... but this happens over and over again...

during the entire process even though the block is modified as indicated by the block dumps...
row#0[1200] flag: ---D---, lock: 2, len=11, data:(6): 01 c0 01 5c 00 00 col 0; len 2; (2): c1 02
row#0[1200] flag: -------, lock: 0, len=11, data:(6): 01 c0 01 5c 00 00 col 0; len 2; (2): c1 02

the strange behavior is that even though buffers are modified the the buffers are not marked as dirty unless we commit or rollback the transaction... but the dirty flag can indicate a block may contain uncommitted changes...

The query used is as follows:
select
a.object_name,b.dbarfil"FILE_ID",b.dbablk"BLOCK",decode(b.state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi',9,'memory',10,'mwrite',11,'donated',12,'protected',13,'securefile',14,'siop',15,'recckpt',16,'flashfree',17,'flashcur',18,'flashna')"STATE",decode(bitand(flag,1),0,'NO','YES')"DIRTY",count(*) from x$bh b,dba_objects a where b.obj=a.data_object_id and a.owner='VISHNU' group by
a.object_name,b.dbarfil,b.dbablk,decode(b.state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi',9,'memory',10,'mwrite',11,'donated',12,'protected',13,'securefile',14,'siop',15,'recckpt',16,'flashfree',17,'flashcur',18,'flashna'),decode(bitand(flag,1),0,'NO','YES');

select obj,dbablk,CR_SCN_BAS,CR_SCN_WRP ,tch ,decode( state,0,'free',1,'xcur',2,'scur',3,'cr',4,'read',5,'mrec',6,'irec',7,'write',8,'pi',9,'memory',10,'mwrite',11,'donated',12,'protected',13,'securefile',14,'siop',15,'recckpt',16,'flashfree',17,'flashcur',18,'flashna')"STATE" from x$bh where dbablk in (367,372);

the database version is 19c oct 19 psu..

this raises a different question...

       OBJ DBABLK CR_SCN_BAS CR_SCN_WRP TCH STATE NXT_REPL PRV_REPL ---------- ---------- ---------- ---------- ---------- ----------

---------------- ----------------
40  372       0  0     1 xcur  0000000091FD8D48 0000000091FD8A78
     74516  367 2130034  0     1 cr  00000000913F88F0 00000000913F8620
     74516  367 2130033  0     1 cr  00000000913F8A58 00000000913F8788
     74516  367 2130017  0     1 cr  00000000913F8BC0 00000000913F88F0
     74516  367 2130015  0     1 cr  00000000913F8D28 00000000913F8A58
     74516  367 2130013  0     1 cr  00000000913F8E90 00000000913F8BC0
     74516  367 2130011  0     1 cr  00000000913F8FF8 00000000913F8D28
     74516  367       0  0     4 xcur  0000000091FED388 0000000091FED0B8
     74517  372 2130034  0     1 cr  00000000913E1CB8 00000000913E19E8
     74517  372 2130033  0     1 cr  00000000913E1E20 00000000913E1B50
     74517  372 2130017  0     1 cr  00000000913E2258 00000000913E1F88
     74517  372 2130015  0     1 cr  00000000913E23C0 00000000913E20F0
     74517  372 2130013  0     1 cr  00000000913E2528 00000000913E2258
     74517  372 2130011  0     1 cr  00000000913E27F8 00000000913E23C0
     74517  372       0  0     4 xcur  0000000091FD9018 0000000091FD8D48

of we observe the NXT_REPL and PRV_REPL for these CR blocks they appear to skip the cr version of a block (prev SCN)... Not sure what to make out of this...

can someone please tell me whether I am missing anything here...

Thanks,
Vishnu

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