Re: Small and complicated query run time improvement inputs

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