Re: Improve SQL run time

Hi Jonathan and everyone,

The problem was fixed after updating the SQL with the analytical function, and the NO_UNNEST hint returned comparable results when seen at run time.

Thank you for your significant help.

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

> 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