Re: Improve Query sqlm_bshtdhgmsvqad

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 2 Nov 2024 01:07:18 +0000
Message-ID: <CAOVevU6+mLeUOKAvmnJCNUM+ULErRgqXpu=B+YE8ViWKRMrRXg_at_mail.gmail.com>

Hi Amit,

1. Avoid such ORs: (RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS') OR RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS'))

It's better to rewrite it as:
(

    select ...
    from

        APPS.RA_CUSTOMER_TRX_ALL    RA_CUSTOMER_TRX_ALL
        INNER JOIN APPS.RA_CUSTOMER_TRX_LINES_ALL
 RA_CUSTOMER_TRX_LINES_ALL
            ON

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID=RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID     where RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG='Y'       and RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS') union

    select ...

        APPS.RA_CUSTOMER_TRX_ALL    RA_CUSTOMER_TRX_ALL
        INNER JOIN APPS.RA_CUSTOMER_TRX_LINES_ALL
 RA_CUSTOMER_TRX_LINES_ALL
            ON

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID=RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID     where RA_CUSTOMER_TRX_ALL.COMPLETE_FLAG='Y'       and
RA_CUSTOMER_TRX_LINES_ALL.LAST_UPDATE_DATE>=TO_DATE(SUBSTR('2024-10-18 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS') )

The optimizer often can re-write such things using OR_EXPAND or USE_CONCAT transformations, but your query is too complex, so it would be better if you optimize it.

2. Avoid TO_CHAR in such cases:

LEFT OUTER JOIN (APPS.OE_ORDER_LINES_ALL OE_ORDER_LINES_ALL

                INNER JOIN APPS.OE_ORDER_HEADERS_ALL
 OE_ORDER_HEADERS_ALL
                    ON

OE_ORDER_LINES_ALL.HEADER_ID=OE_ORDER_HEADERS_ALL.HEADER_ID)     ON
RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6=TO_CHAR(OE_ORDER_LINES_ALL.LINE_ID)) It's better to rewrite like

TO_NUMBER(RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE6 DEFAULT NULL ON CONVERSION ERROR) = OE_ORDER_LINES_ALL.LINE_ID

--
Kind regards,
Sayan


On Fri, Nov 1, 2024 at 3:40 PM 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

>



-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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

Received on Sat Nov 02 2024 - 02:07:18 CET