DML restart and tracked columns

Which columns are rely tracked by oracle to grantee write consistency and if needed trigger a DML restart when using the default statement-level consistency ?

Sometimes you may see strange ,unexpected behavior and write consistency bugs because not all the columns that are supposed to be tracked ,are really tracked !

Just check the following articles :

A bug from 2011 (still there in 18c) : http://db-blog.web.cern.ch/blog/franck-pachot/2018-09-oracle-write-consistency-bug-and-multi-thread-de-queuing

A Bug from 2006  (still there in 12.2) : http://blog.sqlora.com/en/merge-and-ora-30926-part-2-or-differences-in-write-consistency-between-update-and-merge/

So how to know it the columns that are supposed to be tracked really are ? I’m going only to treat UPDATE and MERGE statement here.

Continue reading

Oracle Linux UEK 5 : DTrace and the PID provider

The UEK5 kernel is out and as usually¬† there is many enhancements and new features included in it ! One of the notable new feature is the dtrace PID provider Which allow¬† setting “function boundary probes on user space functions, and to probe most arbitrary instructions within user space functions”¬† check uek5-features-dtrace

Let’s give it a try :

Continue reading

Oracle trace events hunting : Events annotations/Events Sniffing

Based on my previous blog posts :

I created two files that can help us to quickly extract Events checked in specific oracle core function :

Using those files we can for example :

Continue reading

Oracle trace events hunting : Undocumented events/Filling the gaps

In my last blog post Oracle trace events hunting : dbgdChkEventIntV i talked about how we can extract events that are checked in specific oracle core function by analyzing the arguments passed to dbgdChkEventIntV function. I used for that a mapping file called dbgdChkEventIntV_event_list.txt   (Basically mapping EventId to actual Event Name)

When we analyze the mapping file we observe something :

Capture 150

The different events seems to have a sequential Event ID¬† (stored in an array ?)¬† ! So what is stored in slot 1160002,116004 ,116007 etc ? Let’s check !

Continue reading

Oracle trace events hunting : dbgdChkEventIntV

Few days ago i published a blog post talking about write consistency & DML restart¬† . During my investigation i was interested in the function “dmltrace” and i noticed that this function was instrumented with the dbk*/dbg*¬† debug functions introduced in 11g.

Capture 151

The question is how to enable this tracing facility ?  Which events are checked by that function ?

We know how to extract the trace events number from the old ksdpec function (kernel service debug internal errors parser post event and check trigger condition using¬†http://orafun.info/ of course ūüėĬ† ) thank to Dennis Yurichev see here and here.¬†

We also know how to extract it from dbkdChkEventRdbmsErr (DB kernel debug check event of RDBMS error) thanks to Yong Huang see here.

But here the function is instrumented differently !

Continue reading

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.

Continue reading