Re: First_rows optimization

Thank You so much Jonathan and Mark. It helps.

Definitely not seeing any of the docs related to peoplesoft performance, stating the parameters like optimizer_mode as 'first_rows_1' and optimizer_cost_adj, optimizer_index_caching to set as non default values as we have it now.

 Will try to search more in the MOS. However if no such things are found , we will probably then plan to move these three global parameters back to defaults.

On Wed, Jan 19, 2022 at 11:27 PM Powell, Mark <mark.powell2_at_dxc.com> wrote:

> I know I have seen Peoplesoft Performance and Tuning document recently,
> but I could not find what I remembered. The following articles while old
> do contain links to other articles that may still be useful such as tracing
> and statistics gathering.
>
> After Database Upgrade to Oracle Database 19c, Performance Issues with
> Peoplesoft Complex Queries (Doc ID 2744611.1)
> E-ORA: PS Query Performance Issues after Oracle Upgrade to 12.1.0.2 (Doc
> ID 2163277.1)
> E-ORA: Advice for the Oracle DBA in Supporting PeopleSoft Applications
> (Doc ID 1445965.1)
> E-QR: PeopleSoft Query Performance Guidelines & Troubleshooting Tips -
> Technical Brief (Doc ID 2284809.1)
> EAR8.8+: Troubleshooting Performance Issues in FSCM PeopleSoft
> Applications (Doc ID 984534.1)
> Performance and Tuning for Oracle’s PeopleSoft HCM 9.1 HR Self Service on
> Oracle Exalogic/Exadata X2-2 Servers (Doc ID 1384242.1)
>
> Mark Powell
> Database Administration
> (313) 592-5148
>
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Sent:* Wednesday, January 19, 2022 12:41 PM
> *To:* Oracle L <oracle-l_at_freelists.org>
> *Subject:* 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
> <https://clicktime.symantec.com/32ynBacZpqKjoCWkc6MVpm47VN?u=https%3A%2F%2Fwww.freelists.org%2Fpost%2Foracle-l%2FSimple-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
> <https://clicktime.symantec.com/37ZZaS5KwAbdu2gLz6Vv6Pn7VN?u=http%3A%2F%2Fwww.hourim.wordpress.com%2F>
>
> Let's Connect -
> <https://clicktime.symantec.com/3HBXn13i7N2Y4PKekuLnWuN7VN?u=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F>*Linkedin
> Profile
> <https://clicktime.symantec.com/3HBXn13i7N2Y4PKekuLnWuN7VN?u=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F>*
>
> My Twitter
> <https://clicktime.symantec.com/3DA1uoSSF85wWYF9SjMXAGQ7VN?u=https%3A%2F%2Ftwitter.com%2FMohamedHouri>
> - MohamedHouri
> <https://clicktime.symantec.com/3DA1uoSSF85wWYF9SjMXAGQ7VN?u=https%3A%2F%2Ftwitter.com%2FMohamedHouri>
>
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog
> <https://clicktime.symantec.com/37ZZaS5KwAbdu2gLz6Vv6Pn7VN?u=http%3A%2F%2Fwww.hourim.wordpress.com%2F>
>
> Let's Connect -
> <https://clicktime.symantec.com/3HBXn13i7N2Y4PKekuLnWuN7VN?u=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F>*Linkedin
> Profile
> <https://clicktime.symantec.com/3HBXn13i7N2Y4PKekuLnWuN7VN?u=http%3A%2F%2Ffr.linkedin.com%2Fpub%2Fmohamed-houri%2F11%2F329%2F857%2F>*
>
> My Twitter
> <https://clicktime.symantec.com/3DA1uoSSF85wWYF9SjMXAGQ7VN?u=https%3A%2F%2Ftwitter.com%2FMohamedHouri>
> - MohamedHouri
> <https://clicktime.symantec.com/3DA1uoSSF85wWYF9SjMXAGQ7VN?u=https%3A%2F%2Ftwitter.com%2FMohamedHouri>
>
>
>
>

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