In this post I will discuss and demonstrate how oracle creates CR (consistent read) blocks.
Read consistency implementation in Oracle
Whenever a user queries data, he is to be displayed the data as of the time/SCN# when he queried it.
If the desired block is not in the buffer,
The block is read from the disk (status=xcur)
If clone(s) of the block are already there in buffer
The clone just later than the desired time is selected.
Undo is applied to the clone so as to make it consistent with the time/SCN# of the query (status=cr).
If the block is in the buffer and is dirty and no clones are there
A copy(clone) of the current block is made.
Undo is applied to the clone so as to make it consistent with the time/SCN# of the query (status=cr)
DML is always performed on the current copy of the block (Status=xcur).
Whenever an update is made to the block,
If the current block is in the buffer (status = xcur)
A clone of the current block is made (status=cr)
Update is performed on the current block (status=xcur)
Else (the desired block is not in the buffer)
The block is read from the disk (status=xcur)
A clone of the block is also made (status=cr)
Update is performed on the current block (status=xcur)
As multiple updates/queries are issued for the same block, multiple CR blocks of the same block may be created. Since all the CR clones of the same block are on the same chain, the processes searching for the block will take long time to scan the chain and there will be contention for CBC latch. Hence Oracle has limited the maximum no. of CR clones of a block to 6 (one current (xcur) and 5 CR clones). But it is a soft limit in the sense that if more than 5 processes are simultaneously updating different rows in the same block, more than 6 CR blocks may be created. The maximum no. of CR clones of a block can be modified by an undocumented parameter _db_block_max_cr_dba.
Let us see a practical demonstration of the above concept:
– Create simple table whose one row occupies one block
HR1> drop table t1 purge;
create table t1
(c1 int, c2 char(2000), c3 char(2000), c4 char(2000));
– Insert one row
HR1> insert into t1 values(1, ‘x’, ‘x’, ‘x’);
commit;
– Get the block address and browse X$BH view for that block.
HR> select dbms_rowid.rowid_relative_fno(rowid) as file#,
dbms_rowid.rowid_block_number(rowid) as block#
from t1;
File# Block#
—— ——
4 550
– Check in x$bh that We have one CURRENT block(state=xcur). CURRENT block is always one and only one.
– 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: 550
old 7: dbablk = &block_no
new 7: dbablk = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
Now, let’s see how consecutive DML statements generate CR blocks.
– update and monitor cr block
– 1st update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Check in x$bh that We have one CURRENT block(state=xcur) and on cr clone (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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24244872 0 0 0 0 T1
– 2nd update
HR> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24244981 0 0 0 0 T1
4 550 1 cr 24244872 0 0 0 0 T
– 3rd update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24245051 0 0 0 0 T1
4 550 1 cr 24244981 0 0 0 0 T1
4 550 1 cr 24244872 0 0 0 0 T1
– 4th update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24245082 0 0 0 0 T1
4 550 1 cr 24245051 0 0 0 0 T1
4 550 1 cr 24244981 0 0 0 0 T1
4 550 1 cr 24244872 0 0 0 0 T1
– 5th update
HR> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Note that another cr clone has been created. Each clone has a different SCN# as of the time when it was created.
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24245122 0 0 0 0 T1
4 550 1 cr 24245082 0 0 0 0 T1
4 550 1 cr 24245051 0 0 0 0 T1
4 550 1 cr 24244981 0 0 0 0 T1
4 550 1 cr 24244872 0 0 0 0 T1
6 rows selected.
– 6th update
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
– Note that new cr clone has not been created because the limit of the 6 (1 xcur and 5 cr) has been reached.
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24245135 0 0 0 0 T1
4 550 1 cr 24245122 0 0 0 0 T1
4 550 1 cr 24245082 0 0 0 0 T1
4 550 1 cr 24245051 0 0 0 0 T1
4 550 1 cr 24244981 0 0 0 0 T1
6 rows selected.
– Check the value of the parameter _db_block_max_cr_dba
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 6 F F F Maximum Allowed Number of CR buffers per dba
– Increase the value of the parameter to 8
SYS>alter system set “_db_block_max_cr_dba”= 8 scope=spfile;
shu immediate;
startup;
– Issue update seven times and check that 2 more clones have been created
HR1> update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
update t1 set c2 = ‘y’, c3 = ‘y’, c4 = ‘y’ where c1 = 1;
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24246329 0 0 0 0 T1
4 550 1 cr 24246328 0 0 0 0 T1
4 550 1 cr 24246327 0 0 0 0 T1
4 550 1 cr 24246325 0 0 0 0 T1
4 550 1 cr 24246324 0 0 0 0 T1
4 550 1 cr 24246323 0 0 0 0 T1
4 550 1 cr 24246322 0 0 0 0 T1
8 rows selected.
– Issue a query from another session of HR
HR2>select * from t1;
– Check that a new clone has been created and an earlier one with
scn#=24246322 has been overwritten by scn#=24246435 and undo tablespace has been read to create this one. (uba_file=3,uba_blk=174754)
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 = 550
DBARFIL DBABLK CLASS STATE CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———- ———- ——————–
4 550 1 cr 24246435 0 3 174754 1022 T1
4 550 1 xcur 0 0 0 0 0 T1
4 550 1 cr 24246329 0 0 0 0 T1
4 550 1 cr 24246328 0 0 0 0 T1
4 550 1 cr 24246327 0 0 0 0 T1
4 550 1 cr 24246325 0 0 0 0 T1
4 550 1 cr 24246324 0 0 0 0 T1
4 550 1 cr 24246323 0 0 0 0 T1
—————————————————————————–
In my next post , I will demonstrate that _db_block_max_cr_dba is a soft limit and CR clones more than its value can be created.
—————————————————————————————————-
Related links:
———————–