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@xxxxxxxxx>:
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@xxxxxxxxx>
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@xxxxxxxxxx> 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
