Re: Small and complicated query run time improvement inputs

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