Performance Overhead with FBDA 11.1 vs 11.2

Blog came about as a result of evaluating alternatives to a trigger base audit mechanism. Few simple test cases were run against databases on 11.1 and 11.2 with FBDA enabled and disabled. Test consists of inserting 100k rows into a table (which doesn't have any indexes and only two columns), then updating and deleting one row, updating and deleting 9999 rows then creating an unique index and running a update and delete loop. A commit is issued soon after the DML which stresses the fbda process and also makes the undo segments eligible for re-write once fbda has done with archiving. Flashback archive was created on a separate tablespace and the storage system is a file system (RHEL 5, ext3) not ASM and both 11.2 and 11.1 databases resided in the same machine.

On 11.2 (PSU 11.2.0.2.2)
Even though it was said in FBDA documentations that insert statements does not generate any records in the flashback archive, inserts were slow on tables with FBDA enabled than on table without FBDA. strace on the fbda process showed burst of activity when inserts were going on, though no records are written clearly some work is being carried out which adds some overhead.
However the individual DML statements (outside the loops) that were run (with FBDA enabled) had elapsed time that were close to DML statements run without FBDA.Time shown is inclusive of execute time and commit timeIt seem if the fbda is able to keep up with the modification the overhead is less but whenever there's burst of activity it is possible to exhaust the fbda process to add considerable overhead.

On 11.1 (PSU 11.1.0.7.7)
It was difficult to get the test case working on 11.1 at times with PL/SQL terminating with the following

SQL>  begin
  2      for i in 1 .. 100000
  3      loop
  4      insert into x values(i,i||'abcdefg');
  5      commit;
  6      end loop;
  7      end;
  8      /

 begin
*
ERROR at line 1:
ORA-55616: Transaction table needs Flashback Archiver processing
ORA-06512: at line 4

Explanation for this error says

*Cause: Too many transaction table slots were being taken by transactions on tracked tables.
*Action: Wait for some amount of time before doing tracked transactions.

Which indicates it is possible to exhaust the slots in the tracker table and worryingly this will put an end to further DML on that table until more slots are available. Overhead was far greater than in 11.2However the key difference between 11.2 and 11.1 came when the elapsed time on individual DMLs were compared. Although execute time was low and similar to elapsed times of 11.2 or even without FBDA, commit time was far greater than in 11.2It could be said that 11.2 has some improvements over 11.1 when it comes to FBDA but still careful consideration must be given to the performance overhead introduced by FBDA.

From metalink
Bug : COMMIT DELAY WHEN UPDATING TABLE IN FLASHBACK DATA ARCHIVE MODE 8226666
Bug 9786460 - ORA-600 [qertbfetchbyrowid_fda:no selected row] after bugfix 8226666 [ID 9786460.8]

Test Case

set timing on
--create table x (a number, b varchar2(1000)); -- for without FBDA
create table x (a number, b varchar2(1000)) flashback archive fbdarchive; -- for with FBDA

begin
  for i in 1 .. 100000
  loop
    insert into x values(i,i||'abcdefg');
    commit;
   end loop;
end;
/
update x set b = 'aa' where a = 10;
commit;
delete from x where a = 10;
commit;
update x set b = 'bbbb' where a < 10001;
commit;
delete from x where a < 10001;
commit;

create unique index aidx on x(a) compute statistics nologging;

begin
  for i in 1 .. 100000
  loop
    update x set b = 'abcdef' where a = i;
    commit;
   end loop;
end;
/

begin
  for i in 1 .. 100000
  loop
    delete from x where a = i;
    commit;
   end loop;
end;
/