Re: First_rows optimization

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