Re: Plan change with OR expansion
Date: Sat, 19 Feb 2022 18:03:49 +0530
Message-ID: <CAEjw_fjLm+TXPPnxvSvN+HVc8+dshrDjsn1ZtfKfxpQeW=ZjTg_at_mail.gmail.com>
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 /*+*/.....
select....
**************Plan_hash_value - A
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'))
FROM "FORCE_VIEW1" "FV1"
WHERE "ANUM" = :1 OR "TNUM" = :2
Below is the script of the above view:- FORCE_VIEW1
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':-
| 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"_at_"SEL$2" "TFMA"_at_"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
*/
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"_at_"SEL$3" "TFMA"_at_"SEL$2" "TSSS"@"SEL$2")
LEADING(_at_"SEL$5C160134_2" "TSSS"_at_"SEL$5C160134_2"
"TFTD"_at_"SEL$5C160134_2" "TMMC"_at_"SEL$5C160134_2" "TMCT"@"SEL$5C160134_2"
"TFMA"_at_"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 Sat Feb 19 2022 - 13:33:49 CET