RE: Plan change with OR expansion
Date: Mon, 21 Feb 2022 15:01:52 -0500
Message-ID: <030101d8275d$df2f2400$9d8d6c00$_at_rsiz.com>
Run a 10053 trace with the view and with the exposed text and see what the difference is.
mwf
From: Pap [mailto:oracle.developer35_at_gmail.com]
Sent: Monday, February 21, 2022 5:03 AM
To: Mark W. Farnham
Cc: Sayan Malakshinov; Oracle L
Subject: Re: Plan change with OR expansion
force_view1 is actually created in the local database. And it's on top of another force view which is created in the remote database and that actually contains the transaction table joins.
And there are multiple views created in remote databases(like force_view1, force_view2 based on different date range criteria ) and are accessed by queries from this local DB dynamically through a wrapper view as I mentioned above.
Exposing the underlying text/scripts rather than using the view name is going for good plan when testing manually but I was trying to understand why it doesn't work with the DML when called from application. Even the cost of bad plan is so huge it's still opting that path. What is the cause of this?
On Sun, Feb 20, 2022 at 8:33 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
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-lReceived on Mon Feb 21 2022 - 21:01:52 CET