Re: Improve SQL run time

Thank you so much, Jonathan. We do, indeed, have two function-based indexes. Our DBA also gave insights, namely, to apply the NO_UNNEST hint in the section mentioned by Lothar and Timur. Another concern is, if we utilize hints now, will they still function if we upgrade the database to 19c?

Best Regards,
AMIT SAROHA On Mon, Oct 16, 2023 at 12:53 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> You're on 12.1.0.2, so oddities in the way predicates are reported are
> fairly common when you start using virtual columns, column groups, and
> function-based indexes. It looks to me as if you've got at least one index
> on (to_char(something), to_char(something_else)) that has allowed for the
> appearance of the SYS_NC columns. (The :b1, :b2 etc. are due to features
> such as predicate pushing, in-line correlated scalar subqueries etc.).
>
> Regards
> Jonathan Lewis
>
>
> On Mon, 16 Oct 2023 at 17:12, Amit Saroha <eramitsaroha_at_gmail.com> wrote:
>
>> 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.
>>>
>>>

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