Re: [External] Remote database table cardinality estimation
- From: "Jeffrey Beckstrom" <jbeckstrom@xxxxxxxxx>
- To: "oracle-l-freelist" <oracle-l@xxxxxxxxxxxxx>, <loknath.73@xxxxxxxxx>
- Date: Mon, 30 Nov 2020 15:45:27 -0500
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@xxxxxxxxx> 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@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' )
Other related posts:
