Re: Small and complicated query run time improvement inputs
- From: Amit Saroha <eramitsaroha@xxxxxxxxx>
- To: Andy Sayer <andysayer@xxxxxxxxx>
- Date: Thu, 26 Aug 2021 09:58:53 -0400
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
SQL Monitoring Report
SQL Text
------------------------------
WITH go_up_in_chain AS ( SELECT + CONNECT_BY_FILTERING CASE WHEN
CONNECT_BY_ISLEAF = 1 THEN msib1.inventory_item_id END AS from_item_id , CASE
WHEN CONNECT_BY_ISLEAF = 1 THEN msib1.segment1 END AS from_item ,
mri.related_item_id AS to_item_id , msib2.segment1 AS to_item , LEVEL AS
priority , 'UP' AS direction FROM apps.mtl_related_items mri INNER JOIN
apps.mtl_system_items_b msib1 ON mri.inventory_item_id =
msib1.inventory_item_id AND mri.organization_id = msib1.organization_id INNER
JOIN
apps.mtl_system_items_b msib2 ON mri.related_item_id = msib2.inventory_item_id
AND mri.organization_id = msib2.organization_id INNER JOIN
apps.fnd_lookup_values flv ON mri.relationship_type_id =
TO_NUMBER(TO_CHAR(FLV.lookup_code)) AND flv.lookup_type =
'MTL_RELATIONSHIP_TYPES' AND flv.meaning LIKE 'MNAO%' WHERE
msib1.organization_id = 85 AND msib2.organization_id = 85 AND SYSDATE BETWEEN
NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1) AND
CONNECT_BY_ISCYCLE = 0 START WITH
msib2.segment1 = '057825130B' AND mri.attr_char1 IN ('AN') AND (
mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id ,
inventory_item_id FROM mtl_related_items ) CONNECT BY NOCYCLE PRIOR
mri.inventory_item_id = mri.related_item_id AND mri.attr_char1 IN ('AN') AND (
mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id ,
inventory_item_id FROM mtl_related_items ) ), go_down_in_chain AS ( SELECT +
CONNECT_BY_FILTERING CONNECT_BY_ROOT
(msib1.inventory_item_id) AS from_item_id , CONNECT_BY_ROOT (msib1.segment1) AS
from_item , mri.related_item_id AS to_item_id , msib2.segment1 AS to_item ,
LEVEL AS priority , 'DOWN' AS direction FROM apps.mtl_related_items mri INNER
JOIN apps.mtl_system_items_b msib1 ON mri.inventory_item_id =
msib1.inventory_item_id AND mri.organization_id = msib1.organization_id INNER
JOIN apps.mtl_system_items_b msib2 ON mri.related_item_id =
msib2.inventory_item_id AND mri.organization_id =
msib2.organization_id INNER JOIN apps.fnd_lookup_values flv ON
TO_NUMBER(TO_CHAR(FLV.lookup_code)) = mri.relationship_type_id AND
flv.lookup_type = 'MTL_RELATIONSHIP_TYPES' AND flv.meaning LIKE 'MNAO%' WHERE
msib1.organization_id = 85 AND msib2.organization_id = 85 AND SYSDATE BETWEEN
NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1) AND
CONNECT_BY_ISCYCLE = 0 START WITH msib1.segment1 = '057825130B' AND (
mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT
related_item_id , inventory_item_id FROM mtl_related_items ) CONNECT BY NOCYCLE
PRIOR flv.meaning != 'MNAO MKTG KITS' AND mri.inventory_item_id = PRIOR
mri.related_item_id AND PRIOR mri.attr_char1 IN ('A') AND (
mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id ,
inventory_item_id FROM mtl_related_items ) ), tree AS ( SELECT FROM
go_down_in_chain UNION ALL SELECT FROM go_up_in_chain ) SELECT + monitor
FIRST_VALUE ( FROM_item ) OVER ( ORDER BY CASE WHEN
direction = 'UP' THEN 1 ELSE 2 END, CASE WHEN direction = 'UP' THEN - priority
ELSE priority END ) from_item , t.to_item to_item FROM tree t ORDER BY CASE
WHEN direction = 'UP' THEN 1 ELSE 2 END , CASE WHEN direction = 'UP' THEN -
priority ELSE priority END
Global Information
------------------------------
Status DONE (ALL ROWS)
Instance ID 1
Session ASAROHA (152538061)
SQL ID cdvsvxamf06ck
SQL Execution ID 16777217
Execution Started 08262021 065531
First Refresh Time 08262021 065531
Last Refresh Time 08262021 065532
Duration 1s
ModuleAction SQL Developer-
Service EBSPRD
Program SQL Developer
Fetch Calls 1
Global Stats
======================================
Elapsed Cpu Fetch Buffer
Time(s) Time(s) Calls Gets
======================================
0.80 0.80 1 636K
======================================
SQL Plan Monitoring Details (Plan Hash Value=3872023562)
================================================================================================================================================================================
Id Operation Name
Rows Cost Time Start Execs Rows Mem Activity Activity
Detail
(Estim) Active(s) Active (Actual) (Max) (%) (#
samples)
================================================================================================================================================================================
0 SELECT STATEMENT
1 +1 1 3
1 WINDOW SORT
4 3800 1 +1 1 3 2048
2 VIEW
4 3800 1 +1 1 3
3 UNION-ALL
1 +1 1 3
4 VIEW
2 79 1 +1 1 1
5 FILTER
1 +1 1 1
6 CONNECT BY WITH FILTERING
1 +1 1 1
7 NESTED LOOPS
1 33 1 +1 1 1
8 NESTED LOOPS
1 33 1 +1 1 1
9 NESTED LOOPS ANTI
1 31 1 +1 1 1
10 NESTED LOOPS
1 30 1 +1 1 1
11 MERGE JOIN CARTESIAN
1 26 1 +1 1 15
12 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
1 3 1 +1 1 3
13 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2
1 2 1 +1 1 3
14 BUFFER SORT
7 23 1 +1 3 15 2048
15 TABLE ACCESS BY INDEX ROWID BATCHED MTL_SYSTEM_ITEMS_B
7 23 1 +1 1 5
16 INDEX SKIP SCAN MTL_SYSTEM_ITEMS_B_N1
7 21 1 +1 1 5
17 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS
1 3 1 +1 15 1
18 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
1 2 1 +1 15 1
19 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
410K 2 1
20 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1 1 +1 1 1
21 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2 1 +1 1 1
22 NESTED LOOPS
1 44 1
23 NESTED LOOPS
1 43 1
24 NESTED LOOPS ANTI
1 41 1
25 NESTED LOOPS
1 39 1 +1 1 0
26 MERGE JOIN CARTESIAN
1 36 1 +1 1 3
27 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
1 3 1 +1 1 3
28 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2
1 2 1 +1 1 3
29 BUFFER SORT
1 33 1 +1 3 3 2048
30 CONNECT BY PUMP
1 +1 1 1
31 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS
1 3 3
32 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
1 2 3
33 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
410K 2
34 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2
35 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1
36 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2
37 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1
38 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
1 2
39 VIEW
2 3721 1 +1 1 2
40 FILTER
1 +1 1 2
41 CONNECT BY WITH FILTERING
1 +1 1 2
42 NESTED LOOPS
1 1241 1 +1 1 1
43 NESTED LOOPS
1 1241 1 +1 1 83215
44 NESTED LOOPS
1 1239 1 +1 1 83215
45 NESTED LOOPS ANTI
1 1238 1 +1 1 83215
46 NESTED LOOPS
3 1233 1 +1 1 84018
47 TABLE ACCESS BY INDEX ROWID BATCHED FND_LOOKUP_VALUES
1 3 1 +1 1 3
48 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2
1 2 1 +1 1 3
49 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS
12811 1230 1 +1 3 84018
50 INDEX RANGE SCAN MTL_RELATED_ITEMS_N1
51249 123 1 +1 3 197K
51 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
410K 2 1 +1 84017 803
52 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2 1 +1 83215 83215
53 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1 1 +1 83215 83215
54 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1 1 +1 83215 83215
55 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2 1 +1 83215 1
56 NESTED LOOPS
1 2478 1 +1 2 1
57 NESTED LOOPS
1 2477 1 +1 2 1
58 NESTED LOOPS ANTI
1 2475 1 +1 2 1
59 NESTED LOOPS
1 2474 1 +1 2 1
60 MERGE JOIN CARTESIAN
1 1244 1 +1 2 6
61 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
1 3 1 +1 2 6
62 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2
1 2 1 +1 2 6
63 BUFFER SORT
1 1241 1 +1 6 6 2048
64 CONNECT BY PUMP
1 +1 2 2
65 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS
1 1230 1 +1 6 1
66 INDEX RANGE SCAN MTL_RELATED_ITEMS_N1
51249 123 1 +1 6 394K
67 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
410K 2 1
68 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2 1 +1 1 1
69 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1 1 +1 1 1
70 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
1 2 1 +1 1 1
71 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
1 1 1 +1 1 1
72 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1
1 2 1
================================================================================================================================================================================
Other related posts:
