Estimations are as good as it can be, what else can be done here?
Date: Wed, 8 Nov 2023 09:44:21 +0530
Message-ID: <CAEzWdqeH_ijeRX-46mCYVxk4azHMEJXZbAXVRbuAJAY6tmwMuQ_at_mail.gmail.com>
Hello Listers,
It's version 19C of oracle.
We have a search query which is getting submitted from the UI and the
expected response time is <10 seconds to display the first ~200 odd rows on
the screen, but it takes ~50 seconds for that. The sql monitor shows the
cardinality estimation is as good as it can be. The query has ~10 tables
used in the join conditions. I wanted to understand what else can be
done(like query modification, design change etc) to make this query finish
in <10 seconds response time.
The time seems equally distributed in the plan, just that the sorting of the final result set before publishing is consuming ~20% of the execution time, but we need those data sets to be sorted in that order before showing up on the screen. So not sure what else can be done here, to improve this?
The main query is based on the top of a view, but I even executed it manually by exposing the internal query which is used for creating the view. Not seeing much of a difference.
Below I have published the query and its sql monitor.
https://gist.github.com/databasetech0073/046b198e977d200c70778a973c223ed9
Regards
Yudhi
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 08 2023 - 05:14:21 CET