RE: Improve Query sqlm_bshtdhgmsvqad

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 4 Nov 2024 15:23:56 -0500
Message-ID: <16b601db2ef7$7a270920$6e751b60$_at_rsiz.com>

I believe that means one of your users is defining some parameters in OBIEE and selecting from a list of tables to do some joins. IF that is correct and this is purely for reporting as opposed to something that will be injected back into your stream of single source of truth, THEN:  

from the existing fragments:

(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))
   
(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)
  And (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')
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')) )

)  

I believe you can construct a table using the date the user is probably inputting as a parameter in place of TO_DATE(SUBSTR('2024-10-18 00:56:29',0,19),'YYYY-MM-DD HH24:MI:SS') to create the pre-reduced versions of OE_ORDER_LINES_ALL, RA_CUSTOMER_TRX_ALL, and RA_CUSTOMER_TRX_LINES_ALL, perhaps calling them <the_same>_NOW instead of ALL.  

My guess is that each of these three pre-pruned will be so small that creating optimal indexes after population will be trivial.  

Probably a rough guide is:

Truncate all three _NOW tables and drop their indexes.

Insert into RA_CUSTOMER_TRX_NOW where the _ALL date is correct and the COMPLETE_FLAG=’Y’

Insert into RA_CUSTOMER_TRX_NOW where the _ALL date is no good, the COMPLETE_FLAG=’Y’ and the transaction_id from RA_CUSTOMER_TRX_LINES_ALL meets the date requirement.

(I think that gets you all the RA_CUSTOMER_TRX_NOW rows.)

Insert into RA_CUSTOMER_TRX_LINES_NOW from RA_CUSTOMER_TRX_LINES_ALL where the transaction_id is in RA_CUSTOMER_TRX_NOW  

Insert into OE_ORDER_LINES_NOW from OE_ORDER_LINES_ALL where LINE_ID is in select to_number(INTERFACE_LINE_ATTRIBUTE6) from RA_CUSTOMER_TRX_LINES_NOW  

I haven’t check for typos, and I have a presumption in there that all the INTERFACE_LINE_ATTRIBUTE6 selected will to_number. (You could play that safe by using the opposite to_char.)  

Now if I’m wrong and the number of recently updated complete transactions is large, this won’t help. But if I’m right (which you can find out without disruption), then all the rest won’t matter (much) because the set from these three pre-pruned tables will be relatively small and you won’t care. They don’t need to confuse the CBO by adding in the date predicate constant, but they may need to link between OE_ORDER_LINES_NOW and RA_CUSTOMER_TRX_NOW rows. I think all the remaining stuff is likely to be small enough without pre-pruning, but your mileage may vary.  

Community please let Amit (and me) know if I’ve made a logic error or a typo. I think the general approach is valid, though you may need to coordinate creation of the pruned tables or render them as global temporaries depending on what OBIEE needs. (And they will need to be visible to OBIEE so that your users can select them.) In “olden days” particularly pre-7, using 5 or 6, we often produced reduced sets before doing the joins. Sometimes it is still worth the trouble. IF you test with your actual data, I wish you joy.  

I think is how you implement Sayan’s suggestion for OBIEE, and I’ve tweaked the OR into two steps so I don’t think you need the union.  

mwf    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amit Saroha Sent: Monday, November 04, 2024 10:59 AM To: Sayan Malakshinov; Lothar Flatz; Jonathan Lewis Cc: oracle-l_at_freelists.org
Subject: Re: Improve Query sqlm_bshtdhgmsvqad  

Dear All,  

Thank you for all of your input.  

The query is generated by OBIEE, so there is little room for modification; nevertheless, because this is standard Oracle code, we will contact Oracle to see if they can help us rebuild the query in the proposed method. On the other side, I will sample the session to see if I can find out about the migrated rows in the lines table and notify you.

Best Regards,

AMIT     On Sat, Nov 2, 2024 at 10:30 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

On 11/1/24 9:07 PM, Sayan Malakshinov wrote:

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

 

Hi Sayan,

I believe that it would be better to change the data model. People have to understand that most of the huge and unwieldy queries like the OP has given us the opportunity to study are most of the time a result of lacking data model. Missing objects and inadequate indexing can cause that. Also, translating an OLTP data model to the data warehouse environment can do that.  B*Tree indexes are not al ways the best. Unfortunately, bitmap indexes can have very interesting consequences in an OLTP environment so they're normally not used for that.  Oracle offers a ton of options (clustering, hash clusters, IoT) which are rarely used. Fixing the model first is probably the best advice for Amit.

Regards

-- 
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com

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





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

Received on Mon Nov 04 2024 - 21:23:56 CET