Re: [External] Remote database table cardinality estimation

 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_at_gcrta.org> 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
>
> >>> Lok P <loknath.73_at_gmail.com> 11/30/20 3:40 PM >>>
> Basically I was thinking if oracle make the cardinality estimation by
> 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_at_gmail.com> 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' )
>>
>>

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