Re: Improve SQL run time

Hi Amit,

your issues start at plan line id 6, where Oracle estimated 1 row to be returned and your query produced almost 4K. It went all wrong after this. The time spent on lines 10-12 is a consequence of the underestimated cardinality of step 6.
Line id 6 uses "ASN_LINES"."SYS_NC00311$"=:B1 AND "ASN_LINES"."SYS_NC00322$"=:B2 access predicates and most likely you're either completely or partially missing stats on those two hidden columns. Since it's an eBS DB check Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1) for the way to gather extended stats properly. Then ensure the stats for both columns & extended stats is there & re-run the query.
If you can rewrite the underlying query then using max() keep (dense_rank ...) for locating the "last" row may help too.

On Mon, Oct 16, 2023 at 6:08 PM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi All,
>
> I'm executing the enclosed query and want to minimize the response time to
> 10 seconds. The SQL monitor, SQL, and SQL Plan are enclosed for your
> feedback.
>
> Please evaluate and provide feedback and if more information is needed,
> please let me know.
>
> Thank you for your help in advance.
>
> Best Regards,
> Amit
>

-- 
Regards
Timur Akhmadeev

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