Flashback Data Archive performance (pt 1)

I did some research on Flashback Data Archive (FBDA) for my upcoming Oracle performance book, but decided not to include it in the book due to space limitations and because not many people are using FBDA just yet.

FBDA – also called Total Recall - is described by Oracle like this:

Overview

Flashback Data Archive provides the ability to automatically track and store all transactional changes to a record for the duration of its lifetime. This feature also provides seamless access to historical data with "as of" queries. It provides flashback functionality for longer time periods than your undo data. You can use Flashback Data Archive for compliance reporting, audit reports, data analysis and decision support.

http://www.oracle.com/technology/obe/11gr1_db/security/flada/flada.htm

Sounds good, but from my brief tests you pay a very high price for this functionality. Also, it looks like the background processing has been changed to foreground processing in 11.0.6.   In 11.0.6, it's the FBDA background process that populates the FBDA tables, but in 11.0.7 this is done by the session that issues the DML.

Let’s review FBDA set up first. Create a tablespace and a data archive:

CREATE TABLESPACE fb_arc_ts1 DATAFILE

'/oradata/g11r22a/fb_arc1.dbf' SIZE 1024 M AUTOEXTEND OFF;

DROP FLASHBACK ARCHIVE fb_arc1;

/* Create the flashback archive */

CREATE FLASHBACK ARCHIVE DEFAULT fb_arc1

TABLESPACE fb_arc_ts1

QUOTA 1024 M

RETENTION 1 DAY;

Now we can mark a table for flashback archive:

ALTER TABLE fba_test_data FLASHBACK ARCHIVE;

DML statements run about the same time on a FBDA table, but COMMIT times go through the roof:

SQL> UPDATE fba_test_data

2 SET datetime = datetime + .01;

999999 rows updated.

Elapsed: 00:01:13.43

SQL>

SQL> COMMIT;

Commit complete.

Elapsed: 00:24:10.29

That’s right – 1 minute update, 24 minute commit time!! I’ve seen the same performance from DELETEs and INSERTs.

When the COMMIT occurs, Oracle runs recursive SQL to update the data archive. Here’s an example of one of the SQLs that runs (shown in Spotlight on Oracle's trace file viewer):

The statement above is the final in a sequence of at least 4 that are executed for every transaction.

In 11.0.6, there’s a bug in the FBDA SQL. The following SQL has a hint missing the “+” sign. Consequently the intended direct path inserts do not occur and - in some cases - free buffer waits can result:

The free buffer waits are a consequence of creating lots of new blocks for the DBWR to write to disk while at the same time pulling lots of blocks into the buffer cache from the source table.  If the DBWR can't write out to disk as fast as you are creating new blocks then the free buffer waits results.

FBDA will large transactions is therefore not snappy.  For short transactions the effect (at COMMIT time on 11.0.7) is less noticeable:

SQL> INSERT INTO fba_test_data d(id, datetime, data)

2 SELECT ROWNUM id,

3 SYSDATE - 1000 + DBMS_RANDOM.VALUE(1, 1000) datetime,

4 RPAD(SYSDATE - 1000 + DBMS_RANDOM.VALUE(1, 1000), 900,

5 'x')

6 data

7 FROM DUAL

8 CONNECT BY ROWNUM < 10;

9 rows created.

Elapsed: 00:00:00.48

SQL>

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.53

The SQLs that FBDA generates are interesting case studies for SQL tuning – the use of hints in particular is interesting since it limits the ability of Oracle to respond to different volumes of changes with different SQL plans. I’ll post an analysis of the various SQLs issued in a future post.

For now, the clear lesson is to be very cautious when implementing FBDA – it definitely has some performance implications!