Re: Improve Query sqlm_bshtdhgmsvqad

Half your time is spent on single block reads of oe_order_lines_all at operation 59 - as before these are (almost certainly) the single block reads as Oracle follows chained and/or migrated rows. If not they are reads of the undo tablespace. Did you ever pursue the session activity stats to find out which? Almost all of those rows are discared in the hash join at operation 28.

The build table for operation 28 has a descent that accounts for most of the rest of the time - the hash join at operation 47 which does a massively spill to disc when it builds 15M rows, and probes with the result of a 230M row tablescan. The sad thing about this join is that Oracle's estimates are in the right ballpark and it STILL wants to do it. This suggests a need to manipulate the code to find a different path (possibly following suggestions from Sayan) that eliminates excessive data more efficiently. Again almost all of the data disappears on that join (output is 230K rows from 230M input).

Regards
Jonathan Lewis

Regards
Jonathan Lewis

On Fri, 1 Nov 2024 at 15:40, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi All,
>
> I have another query which takes hours to run hence 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
>

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