Re: Slow Query with ROWNUM

Sayan,
I think the document_num is a varchar2(), if it were numeric the predicate would show as to_number(:B1).

Amit,
I think the problem here is Oracle (12.1.0.2) is getting in a mess because of the rownum = 1 (possibly further encouraged to do the wrong thing by the optimizer_index_cost_adj = 80). It's optimising for first_row_1, and you can see that it prefers a tablescan of PO_HEADERS_ALL that has a (nominal) cost of 5,947 over an index access path to XXPO01_ASN_INBOUND with a cost of 14 to make use of that bind variable. Once that choice has been made the plan is a disaster.

The reason for such a stupid starting point is that Oracle probably thinks that it will find a matching row almost immediately and NOT incure the whole of the cost of the tablescan, while it thinks that of the estimated 102 rowids from the index it may have to get through most of the table accesses before finding a row. That's the sort of thing that can go wrong with forst_rows_1 / rownum = 1 / fetch first 1

It's possible (and this is a wild guess without see lots more stats and index definitions) that you need a histogram on PO_HEADERS_ALL. attribute1 - or if that column is almost always null maybe you've got a histogram that needs to be deleted. Either way I think you need to get Oracle to start at the INBOUND table. This may mean you just have to apply a hint/patch to force the right starting point (e.g. leading() with that table alias).

Regards
Jonathan Lewis

On Thu, 19 Oct 2023 at 01:37, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Amit,
>
> Could you provide all statistics of the table XXPO01T_ASN_INBOUND (all
> indexes, columns, histograms)?
> Firstly, I'd check if DOCUMENT_NUM is really varchar2 (from the Binds
> section we can see that :B1 is varchar2).
> Then, I'd check if there is an index on it. Obviously, it would be better
> to have an index on this column, as we see that out of 826,000 rows fetched
> by predicates on the INVOICE_NUM and FILE_ID from the table by the time of
> the RTSM report execution, there wasn't a single row satisfying the
> predicate on "XAI"."DOCUMENT_NUM"=:B1.
>
>
> On Thu, Oct 19, 2023 at 1:26 AM Amit Saroha <eramitsaroha_at_gmail.com>
> wrote:
>
>> Thank you for your email. I will give it a try and share the
>> feedback with yo.
>>
>>
>> On Wed, Oct 18, 2023 at 7:34 PM Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>>> On 10/18/23 11:43, Amit Saroha wrote:
>>>
>>> Hi All,
>>>
>>> I want to shorten the response time to my little query, which typically
>>> takes 10 minutes, to only one minute.
>>>
>>> I kindly ask you to have a look and provide your insightful input in
>>> order to decrease the questioning time.
>>>
>>> Let me know if you need more information apart from the SQL monitoring
>>> report.
>>>
>>> Best Regards,
>>> AMIT
>>>
>>> Is it 12c or newer DB? If it is, you can try the following:
>>> SELECT
>>> DECODE(FLV.MEANING,'ORDERED_DATE',TRUNC(PHA.CREATION_DATE),'SHIPPED_DATE',TRUNC(XAI.SHIPPED_DATE),'INVOICE_DATE',TRUNC(TO_DATE(XAA.ATTRIBUTE1)))
>>> FROM FND_LOOKUP_VALUES FLV,PO_HEADERS_ALL PHA,XXPO01T_ASN_INBOUND
>>> XAI,XXPO01T_ASN_AG_LAYOUT XAA WHERE 1 = 1 AND PHA.ATTRIBUTE1 =
>>> XAI.DOCUMENT_NUM AND XAI.FILE_ID = XAA.FILE_ID AND XAI.INVOICE_NUM =
>>> XAA.INVOICE_NUMBER AND XAI.DOCUMENT_NUM = :B1 AND FLV.LOOKUP_TYPE =
>>> 'XXPO_ASN_CONV_DATE_CONTROL' AND NVL(FLV.TAG,'Y') = 'Y' AND FLV.LANGUAGE =
>>> 'US' AND FLV.ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN
>>> NVL(TRUNC(FLV.START_DATE_ACTIVE),SYSDATE) AND
>>> NVL(TRUNC(FLV.END_DATE_ACTIVE),SYSDATE) FETCH FIRST 1 ROWS ONLY;
>>>
>>> Under the hood, that will use analytic functions and may prove a tad
>>> faster than ROWNUM. Not much, though.
>>>
>>> Regards
>>>
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>>
>>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE
> http://orasql.org
>

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