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;
/

Flashback Data Archive 11.1 vs 11.2

Some of the DDL statements that weren't allowed on tables that had FDBA enabled in 11.1 are now allowed on 11.2. Section below gives a summary of the changes.

From 11.1 documentation
DDL Statements Not Allowed on Tables Enabled for Flashback Data Archive

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes error ORA-55610:

ALTER TABLE statement that does any of the following:
Drops, renames, or modifies a column
Performs partition or sub partition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
DROP TABLE statement
RENAME TABLE statement
TRUNCATE TABLE statement

On 11.1.0.7 (11.1.0.7.3 PSU)

SQL> create table x (a number, b varchar2(1000)) flashback archive fbdarchive;

Table created.

SQL> alter table x rename to y;
alter table x rename to y
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


SQL> truncate table x;
truncate table x
               *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

From 11.2 documentation
DDL Statements on Tables Enabled for Flashback Data Archive

Flashback Data Archive supports many DDL statements, including some that alter the table definition or move data. For example:
ALTER TABLE statement that does any of the following:
Adds, drops, renames, or modifies a column
Adds, drops, or renames a constraint
Drops or truncates a partition or sub partition operation
TRUNCATE TABLE statement
RENAME statement that renames a table

Some DDL statements cause error ORA-55610 when used on a table enabled for Flashback Data Archive. For example:
ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause

ALTER TABLE statement that moves or exchanges a partition or sub partition operation

DROP TABLE statement

On 11.2 (11.2.0.2)

SQL> create table x (a number, b varchar2(1000)) flashback archive auditarchive;

Table created.

SQL> alter table x rename to y;

Table altered.

SQL> truncate table y;

Table truncated.

SQL> drop table y;
drop table y
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

More bugs on 11.1 vs 11.2 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]

No DML allowed when flashback data archive quota exceeded

When a flashback data archive exceeds its quota on the tablepsace, it will log an alert on to the alert log but more importantly all DML statements will result in an error until space is added.

1. Create a flashback archive and grant privileges to a user. The flashback archive in this case will have a quota of only 2 MB

sqlplus / as sysdba

SQL> create flashback archive flash1 tablespace asmbkp quota 2m retention 1 year;

SQL> grant flashback archive on flash1 to asanga;

2. Create a table with flashback archiving

conn asanga/***

SQL> create table x ( a char(2000), b char(2000), c char(2000), d char(2000)) flashback archive flash1;

Table created.

3. Insert a single row into the table and continue to update that row

SQL> insert into x values ('x','y','z','i');

SQL> begin
2  for i in  1 .. 100000
3  loop
4  update x set a = i||'x', b = a, c = a, d = a;
5  commit;
6  end loop;
7  end;
8  /
begin
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended
ORA-06512: at line 4

Foreground session receives the flashback runs out of space error and following could be seen on the alert log

ORA-1688: unable to extend table ASANGA.SYS_FBA_HIST_77187 partition HIGH_PART by 1024 in tablespace ASMBKP
Fri Sep 10 02:28:22 2010
ORA-1688: unable to extend table ASANGA.SYS_FBA_HIST_77187 partition HIGH_PART by 1024 in tablespace ASMBKP
Flashback Archive FLASH1 ran out of space in tablespace ASMBKP.
Flashback archive FLASH1 is full, and archiving is suspended.
Please add more space to flashback archive FLASH1.
Flashback Archive FLASH1 ran out of space in tablespace ASMBKP.
ORA-1688: unable to extend table ASANGA.SYS_FBA_HIST_77187 partition HIGH_PART by 1024 in tablespace ASMBKP
Flashback Archive FLASH1 ran out of space in tablespace ASMBKP.

4. Further inserts are also suspended

SQL> insert into x values('1','2','3','4');
insert into x values('1','2','3','4')
          *
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended

Update on 2011-06-29

There has been some new development with regard to the above post. It seem the error thrown is not due to flashback quota exceeding but tablespace quota exceeding. It seem quota limit has no effect.

Below is the test case.

SQL> create tablespace asmbkp datafile '+DATA(datafile)' size 10M autoextend on next 10M maxsize 100M;

SQL>  create flashback archive flash1 tablespace asmbkp quota 2m retention 1 year;

SQL> grant flashback archive on flash1 to asanga;

SQL> conn asanga/asa
Connected.
SQL> create table x ( a char(2000), b char(2000), c char(2000), d char(2000)) flashback archive flash1;

Table created.

SQL> insert into x values ('x','y','z','i');

1 row created.

SQL> commit;

Commit complete.

SQL> begin
 2      for i in  1 .. 100000
 3      loop
 4      update x set a = i||'x', b = a, c = a, d = a;
 5     commit;
 6      end loop;
 7      end;
 8      /
begin
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended
ORA-06512: at line 4

But the error is not due to the fact flashback archive has reached it limit of 2m, ORA-55617 happens because tablespace has reached it max size. This error should have happened when flashback archive internal table reached 2M.

Query showing quota has been set to 2 M

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME    FLASHBACK_ARCHIVE# TABLESPACE_NAME                QUOTA_IN_MB
------------------------- ------------------ ------------------------------ -------------
FLASH1                                     1 ASMBKP                         2

Getting the name of the internal flashback archive table

SQL> select * from dBA_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME OWNER_NAME                     FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME   STATUS
---------- ------------------------------ ------------------------- -------------------  -------------
X          ASANGA                         FLASH1                    SYS_FBA_HIST_87387   ENABLED

Size of the internal flashback archive table

SQL> select sum(bytes)/1024/1024 as "MB" from dba_segments where segment_name='SYS_FBA_HIST_87387';

MB
--
96

Maximum and current size of the datafile (only one datafile in this tablespace)

SQL> select tablespace_name,bytes/1024/1024 as "Size MB",maxbytes/1024/1024 as "MaxSize MB" from dba_data_files where tablespace_name='ASMBKP';

TABLESPACE_NAME                   Size MB MaxSize MB
------------------------------ ---------- ----------
ASMBKP                                100        100

Above was tested on 11.2.0.2 With Patchset 11.2.0.2.0 set. Same behavior is also seen on 11.1.0.7 with PSU 11.1.0.7.7. Oracle support has suggested Bug 7120053 - ORA-55617 in flashback archive tablespace even if used size does not reach quota [ID 7120053.8] but issue suggested on the metalink and above issue is not the same.

Update on 2011-07-08
Response from Oracle is The Flashback archiving is handled by fbda Background Process and it checks for Tablespace Quota every 1 Hour. So the Limit can be exceeded until the next fbda-Run detects it. And 11g feature: Flashback Data Archive Guide. [ID 470199.1] will be updated with this information.

So carried out the test but limited the number of loops to 10. This created a flashback archive table of size 8MB exceeding the 2M quote. Waited until fbda to check the quota limit again and following could be seen on the alert log

Fri Jul 08 12:33:49 2011
Flashback Archive FLASH1 ran out of space in tablespace TEST.
Flashback archive FLASH1 is full, and archiving is suspended.
Please add more space to flashback archive FLASH1.

Same message is repeated almost every hour

Fri Jul 08 13:38:49 2011
Flashback Archive FLASH1 ran out of space in tablespace TEST.

And trying to update would cause the following error and no further DML will be allowed.

SQL>  update x set a ='aaa' ,b = a, c = a, d = a;
 update x set a ='aaa' ,b = a, c = a, d = a
        *
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended

SQL> delete from x where a='10x';
delete from x where a='10x'
            *
ERROR at line 1:
ORA-55617: Flashback Archive "FLASH1" runs out of space and tracking on "X" is
suspended