Update Restart and new Active Undo Extent

Blog:  That old restart problem again...
(http://tkyte.blogspot.com/2010/04/that-old-restart-problem-again.html)
presents a new case of update restart, which demonstrates that restart can also happen within a single session, not necessarily two sessions with one being blocked by a update from another session. Inherently single session restart is distinct from the normal two sessions' restart  in which all rows till first detected changed row are restarted, however in single session restart, only occasionally a few rows are restarted.

Referring to Oracle documentation, it is not evident to get a clue about such behavior. Sometimes I hear the warning: "Don't go searching for it in the documentation" (Blog:  NO_DATA_NEEDED - something I learned recently in http://tkyte.blogspot.com/2010/04/nodataneeded-something-i-learned.html).

So it is worth of pursuing some research on it. Following modified script shows certain coincidence of new active undo_extent and restart.

I slightly modified the code in Blog:  That old restart problem again...: by adding a helper function to count the active undo extents, and some lines to print out this count.

create or replace function get_undo_extent_cnt return number
as
  l_cnt number;
begin   
 select count(*) into l_cnt from dba_undo_extents where status = 'ACTIVE';
 return l_cnt;
end;
/

create or replace package pacepack
as
  type array is table of number index by varchar2(40);
  g_data                         array;
  g_cnt                           number;
  g_restart_cnt                number;
  g_undo_extent_cnt       number;
end pacepack;
/

drop table delete_data;

create table delete_data as
select owner, object_name, rownum id
from  dba_objects
where rownum  < 53001;
    
create or replace trigger delete_data_bt
  before delete or update on delete_data
  for each row
begin
  if ( pacepack.g_data.exists(rowidtochar(:old.rowid)))
  then
    pacepack.g_restart_cnt := pacepack.g_restart_cnt + 1;
   
    dbms_output.put_line( 'doing "' || :old.rowid ||
                           '" again was called ' || pacepack.g_cnt );
    dbms_output.put_line('-- Restart#: ' || pacepack.g_restart_cnt ||
                            ', Undo Extent Count: ' || get_undo_extent_cnt);
  else
    pacepack.g_data(rowidtochar(:old.rowid)) := 1;
  end if;
      
  pacepack.g_cnt        := pacepack.g_cnt + 1;
end;
/

Assume you are the only one on the database, then run the same script:

declare
  cursor l_delete_csr is
    select *
      from delete_data
       for update;
  l_cnt number := 0;
begin
  pacepack.g_data.delete;
  pacepack.g_cnt         := 0;
  pacepack.g_restart_cnt := 0;
        
  for l_delete_row in l_delete_csr loop
   update delete_data
      set owner       = lower(owner) 
   where current of l_delete_csr;
   l_cnt := l_cnt + 1;
  end loop;
   
  dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt ||
                         ' local count = '   || l_cnt );     
 end;
/

output:

doing "AAAwCHAAGAADPBVABH" again was called 4667
-- Restart#: 1, Undo Extent Count: 12
doing "AAAwCHAAGAADPCKAC0" again was called 16017
-- Restart#: 2, Undo Extent Count: 13
doing "AAAwCHAAGAADPDIABc" again was called 27282
-- Restart#: 3, Undo Extent Count: 14
doing "AAAwCHAAGAADPEEAAK" again was called 38547
-- Restart#: 4, Undo Extent Count: 15
doing "AAAwCHAAGAADPFAAAc" again was called 49812
-- Restart#: 5, Undo Extent Count: 16
trigger count = 53005 local count = 53000

So for each restart, a new active Undo Extent is created. If you run it repeatedly, you will see the same behavior.

As I tested in 10g and 11g (undo_management=AUTO), this output is confirmed,  but I would be careful to have any earlier conclusion before Oracle says.

The same behavior is also observed for the single row update.

declare
  cursor l_delete_csr (p_id number) is
    select *
      from delete_data where id = p_id
       for update;
  l_cnt number := 0;
begin
  pacepack.g_data.delete;
  pacepack.g_cnt         := 0;
  pacepack.g_restart_cnt := 0;
 
  for i in 1..53000 loop       
    for l_delete_row in l_delete_csr(i) loop
     update delete_data
        set owner       = lower(owner) 
     where current of l_delete_csr;
     l_cnt := l_cnt + 1;
    end loop;
  end loop;
 
  dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt ||
                         ' local count = '   || l_cnt );     
 end;
/

Output:

doing "AAAuFnAAGAAFli9AB7" again was called 9106
-- Restart#: 1, Undo Extent Count: 2
doing "AAAuFnAAGAAFljiABt" again was called 15537
-- Restart#: 2, Undo Extent Count: 3
doing "AAAuFnAAGAAFlkLAA7" again was called 21938
-- Restart#: 3, Undo Extent Count: 4
doing "AAAuFnAAGAAFlkvAC2" again was called 28339
-- Restart#: 4, Undo Extent Count: 5
doing "AAAuFnAAGAAFllWABm" again was called 34740
-- Restart#: 5, Undo Extent Count: 6
doing "AAAuFnAAGAAFll8ACR" again was called 41141
-- Restart#: 6, Undo Extent Count: 7
doing "AAAuFnAAGAAFlmjABj" again was called 47542
-- Restart#: 7, Undo Extent Count: 8
trigger count = 53007 local count = 53000

The Blog: Three new "hints" (http://rwijk.blogspot.com/2009/10/three-new-hints.html) shows a new  Oracle 11.2.0.1 hint: RETRY_ON_ROW_CHANGE, which I would call it block level restart, in comparing to the traditional row level (maybe this hint already exists in 10g since it is mentioned in 10g  "Bug 6082734  Dump (kdr9igtl) accessing a row from an IOT").

One interesting code in this Blog is that it uses after trigger to catch the restart.