Per Connor's request, I asked our DBA to generate the trace files and sent them to the asktom_us e-mail address.

Thanks.

Alfred
------------------------------------------------------------------------------
Hello,

I have observed in an Oracle 12c instance, where the optimizer is choosing to use an Index Full Scan over and Index Fast Full Scan (FFS) when it really should be using the latter. Here is a simple example using one of the Oracle SYS tables, but in our production system, I have observed this in our of our larger tables:

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 15 10:23:51 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Mon Jan 15 2018 10:22:23 -05:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> set autotrace on
SQL> select  count(*) n
  2  from    sys.obj$ t
  3  ;

         N
----------
     26756


Execution Plan
----------------------------------------------------------
Plan hash value: 2527374323

-------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |    17   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |        |     1 |            |          |
|   2 |   INDEX FULL SCAN| I_OBJ1 | 26757 |    17   (0)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         88  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select  /*+ index_ffs(t I_OBJ1) */ count(*) n
  2  from    sys.obj$ t
  3  ;

         N
----------
     26756


Execution Plan
----------------------------------------------------------
Plan hash value: 3951003077

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    23   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_OBJ1 | 26757 |    23   (0)| 00:00:01 |
------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         93  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

In this case, with the sys.obj$ table, the difference in run time is small, but the query with the FFS, using the hint, runs a bit faster than the default query without the hint. In production, the difference in time is measured in hours (or even days). In theory, and in practice, using the FFS would be the better choice. However, for some reason the optimizer seems to conclude that cost of using the FFS is more than the cost of using the Full Scan.

I have not seen this in another 12c instance (same version, but more powerful machine) that we have. We have also compared system parameters between these two systems, and nothing seems to be out of order.

I have searched the interweb looking for how the cost is calculated, but there many sources mention that there are so many variables involved and I cannot find a precise formula for this calculation.


Is there something that needs to be tweaked in order to get the optimizer to choose the FFS over a Full Scan without using a hint (where appropriate)?

Thanks.

Alfred

  Access Path: index (index (FFS))
    Index: I_OBJ1
    resc_io: 23.000000  resc_cpu: 3823644
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 

  Access Path: index (FullScan)
    Index: I_OBJ1
    resc_io: 87.000000  resc_cpu: 5971565
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 

optimizer_index_cost_adj            = 20

  Access Path: index (FullScan)
    Index: I_OBJ1
    resc_io: 87.000000  resc_cpu: 5971565
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
 ===>   Cost: 17.450418  Resp: 17.450418  Degree: 1   <======

SQL> select 87/5 from dual;

      87/5
----------
      17.4