Write consistency and DML restart

Few weeks ago Tanel Poder published a great video talking about Oracle’s write consistency , DML restarts and demonstrating also how we can detect them.He also published a script that allow finding UPDATE/DELETE statements that hit the write consistency issue & have to restart under the hood. But as he stated it uses V$SQL_PLAN_MONITOR, so requires Diag+Tuning pack licenses.

The purpose of this blog post is to show another way to detect statements hitting the write consistency issue.

Test env : oracle 18c/OEL6/UEK4

Test script :

drop table DEMO;
create table DEMO (ID ,STATUS,NAME,SALT,SALT2, CREATED)
as select rownum,cast('NEW' as varchar2(10)),cast(null as varchar2(10)),cast(rpad('*',2000) as varchar2(2000)),cast(rpad('*',2000) as varchar2(2000)),sysdate+rownum/24/60 from xmltable('1 to 200000');

exec dbms_stats.gather_table_stats(null,'DEMO');

A modified version of Franck Pachot script  (as i was having fun with his example ) :p

And here is the test case i’am using :

Session 1	Session 2	Session 3 (Write consistency issue)
UPDATE DEMO SET STATUS = ‘HOLDING’ WHERE id = ‘140000’ and STATUS = ‘NEW’ ;
	UPDATE DEMO SET STATUS = ‘HOLDING’ WHERE id = ‘170000’ and STATUS = ‘NEW’ ;
		UPDATE DEMO SET STATUS = ‘HOLDING’ WHERE id in ( ‘140000’,’170000′) and STATUS = ‘NEW’ ;
commit;
	commit;
		Statement will restart multiple times  under the hood

I started by comparing oracle core functions execution flow of statement issued in session 1 (no restart needed) and statement issued in session 3 (restart needed) (I used intel pin tool debugtrace.so for tracing function execution)

In session 3 it seem that when the write consistency issue has been hit we called the “dmltrace” function

It seem that there is some tracing facility implemented in there so i took a look at oradebug doc COMPONENT (i should have started there :p) and i spotted this :

Ok let’s give it a try using :

alter session set events 'trace[DML]   disk=high ';
exec dbms_monitor.session_trace_enable;

Here is an extract from session 1 trace (no restart needed)  :

Ok we got something here !  “updThreePhaseExe” ! there is three phases … emmmm ok ! And we are running the NOT LOCKED phase.

And then “updaul” function is called  which is using my new favorite website (Thanks to Frits Hoogland and Kamil Stawiarski)  🙂  http://orafun.info/

The update is then completed without restart as we can see (str=1)

Here is an extract from session 3 trace (restart needed)  :

So the update started and is running in the “NOT LOCKED” phase.And then we see this :

After hitting the first write consistency issue the transaction is rolled back and the update is restarted. It’s now running in the LOCK phase and will go into SELECT FOR UPDATE mode as Tom Kyte explained here .

What happen next ?

After hitting the second write consistency issue the LOCK phase is restarted again.

And then !

Ok no more write consistency issue ! The update enter now the “ALL LOCKED” phase  and run the UPDATE against that locked set of data, thus ensuring this time that it can complete without restarting as Tom Kyte explained here .

The data is now updated and the DML statement has restarted 4 times (str=4).

Ok lets come back to our  initial question ! how we can find all statements hitting the write consistency issue ?

Based on the previous finding and other tests we can use the following :

alter system set events 'trace[DML] {callstack: fname dmlTrace} disk=high trace("DML restarted sqlid : %\n", sqlid())';

And here is a quick test using a delete statement in this case :

That’s it 😀