Odd behavior with queries having DB link in 19C
Date: Thu, 11 Nov 2021 21:39:43 +0530
Message-ID: <CAKna9VYb7xuE-2E3f7n5LbYL0VHBuVTa_4jO94D0Ze04thp4jg_at_mail.gmail.com>
Hello, After migrating from 11.2.0.4 to 19C(19.9.0.0.0), we saw many queries were opting for suboptimal paths and thus we decided to set the optimizer_feature_enable parameter back to 11.2.0.4 in production to avoid these issues. So now we have this database with 19C DB version but with OFE as 11.2.0.4. But strangely we are still seeing some of the queries(mainly having DB link) to this database from another database are performing poorly because of a bad execution path. The Source database is on version 11.2.0.4 + OFE 11.2.0.4 and this/target database is on version 19C with OFE-11.2.0.4. So I wanted to understand if this combination can cause some bad estimation or change in costing, mainly in cases of involvement of DB link?
Below is a sample query ,which runs in source database(which is having both
DB version and OFE as 11.2.0.4), this query was running with default plan path- 1 as below but post this target database changed to 19c with OFE-11.2, its opting for path -2. I do see there is a big deviation in estimation of rows for table PE because of the predicate its now evaluating i.e. "PE.CCNA is not null ". But wondering why it's not going for the cheaper indexed path.When we tried to set the good path through sql profile in source database, we saw in the remote queries its submitting in the target database(i.e. with DB version 19C and OFE-11.2.0.4) having additional hints added to them as below and are causing them to go for full scan (even we have suitable index for them i.e. column PE in table PE is a primary key and a unique index on column CNA of table CNA exists in the target database). So here basically table PE having a column name as PE and table CNA also having column name as CNA and both are unique.
I can see from the outline of the good plan, PATH-1 that its adding full hints to table PE and CNA, but it was somehow working fine while our target database was on 11.2.0.4(wrt both DB version and OFE) with same outline, so i am assuming it must not be submitting those remote queries in target database with those additional hints, so why it's doing that now?
Remote queries even after adding profile to the query in source database
spawning with below hints which were mostly not happening this way when
target database was on 11.2.0.4:
SELECT /*+ ALL_ROWS USE_NL ("P") FULL ("P") */ "PE","CNA" FROM "PE" "P"
WHERE "CNA" IS NOT NULL AND :1="PE"
SELECT /*+ ALL_ROWS USE_NL ("C") FULL ("C") */
"CNA","L1","L2","L3","L4","L5" FROM "CNA" "C" WHERE :1="CNA"
*Query:- *
SELECT .......
FROM TBRBP TBRBP, PE_at_dblnk1 PE, CNA_at_dblnk1 CNA
WHERE TBRBP.PDAY = :B1
AND TBRBP.PNUM = PE.PE
AND PE.CCNA = CNA.CNA
*PATH:-1 *
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
| 0 | SELECT STATEMENT | | |
| 1390 (100)| | | |
| 1 | NESTED LOOPS | | 227 |
22019 | 1390 (1)| 00:00:17 | | |
| 2 | NESTED LOOPS | | 227 |
9307 | 709 (1)| 00:00:09 | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TBRBP | 227 |
3405 | 27 (0)| 00:00:01 | | |
| 4 | INDEX RANGE SCAN | TBRBP_IX1 | 227 |
| 3 (0)| 00:00:01 | | |
| 5 | REMOTE | PE | 1 |
26 | 3 (0)| 00:00:01 | dblnk1 | R->S |
| 6 | REMOTE | CNA | 1 |
56 | 3 (0)| 00:00:01 | dblnk1 | R->S | ----------------------------------------------------------------------------------------------------------------------
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$1")
INDEX_RS_ASC(_at_"SEL$1" "TBRBP"_at_"SEL$1" ("TBRBP"."PDAY"))
FULL(_at_"SEL$1" "PE"_at_"SEL$1")
FULL(_at_"SEL$1" "CNA"_at_"SEL$1")
LEADING(_at_"SEL$1" "TBRBP"_at_"SEL$1" "PE"@"SEL$1" "CNA"@"SEL$1")
USE_NL(_at_"SEL$1" "PE"_at_"SEL$1")
USE_NL(_at_"SEL$1" "CNA"_at_"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
1 - :B1 (DATE): 09/13/2019 00:00:00 Remote SQL Information (identified by operation id):
5 - SELECT "PE","CCNA" FROM "PE" "PE" WHERE :1="PE" (accessing ' dblnk1.CMPNY1.COM' )
6 - SELECT "CNA","L1","L2","L3","L4","L5" FROM "CNA" "CNA" WHERE :1="CNA" (accessing 'dblnk1.CMPNY1.COM' )
*PATH : 2*
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
| 0 | SELECT STATEMENT | | |
| 70439 (100)| | | |
| 1 | NESTED LOOPS | | 125 |
12250 | 70439 (2)| 00:14:06 | | |
| 2 | HASH JOIN | | 125 |
5125 | 70063 (2)| 00:14:01 | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TBRBP | 125 |
1875 | 17 (0)| 00:00:01 | | |
| 4 | INDEX RANGE SCAN | TBRBP_IX1 | 125 |
| 3 (0)| 00:00:01 | | |
| 5 | REMOTE | PE | 40M|
1009M| 69927 (2)| 00:14:00 | dblnk1 | R->S |
| 6 | REMOTE | CNA | 1 |
57 | 3 (0)| 00:00:01 | dblnk1 | R->S | ----------------------------------------------------------------------------------------------------------------------
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$1")
FULL(_at_"SEL$1" "PE"_at_"SEL$1")
INDEX_RS_ASC(_at_"SEL$1" "TBRBP"_at_"SEL$1" ("TBRBP"."PDAY"))
FULL(_at_"SEL$1" "CNA"_at_"SEL$1")
LEADING(_at_"SEL$1" "PE"_at_"SEL$1" "TBRBP"@"SEL$1" "CNA"@"SEL$1")
USE_HASH(_at_"SEL$1" "TBRBP"_at_"SEL$1")
USE_NL(_at_"SEL$1" "CNA"_at_"SEL$1")
SWAP_JOIN_INPUTS(_at_"SEL$1" "TBRBP"_at_"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
1 - :B1 (DATE): 10/24/2021 00:00:00 Remote SQL Information (identified by operation id):
5 - SELECT "PE","CCNA" FROM "PE" "PE" WHERE "CCNA" IS NOT NULL (accessing 'dblnk1.CMPNY1.COM' )
6 - SELECT "CNA","L1","L2","L3","L4","L5" FROM "CNA" "CNA" WHERE :1="CNA" (accessing 'dblnk1.CMPNY1.COM' )
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 11 2021 - 17:09:43 CET