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

Mark,

The alphabetical tie-break is about a local optimizer picking an index to access a table.
The "most recent first" (i.e. descending order of object_id) is to select a limit of 20 sets of index metadata (per table) from a remote database when the local optimizer is optimizing a distributed query.

Regards
Jonathan Lewis

On Fri, 12 Nov 2021 at 18:09, Powell, Mark <mark.powell2_at_dxc.com> wrote:

>
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Lok P <loknath.73_at_gmail.com>
> *Sent:* Friday, November 12, 2021 8:26 AM
> *To:* Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *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_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/
> <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_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