Re: First_rows optimization

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> 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>
> 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>
>>
>>

-- 

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