Debugging Total Recall not using flashback archives

We are heavily developing and testing applications with Oracle Total Recall or Flashback Archives. Today, we encountered something a little weird: Flashback queries against some tables (that have had FBA enabled for quite some time now) would only use UNDO data and therefor fail if we wanted to go further back in time than a few days. This can be observed by looking at the execution plan. Good old regular flashback query (using only undo data) would show up like a regular query and only report to touch the base tables. The actual access to undo segments is hidden here but that just makes sense because all the database is doing is accessing a consistent image at some past scn which is not much different from getting a consistent image of current blocks which might also use undo.
But when flashback archives are involved, we see access to tables named SYS_FBA_12345 and partitioning clauses. Here are two examples, the first one does not touch flashback archives even though they are set up correctly:

SQL> SELECT COUNT(*) FROM A AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAL ‘2’ DAY);
COUNT(*)
———-
378
Execution Plan
———————————————————-
Plan hash value: 321999946
———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| A_PK | 378 | 1 (0)| 00:00:01 |
———————————————————————-

So this one does not access a flashback archive even though it does exist. And the query fails if we try to go back in time for more than a few hours or days:

SQL> SELECT COUNT(*) FROM A AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAl ‘3’ DAY);
SELECT COUNT(*) FROM A AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAl ‘3’ DAY)
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 6 with name
“_SYSSMU6_2228977038$” too small
If things are looking good and the query does access the flashback archives correctly, the execution plan should look more like this example:
SQL> SELECT COUNT(*) FROM B AS OF TIMESTAMP (SYSTIMESTAMP-INTERVAL ‘3’ DAY);

COUNT(*)
———-
1

Execution Plan
———————————————————-
Plan hash value: 2293674641
————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 8 (13)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | VIEW | | 2 | | 8 (13)| 00:00:01 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | FILTER | | | | | | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 28 | 3 (0)| 00:00:01 | KEY | 1 |
|* 6 | TABLE ACCESS FULL | SYS_FBA_HIST_94168 | 1 | 28 | 3 (0)| 00:00:01 | KEY | 1 |
|* 7 | FILTER | | | | | | | |
|* 8 | HASH JOIN OUTER | | 1 | 2031 | 5 (20)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| B | 1 | 3 | 1 (0)| 00:00:01 | | |
| 10 | INDEX FULL SCAN | B_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS FULL | SYS_FBA_TCRV_94168 | 1 | 2028 | 3 (0)| 00:00:01 | | |
————————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

4 – filter(“TIMESTAMP_TO_SCN”(SYSTIMESTAMP(6)-INTERVAL’+03 00:00:00′ DAY(2) TO SECOND(0))<689239504) 6 - filter("ENDSCN"<=689239504 AND ("OPERATION" IS NULL OR "OPERATION"<>‘D’) AND
“ENDSCN”>”TIMESTAMP_TO_SCN”(SYSTIMESTAMP(6)-INTERVAL’+03 00:00:00′ DAY(2) TO SECOND(0)) AND (“STARTSCN” IS NULL
OR “STARTSCN”<="TIMESTAMP_TO_SCN"(SYSTIMESTAMP(6)-INTERVAL'+03 00:00:00' DAY(2) TO SECOND(0)))) 7 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(SYSTIMESTAMP(6)-INTERVAL'+03 00:00:00' DAY(2) TO SECOND(0)) OR "STARTSCN" IS NULL) 8 - access("T".ROWID=CHARTOROWID("RID"(+))) 9 - filter("T"."VERSIONS_STARTSCN" IS NULL) 11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>689239504) AND (“STARTSCN”(+) IS NULL OR
“STARTSCN”(+)<689239504)) Note ----- - dynamic sampling used for this statement (level=2)

So why does Table A not use the flashback archives? After digging around a little bit, I noticed that the corresponding SYS_FBA_21345 table did not exist even though we already have a name for it in the data dictionary:

SQL> select ARCHIVE_TABLE_NAME FROM DBA_FLASHBACK_ARCHIVE_TABLES WHERE TABLE_NAME = ‘TABLE_A’;

ARCHIVE_TABLE_NAME
—————————————————–
SYS_FBA_HIST_94151

SQL> SELECT * FROM SYS_FBA_HIST_94151;
SELECT * FROM SYS_FBA_HIST_94151
*
ERROR at line 1:
ORA-00942: table or view does not exist


Querying the FBA table directly like that works well for Table B…
The explanation seems to be that the flashback archive table is not created until we actually update (or delete) stuff from the base table. And all we have done to this test instance until this point in time was insert rows. We never actually changed or deleted any rows. So all I needed to do was “update” a single row from that table and wait a few minutes for FBDA to do its job. Since FBDA is running asynchronously you have to wait a few minutes aftter the update until the flashback archive table gets generated.
SQL> UPDATE A SET ID=ID where ID=8;

1 row updated.
SQL> COMMIT;

After that flashback queries against A worked just like expected. So remember to perform just any simple update after you enable flashback archives for a table or you might find yourself debugging exactly the same issue later.

One thought on “Debugging Total Recall not using flashback archives

  1. Pingback: the new, improved and free flashback data archives in 12c | portrix systems

Leave a Reply

Your email address will not be published. Required fields are marked *