Re: First_rows optimization

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> 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> 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.
>>
>>
>> https://www.freelists.org/post/oracle-l/Simple-query-opting-higher-cost-path
>>
>> On Tue, Jan 18, 2022 at 10:41 AM Pap <oracle.developer35_at_gmail.com>
>> wrote:
>>
>>> Hello Listers, We have a peoplesoft application database and almost all
>>> the time we see the queries in the backend as above ~2000-3000+ lines with
>>> multiple UNIONS in them. And the estimation seems really bad making us
>>> confused many times but then we see the optimizer_mode has been set as
>>> first_rows_1. Thus the indexed path is favored all the time even though few
>>> of the queries perform better by forcing a full scan path. So I wanted to
>>> check with experts here if those are really the recommended setups for
>>> peoplesoft databases in general and thus should not be changed? During
>>> encountering any performance issue many times we don't get the sql monitors
>>> because of such long queries.
>>>
>>> Along with above there are other parameters like _unnest_subquery,
>>> _gby_hash_aggregation_enabled, _optimizer_skip_scan_enabled etc are set as
>>> non default i.e. false.
>>>
>>> Regards
>>> Pap
>>>
>>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

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