Re: Improve Query sqlm_9csc97utkgjub

Amit,

I doubt anyone's willing to spend time analyzing your query in great detail for free - it's 600+ lines of SQL after all.

From a high level I think you should try: 1) forcing serial direct reads in your session before running this query - "_serial_direct_reads"=always (+ flush the buffer_cache) - it looks like OE_ORDER_LINES_ALL table is full scanned via buffered reads which does not help most of the time
2) get rid of the hints that are ignored / unresolved at least, ideally all of them
3) SQL Tuning advisor
4) set up you eBS database as per recommended Oracle configuration; at least optimizer_index_cost_adj should be default 100 5) increase PGA significantly

HTH On Wed, Oct 23, 2024 at 5:03 PM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi All,
> I am requesting that you check the included SQL monitoring data and let me
> know if there is anything we can do to optimize the run time.
>
> Thank you for your assistance and advice in advance.
>
> Best Regards,
> AMIT
>

-- 
Regards
Timur Akhmadeev

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