Re: Improve SQL run time

Did the /*+ no_unnest */ hint actually make the query go significantly faster?
At present the critical code is running as a non-merged view with pushed predicate accessed by a nested loop join - this means it is effectively behaving like a "not-unnested" subquery that HAS to execute the maximum number of times (i.e. the 68K of the previous join). If you force a no_unnest you MIGHT find that Oracle manages to gain huge advantage from scalar subquery caching and drop the number of executions to a few thousand and get enough benefit to leave it at that.

If you add the hint and then upgrade, though, you may find that 19c is able to do other transformations that either make the hint unresolvable, or make Oracle follow the hint but produce a different plan. If you hint and it works well you need to create a baseline (or capture the current Outline Data and connect it to the query as an SQL Patch to give you the best chance of the hint working.

I haven't looked closely at the query, but you might find that better cardinality estimates in the first few row estimates result in Oracle NOT using a nested loop with pushed predicate but switching to a one-off brute force hash join that could operate much more efficiently. Without examining the SQL and knowing your data, indexes etc. it's hard to draw any firm conclusion about quickest, easiest, good enough solution.

Regards
Jonathan Lewis

On Mon, 16 Oct 2023 at 18:24, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> 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