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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Amit Saroha
Sent: Tuesday, August 24, 2021 10:43 AM
To: ORACLE-L (oracle-l@xxxxxxxxxxxxx)
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
