Re: Small and complicated query run time improvement inputs

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
>>>>>
>>>>

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





text/plain attachment: sqlmon.txt