In this Blog, we would try to set apart root block modification from DML operations so that root block modification is performed at first, and then subsequent DML are executed.
The purpose is only for illustrative, experimental and feasibility studies in the hope that Oracle can refine the idea.
Test code is based on Blogs:
Index Service ITL and Recursive Transaction
Cache Buffer Chains Latch Contention Case Study-2: Reverse Primary Key Index
Oracle 19.18 Wait Event 'latch: cache buffers chains' P3: block number
Note: Tested in Oracle 19.18.
1. Test Setup
--=================== Test Setup in session-1 ===================--
drop table test_tab purge;
create table test_tab (id number, val number);
drop index test_tab#r;
create unique index test_tab#r on test_tab(id) reverse;
alter table test_tab add constraint test_tab#r primary key (id);
truncate table test_tab;
insert into test_tab select level, -level from dual connect by level <= 4905;
commit;
-- Test Code to insert one row to trigger leaf block split, and root block modification
create or replace procedure root_modi_cbc_latch_workaround (p_insert_id number, p_allow_seconds number := 5) as
type t_name_tab is table of dba_segments.segment_name%type;
type t_file_tab is table of dba_segments.header_file%type;
type t_block_tab is table of dba_segments.header_block%type;
l_name_tab t_name_tab := t_name_tab();
l_file_tab t_file_tab := t_file_tab();
l_block_tab t_block_tab := t_block_tab();
l_cnt number := 0;
l_scn number;
l_scn_ts timestamp;
l_modi_ts timestamp;
l_msg_str varchar2(200);
l_modi boolean := false;
begin
-- collect indexes and their root files/root blocks
-- @TODO store those in a package variable to workaround dba_segments performance
with sq as (select /*+ materialize */ index_name from dba_indexes where table_name = 'TEST_TAB')
select /*+ leading(q) use_nl(s) */ q.index_name, header_file, header_block + 1
bulk collect into l_name_tab, l_file_tab, l_block_tab
from sq q, dba_segments s where s.segment_name = q.index_name;
dbms_session.sleep(p_allow_seconds); -- simulate appliaction
-- check if there are any root block modifications. If yes, rollback, re-insert. If not, exit loop.
for i in 1 ..2 loop
dbms_output.put_line(systimestamp||'--- Run ' ||i||'--- set savepoint');
savepoint sp;
insert into test_tab values(p_insert_id, -p_insert_id);
l_modi_ts := cast (systimestamp as timestamp);
if i = 1 then
for k in 1..l_name_tab.count loop
select count(1) into l_cnt from v$bh
where file#=l_file_tab(k) and block# = l_block_tab(k) and status = 'xcur'; --and dirty = 'Y'
select max(hscn_wrp*power(2, 32) + hscn_bas) into l_scn from x$bh
where l_cnt > 0 and state = 1 and hscn_wrp > 0 and file#=l_file_tab(k) and dbablk=l_block_tab(k); --state=1, 'xcur'
if l_scn > 0 then
l_scn_ts := scn_to_timestamp(l_scn); -- scn_to_timestamp session cached. run test in one new session.
end if;
l_msg_str := systimestamp||'--- Run ' ||i||'--- Index '||l_name_tab(k)||' Root (File = '||l_file_tab(k)||', Block = '||l_block_tab(k)||'), '||
'l_cnt = '||l_cnt||', l_scn = '||l_scn||', l_scn_ts = '||l_scn_ts;
dbms_output.put_line(l_msg_str);
-- scn_to_timestamp: the usual precision of the result value is 3 seconds.
if l_scn_ts between l_modi_ts - numtodsinterval(p_allow_seconds, 'SECOND') and l_modi_ts then
dbms_output.put_line('*** Root modified at '||l_modi_ts||', Root Index Modi at '||l_scn_ts ||
', rollback to make only root modi trx performed, then re-insert.');
l_modi := true;
rollback to savepoint sp; -- only rollback main trx, root modi recursive trx committed (un-rollbackable)
exit; -- one index root modi found, finish modi recursive trx, exit internal loop
end if;
end loop;
if not l_modi then
dbms_output.put_line('*** Root not modified');
exit;
end if;
end if;
end loop;
end;
/
2. Test Run
--========== Test Run in session-2 (to repeat test, run "Test Setup" at first) ==========--
set serveroutput on
alter session set nls_timestamp_format ='YYYY*MON*DD HH24:MI:SS.FF9';
select scn_to_timestamp(ora_rowscn), t.* from test_tab t where id >= 4904 order by id;
exec root_modi_cbc_latch_workaround(4906);
exec root_modi_cbc_latch_workaround(4907);
select scn_to_timestamp(ora_rowscn), t.* from test_tab t where id >= 4904 order by id;
rollback;
3. Test Output
--=================== Test Output in session-2 ===================--
12:01:34 SQL > select scn_to_timestamp(ora_rowscn), t.* from test_tab t where id >= 4904 order by id;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ID VAL
-------------------------------- ---------- ----------
2023*SEP*20 12:01:17.000000000 4904 -4904
2023*SEP*20 12:01:17.000000000 4905 -4905
2 rows selected.
12:01:34 SQL > exec root_modi_cbc_latch_workaround(4906);
20-SEP-2023 12:01:42 +02:00--- Run 1--- set savepoint
20-SEP-2023 12:01:42 +02:00--- Run 1--- Index TEST_TAB#R Root (File = 22, Block = 35467), l_cnt = 1,
l_scn = 12776808521751, l_scn_ts = 2023*SEP*20 12:01:41.000000000
*** Root modified at 2023*SEP*20 12:01:42.271069000, Root Index Modi at 2023*SEP*20 12:01:41.000000000,
rollback to make only root modi trx performed, then re-insert.
20-SEP-2023 12:01:42 +02:00--- Run 2--- set savepoint
Elapsed: 00:00:07.80
12:01:42 SQL > exec root_modi_cbc_latch_workaround(4907);
20-SEP-2023 12:01:50 +02:00--- Run 1--- set savepoint
20-SEP-2023 12:01:50 +02:00--- Run 1--- Index TEST_TAB#R Root (File = 22, Block = 35467), l_cnt = 1,
l_scn = 12776808521752, l_scn_ts = 2023*SEP*20 12:01:41.000000000
*** Root not modified
Elapsed: 00:00:07.86
12:01:50 SQL > select scn_to_timestamp(ora_rowscn), t.* from test_tab t where id >= 4904 order by id;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ID VAL
-------------------------------- ---------- ----------
2023*SEP*20 12:01:17.000000000 4904 -4904
2023*SEP*20 12:01:17.000000000 4905 -4905
2023*SEP*20 12:01:17.000000000 4906 -4906
2023*SEP*20 12:01:17.000000000 4907 -4907
4 rows selected.
12:01:50 SQL > rollback;
Rollback complete.
