Flashback Data Archive Query Performance - Full Scan In A Join (Doc ID 2873919.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Potential performance degradation and its resolution while doing Flashback queries with Joins on two tables

Background:
Consider a use case that needs two tables — students and branches defined as follows:

one can see that the two queries above look almost the same, except that in the former query, an AS OF SCN clause is used to operate the query on older versions of the table.

Although the queries look similar at this level, there are a lot of transformations the 1st query (with the AS OF SCN clause) goes through during runtime, compared to the 2nd query (plain join query without the AS OF SCN clause).

To understand this better, one has to understand at a high-level, how the facility of a Flashback Archive is provided.

First of all, when someone enables and starts using Flashback Data Archive(FDA) for a table, the FDA will internally create corresponding history tables. These history tables are where FDA will keep all the historical row versions whenever some DML happens on an FDA-tracked base table.

To support AS OF SCN queries, FDA maintains the startscn and endscn for each row version present inside the history tables.
They are roughly equivalent to the timestamps and denote the start and end time for the lifespan of a particular row version in a table.
For a DML query on an FDA-tracked base table, the corresponding row versions are added to the history tables during archival, only after the corresponding transaction is committed. The archival happens periodically through a dedicated background process named FBDA.

At a high-level, the startscn and endscn are managed as follows:

1. Consider a DML query that inserts a row into an FDA-tracked base table. After the corresponding transaction is committed, and when the FBDA background process resumes its archiving, the corresponding row version for the inserted row will be added into the history tables. The startscn for the row version will be set to the transaction’s commitscn. And the endscn will be kept as NULL.  The pair of scns indicate that the row version would be valid from given startscn till the specified endscn. The NULL value for the endscn indicates that the row version is currently valid and that its lifespan has not ended yet.
2. Consider a DML query that updates a row from an FDA-tracked base table. When the corresponding transaction is committed, and the archival process begins its execution, the corresponding row version for the updated row would be added into the history tables. This way, there would be two row versions for the same row, present in the history tables. (a newer one, that got added with this update and the older one which was present earlier due to the insert as shown above in Point No. 1)

The startscn for the newer row version will be set to the transaction’s commit scn, and its endscn will be set to NULL (to indicate that this is the currently valid version of the row). On the other hand, since the older row version would no longer be valid, its endscn will be set to the commitscn of the transaction corresponding to the DML query that updated the row.
So this could be seen as closing the lifespan of the older row version by setting its endscn and starting the lifespan of the newer row version (by setting the newer row version’s startscn same as the older row version’s endscn and keeping the newer row version’s endscn to be NULL).

But note that to satisfy an AS OF SCN query, FDA would not always use the history tables to fetch the row versions for a table at a given scn. This is because, depending upon the provided scn, the row versions might not be archived yet, but they could be present in the UNDO tablespace.
So there’s a concept of a barrierscn in FDA. The barrierscn is an scn which will mark a point in history up to which all the data has been surely archived into the history tables.
So if someone wants a row version at an scn before the barrierscn, then one can get it from the HIST table. On the other hand, if one wants a row version at an scn greater than the barrierscn, then the row version would be recreated from the undo.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.