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@xxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Jonathan Lewis <jlewisoracle@xxxxxxxxx>
*Sent:* Wednesday, January 19, 2022 12:41 PM
*To:* Oracle L <oracle-l@xxxxxxxxxxxxx>
*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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxx> 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>
