read Archives - ORACLE IN ACTION

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;