Plan change with OR expansion

From: Pap <oracle.developer35_at_gmail.com>
Date: Sat, 19 Feb 2022 02:20:33 +0530
Message-ID: <CAEjw_fi3X4UY69tJy2W9dCYuDak8=PV62e6-Ve7qsT9DTfpomw_at_mail.gmail.com>

Hello Experts, We are seeing two different plans for the same remote query. The difference in cost between them is huge i.e. ~29K VS 19 Million. Currently the query is going for the good plan by default when we test it manually locally in the remote database, so wanted to understand if the below plan gives any clue about why it opted for the bad plan when it was spawned from the DB link that has such a big difference in cost?

This select is spawned as part of a DML query from the main database. In phv-A below its not doing OR expansion but in the phv-b its opting for OR expansion path. Same set of Indexes were used in both the queries , so we can ignore the thought of unusable indexes.

V$sql_shared_cursor is just having a bad plan in it and showing 'rolling_invalid_mismatch' flash as 'Y'. But in that case will the cost be deviated by such a big margin and also i am unable to get any hints from the estimated cardinality/costs in below paths, which can point to any specific object.

Plan_hash_value - A

| 0 | SELECT STATEMENT | |

      |       | 29463 (100)|          |       |       |


|   1 |  NESTED LOOPS OUTER                    |                          |


   18 |  5058 | 29463   (1)| 00:05:54 |       |       |


|   2 |   NESTED LOOPS                         |                          |


   18 |  4338 | 29403   (1)| 00:05:53 |       |       |


|   3 |    NESTED LOOPS                        |                          |


   18 |  3978 |    75   (2)| 00:00:01 |       |       |


|   4 |     NESTED LOOPS                       |                          |


   18 |  3888 |    57   (2)| 00:00:01 |       |       |


|   5 |      PARTITION RANGE ITERATOR          |                          |


   18 |  3744 |    39   (3)| 00:00:01 |   186 |   194 |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| TFTD                     |
   18 |  3744 |    39   (3)| 00:00:01 |   186 |   194 |


|   7 |        BITMAP CONVERSION TO ROWIDS     |                          |


      |       |            |          |       |       |


|   8 |         BITMAP OR                      |                          |


      |       |            |          |       |       |


|   9 |          BITMAP CONVERSION FROM ROWIDS |                          |


      |       |            |          |       |       |


|  10 |           SORT ORDER BY                |                          |


      |       |            |          |       |       |
|* 11 |            INDEX RANGE SCAN            | TFTD_IX4                 |
      |       |    28   (0)| 00:00:01 |   186 |   194 |


|  12 |          BITMAP CONVERSION FROM ROWIDS |                          |


      |       |            |          |       |       |
|* 13 |           INDEX RANGE SCAN             | TFTD_IX10                |
      |       |     1   (0)| 00:00:01 |   186 |   194 |


|  14 |      TABLE ACCESS BY INDEX ROWID       | TMCT                     |


    1 |     8 |     1   (0)| 00:00:01 |       |       |
|* 15 |       INDEX RANGE SCAN                 | TMCT_IX1                 |
    1 |       |     0   (0)|          |       |       |


|  16 |     TABLE ACCESS BY INDEX ROWID        | TMMC                     |


    1 |     5 |     1   (0)| 00:00:01 |       |       |
|* 17 |      INDEX RANGE SCAN                  | TMMC_IX1                 |
    1 |       |     0   (0)|          |       |       |


|  18 |    PARTITION RANGE ALL                 |                          |


    1 |    20 |  1629   (0)| 00:00:20 |     1 |   535 |


|  19 |     TABLE ACCESS BY LOCAL INDEX ROWID  | TSSS                     |


    1 |    20 |  1629   (0)| 00:00:20 |     1 |   535 |
|* 20 |      INDEX RANGE SCAN                  | TSSS_IX2                 |
    1 |       |  1628   (0)| 00:00:20 |     1 |   535 |


|  21 |   PARTITION RANGE AND                  |                          |


    1 |    40 |     4   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 22 |    TABLE ACCESS BY LOCAL INDEX ROWID   | TFMA                     |
    1 |    40 |     4   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 23 |     INDEX RANGE SCAN                   | TFMA_IX1                 |
    3 |       |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
-----------------------------------------------------------------------------------------------------------------------------------



Plan_hash_value - B


-------------------------------------------------------------------------------------------------------------------------------------


| Id  | Operation                                | Name


| Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT                         |


 |       |       |    19M(100)|          |       |       |


|   1 |  CONCATENATION                           |


 |       |       |            |          |       |       |


|   2 |   NESTED LOOPS                           |


 |    18 |  5058 | 29821   (1)| 00:05:58 |       |       |


|   3 |    NESTED LOOPS                          |


 |    18 |  5058 | 29821   (1)| 00:05:58 |       |       |


|   4 |     NESTED LOOPS OUTER                   |


 |    18 |  4698 |   493   (1)| 00:00:06 |       |       |


|   5 |      NESTED LOOPS                        |


 |    18 |  3978 |   433   (0)| 00:00:06 |       |       |


|   6 |       NESTED LOOPS                       |


 |    18 |  3888 |   415   (0)| 00:00:05 |       |       |


|   7 |        PARTITION RANGE ITERATOR          |


 |    18 |  3744 |   397   (0)| 00:00:05 |   186 |   194 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| TFTD


|    18 |  3744 |   397   (0)| 00:00:05 |   186 |   194 |


|*  9 |          INDEX RANGE SCAN                | TFTD_IX4


|    39 |       |    28   (0)| 00:00:01 |   186 |   194 |

|  10 |        TABLE ACCESS BY INDEX ROWID       | TMCT


|     1 |     8 |     1   (0)| 00:00:01 |       |       |


|* 11 |         INDEX RANGE SCAN                 | TMCT_IX1


|     1 |       |     0   (0)|          |       |       |

|  12 |       TABLE ACCESS BY INDEX ROWID        | TMMC


|     1 |     5 |     1   (0)| 00:00:01 |       |       |


|* 13 |        INDEX RANGE SCAN                  | TMMC_IX1


|     1 |       |     0   (0)|          |       |       |


|  14 |      PARTITION RANGE AND                 |


 |     1 |    40 |     4   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 15 |       TABLE ACCESS BY LOCAL INDEX ROWID  | TFMA


|     1 |    40 |     4   (0)| 00:00:01 |KEY(AP)|KEY(AP)|


|* 16 |        INDEX RANGE SCAN                  | TFMA_IX1


|     3 |       |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|


|  17 |     PARTITION RANGE ALL                  |


 |     1 |       |  1628   (0)| 00:00:20 |     1 |   535 |
|* 18 |      INDEX RANGE SCAN                    | TSSS_IX2


|     1 |       |  1628   (0)| 00:00:20 |     1 |   535 |

|  19 |    TABLE ACCESS BY LOCAL INDEX ROWID     | TSSS

|     1 |    20 |  1629   (0)| 00:00:20 |     1 |     1 |


|  20 |   NESTED LOOPS OUTER                     |


 |   520 |   142K|    19M  (3)| 66:22:06 |       |       |
|* 21 |    HASH JOIN                             |
 |   520 |   122K|    19M  (3)| 66:21:41 |       |       |


|  22 |     TABLE ACCESS STORAGE FULL            | TMCT


|   189 |  1512 |    24   (0)| 00:00:01 |       |       |


|* 23 |     HASH JOIN                            |
 |   520 |   118K|    19M  (3)| 66:21:41 |       |       |


|  24 |      TABLE ACCESS STORAGE FULL           | TMMC

|   110 |   550 |    24   (0)| 00:00:01 |       |       |


|  25 |      NESTED LOOPS                        |


 |   520 |   115K|    19M  (3)| 66:21:41 |       |       |


|  26 |       NESTED LOOPS                       |


 |    17G|   115K|    19M  (3)| 66:21:41 |       |       |


|  27 |        PARTITION RANGE ALL               |


 |    17G|   325G|    19M  (2)| 66:01:11 |     1 |   535 |


|  28 |         TABLE ACCESS STORAGE FULL        | TSSS

|    17G|   325G|    19M  (2)| 66:01:11 |     1 |   535 |


|  29 |        PARTITION RANGE ITERATOR          |


 |     1 |       |     0   (0)|          |   186 |   194 |
|* 30 |         INDEX RANGE SCAN                 | TFTD_IX10
 |     1 |       |     0   (0)|          |   186 |   194 |
|* 31 |       TABLE ACCESS BY LOCAL INDEX ROWID  | TFTD


|     1 |   208 |     0   (0)|          |     1 |     1 |


|  32 |    PARTITION RANGE AND                   |


 |     1 |    40 |     4   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 33 |     TABLE ACCESS BY LOCAL INDEX ROWID    | TFMA


|     1 |    40 |     4   (0)| 00:00:01 |KEY(AP)|KEY(AP)|


|* 34 |      INDEX RANGE SCAN                    | TFMA_IX1


|     3 |       |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|


-------------------------------------------------------------------------------------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 18 2022 - 21:50:33 CET

This message: [ Message body ]
Next message: Sayan Malakshinov: "Re: Plan change with OR expansion"
Previous message: Andy Sayer: "Re: Sql with Connect By is Slow"
In reply to Krishnaprasad Yadav: "Sql with Connect By is Slow"
Next in thread: Sayan Malakshinov: "Re: Plan change with OR expansion"
Reply: Sayan Malakshinov: "Re: Plan change with OR expansion"
Reply: Pap: "Re: Plan change with OR expansion"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message