Re: Improve SQL run time

Hi Timur,

Thank you for the valuable feedback.

I'm curious, did Oracle generate such columns, because I don't see them in SQL developer, and we never added those hidden columns.

Best Regards,
AMIT On Mon, Oct 16, 2023 at 11:50 AM Timur Akhmadeev <timur.akhmadeev_at_gmail.com> wrote:

> 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