Re: Improve Query sqlm_bshtdhgmsvqad

Hi Jonathan,

the hash join at operation 47 seems to spill to disk after using 1GB of PGA. Total temp usage of 33 GB.
Apart from the certainly correct point that the plan needs to be fixed, it seems to me that it could not hurt to increase PGA substantially.

Hash Join in Line 40 is an left outer join, join criteria being : RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE6"=TO_CHAR("OE_ORDER_LINES_ALL"."LINE_ID") Probably we do not have an index on
TO_CHAR("OE_ORDER_LINES_ALL"."LINE_ID") . Thus resulting in a Hash Join.

Let me know what you think.

Regards

Lothar

Am 02.11.2024 um 11:14 schrieb Jonathan Lewis:
>
> 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