Re: Improve SQL run time

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