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_at_gmail.com> 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/
>
> Regards
> Jonathan Lewis
>
>
>
> On Thu, 11 Nov 2021 at 16:10, 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?
>>
>>

--
http://www.freelists.org/webpage/oracle-l