Re: [External] Remote database table cardinality estimation

From: Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
Date: Mon, 30 Nov 2020 15:45:27 -0500
Message-Id: <5FC559E70200000B00085616_at_groupwise2014.gcrta.org>

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

| 14274 | 5.21 | 0.00 | 0.00 | 14268 | 67307 | 1 | 8192 |

SQL Plan Monitoring Details (Plan Hash Value=1724585072)

| 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

Received on Mon Nov 30 2020 - 21:45:27 CET

This message: [ Message body ]
Next message: Mark W. Farnham: "RE: Table with ~255+ columns"
Previous message: Lok P: "Re: Remote database table cardinality estimation"
In reply to: Lok P: "Re: Remote database table cardinality estimation"
In reply to Lok P: "Remote database table cardinality estimation"
Next in thread: Jonathan Lewis: "Re: [External] Remote database table cardinality estimation"
Reply: Jonathan Lewis: "Re: [External] Remote database table cardinality estimation"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message