Yes I already tried those two remote queries without hints on the target
database and they are opting for the index unique scan path without any
issue and the cost is coming as <4. But the issue is somehow it's now
coming with those full hints which was mostly not happening when the target
database was on 11.2. So i am wondering if this behaviour is expected or
say corrected in version 19C? Basically two things i am unable to
understand here,
What I mean is in case we force path-1 through profile,
For e.g. a per the outline of the query path-1, it is using a full(PE) and
FULL(CNA) in the remote tables. But still when they were getting submitted
in the remote database they were not having those hints embedded when the
target database was on 11.2. But now that the target database has been
upgraded to 19c (even OFE is 11.2) the remote queries are getting submitted
as per the outline in the source database and creating issues for us. Is
this understanding correct?
Another issue is , now that it's by default going for path-2 which has an
estimation of table PE as ~40milion and overall query cost of 70439 which
is a lot higher than the path-1 cost. So why is it happening in such a way?
On Fri, Nov 12, 2021 at 2:08 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Can you try executing the remote queries independently as it is(without
those additional hints) in the target database side and see if they are
going for the index unique scan with less cost as compared to those full
scans. And i hope you have not changed any other database parameters like
'***Index_cost_adj', "***index_caching" etc as part of the upgrade.
On Thu, Nov 11, 2021 at 10:19 PM Lok P <loknath.73@xxxxxxxxx> wrote:
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@xxxxxxxxx>
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@xxxxxxxxx> 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@dblnk1 PE, CNA@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(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TBRBP"@"SEL$1" ("TBRBP"."PDAY"))
FULL(@"SEL$1" "PE"@"SEL$1")
FULL(@"SEL$1" "CNA"@"SEL$1")
LEADING(@"SEL$1" "TBRBP"@"SEL$1" "PE"@"SEL$1" "CNA"@"SEL$1")
USE_NL(@"SEL$1" "PE"@"SEL$1")
USE_NL(@"SEL$1" "CNA"@"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(@"SEL$1")
FULL(@"SEL$1" "PE"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TBRBP"@"SEL$1" ("TBRBP"."PDAY"))
FULL(@"SEL$1" "CNA"@"SEL$1")
LEADING(@"SEL$1" "PE"@"SEL$1" "TBRBP"@"SEL$1" "CNA"@"SEL$1")
USE_HASH(@"SEL$1" "TBRBP"@"SEL$1")
USE_NL(@"SEL$1" "CNA"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "TBRBP"@"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' )
