Re: First_rows optimization

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
>

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