First_rows optimization
From: Pap <oracle.developer35_at_gmail.com>
Date: Tue, 18 Jan 2022 10:40:55 +0530
Message-ID: <CAEjw_fi8O0OoQpvC3gCqrUmMVs4=wFn1ZtiqXoQrDpsA=gFegA_at_mail.gmail.com>
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.
Date: Tue, 18 Jan 2022 10:40:55 +0530
Message-ID: <CAEjw_fi8O0OoQpvC3gCqrUmMVs4=wFn1ZtiqXoQrDpsA=gFegA_at_mail.gmail.com>
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-lReceived on Tue Jan 18 2022 - 06:10:55 CET