Oracle Scratchpad

May 4, 2018

FBI Limitation

Filed under: CBO,distributed,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 9:19 am BST May 4,2018

Latest update: Dec 2020

A recent question on the ODC (OTN) database forum prompted me to point out that the optimizer doesn’t consider function-based indexes on remote tables in distributed joins. I then spent 20 minutes trying to find the blog note where I had demonstrated this effect, or an entry in the manuals reporting the limitation – but I couldn’t find anything, so I’ve written a quick demo which I’ve run on 12.2.0.1 to show the effect. First, the SQL to create a couple of tables and a couple of indexes:


rem
rem     Script:         fbi_limitation.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2018
rem

-- create public database link orcl@loopback using 'orcl'; 
define m_target = orcl@loopback

create table t1
segment creation immediate
nologging
as
with generator as (
        select
                rownum id
        from dual
        connect by
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        rownum                          n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e6 -- > comment to avoid WordPress format issue
;

create table t2
nologging
as
select * from t1
;

alter table t1 add constraint t1_pk primary key(id);
alter table t2 add constraint t2_pk primary key(id);
create unique index t2_f1 on t2(id+1);

begin
        dbms_stats.gather_table_stats(
                ownname     => user,
                tabname     => 'T1',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname     => user,
                tabname     => 'T2',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
end;
/


The code is very simple, it creates a couple of identical tables with an id column that will produce an index with a very good clustering_factor. You’ll notice that I’ve (previously) created a public database link that is (in my case) a loopback to the current database and the code defines a variable that I can use as a substitution variable later on. If you want to do further tests with this model you’ll need to make some changes in these two lines.

So now I’m going to execute a query that should result in the optimizer choosing a nested loop between the tables – but I have two versions of the query, one which treats t2 as the local table it really is, and one that pretends (through the loopback) that t2 is remote.


set serveroutput off

select
        t1.v1, t2.v1
from
        t1,
        t2
--      t2@orcl@loopback
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;


select * from table(dbms_xplan.display_cursor);

select
        t1.v1, t2.v1
from
        t1,
--      t2
        t2@orcl@loopback
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;

select * from table(dbms_xplan.display_cursor);

Here are the two execution plans, pulled from memory – including the “remote” section in the distributed case:


SQL_ID  fthq1tqthq8js, child number 0
-------------------------------------
select  t1.v1, t2.v1 from  t1,  t2 -- t2@orcl@loopback where  t2.id+1 =
t1.id and t1.n1 between 101 and 110

Plan hash value: 1798294492

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |  2347 (100)|          |
|   1 |  NESTED LOOPS                |       |    11 |   407 |  2347   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | T1    |    11 |   231 |  2325   (4)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    16 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | T2_F1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))
   4 - access("T2"."SYS_NC00005$"="T1"."ID")

Note
-----
   - this is an adaptive plan




SQL_ID  ftnmywddff1bb, child number 0
-------------------------------------
select  t1.v1, t2.v1 from  t1, -- t2  t2@orcl@loopback where  t2.id+1 =
t1.id and t1.n1 between 101 and 110

Plan hash value: 1770389500

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4663 (100)|          |        |      |
|*  1 |  HASH JOIN         |      |    11 |   616 |  4663   (4)| 00:00:01 |        |      |
|*  2 |   TABLE ACCESS FULL| T1   |    11 |   231 |  2325   (4)| 00:00:01 |        |      |
|   3 |   REMOTE           | T2   |  1000K|    33M|  2319   (3)| 00:00:01 | ORCL@~ | R->S |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID"+1)
   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT "ID","V1" FROM "T2" "T2" (accessing 'ORCL@LOOPBACK' )

Both plans show that the optimizer has estimated the number of rows that would be retrieved from t1 correctly (very nearly); but while the fully local query does a nested loop join using the high-precision, very efficient function-based index (reporting the internal supporting column referenced in the predicate section) the distributed query seems to have no idea about the remote function-based index and select all the required rows from the remote table and does a hash join.

Footnote:

Another reason for changes in execution plan when you test fully local and then run distributed is due to the optimizer ignoring remote histograms, as demonstrated in a much older blog note (though still true in 12.2.0.1).

Addendum

After finishing this note, I discovered that I had written a similar note about reverse key indexes nearly five years ago. Arguably a reverse key is just a special case of a function-based index – except it’s not labelled as such in user_tab_cols, and doesn’t depend on a system-generated hidden column. I still haven’t been able to find any references in any manual since 8i stating the reverse key index limitation, and have yet to find an official comment about ignoring function-based indexes.


 
 

Update (Dec 2020)

In a recent thread on the Oracle-L mailing list someone pointed out that with the appropriate hints Oracle would use the function-based index in this example, suggesting that this was evidence that the problem was not a limitation but more of a plan selection issue.

This highlighted an important principle: when the local optimizer tries to generate a plan and decomposes the query to produce remote components it thinks it knows what will happen when those component SQL statements reach the remote optimizer, but the remote optimizer may do something different because it knows more about its own data than the local optimizer does.

Here’s the test query with “appropriate” hints added, and the resulting execution plan pulled from memory on an instance of 19.3.0.0:


select
        /*+ leading(t1 t2) use_nl(t2) index(t2_f1) gather_plan_statistics */
        t1.v1, t2.v1
from
        t1,
        t2@&m_target
where
        t2.id+1 = t1.id
and     t1.n1 between 101 and 110
;

select * from table(dbms_xplan.display_cursor(format=>'cost allstats last hint_report remote outline'));


------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| Inst   |IN-OUT| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        | 29040 (100)|        |      |     10 |00:00:00.01 |   17864 |
|   1 |  NESTED LOOPS      |      |      1 |     11 | 29040   (7)|        |      |     10 |00:00:00.01 |   17864 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |     11 |  2394   (6)|        |      |     10 |00:00:00.01 |   17864 |
|   3 |   REMOTE           | T2   |     10 |      1 |  2422   (7)| ORCLP~ | R->S |     10 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1"<=110 AND "T1"."N1">=101))

Remote SQL Information (identified by operation id):
----------------------------------------------------
   3 - SELECT /*+ GATHER_PLAN_STATISTICS INDEX ("T2" "T2_F1") USE_NL ("T2") */ "ID","V1" FROM "T2" "T2"
       WHERE :1="ID"+1 (accessing 'ORCLPDB.LOCALDOMAIN@LOOPBACK' )

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1
           -  leading(t1 t2)

   3 -  SEL$1 / T2@SEL$1
         U -  index(t2 t2_f1) / index specified in the hint doesn't exist
           -  use_nl(t2)

The hint set I supplied was one that said:

  • consider only the join order t1 -> t2
  • use a nested loop to get to t2
  • use the function-based index t2_f1 to access t2

There are several details in the resulting output that tell us that the local optimizer doesn’t know anything about the remote function-based index.

From the top down:

  • the cost of the access to t2 is clearly a tablescan cost – the local optimizer thinks it has to do a tablescan
  • the outline information actually includes the hint FULL(@”SEL$1″ “T2″@”SEL$1”) – the local optimizer thinks it has to do a tablescan
  • the hint report tells us that the hint index(t2 t2_f1) is unused because the index doesn’t exist.

Despite this, the run-time activity was a nested loop join using the t2_f1 index to reach the t2 table. At first sight it would be easy to think that this was because the /*+ index(t2 t2_f1) */ hint was forwarded to the remote database – as shown in the Remote SQL Information section of the plan – but that’s not the case. Because the optimizer has been hinted to do a nested loop from t1 to t2 the join predicate WHERE :1=”ID”+1 was included in the SQL sent to the remote database. Even if the index hint had not been present the remote optimizer would have recognised that the arriving query could best be satisfied by a unique index scan on t2_f1.

This is not a case of Oracle being able to use the correct plan when hinted, it’s a case of being a bit lucky that a set of highly suggestive hints produced a lucky result.  Of course in a very simple query like this it’s fairly easy to get lucky, but in a more complex case (multiple remote tables with multiple candidate indexes) the fact that the optimizer is costing for a tablescan when it should be costing for an index is likely to make it much harder to get lucky.

In this case a search through the shared pool showed that the remote instance had actually used the index unique scan rather than doing the full tablescan the local instance was assuming would happen. It’s far more common, though, for this type of local/remote mismatch  to result in the remote instance doing a full tablescan when the local instance was expecting it to do a high precision indexed access, and this is usually because the remote histogram information isn’t available to the local instance.

To be checked

As time (and versions) pass, limitations in the optimizer are removed, so it’s good to have to revisit old notes and re-run tests. Another effect of coming back to an old test is that you think of new details that might be worth checking. I’ve said in the past that “there’s no such thing as a function-based index”. If the remote index is a multi-column index that starts with one or more base columns, and only introduces the function-based bit later in the index will the local  optimizer still fail to pick it up?

 

1 Comment »

  1. Hi Jonathan,

    Very interesting case, I tried to reproduce it with pg 13.1. I used the standard SQL/MED (Management of External Data) feature to mimic the loopback thing (good idea to quickly test distributed queries, thanks !)
    I learnt important things with your case. Most important point to consider : I can choose if the planner (optimizer) estimates remote cost. Default is FALSE and I obtain the “hash join” plan. If I set the option “use_remote_estimate” to TRUE plan seems OK and very similar to the plan obtain by Oracle CBO in the last hinted query.
    Hints are provided as an extension but in this case they don’t seem to be useful. It’s a good thing since we don’t want our developers to put hints in their queries. They can choose the statistics target and they can create indexes and extended statistics if needed. I set the statistics target to 10000 but the plans and executions times were in fact the same with the default value of 100.

    create unlogged table t1
    as
    with generator(rownum) as (select generate_series(1,1e6))
    select 
            rownum id,
            rownum n1,
            lpad(rownum::text,10,'0')            v1,
            lpad('x',100,'x')          padding
    from
            generator;
    SELECT 1000000
    
    create unlogged table t2 as select * from t1;
    SELECT 1000000
     
    alter table t1 add constraint t1_pk primary key(id);
    ALTER TABLE
    
    alter table t2 add constraint t2_pk primary key(id);
    ALTER TABLE
    
    create unique index t2_f1 on t2((id+1));
    CREATE INDEX
    
    set default_statistics_target = 10000;
    SET
    
    analyze t1;
    ANALYZE
    
    analyze t2;
    ANALYZE
    
    CREATE FOREIGN TABLE t2_loopback (
            id integer,
            n1 integer,
            v1 text,
            padding text
    )
            SERVER loopback
            OPTIONS (schema_name 'pilphi', table_name 't2');
    CREATE FOREIGN TABLE
    
    analyze t2_loopback;
    ANALYZE
    
    explain analyze verbose
    select
            t1.v1, t2.v1
    from
            t1,
            t2
       --   t2_loopback t2
    where
            t2.id+1 = t1.id
    and     t1.n1 between 101 and 110
    ;
                                                               QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------
     Gather  (cost=1000.42..26515.70 rows=10 width=22) (actual time=0.759..177.446 rows=10 loops=1)
       Output: t1.v1, t2.v1
       Workers Planned: 2
       Workers Launched: 2
       -&gt;  Nested Loop  (cost=0.42..25514.70 rows=4 width=22) (actual time=101.107..155.792 rows=3 loops=3)
             Output: t1.v1, t2.v1
             Inner Unique: true
             Worker 0:  actual time=151.321..151.326 rows=0 loops=1
             Worker 1:  actual time=151.866..151.872 rows=0 loops=1
             -&gt;  Parallel Seq Scan on pilphi.t1  (cost=0.00..25480.92 rows=4 width=17) (actual time=101.081..155.715 rows=3 loops=3)
                   Output: t1.id, t1.n1, t1.v1, t1.padding
                   Filter: ((t1.n1 &gt;= '101'::numeric) AND (t1.n1   Index Scan using t2_f1 on pilphi.t2  (cost=0.42..8.44 rows=1 width=17) (actual time=0.013..0.014 rows=1 loops=10)
                   Output: t2.id, t2.n1, t2.v1, t2.padding
                   Index Cond: ((t2.id + '1'::numeric) = t1.id)
     Planning Time: 1.388 ms
     Execution Time: 177.541 ms
    
    
    explain analyze verbose
    select
            t1.v1, t2.v1
    from
            t1,
       --   t2
            t2_loopback t2
    where
            t2.id+1 = t1.id
    and     t1.n1 between 101 and 110
    ;
                                                                      QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------------------------------------
     Hash Join  (cost=26582.05..78688.06 rows=10 width=22) (actual time=183.093..7391.176 rows=10 loops=1)
       Output: t1.v1, t2.v1
       Inner Unique: true
       Hash Cond: (((t2.id + 1))::numeric = t1.id)
       -&gt;  Foreign Scan on pilphi.t2_loopback t2  (cost=100.00..49331.00 rows=1000000 width=15) (actual time=3.442..5671.650 rows=1000000 loops=1)
             Output: t2.id, t2.n1, t2.v1, t2.padding
             Remote SQL: SELECT id, v1 FROM pilphi.t2
       -&gt;  Hash  (cost=26481.92..26481.92 rows=10 width=17) (actual time=179.278..179.524 rows=10 loops=1)
             Output: t1.v1, t1.id
             Buckets: 1024  Batches: 1  Memory Usage: 9kB
             -&gt;  Gather  (cost=1000.00..26481.92 rows=10 width=17) (actual time=2.722..179.476 rows=10 loops=1)
                   Output: t1.v1, t1.id
                   Workers Planned: 2
                   Workers Launched: 2
                   -&gt;  Parallel Seq Scan on pilphi.t1  (cost=0.00..25480.92 rows=4 width=17) (actual time=111.792..170.544 rows=3 loops=3)
                         Output: t1.v1, t1.id
                         Filter: ((t1.n1 &gt;= '101'::numeric) AND (t1.n1   Gather  (cost=1000.00..26481.92 rows=10 width=17) (actual time=0.653..167.575 rows=10 loops=1)
             Output: t1.v1, t1.id
             Workers Planned: 2
             Workers Launched: 2
             -&gt;  Parallel Seq Scan on pilphi.t1  (cost=0.00..25480.92 rows=4 width=17) (actual time=106.041..159.306 rows=3 loops=3)
                   Output: t1.v1, t1.id
                   Filter: ((t1.n1 &gt;= '101'::numeric) AND (t1.n1   Foreign Scan on pilphi.t2_loopback t2  (cost=100.43..108.47 rows=1 width=17) (actual time=0.849..0.851 rows=1 loops=10)
             Output: t2.id, t2.n1, t2.v1, t2.padding
             Remote SQL: SELECT id, v1 FROM pilphi.t2 WHERE (($1::numeric = (id + 1)))
     Planning Time: 4.334 ms
     Execution Time: 179.092 ms
    
    

    Regards,
    Phil Florent

    Comment by Phil Florent — December 3, 2020 @ 7:12 pm GMT Dec 3,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.