In my earlier post consistent reads in oracle part-I, I had demonstrated that no. of CR clones of a block are dictated by the undocumented parameter _db_block_max_cr_dba. I had also mentioned that this is a soft limit in the sense that CR clones more than _db_block_max_cr_dba may also be created.
Let us see a practical demonstration of the above concept.
OVERVIEW:
- Create a table whose one block has 10 rows.
- Update one row in each of 10 sessions
- Check that no. of CR clones created > _db_block_max_cr_dba
IMPLEMENTATION:
– Create simple table whose one block has 10 rows
HR1> drop table t1 purge;
create table t1
(c1 int, c2 char(700));
HR1> var j number;
begin
:j := 1;
for i in 1..10 loop
insert into t1 values(:j, ‘x’);
:j := :j+1;
end loop;
commit;
end;
/
col c1 for 99
select c1 from t1;
C1
—
1
2
3
4
5
6
7
8
9
10
– Get the block address and browse X$BH view for that block.
HR1> select dbms_rowid.rowid_relative_fno(rowid) as file#,
dbms_rowid.rowid_block_number(rowid) as block#
from t1;
FILE# BLOCK#
———- ———-
4 551
4 551
4 551
4 551
4 551
4 551
4 551
4 551
4 551
4 551
– Check that value of the parameter _db_block_max_cr_dba = 8
SYS>SELECT
a.ksppinm “Parameter”,
decode(p.isses_modifiable,’FALSE’,NULL,NULL,NULL,b.ksppstvl) “Session”,
c.ksppstvl “Instance”,
decode(p.isses_modifiable,’FALSE’,’F’,’TRUE’,’T’) “S”,
decode(p.issys_modifiable,’FALSE’,’F’,’TRUE’,’T’,’IMMEDIATE’,’I’,’DEFERRED’,’D’) “I”,
decode(p.isdefault,’FALSE’,’F’,’TRUE’,’T’) “D”,
a.ksppdesc “Description”
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
ORDER BY a.ksppinm;
Enter value for 1: _db_block_max_cr_dba
old 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%&1%’)
new 12: AND UPPER(a.ksppinm) LIKE UPPER(‘%_db_block_max_cr_dba%’)
Parameter Session Instance S I D Description
————————————————– —————————- ——————————————————- – – – ——————————————————————————————
_db_block_max_cr_dba 8 F F F Maximum Allowed Number of CR buffers per dba
– Check in x$bh that We have one CURRENT block(state=xcur).
Enter file#/block# found above when prompted
SYS>select b.dbarfil, b.dbablk, b.class,
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’) as state,
cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
(select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
dbablk = &block_no;
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 551
old 7: dbablk = &block_no
new 7: dbablk = 551
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 551 1 xcur 0 0 0 0 0 T1
4 551 1 free 0 0 0 0 0
Now, let’s see how consecutive DML statements generate CR blocks.
— update and monitor cr block
— Start 10 HR sessions
— Update one row in each of 10 sessions
HR1> update t1 set c2 = ‘y’ where c1 = 1;
/
HR2> update t1 set c2 = ‘y’ where c1 = 2;
/
HR3> update t1 set c2 = ‘y’ where c1 = 3;
/
HR4> update t1 set c2 = ‘y’ where c1 = 4;
/
HR5> update t1 set c2 = ‘y’ where c1 = 5;
/
HR6> update t1 set c2 = ‘y’ where c1 = 6;
/
HR7> update t1 set c2 = ‘y’ where c1 = 7;
/
HR8> update t1 set c2 = ‘y’ where c1 = 8;
/
HR9> update t1 set c2 = ‘y’ where c1 = 9;
/
HR10> update t1 set c2 = ‘y’ where c1 = 10;
/
– Check in x$bh that We have one CURRENT block(state=xcur) and > 8 (_db_block_max_cr_dba) cr clones (status=cr)
SYS>/
Enter value for file_no: 4
old 6: where dbarfil = &file_no and
new 6: where dbarfil = 4 and
Enter value for block_no: 550
old 7: dbablk = &block_no
new 7: dbablk = 551
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 551 1 cr 24257659 0 3 596 1025 T1
4 551 1 cr 24257658 0 3 181 1023 T1
4 551 1 cr 24257657 0 3 181 1023 T1
4 551 1 cr 24257651 0 3 181 1023 T1
4 551 1 cr 24257649 0 3 172517 954 T1
4 551 1 cr 24257648 0 3 181 1023 T1
4 551 1 cr 24257647 0 3 181 1023 T1
4 551 1 cr 24257645 0 3 176654 1063 T1
4 551 1 cr 24257639 0 3 181 1023 T1
4 551 1 cr 24257638 0 3 181 1023 T1
4 551 1 cr 24257635 0 3 279569 1005 T1
4 551 1 cr 24257634 0 3 181 1023 T1
4 551 1 cr 24257633 0 3 181 1023 T1
4 551 1 cr 24257622 0 3 175596 916 T1
4 551 1 cr 24257621 0 3 181 1023 T1
4 551 1 cr 24257620 0 3 181 1023 T1
4 551 1 xcur 0 0 0 0 0 T1
17 rows selected.
– CLEANUP –
HR1> rollback;
HR2>rollback;
HR3> rollback;
HR4> rollback;
HR5> rollback;
HR6> rollback;
HR7> rollback;
HR8> rollback;
HR9> rollback;
HR10> rollback;
drop table t1 purge;
SYS>alter system set “_db_block_max_cr_dba”= 6 scope=spfile;
startup force;
——————————————————————————-
Related links:
—————–
Hi Madam,
Need to clear one doubt why udpate is creating CR blocks. CR block should be created if other session trying to access the modified block then oracle should create the CR from modified/dirty block.
Thanks
-Jamsher