UNDO Practice

An expert is a person who has found out by his own painful experience all the mistakes that one can make in a very narrow field.
Niels Bohr

Book Oracle Core: Essential Internals for DBAs and Developers declared that change vector (the heart of redo and undo) is most important feature of Oracle (Page 5). While redo data(after image) is written to be forgotten, undo data(before image) is active over the life time of instance. Therefore it is worth of doing a couple of exercises on UNDO by following:

"The Beginners' Guide to Becoming an Oracle Expert" (Page 5).

All tests are done in Oracle 11.2.0.4.0 with undo_management=AUTO and db_block_size=8192.

UNDO dump

The first test code is to practice what one learned from the book (see the book for more details).

Run Test Code 1, output looks like:

------------ testhost_ora_25106_block_1.trc ------------
Start dump data blocks tsn: 1172 file#:863 minblk 2082662 maxblk 2082662
scn: 0x0824.e789f0b4 seq: 0x02 flg: 0x04 tail: 0xf0b40602
0x02   0x0078.01c.000006ba  0x00c0054a.03c8.01  ----    1  fsc 0x0000.00000000
bdba: 0x001fc766

------------ testhost_ora_25106_block_2.trc ------------
Start dump data blocks tsn: 1172 file#:863 minblk 2082663 maxblk 2082663
scn: 0x0824.e789f0b8 seq: 0x02 flg: 0x04 tail: 0xf0b80602
0x02   0x0078.01c.000006ba  0x00c0054b.03c8.01  ----    1  fsc 0x0000.00000000
bdba: 0x001fc767

------------ testhost_ora_25106_block_3.trc ------------
Start dump data blocks tsn: 1172 file#:863 minblk 2082659 maxblk 2082659
scn: 0x0824.e789f0bc seq: 0x02 flg: 0x04 tail: 0xf0bc0602
0x02   0x0078.01c.000006ba  0x00c0054c.03c8.01  ----    1  fsc 0x0000.00000000
bdba: 0x001fc763

------------ testhost_ora_25106_undo_1.trc ------------
Start dump data blocks tsn: 2 file#:3 minblk 1354 maxblk 1354
BH (0xc0f6fe38) file#: 3 rdba: 0x00c0054a (3/1354) class: 256 ba: 0xc013a000
scn: 0x0824.e789f0b4 seq: 0x01 flg: 0x04 tail: 0xf0b40201
xid: 0x0078.01c.000006ba  seq: 0x3c8 cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
rdba: 0x00c00549
op: C  uba: 0x00c00549.03c8.38

------------ testhost_ora_25106_undo_2.trc ------------
Start dump data blocks tsn: 2 file#:3 minblk 1355 maxblk 1355
BH (0xcef64d80) file#: 3 rdba: 0x00c0054b (3/1355) class: 256 ba: 0xce018000
scn: 0x0824.e789f0b8 seq: 0x02 flg: 0x04 tail: 0xf0b80202
xid: 0x0078.01c.000006ba  seq: 0x3c8 cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
rdba: 0x00c0054a
op: C  uba: 0x00c0054a.03c8.02

------------ testhost_ora_25106_undo_3.trc ------------
Start dump data blocks tsn: 2 file#:3 minblk 1356 maxblk 1356
BH (0xd2fe5930) file#: 3 rdba: 0x00c0054c (3/1356) class: 256 ba: 0xd2d4c000
scn: 0x0824.e789f0bc seq: 0x02 flg: 0x04 tail: 0xf0bc0202
xid: 0x0078.01c.000006ba  seq: 0x3c8 cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
rdba: 0x00c0054b
op: C  uba: 0x00c0054b.03c8.02

1. Above output showed that all UNDO blocks are linked with "rdba" field, from last to first
    (0x00c0054c -> 0x00c0054b -> 0x00c0054a).

2. The modified ITL in each Data block is also saved in its UNDO block's field marked with:
    "op: C  uba:". So all modified information in Data block are recorded in Before Image.

UNDO Size

We can make an UNDO test by updating one block many times, and querying an un-modified block in a different session. The purpose is trying to extend UNDO length (Space) to infinitive.

Open one session, update first row 1000 times, without commit:

 begin
   for i in 1..1000 loop
     update test_tab set name = rpad('a', 3000, i) where id = '1';
   end loop;
 end;
 /

Open a second session, read second row:

 select value from v$sysstat where name = 'data blocks consistent reads - undo records applied';
 select count(*) from test_tab t where id = '2';
 select value from v$sysstat where name = 'data blocks consistent reads - undo records applied';

The output:

 7401710432
 1
 7401711432

shows that there are 1000 (7401711432 - 7401710432) "data blocks consistent reads - undo records applied".

Even though the second row is not modified and in a different block, it still needs to apply whole UNDO records to make the CR read:

(More statistics can be listed by RUNSTATS in Expert Oracle Database Architecture)

UNDO Duration

Another UNDO test is by continuously inserting rows without commit, and querying in a few other sessions. The purpose is trying to extend UNDO duration (Time) to infinitive.

The similar pattern of code could probably be found in some real applications.
(see Test Code 2)

Start the test by:

 exec insert_no_commit_loop(1);
 exec test_select_loop(24);

From time to time, run following query, we could see elapsed_per_exec of select statement is gradually increasing. At beginning, it is some milliseconds, after a few hours, it could reach a couple of minutes.

select executions, disk_reads
      ,rows_processed, round(rows_processed/executions, 2) rows_per_exec
      ,buffer_gets, round(buffer_gets/executions, 2) buffer_per_exec
      ,round(elapsed_time/1e3) elapsed_time, round(elapsed_time/1e3/executions, 2) elapsed_per_exec
      ,v.*
from v$sql v where lower(sql_text) like '%test_tab2%' and v.executions > 0
order by v.executions desc;

Deeper into Buffer Cloning

In the Section: Deeper into Buffer Cloning (Page 244) of  Oracle Performance Firefighting (4th Printing June 2010) there is some text:

The SCN is 12320, which is before our query stated at time 12330. Therefore, we do not apply the undo. If we did apply the undo, our CR buffer would represent a version of block 7,678 at time
12320, which is too early!

Probably even though 12320 is before 12330, we should also apply this undo since it belongs to an opened transaction. In fact, we should reverse all blocks in this un-committed transaction.

Otherwise, the elapsed_per_exec of above select statement would arrive at a fixpoint (might be Nash equilibrium ?).

In Page 242, text:

If you recall, when a server process locates a desired buffer and discovers a required row has changed since its query began, it must create a back-in-time image of the buffer.

Same as above argument, even a required row is changed before its query began, it should also be reversed if not yet committed.

Temporary Table (GTT): UNDO / Redo

As we know, Oracle fulfils a DML by following 5 steps:

  1. create UNDO Change Vector
  2. create REDO Change Vector
  3. combine both Change Vectors into Redo Buffer, and then write to redo log
  4. write UNDO record into UNDO file
  5. write modified Data into DATA file

(See: Oracle Core: Essential Internals for DBAs and Developers (Page 10); Update Restart and new Active Undo Extent )

In case of GTT, applying the principle:

  Oracle redo log never records temporary data.

to prune the above 5 steps, a DML on GTT is processed by 4 steps:

  1. create UNDO Change Vector
  3. combine UNDO Change Vectors into Redo Buffer, and then write to redo log
  4. write UNDO record into UNDO file
  5. write modified Data into DATA (TEMP) file     

when setting 12c temp_undo_enabled=TRUE, it is further shortened as:

  1. create UNDO Change Vector
  4. write UNDO record into TEMP file (Remember TEMP has NEVER redo, V$TEMPUNDOSTAT)
  5. write modified Data into DATA (TEMP) file    

Test Code 1

drop table test_tab;
create table test_tab (id varchar2(3), scn number, name varchar2(3000), team varchar2(3000));
insert into test_tab values ('1', 1, rpad('a', 3000, 'a'), rpad('x', 3000, 'x'));
insert into test_tab values ('2', 2, rpad('b', 3000, 'b'), rpad('y', 3000, 'y'));
insert into test_tab values ('3', 3, rpad('c', 3000, 'c'), rpad('z', 3000, 'z'));
commit;

set lines 200
column name new_value  hostname
column mysid new_value mysid
column spid new_value  myspid
column dir new_value   mydir

select lower(name) name, sys.dbms_support.mysid mysid from v$database;
select spid from v$session s, v$process p where s.paddr=p.addr and s.sid = sys.dbms_support.mysid;
select '/orabin/app/oracle/admin/'||'&hostname'||'/diag/rdbms/'||'&hostname'||'/'||'&hostname'||'/trace' dir
from dual;

prompt hostname: &hostname, mysid: &mysid, myspid: &myspid, mydir: &mydir

create or replace directory TEST_DUMP_DIR as '&mydir';  
-- see: select * from v$diag_info where name = 'Diag Trace';

drop table dump_text_tab;

create table dump_text_tab (text varchar2(1000)) organization external
(type oracle_loader default directory TEST_DUMP_DIR
 access parameters
  (records delimited by newline CHARACTERSET AL32UTF8
   badfile aaa_db_io:'dump_text_tab.bad'
   logfile aaa_db_io:'dump_text_tab.log'
   discardfile aaa_db_io:'dump_text_tab.dsc'
   fields terminated by ','  OPTIONALLY ENCLOSED BY '"'
   missing field values are null
   reject rows with all null fields
   (text position(1:1000)))
 location ('host_ora_spid_x.trc')) reject limit unlimited;

create or replace procedure dump_read(loc varchar2, type varchar2) as
begin
  dbms_output.put_line('------------ '||loc||' ------------');
  execute immediate q'[alter table dump_text_tab location (']'||loc||q'[')]';
  if type = 'block' then
    for c in (
      select text from dump_text_tab
      where text like 'Start dump data blocks tsn:%'
         or text like 'scn:%'
         or text like '%-    1  fsc%'
         or text like 'bdba:%') loop
      dbms_output.put_line(c.text);
    end loop;
  else
  for c in (
    select text from dump_text_tab
    where text like 'Start dump data blocks tsn:%'
       or text like 'BH (%'
       or text like 'scn:%'
       or text like 'xid:%'
       or text like 'rdba:%'
       or text like 'op: C  uba:%') loop
    dbms_output.put_line(c.text);
  end loop;   
  end if;
end;
/

------------ Block 1 ------------
def id = 1

update test_tab set scn = dbms_flashback.get_system_change_number, name = upper(name), team = upper(team) where id = '&id';
-- dbms_flashback.get_system_change_number call makes test stable.

column file_id new_value  fid
column block_nr new_value blk
column location new_value loc_block_1

select dbms_rowid.rowid_to_absolute_fno(t.rowid, 'K', 'TEST_TAB') file_id
      ,dbms_rowid.rowid_block_number(t.rowid)           block_nr
      ,'&hostname'||'_ora_'||trim('&myspid')||'_block_&id'||'.trc' location
from test_tab t where id = '&id';

prompt fid: &fid, blk: &blk, loc: &loc_block_1

alter session set tracefile_identifier = 'block_&id';
alter system flush buffer_cache;
alter system dump datafile &fid block &blk; 

------------ UNDO 1 ------------
column location new_value loc_undo_1
select ubafil    file_id
      ,ubablk    block_nr
   ,'&hostname'||'_ora_'||trim('&myspid')||'_undo_&id'||'.trc' location
from v$session s, v$transaction x
where s.saddr = x.ses_addr and s.sid = sys.dbms_support.mysid;

alter session set tracefile_identifier = 'undo_&id';
alter system flush buffer_cache;
alter system dump datafile &fid block &blk; 

------------ Block 2 ------------
def id = 2

update test_tab set scn = dbms_flashback.get_system_change_number, name = upper(name), team = upper(team) where id = '&id';

column location new_value loc_block_2
select dbms_rowid.rowid_to_absolute_fno(t.rowid, 'K', 'TEST_TAB') file_id
      ,dbms_rowid.rowid_block_number(t.rowid)           block_nr
      ,'&hostname'||'_ora_'||trim('&myspid')||'_block_&id'||'.trc' location
from test_tab t where id = '&id';

alter session set tracefile_identifier = 'block_&id';
alter system flush buffer_cache;
alter system dump datafile &fid block &blk; 

------------ UNDO 2 ------------
column location new_value loc_undo_2
select ubafil    file_id
      ,ubablk    block_nr
   ,'&hostname'||'_ora_'||trim('&myspid')||'_undo_&id'||'.trc' location
from v$session s, v$transaction x
where s.saddr = x.ses_addr and s.sid = sys.dbms_support.mysid;

alter session set tracefile_identifier = 'undo_&id';
alter system flush buffer_cache;
alter system dump datafile &fid block &blk; 

------------ Block 3 ------------
def id = 3

update test_tab set scn = dbms_flashback.get_system_change_number, name = upper(name), team = upper(team) where id = '&id';

column location new_value loc_block_3
select dbms_rowid.rowid_to_absolute_fno(t.rowid, 'K', 'TEST_TAB') file_id
      ,dbms_rowid.rowid_block_number(t.rowid)           block_nr
      ,'&hostname'||'_ora_'||trim('&myspid')||'_block_&id'||'.trc' location
from test_tab t where id = '&id';

alter session set tracefile_identifier = 'block_&id';
alter system flush buffer_cache;
alter system dump datafile &fid block &blk; 

------------ UNDO 3 ------------
column location new_value loc_undo_3
select ubafil    file_id
      ,ubablk    block_nr
   ,'&hostname'||'_ora_'||trim('&myspid')||'_undo_&id'||'.trc' location
from v$session s, v$transaction x
where s.saddr = x.ses_addr and s.sid = sys.dbms_support.mysid;

alter session set tracefile_identifier = 'undo_&id';
alter system flush buffer_cache;
alter system dump datafile &fid block &blk; 

------------ Block dump read ------------
set serveroutput on
exec dump_read('&loc_block_1', 'block');
exec dump_read('&loc_block_2', 'block');
exec dump_read('&loc_block_3', 'block');

------------ UNDO dump read ------------
exec dump_read('&loc_undo_1', 'undo');
exec dump_read('&loc_undo_2', 'undo');
exec dump_read('&loc_undo_3', 'undo');

Test Code 2

create sequence test_seq;
create table test_tab2 (id number, seq_nr number, cnt number);
create index test_tab2_ind on test_tab2 (id, seq_nr, cnt) compress 1;
create type type_c100 as table of varchar2(100);
/

create or replace procedure insert_no_commit(p_cnt number) as
begin
  insert into test_tab2 select 99, test_seq.nextval, level from dual connect by level <= p_cnt;
  dbms_lock.sleep(0.1);
end;
/

create or replace procedure test_select as
  l_tab   type_c100;
begin
    select rowidtochar(rowid) bulk collect into l_tab
    from   test_tab2 where  id = 99;
    dbms_lock.sleep(0.1);
end;
/

create or replace procedure insert_no_commit_loop(p_job_cnt number)
as
   l_job_id pls_integer;
begin
    for i in 1.. p_job_cnt loop
      dbms_job.submit(l_job_id, 'begin while true loop insert_no_commit(4); end loop; end;');
    end loop;
    commit;
end;   
/

create or replace procedure test_select_loop(p_job_cnt number)
as
   l_job_id pls_integer;
begin
    for i in 1.. p_job_cnt loop
      dbms_job.submit(l_job_id, 'begin while true loop test_select; end loop; end;');
    end loop;
    commit;
end;   
/