Re: First_rows optimization

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