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

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