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;
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 in ( ‘140000’,’170000′) and STATUS = ‘NEW’ ;
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)

Capture 150

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

Capture 151

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 :

Capture 152

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)  :

Capture 200

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/

Capture 201

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

Capture 202

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

Capture 203

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

Capture 204

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 ?

Capture 205

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

And then !

Capture 206

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 .

Capture 207

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 :

Capture 208

That’s it 😀

8 thoughts on “Write consistency and DML restart

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s