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!

Comments

[Sophie]

I am glad to have found this post! I thought that there was something strange going on with my system. Am showing similar slow performance on commits. It's tolerable when only insert a few thousand records, but not for millions!

Any idea if this is fixed in 11.2? FBDA was my major reason for pushing an upgrade on a few databases to 11g, but so far, it's a deal breaker.

[Guy Harrison]

Hi Sophie,

Yes, this should be fixed in 11GR2. I did test this on the 11.2 beta, but could not comment because I was under beta program NDA.

Guy

[Leo]

Now that R2 is officially released, can you confirm that is no longer an issue?

[Guy Harrison]

The specific bug in 11.0.7 in which the APPEND hint was missing it's "+" symbol is definitely fixed in 11Gr2. The INSERT into the SYS_MFBA_NHIST table now uses a parallelized direct path insert plan which - for most databases - should be somewhat faster.

Even so, for large transactions, Flashback data archive adds a lot of overhead to the mix. The UPDATE statement above - which took 1 minute to execute and 24 minutes to commit in 11.0.6 - took about 9 minutes to run 30 seconds to COMMIT on 11.2. The delay in the UPDATE was caused by free buffer waits - perhaps with faster/wider disk stripe and bigger buffer cache it might not have hit. .

Overall, for the bulk inserts, deletes and updates in my little test, Flashback data archive still quadrupled my execution time on 11.2.0.1.

So while 11.2 fixes that one obvious bug, FBDA can still add a lot of overhead. I'd be very caution about running Flashback Data archive without performing benchmarking and optimization. In particular, I'd want to place the flashback data archive on it's own dedicated & fast disk devices and probably try to isolate it to it's own buffer pool (maybe by using a different block size for the FBDA).

Regards, Guy