Re: Improve SQL run time

Thanks for sending the /*+ no_unnest */ version.

Operation 4 is a FILTER that, for each row returned by operation 5 calls the subquery at operation 10. So, in theory the subquery should have executed 237,000 times; but thanks to scalar subquery caching it executed only 9 times. If the subquery had unnested, and become the target of a nested loop join with pushed predicate it would have been called 237,000 times.

It's a wonderful example of the difference you can make by blocking that particular transformation. I'm fairly sure I have written about it (i.e. the trade off between a nested loop and a filter) on my blog but I haven't been able to find the article yet. Maybe it's another one I wrote for someone else.

Another passing thought on upgrades, by the way, and the way things can change. Part of the reason for the massive change in performance is because the subquery turns into a NON-MERGEABLE view when unnested. It's the sort of thing where an upgrade might enhanced the view-merging code in a way that makes this example mergeable, producing a "join then aggregate" rather than the "aggregate then join" we saw in the original plan.

Regards
Jonathan Lewis

On Tue, 17 Oct 2023 at 15:36, Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Jonathan,
>
> Since the original view is a component of a business process and will be
> changed after following our bug-fixing procedure, I have built a new one.
> The monitoring report is included for your perusal. I appreciate you
> providing the link to the article; it's quite useful.
>
> When I flush the buffer cache and shared pool, the query takes less than
> ten seconds; if I don't flush, it takes two seconds.
>
> Best Regards,
> Amit
>
> On Tue, Oct 17, 2023 at 5:36 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Thanks for the follow-up. It would be interesting to see an SQL Monitor
>> report from just the /*+ no_unnest */
>>
>> Change a query from "column = max(subquery)" or "column = avg(subquery)"
>> to an analytic form that avoids the subquery complete is becoming a little
>> more common on the field. Interstingly, though, the optimizer has been able
>> to transform some examples of this type of thing for many years (dating
>> back to 11g). Complex transformations like that often acquire new details
>> (or lose restrictions) over the versions so it's just possible that if you
>> have other code with a similar structure you may find that some example
>> actually do the rewrite internally.
>>
>> I thought I'd got a note about this on my blog, but the only thing I
>> could find was in an article I'd written for Redgate/Simpletalk:
>> Oracle optimizer removing or coalescing subqueries - Simple Talk
>> (red-gate.com)
>> <https://www.red-gate.com/simple-talk/databases/oracle-databases/oracle-optimizer-removing-or-coalescing-subqueries/>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Mon, 16 Oct 2023 at 19:45, Amit Saroha <eramitsaroha_at_gmail.com> wrote:
>>
>>> 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