Function-based index wouldn't help. (In fact there may already be such an
index in place).
The optimizer ignored function-based indexes on remote tables in
distributed joins.
https://jonathanlewis.wordpress.com/2018/05/04/fbi-limitation/
The cardinality estimate for this operation might be one of the basic
guesses (like 1% for "I haven't a clue but it's an equality")
Regards
Jonathan Lewis
On Mon, 30 Nov 2020 at 20:46, Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
wrote:
Your step 42 states:
42 - SELECT "NM_TXT","F_NM","L_NM","EM","A_LCK" FROM
"USER1"."REMOTE_TAB" "CT" WHERE :1=UPPER("NM_TXT") (accessing 'XXXXX.DBLNK'
)
Since you are doing a "upper" on the column, do you have a have a function
index on that column?
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
Basically I was thinking if oracle make the cardinality estimation byLok P <loknath.73@xxxxxxxxx> 11/30/20 3:40 PM >>>
reading the statistics information from the remote database objects during
parsing of the query only? Or it apply some percentage logic to have the
cardinality estimation of the remote object which then can deviate from
actuals and thus may resulted into bad plan?
On Tue, 1 Dec 2020, 1:36 am Lok P, <loknath.73@xxxxxxxxx> wrote:
Its version 11.2.0.4 of oracle database. We have below query spending
quite a lot of time on "SQL*Net message from dblink" on step-42. And it
seems it's because it's scanning that table in FULL, hundreds of times
which seems to be because of some bad estimation. My thought was if line
number -25 in the plan i.e the HASH JOIN is causing the overall estimation
wrong and thus the REMOTE_TAB is getting joined as "Nested loop outer" but
not "Hash Join outer". And possibly REMOTE_TAB would have joined as "hash
join outer" making it finish in quick time. Is my understanding correct
here? OR The estimation of remote object cardinality is posing a problem
for optimizers here and is the cause?
The query is a UNION ALL query, i have shared the predicate section. And
another thing is column NM_TXT of REMOTE_TAB is having an index on it, but
because of the UPPER function it's not getting used. So probably creating a
new function based index on REMOTE_TAB.UPPER(NM_TXT) will make the query
faster even with NESTED LOOP OUTER path. But I want to see if we can fix
any table/column stats , so that the optimizer can by default follow the
HASh JOIN OUTER path? Also that table REMOTE_TAB has ~427K rows and almost
all the rows have distinct NM_TXT values.
Below is the sql monitor but its not formatted well so i also Attached
the same sql monitor of the query , i have replaced the exact object names
with dummy ones.
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL Execution ID : 16777456
Execution Started : 11/23/2020 00:40:07
First Refresh Time : 11/23/2020 04:13:17
Last Refresh Time : 11/23/2020 04:38:00
Duration : 14327s
Program : JDBC Thin Client
Global Stats
=================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs |
Bytes |
=================================================================================
| 14274 | 5.21 | 0.00 | 0.00 | 14268 | 67307 | 1 | 8192 |
=================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1724585072)
=============================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem
| Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | | (%) | (# samples)
|
=============================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | |
| -> 1 | SORT ORDER BY | | 2 | 20772 | 1484 | +12790 | 1 | 0 | 2M | | |
| -> 2 | UNION-ALL | | | | 1484 | +12790 | 1 | 6887 | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 2272 | 1 | +12790 | 1 | 152 | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 11 | 1 | +12790 | 1 | 152 | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 10 | 1 | +12790 | 1 | 152 | | | |
| 6 | NESTED LOOPS | | 1 | 8 | 1 | +12790 | 1 | 152 | | | |
| 7 | NESTED LOOPS | | 1 | 6 | 1 | +12790 | 1 | 152 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1 | 5 | 1 | +12790 | 1 | 1 | |
| |
| 9 | INDEX SKIP SCAN | TAB1_PK | 1 | 4 | 1 | +12790 | 1 | 1 | | | |
| 10 | INDEX RANGE SCAN | G_CTCT_PK | 1 | 1 | 1 | +12790 | 1 | 152 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | G_CTCT | 1 | 2 | 1 | +12790 | 152 |
152 | | | |
| 12 | INDEX UNIQUE SCAN | G_CTCT_PK | 1 | 1 | 1 | +12790 | 152 | 152 | |
| |
| 13 | TABLE ACCESS BY INDEX ROWID | PU_PRFL | 1 | 2 | 1 | +12790 | 152 |
141 | | | |
| 14 | INDEX UNIQUE SCAN | PU_PRFL_IDX | 1 | 1 | 1 | +12790 | 152 | 141 |
| | |
| 15 | TABLE ACCESS BY INDEX ROWID | P_RL | 1 | 1 | 1 | +12790 | 152 |
141 | | | |
| 16 | INDEX UNIQUE SCAN | P_RL_PK | 1 | | 1 | +12790 | 152 | 141 | | | |
| 17 | REMOTE | REMOTE_TAB | 1 | 2261 | 1 | +12790 | 152 | 73 | | | |
| -> 18 | NESTED LOOPS OUTER | | 1 | 18500 | 1484 | +12790 | 1 | 6735 | |
| |
| -> 19 | HASH JOIN | | 1 | 16239 | 1484 | +12790 | 1 | 6736 | 2M | | |
| 20 | NESTED LOOPS OUTER | | 2 | 14795 | 1 | +12790 | 1 | 8356 | | | |
| 21 | NESTED LOOPS OUTER | | 2 | 14793 | 1 | +12790 | 1 | 8356 | | | |
| 22 | HASH JOIN | | 2 | 14789 | 1 | +12790 | 1 | 8356 | | | |
| 23 | NESTED LOOPS | | 2 | 8903 | 1 | +12790 | 1 | 8356 | | | |
| 24 | NESTED LOOPS | | 4 | 8903 | 1 | +12790 | 1 | 8356 | | | |
| 25 | HASH JOIN | | 4 | 8895 | 1 | +12790 | 1 | 8356 | | | |
| 26 | VIEW | VW_NSO_1 | 2 | 7 | 1 | +12790 | 1 | 15 | | | |
| 27 | HASH UNIQUE | | 2 | | 1 | +12790 | 1 | 15 | | | |
| 28 | NESTED LOOPS | | 2 | 7 | 1 | +12790 | 1 | 15 | | | |
| 29 | NESTED LOOPS | | 2 | 7 | 1 | +12790 | 1 | 15 | | | |
| 30 | INDEX SKIP SCAN | TAB1_PK | 1 | 4 | 1 | +12790 | 1 | 1 | | | |
| 31 | INDEX RANGE SCAN | MRC_IDX | 1 | 2 | 1 | +12790 | 1 | 15 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | MRC | 2 | 3 | 1 | +12790 | 15 | 15 |
| | |
| 33 | INDEX FULL SCAN | M_CTCT | 1M | 8862 | 1 | +12790 | 1 | 1M | | | |
| -> 34 | INDEX UNIQUE SCAN | G_CTCT_PK | 1 | 1 | 1484 | +12790 | 8356 |
8356 | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | G_CTCT | 1 | 2 | 1 | +12790 | 8356 |
8356 | | | |
| 36 | TABLE ACCESS FULL | MRC | 639K | 5874 | 1 | +12790 | 1 | 641K | |
| |
| 37 | TABLE ACCESS BY INDEX ROWID | PU_PRFL | 1 | 2 | 1 | +12790 | 8356
| 8356 | | | |
| 38 | INDEX UNIQUE SCAN | PU_PRFL_IDX | 1 | 1 | 1 | +12790 | 8356 | 8356
| | | |
| 39 | TABLE ACCESS BY INDEX ROWID | P_RL | 1 | 1 | 1 | +12790 | 8356 |
8352 | | | |
| 40 | INDEX UNIQUE SCAN | P_RL_PK | 1 | | 1 | +12790 | 8356 | 8352 | | |
|
| -> 41 | TABLE ACCESS FULL | TAB1 | 394K | 1437 | 1484 | +12790 | 1 |
9033 | | | |
| -> 42 | REMOTE | REMOTE_TAB | 1 | 2261 | 3302 | +11027 | 6736 | 379 | |
100.00 | SQL*Net message from dblink (3248) |
=============================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("C"."CID" LIKE :1) filter("C"."CID" LIKE :1)
10 - access("CC"."AB_ID"="C"."AB_ID" AND "CC"."CID"="C"."CID")
11 - filter(("GC"."M_CID"='C' AND "GC"."AB_ID" IS NOT NULL AND
"GC"."C_TYP"='X' AND "GC"."AB_ID"="CC"."AB_ID"))
12 - access("GC"."CT_ID"="CC"."CT_ID")
13 - filter("GC"."AB_ID"="U"."AB_ID")
14 - access("GC"."CT_ID"="U"."CT_ID")
16 - access("U"."RID"="ROL"."RID")
19 - access("M"."CID"="C"."CID")
22 - access("MC"."M_ID"="M"."M_ID")
25 - access("MC"."M_ID"="M_ID")
30 - access("C"."CID" LIKE :2) filter("C"."CID" LIKE :2)
31 - access("M"."AB_ID"="C"."AB_ID" AND "M"."CID"="C"."CID")
34 - access("GC"."CT_ID"="MC"."CT_ID")
35 - filter(("GC"."AB_ID" IS NOT NULL AND "GC"."M_CID"='M' AND
"GC"."C_TYP"='X' AND "GC"."AB_ID"="MC"."AB_ID"))
37 - filter("GC"."AB_ID"="U"."AB_ID")
38 - access("GC"."CT_ID"="U"."CT_ID")
40 - access("U"."RID"="ROL"."RID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
17 - SELECT "NM_TXT","F_NM","L_NM","EM","A_LCK" FROM "USER1"."REMOTE_TAB"
"CT" WHERE :1=UPPER("NM_TXT") (accessing 'XXXXX.DBLNK' )
42 - SELECT "NM_TXT","F_NM","L_NM","EM","A_LCK" FROM "USER1"."REMOTE_TAB"
"CT" WHERE :1=UPPER("NM_TXT") (accessing 'XXXXX.DBLNK' )
