Re: Odd behavior with queries having DB link in 19C

Thank You. Actually that was my first thought only in this situation to see the behaviour by forcing the path-1 outline, but because of the presence of the DB link I am unable to run it because that db link credential is only accessible to the specific application user/schema from which it gets called. And yes the bad part is , this type of estimation and plan deviation is observed in many queries. must be because this table PE is a very commonly used one.

On Thu, Nov 11, 2021 at 10:13 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Can you try forcing the outline of the cheaper execution path(which is
> spawning the indexed remote query without hints on the target database) to
> the query and see what the cost is now and if that gives you some clue?
>
> On Thu, Nov 11, 2021 at 9:40 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>> 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-l