Re: Small and complicated query run time improvement inputs

I see the index skip scan is now gone from the plan.

But, there is merge cartesian inside multi level nested loops.

That is not something I would care to see in a plan for SQL that must be quick.

Perhaps revisiting the purpose of the query and rethinking how that purpose is to be realized is in order.

Just a thought.

On Thu, Aug 26, 2021 at 07:00 Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Andy,
>
> Please find the details enclosed.
>
>
> Best Regards,
> AMIT SAROHA
>
>
> On Thu, Aug 26, 2021 at 9:42 AM Andy Sayer <andysayer_at_gmail.com> wrote:
>
>> Can you share the new complete plan with the connect_by_filtering hint.
>> The hint was more to see what Oracle thought was so expensive about this
>> option so we can see what we can do to help it - I imagine once we’ve done
>> that you won’t need the hint as well
>>
>> Thanks,
>> Andrew
>>
>> On Thu, 26 Aug 2021 at 14:40, Amit Saroha <eramitsaroha_at_gmail.com> wrote:
>>
>>> Hi Mark, Andy - There is no improvement post adding LEADING HINT but
>>> CONNECT_BY_FILTERING the run time is improved between *.7 and .8
>>> seconds; *which is really good but do you think any other options to
>>> bring it down below *.5 *seconds?
>>>
>>> Best Regards,
>>> AMIT SAROHA
>>>
>>>
>>> On Wed, Aug 25, 2021 at 2:33 PM Amit Saroha <eramitsaroha_at_gmail.com>
>>> wrote:
>>>
>>>> Thank you, Andy, Mark for the valuable inputs. I'll try the
>>>> suggestions and revert back to you.
>>>>
>>>>
>>>> Best Regards,
>>>> AMIT SAROHA
>>>>
>>>>
>>>> On Wed, Aug 25, 2021 at 1:13 PM Andy Sayer <andysayer_at_gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> Materializing any of the filters only contained in the where clauses
>>>>> of the connect by queries is not a valid rewrite. Remember that where is
>>>>> evaluated after the connect by and since these conditions aren't included
>>>>> in the connect by conditions we can't filter them out until after all the
>>>>> work is done. If the organization_id filters are valid to include in
>>>>> the connect by clauses then it will likely give you a very quick win by
>>>>> adding them.
>>>>>
>>>>> It is the go_up_in_chain CTE which is taking all the time, it's much
>>>>> longer than the other one as it is not using the good looking predicate segment1
>>>>> = '057825130B' to start the recursion. I would guess that it has
>>>>> decided not to because it is unable to use indexes in a nice way going this
>>>>> way though the chain.
>>>>>
>>>>> Quick sanity check, do you have an index that would cover predicates:
>>>>> mtl_related_items (attr_char1, related_item_id) -- it looks like you
>>>>> have an index which starts with related_item_id (MTL_RELATED_ITEMS_U1) but
>>>>> I don't think this is selective enough on it's own.
>>>>>
>>>>> If you do have that index and the plan is remaining the same, try
>>>>> adding the /*+CONNECT_BY_FILTERING*/ hint to the go_up_in_chain CTE
>>>>> and see where it believes the cost of doing things this way are.
>>>>>
>>>>> Thanks,
>>>>> Andrew
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Wed, 25 Aug 2021 at 14:37, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>>>>
>>>>>> So better stats might improve the plan, but if that’s a problem, I’d
>>>>>> take a whack at materialized with processing of apps.mtl_related_items mri
>>>>>> in both halves to present just the rows that are already pruned for SYSDATE
>>>>>> BETWEEN NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1)
>>>>>> and
>>>>>>
>>>>>> either mri.attr_char1 IN ('AN') or mri.attr_char1 IN ('A') depending
>>>>>> on which half.
>>>>>>
>>>>>>
>>>>>>
>>>>>> If we’re sweating bits, a pre-pruned materialized
>>>>>> apps.fnd_lookup_values flv is probably tiny and you can present the rows
>>>>>> with the to_number on lookup_code already done to match
>>>>>> relationship_type_id, of course also pruning to AND flv.lookup_type =
>>>>>> 'MTL_RELATIONSHIP_TYPES'
>>>>>>
>>>>>> AND flv.meaning
>>>>>> LIKE 'MNAO%'
>>>>>>
>>>>>>
>>>>>>
>>>>>> Probably apps.mtl_system_items_b msib1 and msib2 are too big to
>>>>>> materialize, but let Oracle decide that after you prune them both to
>>>>>> organization_id = 85 in a with.
>>>>>>
>>>>>>
>>>>>>
>>>>>> From your actual counts and timing stats, you can see that line 49 is
>>>>>> driving your foobar number of nested loops iterations, guessing 51K and
>>>>>> giving 196K, while going on to discard all but 3.
>>>>>>
>>>>>>
>>>>>>
>>>>>> So probably you want to force the join of the pruned mri and pruned
>>>>>> flv to take place before you join that result set to two copies of
>>>>>> mtl_system_items_b.
>>>>>>
>>>>>>
>>>>>>
>>>>>> So you do THAT in a with hinted to stop oracle from undoing it,
>>>>>> keeping just the columns you need
>>>>>>
>>>>>>
>>>>>>
>>>>>> from mri_flv_pruned_an
>>>>>>
>>>>>> inner join mtl_system_items_b_85 msib1 ON
>>>>>> mri_flv_pruned_an.inventory_item_id = msib1.inventory_item_id
>>>>>>
>>>>>> inner join mtl_system_items_b_85 msib2 ON
>>>>>> mri_flv_pruned_an.inventory_item_id = msib2.inventory_item_id
>>>>>>
>>>>>>
>>>>>>
>>>>>> gets you down to AND CONNECT_BY_ISCYCLE = 0, which is so much easier
>>>>>> to read I’d do it just for clarity.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Factoring that pruning out early in the source code may significantly
>>>>>> improve Oracle’s plan, but it definitely removes brain pollution.
>>>>>>
>>>>>>
>>>>>>
>>>>>> You’ll notice I intentionally didn’t look up the names of the hints
>>>>>> for you.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Good luck,
>>>>>>
>>>>>>
>>>>>>
>>>>>> mwf
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>>>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Amit Saroha
>>>>>> *Sent:* Tuesday, August 24, 2021 10:43 AM
>>>>>> *To:* ORACLE-L (oracle-l_at_freelists.org)
>>>>>> *Subject:* Small and complicated query run time improvement inputs
>>>>>>
>>>>>>
>>>>>>
>>>>>> Hi All,
>>>>>>
>>>>>>
>>>>>>
>>>>>> I have a query which takes *1.5* seconds to execute which is not
>>>>>> enough for my application. I must reduce the time somehow to around
>>>>>> *.2-.5* seconds.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Please look at the enclosed query, plan and sql monitoring report and
>>>>>> share the feedback to improve the time.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Best Regards,
>>>>>>
>>>>>> AMIT
>>>>>>
>>>>> --
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Oracle ACE Alumni
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

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