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.

Write consistency : Not all restarts are equals

When writing my blog post Write consistency and DML restart i noticed something that had motivated me to dig deeper. And here we go !

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 :

Oracle trace events hunting : Events annotations/Events Sniffing

Based on my previous blog posts :

• Oracle trace events hunting : dbgdChkEventIntV
• Oracle trace events hunting : Undocumented events/Filling the gaps
• Oracle trace events hunting : KST tracing/ X$TRACE

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

• oracle_function_to_event_mapping18c.txt
• oracle_function_to_event_mapping18c.sed

Using those files we can for example :

Oracle trace events hunting : KST tracing/ X$TRACE

This  is my third blog post about Oracle trace events hunting.For the previous one please check part1 and part2.

When we take a look at the TRACE event which is the Main event to control the UTS tracing  we notice that it have a memory option which mean activate the tracing in-memory (If possible)

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 :

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 !

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.

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 !

Slow database startup and unified auditing

Few days ago i was notified of a very slow database startup time after someone installed a new application. Here is the result obtained in a clone environment  :

5 Min ! That’s great … let’s see what’s going on :

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.

POUG : The power of linux advanced tracer slides

What i have to say ? I’m still amazed of how awesome was this POUG conference like usually. A lot of geeks coming  from all around the world sharing knowledge ,learning new stuffs , meeting new and old friends and having fun 😀

So thanks to the POUG team for letting me be part of this great experience again 🙂