LOK, I thought Oracle changed the tie breaker when choosing between indexes
from the highest object id to choosing based on the alphabetic index name?
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Lok P <loknath.73@xxxxxxxxx>
Sent: Friday, November 12, 2021 8:26 AM
To: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Odd behavior with queries having DB link in 19C
Thank you so much Jonathan. You are spot on.
It was an in -place upgrade. Btw, I think we are hitting the 20 index
restriction here as one of the newest indexes having a very high numbered
object_id and was created recently. So if i am correct , as the newly created
index is having the highest object_id now, So the only workaround in this case
would be to prioritize and drop and create the first ~20 top index by priority
, those we will be mostly utilizing for the remote queries, so that they will
have highest object_ids.
On Fri, Nov 12, 2021 at 5:52 PM Jonathan Lewis
<jlewisoracle@xxxxxxxxx<mailto:jlewisoracle@xxxxxxxxx>> wrote:
The cost figures show that this database knows that the access path to the
first remote table is awful, and you've said that the stats on the remote index
haven't really changed even though the sample size has.
This suggests that this database doesn't know about the remote index, or thinks
it can't be used. This prompts two thoughts:
a) when you upgraded from 11g to 19c did you upgrade in place, or did you
create a new database and export/import - if the latter then maybe you've
changed the database character set and there's a character converstion required
that makes this database think the other database won't be able to use the
"obvious" index.
b) for distributed queries Oracle will only consider the first 20 indexes on
any individual table at the remote site, so if some dropped and recreated
indexes during the upgrade (for whatever reason) then maybe the index that was
being used is no longer in the first 20 for that table.
See
https://jonathanlewis.wordpress.com/2018/05/08/20-indexes/<https://clicktime.symantec.com/3FMUzpCbsPLoMSBkMnLjvSP7Vc?u=https%3A%2F%2Fjonathanlewis.wordpress.com%2F2018%2F05%2F08%2F20-indexes%2F>
Regards
Jonathan Lewis
On Thu, 11 Nov 2021 at 16:10, Lok P
<loknath.73@xxxxxxxxx<mailto: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?
