Re: Small and complicated query run time improvement inputs

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