If this is indeed Ebiz manufacturing, be extraordinarily careful about adding
indexes. If it’s a clone for reporting, go for it, but if it is the “real”
object, en garde.
From: Andy Sayer [mailto:andysayer@xxxxxxxxx] ;
Sent: Friday, August 27, 2021 7:57 AM
To: Mark W. Farnham
Cc: Amit Saroha; ORACLE-L
Subject: 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@xxxxxxxxx> 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@xxxxxxxx> 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@xxxxxxxxx] ;
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxx> 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@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
