Strange Execution Plan with Oracle Invisible Index

Question

My question is about a strange execution plan I am seeing while working with INVISIBLE indexes. Database 11g

Two invisible indexes were created for performance testing of a specific query.

When I am running that query without any hint and parameter OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE, the query is doing expected full table scans and returns rows in 148 sec with degree of parallelism of 21 (Query #1 below)

When I am running that query with hint /*+ USE_INVISIBLE_INDEXES */ and parameter OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE, the query is doing INDEX FAST FULL SCAN and returns rows in 122 sec with degree of parallelism of 26 (Query #2 below)

When I am running that query by naming the indexes with named hint /*+ INDEX */ and parameter OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE, the query is doing INDEX FULL SCAN and INDEX RANGE SCAN and taking the maximum time of 649 sec with degree of parallelism of 13 (Query #3 below)

The trend of d.o.p and the plan and the execution time remains this way whichever order I run the queries.

Was curious if anybody out there can explain why naming the indexes is causing the highest cost and return time ? If I have to use the indexes, then I have to name it. Using hint /*+ USE_INVISIBLE_INDEXES */ is not an option after code promotion.

Indexes :

CREATE INDEX INVOICELINE_IDX_PRF2
ON INVOICELINE (invoiceheaderid, chargetypeid, agreementid, unpaidamount)
INVISIBLE COMPUTE STATISTICS ;

CREATE INDEX INVOICEHEADER_IDX_PRF0
ON INVOICEHEADER (id, TRUNC(invoiceduedate))
INVISIBLE COMPUTE STATISTICS ;

With hint : No hint used ; Full table scans

Output time : 148 sec

Query #1 :

    WITH V1
    AS 
    (
        SELECT 
             T1.agreementid             AS  agrmnt_id
            ,T1.invoicelineamount       AS  invc_line_amt
            ,TRUNC(T2.invoiceduedate)   AS  invc_due_dt
            ,T1.unpaidamount            AS  unpaid_amt
            ,T3.groupname               AS  grp_nm
        FROM
             INVOICELINE        T1
            ,INVOICEHEADER      T2
            ,CHARGETYPE         T3
        WHERE 1=1
          AND T2.id = T1.invoiceheaderid
          AND T3.id = T1.chargetypeid
    )
    SELECT /*+ PARALLEL(AUTO) */
        agrmnt_id, invc_due_dt, SUM(unpaid_amt) AS sum_amount
    FROM
        V1
    WHERE 1=1
      AND UPPER(grp_nm)='INSTALMENT'
    GROUP BY
        agrmnt_id, invc_due_dt
    ;

Execution Plan:

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                  |       |       |       |  6329 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                            |                  |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                      | :TQ10003         |  1381K|    55M|       |  6329   (6)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                           |                  |  1381K|    55M|    68M|  6329   (6)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                             |                  |  1381K|    55M|       |  6329   (6)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH                          | :TQ10002         |  1381K|    55M|       |  6329   (6)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY                        |                  |  1381K|    55M|    68M|  6329   (6)| 00:00:01 |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN                           |                  |  1381K|    55M|       |  5804   (6)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE                         |                  |  1381K|    35M|       |  3928   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND BROADCAST                 | :TQ10001         |  1381K|    35M|       |  3928   (7)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |
|  10 |           VIEW                             | VW_GBC_13        |  1381K|    35M|       |  3928   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |            HASH GROUP BY                   |                  |  1381K|    63M|    79M|  3928   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|  12 |             PX RECEIVE                     |                  |  1381K|    63M|       |  3928   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|  13 |              PX SEND HASH                  | :TQ10000         |  1381K|    63M|       |  3928   (7)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  14 |               HASH GROUP BY                |                  |  1381K|    63M|    79M|  3928   (7)| 00:00:01 |  Q1,00 | PCWP |            |
|* 15 |                HASH JOIN                   |                  |  1381K|    63M|       |  3348   (8)| 00:00:01 |  Q1,00 | PCWP |            |
|  16 |                 JOIN FILTER CREATE         | :BF0000          |     6 |   162 |       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 17 |                  TABLE ACCESS STORAGE FULL | CHARGETYPE       |     6 |   162 |       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  18 |                 JOIN FILTER USE            | :BF0000          |   138M|  2766M|       |  3324   (7)| 00:00:01 |  Q1,00 | PCWP |            |
|  19 |                  PX BLOCK ITERATOR         |                  |   138M|  2766M|       |  3324   (7)| 00:00:01 |  Q1,00 | PCWC |            |
|* 20 |                   TABLE ACCESS STORAGE FULL| INVOICELINE      |   138M|  2766M|       |  3324   (7)| 00:00:01 |  Q1,00 | PCWP |            |
|  21 |         PX BLOCK ITERATOR                  |                  |   129M|  1857M|       |  1855   (2)| 00:00:01 |  Q1,02 | PCWC |            |
|* 22 |          TABLE ACCESS STORAGE FULL         | INVOICEHEADER    |   129M|  1857M|       |  1855   (2)| 00:00:01 |  Q1,02 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------

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

    7 - access(T2.ID=ITEM_1)
    15 - access(T3.ID=T1.CHARGETYPEID)
    17 - storage(UPPER(T3.GROUPNAME)=U'INSTALMENT')
       filter(UPPER(T3.GROUPNAME)=U'INSTALMENT')
    20 - storage(:Z>=:Z AND :Z<=:Z AND SYS_OP_BLOOM_FILTER(:BF0000,T1.CHARGETYPEID))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,T1.CHARGETYPEID))
    22 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - automatic DOP: Computed Degree of Parallelism is 21

With hint : /*+ USE_INVISIBLE_INDEXES */

Output time : 122 sec

Query #2 :

    WITH V1
    AS 
    (
        SELECT /*+ USE_INVISIBLE_INDEXES */
             T1.agreementid             AS  agrmnt_id
            ,T1.invoicelineamount       AS  invc_line_amt
            ,TRUNC(T2.invoiceduedate)   AS  invc_due_dt
            ,T1.unpaidamount            AS  unpaid_amt
            ,T3.groupname               AS  grp_nm
        FROM
             INVOICELINE        T1
            ,INVOICEHEADER      T2
            ,CHARGETYPE         T3
        WHERE 1=1
          AND T2.id = T1.invoiceheaderid
          AND T3.id = T1.chargetypeid
    )
    SELECT /*+ PARALLEL(AUTO) */
        agrmnt_id, invc_due_dt, SUM(unpaid_amt) AS sum_amount
    FROM
        V1
    WHERE 1=1
      AND UPPER(grp_nm)='INSTALMENT'
    GROUP BY
        agrmnt_id, invc_due_dt
    ;

Execution plan:

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                           |       |       |       |  1505 (100)|          |        |      |            |
|   1 |  PX COORDINATOR                              |                           |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                        | :TQ10003                  |  1381K|    55M|       |  1505   (7)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                             |                           |  1381K|    55M|    68M|  1505   (7)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                               |                           |  1381K|    55M|       |  1505   (7)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH                            | :TQ10002                  |  1381K|    55M|       |  1505   (7)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY                          |                           |  1381K|    55M|    68M|  1505   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN                             |                           |  1381K|    55M|       |  1082   (9)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE                           |                           |  1381K|    35M|       |   815   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND BROADCAST                   | :TQ10001                  |  1381K|    35M|       |   815   (7)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |
|  10 |           VIEW                               | VW_GBC_13                 |  1381K|    35M|       |   815   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |            HASH GROUP BY                     |                           |  1381K|    63M|    79M|   815   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|  12 |             PX RECEIVE                       |                           |  1381K|    63M|       |   815   (7)| 00:00:01 |  Q1,01 | PCWP |            |
|  13 |              PX SEND HASH                    | :TQ10000                  |  1381K|    63M|       |   815   (7)| 00:00:01 |  Q1,00 | P->P | HASH       |
|  14 |               HASH GROUP BY                  |                           |  1381K|    63M|    79M|   815   (7)| 00:00:01 |  Q1,00 | PCWP |            |
|* 15 |                HASH JOIN                     |                           |  1381K|    63M|       |   346  (15)| 00:00:01 |  Q1,00 | PCWP |            |
|* 16 |                 TABLE ACCESS STORAGE FULL    | CHARGETYPE                |     6 |   162 |       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  17 |                 PX BLOCK ITERATOR            |                           |   138M|  2766M|       |   326  (10)| 00:00:01 |  Q1,00 | PCWC |            |
|* 18 |                  INDEX STORAGE FAST FULL SCAN| INVOICELINE_IDX_PRF2      |   138M|  2766M|       |   326  (10)| 00:00:01 |  Q1,00 | PCWP |            |
|  19 |         PX BLOCK ITERATOR                    |                           |   129M|  1857M|       |   250  (11)| 00:00:01 |  Q1,02 | PCWC |            |
|* 20 |          INDEX STORAGE FAST FULL SCAN        | INVOICEHEADER_IDX_PRF0    |   129M|  1857M|       |   250  (11)| 00:00:01 |  Q1,02 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

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

    7 - access(T2.ID=ITEM_1)
    15 - access(T3.ID=T1.CHARGETYPEID)
    16 - storage(UPPER(T3.GROUPNAME)=U'INSTALMENT')
       filter(UPPER(T3.GROUPNAME)=U'INSTALMENT')
    18 - storage(:Z>=:Z AND :Z<=:Z)
    20 - storage(:Z>=:Z AND :Z<=:Z)

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - automatic DOP: Computed Degree of Parallelism is 26

With hint : /*+ INDEX(T1 INVOICELINE_IDX_PRF2) INDEX(T2 INVOICEHEADER_IDX_PRF0) */

Output time : 649 sec

Query #3 :

    WITH V1
    AS 
    (
        SELECT /*+ INDEX(T1 INVOICELINE_IDX_PRF2) INDEX(T2 INVOICEHEADER_IDX_PRF0) */
             T1.agreementid             AS  agrmnt_id
            ,T1.invoicelineamount       AS  invc_line_amt
            ,TRUNC(T2.invoiceduedate)   AS  invc_due_dt
            ,T1.unpaidamount            AS  unpaid_amt
            ,T3.groupname               AS  grp_nm
        FROM
             INVOICELINE        T1
            ,INVOICEHEADER      T2
            ,CHARGETYPE         T3
        WHERE 1=1
          AND T2.id = T1.invoiceheaderid
          AND T3.id = T1.chargetypeid
    )
    SELECT /*+ PARALLEL(AUTO) */
        agrmnt_id, invc_due_dt, SUM(unpaid_amt) AS sum_amount
    FROM
        V1
    WHERE 1=1
      AND UPPER(grp_nm)='INSTALMENT'
    GROUP BY
        agrmnt_id, invc_due_dt
    ;

Execution plan:

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |       |       |       |   658K(100)|          |        |      |            |
|   1 |  PX COORDINATOR                         |                           |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)                   | :TQ10002                  |  1381K|    55M|       |   658K  (1)| 00:00:26 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                        |                           |  1381K|    55M|    68M|   658K  (1)| 00:00:26 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                          |                           |  1381K|    55M|       |   658K  (1)| 00:00:26 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH                       | :TQ10001                  |  1381K|    55M|       |   658K  (1)| 00:00:26 |  Q1,01 | P->P | HASH       |
|   6 |       HASH GROUP BY                     |                           |  1381K|    55M|    68M|   658K  (1)| 00:00:26 |  Q1,01 | PCWP |            |
|   7 |        NESTED LOOPS                     |                           |  1381K|    55M|       |   657K  (1)| 00:00:26 |  Q1,01 | PCWP |            |
|   8 |         VIEW                            | VW_GBC_11                 |  1381K|    35M|       |   302K  (1)| 00:00:12 |  Q1,01 | PCWP |            |
|   9 |          HASH GROUP BY                  |                           |  1381K|    63M|    79M|   302K  (1)| 00:00:12 |  Q1,01 | PCWP |            |
|  10 |           PX RECEIVE                    |                           |  1381K|    63M|       |   302K  (1)| 00:00:12 |  Q1,01 | PCWP |            |
|  11 |            PX SEND HASH                 | :TQ10000                  |  1381K|    63M|       |   302K  (1)| 00:00:12 |  Q1,00 | P->P | HASH       |
|  12 |             HASH GROUP BY               |                           |  1381K|    63M|    79M|   302K  (1)| 00:00:12 |  Q1,00 | PCWP |            |
|  13 |              NESTED LOOPS               |                           |  1381K|    63M|       |   301K  (1)| 00:00:12 |  Q1,00 | PCWP |            |
|  14 |               PX BLOCK ITERATOR         |                           |       |       |       |            |          |  Q1,00 | PCWC |            |
|* 15 |                TABLE ACCESS STORAGE FULL| CHARGETYPE                |     6 |   162 |       |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 16 |               INDEX FULL SCAN           | INVOICELINE_IDX_PRF2      |   222K|  4569K|       | 50330   (1)| 00:00:02 |  Q1,00 | PCWP |            |
|* 17 |         INDEX RANGE SCAN                | INVOICEHEADER_IDX_PRF0    |     1 |    15 |       |     0   (0)|          |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------

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

    15 - storage(:Z>=:Z AND :Z<=:Z AND UPPER(T3.GROUPNAME)=U'INSTALMENT')
       filter(UPPER(T3.GROUPNAME)=U'INSTALMENT')
    16 - access(T3.ID=T1.CHARGETYPEID)
       filter(T3.ID=T1.CHARGETYPEID)
    17 - access(T2.ID=ITEM_1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - automatic DOP: Computed Degree of Parallelism is 13

EDIT

Table counts

                                   SAMPLE_SIZE  LAST_ANALYZED
INVOICELINE    Count = 138,145,934  71258201    04-JUN-18
INVOICEHEADER  Count = 129,865,795 133224960    02-APR-19 
CHARGETYPE     Count = 620               597    15-JUN-18

can you please tell me how to do it here ? I have tried several times, but its coming all jumbled up :( — Jay
Stack Overflow uses a simple text formatting system called MarkDown (instructions here). Query plans would normally be formatted the same way as code. To do this you add four spaces to the beginning of each line. If you've pasted something into your question (or answer) and it looks all jumbled you can select that section of your message and click the {} "format-as-code" icon at the top of the edit box, which will automatically add four spaces to the beginning of each selected line. — Bob Jarvis - Reinstate Monica
Oracle uses INDEX STORAGE FAST FULL SCAN for query #2. The documentation of INDEX Hint is not entirely clear, but I guess that this hint forces the database to use INDEX FULL SCAN instead of INDEX FAST FULL SCAN for query #3. There is a separate hint: INDEX_FFS -perform a fast full index scan, use this hint. — krokodilko

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2019-04-08T21:45:45

Hmmm...I can't say I'm surprised. Based on the table row counts, sample size, and last-analyzed info, I'd say your best bet is to at a minimum have the INVOICELINE and CHARGETYPE tables analyzed, as in

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS('owner', 'INVOICEHEADER');
  DBMS_STATS.GATHER_TABLE_STATS('owner', 'INVOICELINE');
  DBMS_STATS.GATHER_TABLE_STATS('owner', 'CHARGETYPE');
END;

Change 'owner' above to the correct owner of the table.

CHARGETYPE is a pretty small table and probably isn't affecting things much, but it won't hurt. INVOICELINE, on the other hand, has almost doubled in size since it was last analyzed - and that analysis was over nine months ago so those stats are probably very stale. Give it a try.

Any time I see odd things happening in a plan my first question is always "How good are the stats on the tables involved?". Gathering stats is pretty safe and is unlikely to have any negative effects, except for the time that it takes those procedures to run, so it's generally a good first point-of-attack on a problem like this.

Best of luck.

Strange Execution Plan with Oracle Invisible Index

EDIT

1 Answers