Flashback query FTS costs

There has been some information written on the subject already (see this post by Randolf Geist).

In a nutshell, the way optimizer costs full table scans when using flashback query makes it look much more expensive than without. What further complicates the problem is the fact that index access costs remain the same regardless of whether you're using flashback query or not. As a result you will be much more likely to see an index access paths when using flashback query.

Consider the following example:

SQL> create table test as
  2   select level n, rpad('x', 200, 'x') v
  3    from dual
  4    connect by level <= 10000;
 
Table created
 
SQL> alter table test add constraint pk_test primary key (n);
 
Table altered
 
SQL> exec dbms_stats.gather_table_stats(user, 'test');
 
PL/SQL procedure successfully completed

If I were to execute the following select it will run using a HASH JOIN:

SQL> with v as
(select /*+ cardinality(100) */ level n from dual connect by level <= 100)
select *
        from v, test t
        where v.n=t.n;  2    3    4    5

Execution Plan
----------------------------------------------------------
Plan hash value: 690578125

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |   100 | 21800 |    60   (2)| 00:00:01 |
|*  1 |  HASH JOIN                     |      |   100 | 21800 |    60   (2)| 00:00:01 |
|   2 |   VIEW                         |      |   100 |  1300 |     2   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL            | TEST | 10000 |  2001K|    57   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("V"."N"="T"."N")
   3 - filter(LEVEL<=100)

In the particular case I'm talking about the flashback query was hidden behind the view (to make it transparent to the application) so we essentially had the following:

SQL> create or replace view v_test as
  2  select * from test as of scn dbms_flashback.get_system_change_number;
 
View created

(dbms_flashback.get_system_change_number is only used here as a substitute example)

Now let's see what happens if we run the same query as above but join into this view instead:

SQL> with v as
(select /*+ cardinality(100) */ level n from dual connect by level <= 100)
select *
        from v, v_test t
        where v.n=t.n;  2    3    4    5

Execution Plan
----------------------------------------------------------
Plan hash value: 3196053776

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |   100 | 21800 |   102   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                   |         |       |       |            |          |
|   2 |   NESTED LOOPS                  |         |   100 | 21800 |   102   (0)| 00:00:02 |
|   3 |    VIEW                         |         |   100 |  1300 |     2   (0)| 00:00:01 |
|*  4 |     CONNECT BY WITHOUT FILTERING|         |       |       |            |          |
|   5 |      FAST DUAL                  |         |     1 |       |     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN            | PK_TEST |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID   | TEST    |     1 |   205 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   4 - filter(LEVEL<=100)
   6 - access("V"."N"="N")

The plan suddenly changed to NL join! What happened is flashback query cranked the FTS cost up while leaving index access cost to be the same thus making an FTS to be much less appealing choice for the optimizer. This can make a lot of a difference especially if you're running in the Exadata environment. So what do you do?

One way to deal with the problem is to see how much more expensive the table scan became when using flashback query:

SQL> select * from test
union all
select * from v_test;  2    3

Execution Plan
----------------------------------------------------------
Plan hash value: 2275963031

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 20000 |  4003K|   367  (85)| 00:00:05 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 10000 |  2001K|    57   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST | 10000 |  2001K|   310   (0)| 00:00:04 |
---------------------------------------------------------------------------

The cost went up from 57 to 310. That's how much more expensive the view will going to make an FTS look like to the optimizer. So now we can counter-balance that increase with the corresponding increase in cost of index scans using opt_param hint in our view:

SQL> select round((310/57)*100) x from dual;
 
         X
----------
       544
 
SQL> create or replace view v_test as
  2  select /*+ opt_param('optimizer_index_cost_adj',544) */ *
  3   from test as of scn dbms_flashback.get_system_change_number;
 
View created

The plan will now go back to a HASH JOIN

SQL> with v as
(select /*+ cardinality(100) */ level n from dual connect by level <= 100)
select *
        from v, v_test t
        where v.n=t.n;  2    3    4    5

Execution Plan
----------------------------------------------------------
Plan hash value: 690578125

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |   100 | 21800 |   313   (1)| 00:00:04 |
|*  1 |  HASH JOIN                     |      |   100 | 21800 |   313   (1)| 00:00:04 |
|   2 |   VIEW                         |      |   100 |  1300 |     2   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL            | TEST | 10000 |  2001K|   310   (0)| 00:00:04 |
---------------------------------------------------------------------------------------

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

   1 - access("V"."N"="N")
   3 - filter(LEVEL<=100)

Of course there are a lot of limitations to this approach, mainly that any query which references the view will get the index access cost adjusted accordingly so if you have a bunch of flashback and non-flashback tables in the same query it deserves a careful thought.