Flashback Data Archive Problems!

Hi,

if you don’t know about flashback data archive you can check this post first: https://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/

I mentioned about flashback data archive (FDA) in that post and really flattered it. Well, maybe I shouldn’t! FDA is really fast dml tracking method but thanks to Peter Schlaeger, I noticed some potential problems and “some” solutions. Few tables are created when you add a table into a FDA. 3 of them are in the same schema that your table is in (also some more for indexes) but if you store all context data like client_identifier, client_info, session_user etc then Oracle writes those into SYS_FBA_CONTEXT_AUD table in SYS schema and according to me this is a huge design problem!

as you can see this table just store XID (transaction id which is unique for a database) and all other context data. Every FDA table’s context data are stored in this table. if you use FDA for many active tables that means, SYS_FBA_CONTEXT_AUD table size will be huge! first, since this is a SYS object, it is in SYSTEM tablespace and SYS_FBA_CONTEXT_AUD table will consume too much space. as a result SYSTEM tablespace will grow. we don’t want that because it stores data dictionary. I don’t know why Oracle choose a design like this. that table could be created for every FDA table like SYS_FBA_HIST_nnnnnn or SYS_FBA_TCRV__nnnnnn tables. that way table would be smaller (and faster since it is smaller) and would be created in FDA tablespace not in system.

Secondly, this table has no index on it. if you try to get a context information of a row like:

Oracle queries SYS_FBA_CONTEXT_AUD table with this query:

since there is no index on SYS_FBA_CONTEXT_AUD, for a large amount of context log, this query takes ages! I just use a dummy table and after running many dml, querying context data takes too long. after creating an index on XID column and gather statistics. It become much more better but creating/altering an object in SYS schema is dangerous but this is a workaround to me.

if you want to query rows which are changed by a specific “client_identifier”, using Dbms_Flashback_Archive.Get_Sys_Context becomes a disaster again because we will be calling a function in where clause and client_identifier column also has no index!

So it would be better if this table would be created like other fda tables for specifically original table. At least we could do what we need. instead of calling Dbms_Flashback_Archive.Get_Sys_Context you can join SYS.SYS_FBA_CONTEXT_AUD table to your query (joining Version_XID pseudo column to XID in sys_fba_context_aud table) but you must grant select option for every schema owner for that table.

With creating necessary indexes on SYS_FBA_CONTEXT_AUD, you might solve your performance problems but still one thing that we can not solve; this table is in SYSTEM tablespace and cannot be moved to another! I couldn’t find anything about moving this table in support but I assume it will be an option for further releases (as it was done for audit tables).

Also when you remove FDA from your table, Oracle does not clean context data from SYS_FBA_CONTEXT_AUD.

So, be careful while using FDA and again thanks to Peter for informing me about that problem, at least with a few index querying context data can be solved.