Re: Plan change with OR expansion

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