Re: Small and complicated query run time improvement inputs

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