Constraint Check - Object_ID dependency? buffer clean? dirty? or both clean - dirty?
Date: Tue, 28 Jan 2020 00:08:06 +0530
Message-ID: <CAP-RywwLTyNLy9rGRLi5pUytMm75Th6F3Xj7AWg5H5MnHh=q4g_at_mail.gmail.com>
Hi,
While checking the path of the insert statement, I came across this rather strange behaviour which is very interesting in the sense there are soo many aspects that can go wrong instead of generic behavior.
consider the following:
create table students (roll number, name varchar2(20), mark1 number, mark2
number, mark3 number);
create index mark1_idx on students (mark1); create index mark2_idx on students(mark2); create index mark3_idx on students(mark3);alter table students add constraint students_pk primary key(roll); create unique index test_idx on students(roll, name); //may not make sense now.. but it does soon....
once we execute the above statements and observe the object_ids of each objects. the results will be as follows:
76189 STUDENTS
76190 MARK1_IDX
76191 MARK2_IDX
76192 MARK3_IDX
76193 STUDENTS_PK
76194 TEST_IDX
we can see the students_pk and test_idx have higher object_ids.. now lets
populate this table with some random data.
insert into students select rownum, dbms_random.string(0,20),
round(dbms_random.value()*100), round(dbms_random.value()*100),
round(dbms_random.value()*100)
from dual connect by level < 100;
commit;
now that we have some randomdata... let try to insert a row that causes the constraint violation....
SQL> insert into students values (1,'vishnu',12,12,12);
insert into students values (1,'vishnu',12,12,12)
*
ERROR at line 1:
ORA-00001: unique constraint (VISHNU.STUDENTS_PK) violated
the sQL trace is as follows:
WAIT #139996487045632: nam='Disk file operations I/O' ela= 27
FileOperation=2 fileno=7 filetype=2 obj#=76189 tim=4609606740
WAIT #139996487045632: nam='db file sequential read' ela= 451 file#=7 block#=362 blocks=1 obj#=76189 tim=4609607222 WAIT #139996487045632: nam='db file sequential read' ela= 310 file#=7 block#=361 blocks=1 obj#=76189 tim=4609607703 WAIT #139996487045632: nam='db file sequential read' ela= 283 file#=7 block#=360 blocks=1 obj#=76189 tim=4609608052 WAIT #139996487045632: nam='db file sequential read' ela= 254 file#=7 block#=416 blocks=1 obj#=76189 tim=4609608365 WAIT #139996487045632: nam='db file sequential read' ela= 232 file#=7 block#=403 blocks=1 obj#=76190 tim=4609608782 WAIT #139996487045632: nam='db file sequential read' ela= 227 file#=7 block#=407 blocks=1 obj#=76190 tim=4609609058 WAIT #139996487045632: nam='db file sequential read' ela= 220 file#=7 block#=395 blocks=1 obj#=76191 tim=4609609423 WAIT #139996487045632: nam='db file sequential read' ela= 210 file#=7 block#=399 blocks=1 obj#=76191 tim=4609609679 WAIT #139996487045632: nam='db file sequential read' ela= 243 file#=7 block#=387 blocks=1 obj#=76192 tim=4609609988 WAIT #139996487045632: nam='db file sequential read' ela= 382 file#=7 block#=391 blocks=1 obj#=76192 tim=4609610485 WAIT #139996487045632: nam='db file sequential read' ela= 419 file#=7 block#=379 blocks=1 obj#=76193 tim=4609611028 WAIT #139996487045632: nam='db file sequential read' ela= 400 file#=7 block#=383 blocks=1 obj#=76193 tim=4609611485
from the object_ids we can clearly see the it starting modifying the blocks in the segment according to the OBJECT_ID.. and once it reached the unique index it started rolling back... which can be confirmed using the following statistics:
NAME VALUE
-------------------------------------------------- ----------redo size 2872
rollback changes - undo records applied 4
following a commit or rollback, the status of the blocks are follows:
OBJD BLOCK# D STATUS
---------- ---------- - ----------
76189 423 Y xcur
76189 360 N xcur
76189 362 N xcur
76189 361 N xcur
76190 407 Y xcur
76190 403 N xcur
76191 395 N xcur
76191 399 Y xcur
76192 391 Y xcur
76192 387 N xcur
76193 379 N xcur
76193 383 N xcur
clearly the blocks are modified, the changes are rolled back as well... if a particular block is marked as dirty it should be written to the disk by the DBWR... did the blocks really change?
things become even more interesting:
I took a dump of the leaf block to see if the modifications are really done.... the header portion indicates the blocks are basically same... even the space offsets..
seg/obj: 0x129a0 csc: 0x0000000000723d81 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00180 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000f.015.0000084c 0x0100bca0.008f.05 C--- 0 scn
0x0000000000723d0c
0x02 0x000f.005.0000084c 0x0100bcb3.008f.13 C--- 0 scn 0x0000000000723d17
Leaf block dump
header address 12188024932=0x2d6768064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2
kdxcosdc 1
kdxconro 498
kdxcofbo 1032=0x408
kdxcofeo 2048=0x800
kdxcoavs 1028
kdxlespl 0
kdxlende 0
kdxlenxt 29360517=0x1c00185
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
seg/obj: 0x129a0 csc: 0x0000000000723d81 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1c00180 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000f.015.0000084c 0x0100bca0.008f.05 C--- 0 scn
0x0000000000723d0c
0x02 0x000f.005.0000084c 0x0100bcb3.008f.13 C--- 0 scn 0x0000000000723d17
Leaf block dump
header address 140423079796836=0x7fb6cbca2064 kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2
kdxcosdc 1
kdxconro 498
kdxcofbo 1032=0x408
kdxcofeo 2048=0x800
kdxcoavs 1028
kdxlespl 0
kdxlende 0
kdxlenxt 29360517=0x1c00185
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
XID, UBA indicating the previous transactions, not the current one which
failed which makes sense in a way why store failed/rolled back transaction
and UBA on the header....?
I did even check the STRACE output of the DBWn process to see whether if it
actually writes this dirty block (which is actually not dirty)...
offset=93495296 it basically writes the block to the disk...
more interestingly the unique index leaf block is never modified, since constraint violation - a valid case (this is not the case if the index is not a unique index i mean non unique index policing an unique constraint).... One way we can understand that post rollback since Oracle may not know precisely know whether an entry or different entries may have changed or in the mean while a different session can modify the same block, and modifications are allowed to a block with XCUR state....
it just marks the block as dirty... in fact the block in this case is identical to the on disk version of the block... even this can be explained can be due to cleanup of commit records (commit cleanup)
I have so many questions here..
1. Why is the constraint policing not done before... (i mean more damage
if the gap is far between the object_id of the table and primary key or
unique index object_id).
one way to answer is that due to the inherent object_id population using a
sequence and these number increment upon each object creation and are not
reused...
considering an optimal situation
create table students (roll primary key);
the object_id of the students is less than the object_id of the primary key
even in this case the only after the table blocks are modified and
subsequent constraint violation... the changes are rolled back again...
The only thing I don't understand is
why implement a sequence for object_id population and table's object_id to be lower than unique or primary key constraint (one way to explain what if i create table without constraints and we cannot create an index before the table so object_id increasing),,,
The only recommendation I can come up with is make sure that the unique indexes are created immediately following the table creation. but for the object_id part i mean unnecessary modifying and rollback... i dont think Oracle will ever fix it since it is an exceptional condition and occurs rarely...
Can someone please tell me whether If there is any other thing regarding the object_id that i missing here...
Thanks,
Vishnu
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 27 2020 - 19:38:06 CET