Re: Remote database table cardinality estimation

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