What is going on underneath FDA queries?

Question

Let's say I want to pull data for the TEST_TABLE table for some date. I create a query with FDA syntax:

select * from TEST_TABLE as of timestamp (timestamp 2021.05.05 15:00:15);

I want to check how exactly the query looks like in oracle engine. I.e. what are the conditions of this query, what tables are the data taken from etc....

Execution plan returned me this info:

Predicate Information (identified by operation id):
------------------------------------------
* 4 - filter(("STARTSCN"<=148411288669 OR "STARTSCN" IS NULL) AND "ENDSCN">148411288669 AND ("OPERATION"<>'D' OR "OPERATION" IS NULL) AND "ENDSCN"<=155682149589)
* 5 - filter("STARTSCN"<=148411288669 OR "STARTSCN" IS NULL)
* 7 - filter(("T"."VERSIONS_STARTSCN" IS NULL OR "T"."VERSIONS_STARTSCN"<=148411288669) AND ("T"."VERSIONS_ENDSCN" IS NULL OR "T"."VERSIONS_ENDSCN">148411288669) AND ("T"."VERSIONS_OPERATION" IS NULL
  OR "T"."VERSIONS_OPERATION"<>'D'))
* 8 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>155682149589) AND ("STARTSCN"(+)<155682149589 OR "STARTSCN"(+) IS NULL))
* 9 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))

But it's not quite what I'm looking for... When I add these into where section in TEST_TABLE the results are not the same.

are you using flashback query against a table configured with FDA ? — Roberto Hernandez, Oct 27 '21 at 11:50

Roberto Hernandez · Answer 1 · 2021-10-27T12:55:33.910

If you are referring to which tables are used by Flashback Data Archive, a.k.a FDA, you need first to understand how Oracle works with Flashback query.

Let me show you an example. I will create a small flashback archive group and a table will be assigned to it.

SQL> create flashback archive fda_test tablespace tbrepdata quota 1g retention 1 year ;

Flashback archive created.

SQL> grant flashback archive on fda_test to test ;

Grant succeeded.

SQL> grant flashback archive administer to test ;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test;

Grant succeeded.

SQL> create table test.t1 ( c1 number, c2 number ) flashback archive fda_test ;

Table created.

SQL> insert into test.t1 values ( 1 , 1 ) ;

1 row created.

SQL> insert into test.t1 values ( 2 , 2 ) ;

1 row created.

SQL> insert into test.t1 values ( 3, 3 ) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> update test.t1 set c1=4,c2=4 where c1=3 ;

1 row updated.

SQL> commit ;

Commit complete.

Now, if I do a query

SQL> col versions_startscn format 9999999999999999
SQL> col versions_endscn format 9999999999999999
SQL> r
  1  SELECT versions_startscn,
  2         --versions_starttime,
  3         versions_endscn,
  4         --versions_endtime,
  5         versions_xid,
  6         versions_operation,
  7        c1,
  8        c2
  9*       from test.t1 versions between scn minvalue and maxvalue

VERSIONS_STARTSCN   VERSIONS_ENDSCN VERSIONS_XID     V         C1         C2
----------------- ----------------- ---------------- - ---------- ----------
   13142361651647                   13001C0000AB0000 U          4          4
   13142361651581    13142361651647 20002A00BD960000 I          3          3
   13142361651581                   20002A00BD960000 I          2          2
   13142361651581                   20002A00BD960000 I          1          1

Let's check the plan

SQL> set autotrace traceonly
SQL> r
  1  SELECT versions_startscn,
  2         --versions_starttime,
  3         versions_endscn,
  4         --versions_endtime,
  5         versions_xid,
  6         versions_operation,
  7        c1,
  8        c2
  9*       from test.t1 versions between scn minvalue and maxvalue


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   164 |  4264 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   164 |  4264 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          5  recursive calls
          4  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
       1091  bytes sent via SQL*Net to client
        591  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

As you can see, Oracle is just accessing the table. Why ? Because the data is still in the undo tablespace, as the undo blocks have not yet expired. When you use FDA, Oracle will use always this approach when you use flashback query:

If the data to the Undo Tablespace, data is recover from it.
If the data is no longer available in the Undo tablespace, then it will retrieve the rows from the underlying FDA table.

The underlying table contains the archive data based on the retention established for the archive group

SQL> set lines 200
SQL> SELECT owner_name,
  2         table_name,
  3         flashback_archive_name,
  4         archive_table_name,
  5         status
  6* FROM   dba_flashback_archive_tables where owner_name = 'TEST' and table_name = 'T1'

OWNER_NAME                     TABLE_NAME                     FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME             STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
TEST                           T1                             FDA_TEST                       SYS_FBA_HIST_2779773           ENABLED

If you are sure that the data you are recovering with as of timestamp is no longer in the undo tablespace, you can use a 10046 event to generate a trace file to really see how Oracle is really getting the data.

Although I wonder what is you are looking for in getting that level of detail.

I mean something else... Take example from my question: `select * from TEST_TABLE as of timestamp (timestamp 2021.05.05 15:00:15);` I need to know how the oracle sees this query. For ex: `select * from fda_table_of_test_table where startscn > xxx and endsn < yyy union all select * from test_table where version_startscn > xxx and version endscn < yyy;` Do you know what i mean? — andrea_sottolamontagna, Oct 27 '21 at 12:36
well, it was not clear in your question. If you want that, use a 10046 event. But the FDA table will only appear if the undo blocks are expired — Roberto Hernandez, Oct 27 '21 at 12:53
I have retention set to 3 months. Every day I copy the data from the FDA table (SYS...) to another one on the report base(tab_rap). While I can fire a query on the source table (as of...), I can't do it on the tab_rap table because it is a regular table that stores the same data set as the FDA(SYS...) table. So if I want to find the state of the table for 2021-05-05 (and this is a larger time period than 3 months) I have to use the query on tab_rap (report base). Thanks for the idea with event `10046`, didnt know that. I'll check that.. — andrea_sottolamontagna, Oct 27 '21 at 13:30

What is going on underneath FDA queries?

1 Answers1