Re: SQL Performance

Hi Amit,

As Laurentiu asked already: please be more specific and generous with your information. If the Plan Hash Value is the same, this is an important detail. Otherwise, the new SQL real time monitor gives others the chance to help you.

Can you please share some thoughts and concepts the author of XXOF_312_ASN_CF_LINES_V had in mind?
There are subselects for vendor_number, vendor_site_code and ebs_po_num. They contain a filter ROWNUM = 1 but does *not *have an ORDER BY - this looks similar to the idea of using a DISTINCT - and raises all the related questions.
All these subselects share access on the tables po_headers_all poh and  xxpo01t_asn_inbound
asn_l. As PO_HEADERS_ALL seems to be critical, please translate your query into an outer join with those 2 tables. So they are only visited once and not 3 times - all subselects can then use their attributes for their joins.

Does this help in your effort?

Martin

Am Fr., 15. März 2024 um 21:07 Uhr schrieb Amit Saroha < eramitsaroha_at_gmail.com>:

> I have created the index but no improvement in run time.
>
> Please let me know if I can try anything else?
>
> Best Regards,
> AMIT
>
>
> On Tue, Mar 12, 2024 at 12:20 PM Amit Saroha <eramitsaroha_at_gmail.com>
> wrote:
>
>> Thank you for your feedback, I will create the index and test. Could you
>> also advise what do you mean by re-write?
>>
>> Best Regards,
>> AMIT
>>
>>
>> On Tue, Mar 12, 2024 at 11:13 AM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>>
>>> It should be possible to rewrite the statement avoiding that column
>>> level selects.
>>> Am 12.03.2024 um 16:09 schrieb Lothar Flatz:
>>>
>>> Have to correct myself, line 7 is executed 23 time, thus estimate looks
>>> ok.
>>>
>>> Am 12.03.2024 um 16:03 schrieb Lothar Flatz:
>>>
>>> Hi Amit,
>>>
>>> it is both time the access on table PO_HEADERS_ALL where time is spend.
>>> That is in Line 7 where the selection criteria is "POH"."ATTRIBUTE1"=:B1
>>> AND "POH"."ORG_ID"=:B2. As an easiest option an Index on (ORG_ID, ATTRIBUTE1)
>>> might be useful.
>>> We see that the estimates are somewhat off, as estimated was 1 row,
>>> whereas 23 rows were retrieved.
>>> An improvement of statistics might be advisable.
>>> On line 28, the same table, but this time selection criteria are ("POH"."ATTRIBUTE1"="ASN_L"."DOCUMENT_NUM"
>>> AND "POH"."ORG_ID"="ASN_L"."ORG_ID" AND "POH"."TYPE_LOOKUP_CODE"='STANDARD'
>>> AND NVL("POH"."CLOSED_CODE",'OPEN')='OPEN' AND
>>> NVL("POH"."CANCEL_FLAG",'N')='N').
>>>
>>> As the same columns as above are included and the columns proved to be
>>> selective in line 7 probably the same index would help.
>>> This , if possible create an index on PO_HEADERS_ALL (ORG_ID, ATTRIBUTE1).
>>>
>>>
>>> There might be some testing necessary of just one of the two columns is
>>> enough etc.
>>> However as a first attempt that might be a good startpoint.
>>>
>>> Thanks
>>>
>>> Lothar
>>>
>>> Am 12.03.2024 um 15:38 schrieb Amit Saroha:
>>>
>>> Hi All,
>>> I have the enclosed query running rather slowly, and I'd like to enhance
>>> it such that it finishes in a few seconds. The query is a SELECT statement
>>> on a view and I enclosed the view text.
>>> Please review the accompanying query and monitoring report and provide
>>> input for improvements.
>>> I appreciate your aid and support in advance.
>>>
>>> Best Regards,
>>> AMIT
>>>
>>>
>>>
>>>
>>>

-- 
Martin Berger
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

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