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

Both are interestingly arcane choices having rejected using the suggestion of attributes and for order of preference and a “local” registry of remote index preferences by remote location and name made circa 1993 by the Oracle VLDB group. Sadly, that enhancement request failed to make the list of 62 submitted via company multi-voting and Oracle elected to ignore it as an enhancement request without votes (it is interesting that Oracle did implement some vote-less enhancements either because a company not a member of VLDB demanded it to renew a license or a competitor was marketing the feature.)  

Reminder to self: Endless loop (Think about blogging the “62”, forget to get blood signatures no one will sue me to offset the blood signatures made to enter the discussion, lose interest).  

“The newest index is probably needed” and “The oldest indexes probably contribute to plan stability” are both true statements in by my way of thinking and /sarcasm on/ you can always drop and recreate an index to make it the newest or hint individual queries /sarcasm off/.  

Good luck!  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, November 12, 2021 1:18 PM
To: Oracle L
Subject: 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:

1. 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.
2. 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