Re: Small and complicated query run time improvement inputs

Quick note that the work is being done on the lookups using MTL_RELATED_ITEMS_N1. Check the predicate section of the plan to see if the index I proposed earlier makes sense ( mtl_related_items (attr_char1, related_item_id) ).

Thanks,
Andy

On Fri, 27 Aug 2021 at 10:20, Andy Sayer <andysayer_at_gmail.com> wrote:

> Amit, could you please include the predicate section for the execution
> plan too.
>
> Thanks,
> Andy
>
> On Thu, 26 Aug 2021 at 15:55, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> By the way Andy is quite correct that in the general case you cannot
>> factor out where clause elements of connect by statements.
>>
>>
>>
>> However, this is Ebiz manufacturing, right? Can you verify that because
>> of the part group description rules all of the connect by elements do in
>> fact have ‘A’ or ‘AN’ as appropriate, so that while pruning the source in a
>> way that is not valid in the general case you lose no rows in the actual
>> case.
>>
>>
>>
>> With the important caveat that I left out and Andy corrected, this is a *
>> *possible** big win. Remember, you need to show not just that is the
>> case as the moment, but that by the part definition rules you cannot create
>> a row that would be lost from the connect by from prefiltering. IF memory
>> serves and they haven’t changed things, it seems likely to be true. AND
>> even more likely to be true for fnd_lookup.
>>
>>
>>
>> Likewise, however you can force the bushy join on lookup and related
>> first before the two references of item, that should help. That part is
>> valid in the general case.
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* Amit Saroha [mailto:eramitsaroha_at_gmail.com]
>> *Sent:* Thursday, August 26, 2021 9:59 AM
>> *To:* Andy Sayer
>> *Cc:* Mark W. Farnham; ORACLE-L
>> *Subject:* 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