Home -> Mailing Lists -> Oracle-L -> RE: Plan change with OR expansion

RE: Plan change with OR expansion

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 20 Feb 2022 10:03:02 -0500
Message-ID: <01e601d8266a$f7192a00$e54b7e00$_at_rsiz.com> 








In which database is FORCE_VIEW1 defined?

 



Humor me and try it with the text in-line instead of using the view to see whether that makes a difference between local and remote plans.

 



mwf

 



From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap
Sent: Saturday, February 19, 2022 3:39 PM
To: Sayan Malakshinov


Cc: Oracle L


Subject: Re: Plan change with OR expansion

 



There are ~5 tables used in this query and the plan is also not very long, but if it's the statistics, which is the only cause here behind this big deviation in cost of the two plans. It should look obvious from the estimation of rows from one odd table, but I am unable to see that. And also as all the indexes are used in both the plans so the point of unusable index is out of context. Am I missing anything here?

 



On Sat, Feb 19, 2022 at 6:03 PM Pap <oracle.developer35_at_gmail.com> wrote:



Thank You Sayan. The symptoms suggesting some stats change may be the reason behind this plan change but I am unable to get any clue from the estimated rows in below two plans.  Optimizers know the table TSSS is a big one holding billion rows but still going for partition range all in that killing performance.  Do you see any suspects?

 



Now as i can not run the original DML but runs the select part of that, it now goes for the good execution both from remote and local database. So not sure , if the bad plan has something related to DML only? My worry is, it can again impact when DML will be triggered again from application and spawn the remote SELECT query, so I wanted to understand the cause of this drastically different cost plans and how to avoid this? 

 



Actually I was seeing two child cursors in v$sql_shared_cursor in the remote database for spawned SELECT query:-  first was good phv and the second child cursor was bad phv. So it means the good plan was definitely taken by the remote query for few of the executions. But unfortunately I don't see those good phv executions captured in ASH/AWR(Dba_hist_sqlstat or dba_hist_active_sess_history) which can be because of very quick executions. And yes,  its version 11.2.0.4 database.



Query submitted in local database is below DML which uses a force View:- 



INSERT INTO GTT1 SELECT FV.* FROM FORCE_VIEW1 FV WHERE FV.ANUM = :B2 OR FV.TNUM = :B1;


Query Spawned in Remote Database is below SELECT:- 



SELECT /*+*/.....



  FROM "FORCE_VIEW1" "FV1"



 WHERE "ANUM" = :1 OR "TNUM" = :2

 
 
 Below is the script of the above view:- FORCE_VIEW1
 


 select....


 FROM TFTD , TFMA ,TMCT ,TLCO , TSSS 


    WHERE     TFTD.TID = TFMA.TID(+)      AND TFTD.SID = TSSS.SID
          AND TFTD.SB_ID = TSSS.SB_ID      AND TFTD.SDT = TFMA.SDT(+)
          AND TFTD.SRCODE = TMCT.SCODE    AND TFTD.MCODE = TLCO.MCODE
          AND TFTD.SDT BETWEEN TO_DATE ('01/24/2022', 'MM/DD/YYYY')  AND TO_DATE ('02/01/2022', 'MM/DD/YYYY');

 

 





Below is the plan with format=>'advanced':- 

 



**************Plan_hash_value - A





| Id  | Operation                              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |






|   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)|
-----------------------------------------------------------------------------------------------------------------------------------






Outline Data




  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$5C160134")
      MERGE(_at_"SEL$335DD26A")
      OUTLINE(_at_"SEL$1")
      OUTLINE(_at_"SEL$335DD26A")
      MERGE(_at_"SEL$3")
      OUTLINE(_at_"SEL$2")
      OUTLINE(_at_"SEL$3")
      BITMAP_TREE(_at_"SEL$5C160134" "TFTD"_at_"SEL$2" OR(1 1 ("TFTD"."ANUM" "TFTD"."TID") 2 ("TFTD"."TNUM")))
      INDEX_RS_ASC(_at_"SEL$5C160134" "TMCT"_at_"SEL$2" ("TMCT"."SCODE"))
      INDEX_RS_ASC(_at_"SEL$5C160134" "TMMC"_at_"SEL$3" ("TMMC"."PCODE"))
      INDEX_RS_ASC(_at_"SEL$5C160134" "TSSS"_at_"SEL$2" ("TSSS"."SB_ID" "TSSS"."DID" "TSSS"."SID"))
      INDEX_RS_ASC(_at_"SEL$5C160134" "TFMA"_at_"SEL$2" ("TFMA"."TID" "TFMA"."MACD"))
      LEADING(_at_"SEL$5C160134" "TFTD"_at_"SEL$2" "TMCT"@"SEL$2" "TMMC"@"SEL$3" "TSSS"@"SEL$2" "TFMA"@"SEL$2")
      USE_NL(_at_"SEL$5C160134" "TMCT"_at_"SEL$2")
      USE_NL(_at_"SEL$5C160134" "TMMC"_at_"SEL$3")
      USE_NL(_at_"SEL$5C160134" "TSSS"_at_"SEL$2")
      USE_NL(_at_"SEL$5C160134" "TFMA"_at_"SEL$2")
      END_OUTLINE_DATA




  */
 


Peeked Binds (identified by position):




   1 - :1 (VARCHAR2(30), CSID=873): '1231231333332331'

   2 - :2 (VARCHAR2(30), CSID=873): '1231231333332331'

 
Predicate Information (identified by operation id):



   6 - filter("TFTD"."SDT"<=TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  11 - access("TFTD"."ANUM"=:1)
       filter("TFTD"."ANUM"=:1)
  13 - access("TFTD"."TNUM"=:2)
  15 - access("TFTD"."SRCODE"="TMCT"."SCODE")
  17 - access("TFTD"."MCODE"="PCODE")
  20 - access("TFTD"."SB_ID"="TSSS"."SB_ID" AND "TFTD"."SID"="TSSS"."SID")
       filter("TFTD"."SID"="TSSS"."SID")
  22 - filter(("TFMA"."SDT"<=TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFTD"."SDT"="TFMA"."SDT"))
  23 - access("TFTD"."TID"="TFMA"."TID")
  
  
  
****************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)|
-------------------------------------------------------------------------------------------------------------------------------------





Outline Data




  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$5C160134")
      MERGE(_at_"SEL$335DD26A")
      OUTLINE_LEAF(_at_"SEL$5C160134_1")
      USE_CONCAT(_at_"SEL$5C160134" 8 OR_PREDICATES(1) PREDICATE_REORDERS((3 2) (2 3)))
      OUTLINE_LEAF(_at_"SEL$5C160134_2")
      OUTLINE(_at_"SEL$1")
      OUTLINE(_at_"SEL$335DD26A")
      MERGE(_at_"SEL$3")
      OUTLINE(_at_"SEL$5C160134")
      MERGE(_at_"SEL$335DD26A")
      OUTLINE(_at_"SEL$2")
      OUTLINE(_at_"SEL$3")
      INDEX_RS_ASC(_at_"SEL$5C160134_1" "TFTD"_at_"SEL$2" ("TFTD"."ANUM" "TFTD"."TID"))
      INDEX_RS_ASC(_at_"SEL$5C160134_1" "TMCT"_at_"SEL$2" ("TMCT"."SCODE"))
      INDEX_RS_ASC(_at_"SEL$5C160134_1" "TMMC"_at_"SEL$3" ("TMMC"."PCODE"))
      INDEX_RS_ASC(_at_"SEL$5C160134_1" "TFMA"_at_"SEL$2" ("TFMA"."TID" "TFMA"."MACD"))
      INDEX(_at_"SEL$5C160134_1" "TSSS"_at_"SEL$2" ("TSSS"."SB_ID" "TSSS"."DID" "TSSS"."SID"))
      FULL(_at_"SEL$5C160134_2" "TSSS"_at_"SEL$5C160134_2")
      INDEX(_at_"SEL$5C160134_2" "TFTD"_at_"SEL$5C160134_2" ("TFTD"."TNUM"))
      FULL(_at_"SEL$5C160134_2" "TMMC"_at_"SEL$5C160134_2")
      FULL(_at_"SEL$5C160134_2" "TMCT"_at_"SEL$5C160134_2")
      INDEX_RS_ASC(_at_"SEL$5C160134_2" "TFMA"_at_"SEL$5C160134_2" ("TFMA"."TID" "TFMA"."MACD"))
      LEADING(_at_"SEL$5C160134_1" "TFTD"_at_"SEL$2" "TMCT"@"SEL$2" "TMMC"@"SEL$3" "TFMA"@"SEL$2" "TSSS"@"SEL$2")
      LEADING(_at_"SEL$5C160134_2" "TSSS"_at_"SEL$5C160134_2" "TFTD"@"SEL$5C160134_2" "TMMC"@"SEL$5C160134_2" "TMCT"@"SEL$5C160134_2" "TFMA"@"SEL$5C160134_2")
      USE_NL(_at_"SEL$5C160134_1" "TMCT"_at_"SEL$2")
      USE_NL(_at_"SEL$5C160134_1" "TMMC"_at_"SEL$3")
      USE_NL(_at_"SEL$5C160134_1" "TFMA"_at_"SEL$2")
      USE_NL(_at_"SEL$5C160134_1" "TSSS"_at_"SEL$2")
      NLJ_BATCHING(_at_"SEL$5C160134_1" "TSSS"_at_"SEL$2")
      USE_NL(_at_"SEL$5C160134_2" "TFTD"_at_"SEL$5C160134_2")
      NLJ_BATCHING(_at_"SEL$5C160134_2" "TFTD"_at_"SEL$5C160134_2")
      USE_HASH(_at_"SEL$5C160134_2" "TMMC"_at_"SEL$5C160134_2")
      USE_HASH(_at_"SEL$5C160134_2" "TMCT"_at_"SEL$5C160134_2")
      USE_NL(_at_"SEL$5C160134_2" "TFMA"_at_"SEL$5C160134_2")
      SWAP_JOIN_INPUTS(_at_"SEL$5C160134_2" "TMMC"_at_"SEL$5C160134_2")
      SWAP_JOIN_INPUTS(_at_"SEL$5C160134_2" "TMCT"_at_"SEL$5C160134_2")
      END_OUTLINE_DATA




  */
 


Peeked Binds (identified by position):




   1 - :1 (VARCHAR2(30), CSID=873): '2432342344434242'

   2 - :2 (VARCHAR2(30), CSID=873): '2432342344434242'

 
Predicate Information (identified by operation id):



   8 - filter(("TFTD"."SDT">=TO_DATE(' 2022-01-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFTD"."SDT"<=TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   9 - access("TFTD"."ANUM"=:1)
  11 - access("TFTD"."SRCODE"="TMCT"."SCODE")
  13 - access("TFTD"."MCODE"="PCODE")
  15 - filter(("TFMA"."SDT">=TO_DATE(' 2022-01-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFMA"."SDT"<=TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFTD"."SDT"="TFMA"."SDT"))
  16 - access("TFTD"."TID"="TFMA"."TID")
  18 - access("TFTD"."SB_ID"="TSSS"."SB_ID" AND "TFTD"."SID"="TSSS"."SID")
       filter("TFTD"."SID"="TSSS"."SID")
  21 - access("TFTD"."SRCODE"="TMCT"."SCODE")
  23 - access("TFTD"."MCODE"="PCODE")




  30 - access("TFTD"."TNUM"=:2)


  31 - filter(("TFTD"."SDT">=TO_DATE(' 2022-01-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFTD"."SDT"<=TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFTD"."SID"="TSSS"."SID" AND "TFTD"."SB_ID"="TSSS"."SB_ID" 


              AND LNNVL("TFTD"."ANUM"=:1)))

  33 - filter(("TFMA"."SDT">=TO_DATE(' 2022-01-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFMA"."SDT"<=TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TFTD"."SDT"="TFMA"."SDT"))
  34 - access("TFTD"."TID"="TFMA"."TID")

 

 

 

 



On Sat, Feb 19, 2022 at 2:56 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:



Hi Pap,

 



You need to provide more details. Show full plans with format=>'advanced'. Does it contain bind variables? If - yes, please provide their typical values.



Is your Oracle version < 12.2? As I see "CONCATENATION"  not VW_ORE_XXX with "UNION ALL".



Have you ever had the "good" plan through DBlink or do you get it only locally and manually?

 



On Fri, Feb 18, 2022 at 11:50 PM Pap <oracle.developer35_at_gmail.com> wrote:



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





| Id  | Operation                              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |






|   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)|
-------------------------------------------------------------------------------------------------------------------------------------




 




-- 

Best regards,
Sayan Malakshinov

Oracle performance tuning engineer

Oracle ACE
http://orasql.org






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


Received on Sun Feb 20 2022 - 16:03:02 CET












Original text of this message