Re: First_rows optimization

Mark Powell
Database Administration
(313) 592-5148

If no-one has mentioned it already - check MOS for the settings demanded by your version of Peoplesoft. You may find that a lot of the settings it used to insist on 10 years ago have been removed.

Regarding optimizer_index_caching=80, and the cases where using an index is a bad idea.

First_rows_1 (which I think you'd mentioned) makes the arithmetic of costing very messy, and makes the numbers for cost in an execution plan much harder to understand. It has a HUGE impact on making Oracle pick indexes, but also makes Oracle use tablescans if the predicates suggest that there's a LOT of matching data in the table because Oracle then expects to find the first matching row within a few blocks of starting the tablescan.

Now add optimizer_index_caching = 80 to the calculation and you find that not only does the optimizer pick a bad strategy, it also isn't very good at telling the difference between a good index and a fairly bad index because it has scaled down the cost of using them so much.

Regards
Jonathan Lewis

On Wed, 19 Jan 2022 at 17:29, Pap <oracle.developer35_at_gmail.com<mailto:oracle.developer35_at_gmail.com>> wrote: Thank you Mohamed.

One more thing I just noticed, this peoplesoft database also has optimizer_index_caching= 80, and optimizer_index_cost_adj= 50. Is this non default setup is something peoplesoft product suggested or it must have been done by the team only in the past?

And also we noticed many queries going for index paths here whereas full scan is making those faster. Can this be totally a manually setup which is causing such symptoms and so we should revert thee back to defaults?

On Tue, Jan 18, 2022 at 6:23 PM Mohamed Houri <mohamed.houri_at_gmail.com<mailto:mohamed.houri_at_gmail.com>> wrote: Pap,

However, not able to get it fully , when you said " of course, you should delete these altered sessions wherever you find them." . Do you mean to say , we need to set it back to "all_rows" after the intended query/session finishes with "first_rows_1" optimization mode? Please correct me if I'm wrong.

In those queries where you have noticed slowness, print the corresponding outline and, if you find the hint FIRST_ROWS in this outline, and you manage to correlate this with an INDEX FULL SCAN operation which takes the most time, then you have to intervene in the corresponding batch and get rid of the alter session set optimizer_mode=first_rows_1; from this batch.

Best regards

Mohamed

Le mar. 18 janv. 2022 à 12:26, Pap <oracle.developer35_at_gmail.com<mailto:oracle.developer35_at_gmail.com>> a écrit : Thank You So much. This database is on version 19.9 , but OFE is still 11.2.0.4.

So it means at least the optimizer mode as 'first_row_1' has not to be set as global but rather should be done only in specific cases on session level if required. And thus, this non default value is not a guidance from peoplesoft product.

 However, not able to get it fully , when you said " of course, you should delete these altered sessions wherever you find them." . Do you mean to say , we need to set it back to "all_rows" after the intended query/session finishes with "first_rows_1" optimization mode? Please correct me if I'm wrong.

On Tue, Jan 18, 2022 at 1:35 PM Mohamed Houri <mohamed.houri_at_gmail.com<mailto:mohamed.houri_at_gmail.com>> wrote:

Pap

Regarding PeopleSoft and its recommendations concerning certain parameters of the CBO, we've talked about it a lot here on this list, so I'd definitely like to clarify that from my experience with at least 3 different applications using PeopleSoft.

Firstly, the optimizer_mode= first_rows_1 parameter is not set at a global level but only via a couple of altered sessions. And yes, of course, you should delete these altered sessions wherever you find them. This is exactly what I did for my 3 clients.

Second, setting the _optimizer_skip_scan_enabled parameter to FALSE is indeed suggested by PeopleSoft. I set it to TRUE globally in the first days of testing in UAT. And you should do the same.

 Finally, PeopleSoft suggests changing the _unnest_subquery parameter to FALSE. In this case, it's up to you. In a 12.2 version I set it to TRUE and I had to intervene on several queries to set it locally to FALSE. You can follow my example or do the opposite. You are free. But I'm sure you will whatever value you’ll adopt for this parameter, I'm sure that you will have to intervene locally to force the opposite value. in 19.3 I adopted the opposite strategy

You can follow the PeopleSoft suggestion concerning the _gby_hash_aggregation_enabled parameter

Best regards

Mohamed

Le mar. 18 janv. 2022 à 08:54, Lok P <loknath.73_at_gmail.com<mailto:loknath.73_at_gmail.com>> a écrit : You have not mentioned the current version of this Oracle database ? Not sure of first_rows_1, however , I saw one thread in past as below, in which "Mohamed Houri" mentioned that those three parameters were exactly the ones suggested by peoplesoft to be changed :). So it might be that first_row_1 is also peoplesoft suggested only and it may not be advisable to play with that. Others may comment on this.