RE: Query performance issue
Date: Wed, 16 Oct 2024 06:20:58 -0400
Message-ID: <040b01db1fb5$1878c070$496a4150$_at_rsiz.com>
The first question whenever there is a question about the performance of a query involving UNION is whether or not any members of the UNION are known to certainly be disjoint from each other.
The deduplication part of UNIONs is expensive, so if UNION ALL to combine any of the component queries is valid, then that can make a big difference.
IF the UNION (de-duplicating, not ALL) is required for information correctness, but a small subset of the columns can be used for certain de-duplication, then using that small subset of columns to generate a de-duplicated list of rowids (or a set of index matching columns) either in memory or into an interim filtering table then used to bring back all the required columns *MAY* be faster, which is dependent on the actual data.
Regardless of whether de-duplication is required, assessing the sum of the run times of each component of the UNION run separately is a quick test to see whether (or not) this avenue of exploration could be faster and the likely ceiling of the improvement.
Since de-duplication runs on the order of n log n (at best), it may also be possible to mutually partition some queries such that the sum of nx log nx for each x dependent on the number of rows in each partition is much less than n log n. Then the de-duplicated “partitions” (in the general meaning of the word, not Oracle partitions) can be combined with UNION ALL. Checking the ceiling of the possible time saving should be evaluated before pursuing such complications.
Good luck.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap
Sent: Wednesday, October 16, 2024 12:40 AM
To: Oracle L
Hello Listers,
In the execution path below , the line number marked in bold are the top lines for the IN and NOT IN subquery evaluation and they are showing "Actual time" as Approx ~9 seconds and ~8 seconds and they seems to be summed up and the top lines showing it to be ~19 seconds. Then onwards it keeps on increasing with other "nested loop" joins.
Note:- This query is running on a MYSQL 8.0 database. So I'm wondering if there is any mysql list similar to Oracle list , in which i can share this issue?
Added the query in below path:-
https://gist.github.com/databasetech0073/95bce00c3a6bd4ae8d195401e0383185
SELECT ......
-> Sort: RC_STS.R_CON_ID, RC_STS.R_CON_VER_NB, RC_STS.R_EX_RID (actual time=44392.655..44644.844 rows=745483 loops=1)
-> Stream results (cost=311479029610.37 rows=860847650219) (actual time=8957.556..42133.969 rows=745483 loops=1)
Regards
Pap
Subject: Query performance issue
We have a query below which is running for ~40 seconds. As it's a query which is executed from UI , we were expecting it to finish in <~5 seconds. It has a "IN" and a "NOT IN" subquery , from the execution path it seems the total response time is mainly, to be sum of the "IN" and the "NOT IN" subquery section. My thought was that both "IN" and "NOT IN" should be executed/evaluated in parallel but not in serial fashion.
FROM R_CON_ESTS RC_STS,
R_CON rc,
D_LKP D_LKP_STS,
D_LKP D_LKP_FRQ,
(select RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_VER_NB
from R_CON_E RCE
where RCE.MTNE_ID in (SELECT MI1.MTNE_ID
FROM M_INF mi1 WHERE MI1.AID = :AID
UNION
SELECT rg.RG_MF_SK_ID
from RG_M_F_INF rg where rg.AS_ID =:AID
UNION
SELECT fti.FT_SRK_ID
from M_FT fti where fti.AS_ID= :AID
)
and (RCE.DRV_DT, RCE.AID, RCE.R_CON_ID, RCE.R_CON_ver_nb) NOT IN
(SELECT RCE_NS.DRV_DT, RCE_NS.AID, RCE_NS.R_CON_ID, RCE_NS.R_CON_VER_NB
FROM R_CON_E RCE_NS
WHERE RCE_NS.MTNE_ID NOT IN (select MI2.MTNE_ID
from M_INF MI2 where MI2.AID = :AID
UNION
SELECT RG2.RG_MF_SK_ID
from RG_M_F_INF RG2 where RG2.AS_ID =:AID
UNION
SELECT FTI1.FT_SRK_ID
from M_FT FTI1 where FTI1.AS_ID= :AID
))
) b
where RC_STS.RR_FRQ_NB = D_LKP_FRQ.D_LKP_NB
and RC_STS.R_CON_ESTS_NB = D_LKP_STS.D_LKP_NB
and RC_STS.R_CON_ID = rc.R_CON_ID
and RC_STS.R_CON_VER_NB = rc.R_CON_VER_NB
and RC_STS.AID = rc.AID
and RC_STS.AID = b.AID
and RC_STS.R_CON_ID = b.R_CON_ID
and RC_STS.R_CON_VER_NB = b.R_CON_VER_NB
order by 3,4,2;
-> Nested loop inner join (cost=311479029610.37 rows=860847650219) (actual time=8957.548..40891.903 rows=745483 loops=1)
-> Nested loop inner join (cost=225393084569.25 rows=860847650219) (actual time=8957.541..40597.741 rows=745483 loops=1)
-> Nested loop inner join (cost=139307139528.12 rows=860847650219) (actual time=8957.530..40092.267 rows=745483 loops=1)
-> Nested loop antijoin (cost=53221194487.00 rows=532199430400) (actual time=8957.477..29529.382 rows=671352 loops=1)
-> Nested loop inner join (cost=886687.00 rows=729520) (actual time=0.123..19714.306 rows=692583 loops=1)
-> Filter: <in_optimizer>(RCE.MTNE_ID,<exists>(select #3)) (cost=84215.00 rows=729520) (actual time=0.085..9045.124 rows=692583 loops=1)
-> Covering index scan on RCE using R_58 (cost=84215.00 rows=729520) (actual time=0.055..534.110 rows=742706 loops=1)
-> Select #3 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=4.41..4.41 rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
-> Table scan on <union temporary> (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Union materialize with deduplication (cost=3.18..3.18 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Covering index lookup on mi1 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX', MTNE_ID=<cache>(RCE.MTNE_ID)) (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Single-row covering index lookup on rg using PRIMARY (RG_MF_SK_ID=<cache>(RCE.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX') (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Filter: (fti.AS_ID = 'XXXXXXXXXXXXXXXXXXX') (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Covering index lookup on fti using AK_MFTI (FT_SRK_ID=<cache>(RCE.MTNE_ID)) (cost=0.74 rows=2) (actual time=0.003..0.003 rows=0 loops=50123)
-> Index lookup on rc using R_26 (AID=RCE.AID, R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=1.00 rows=1) (actual time=0.014..0.015 rows=1 loops=692583)
-> Single-row index lookup on <subquery7> using <auto_distinct_key> (DRV_DT=RCE.DRV_DT, AID=RCE.AID, R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=157167.31..157167.31 rows=1) (actual time=0.014..0.014 rows=0 loops=692583)
-> Materialize with deduplication (cost=157167.00..157167.00 rows=729520) (actual time=8957.347..8957.347 rows=25843 loops=1)
-> Filter: ((RCE_NS.DRV_DT is not null) and (RCE_NS.AID is not null) and (RCE_NS.R_CON_ID is not null) and (RCE_NS.R_CON_VER_NB is not null)) (cost=84215.00 rows=729520) (actual time=1737.420..8871.505 rows=50123 loops=1)
-> Filter: <in_optimizer>(RCE_NS.MTNE_ID,<exists>(select #8) is false) (cost=84215.00 rows=729520) (actual time=1737.417..8860.489 rows=50123 loops=1)
-> Covering index scan on RCE_NS using R_58 (cost=84215.00 rows=729520) (actual time=0.039..531.571 rows=742706 loops=1)
-> Select #8 (subquery in condition; dependent)
-> Limit: 1 row(s) (cost=4.41..4.41 rows=1) (actual time=0.010..0.010 rows=1 loops=742706)
-> Table scan on <union temporary> (cost=4.41..5.70 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Union materialize with deduplication (cost=3.18..3.18 rows=2) (actual time=0.010..0.010 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.13 rows=1) (actual time=0.007..0.007 rows=1 loops=742706)
-> Covering index lookup on MI2 using M_INF_AID_index (AID='XXXXXXXXXXXXXXXXXXX', MTNE_ID=<cache>(RCE_NS.MTNE_ID)) (cost=1.13 rows=1) (actual time=0.006..0.006 rows=1 loops=742706)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=1.10 rows=1) (actual time=0.004..0.004 rows=1 loops=132294)
-> Single-row covering index lookup on RG2 using PRIMARY (RG_MF_SK_ID=<cache>(RCE_NS.MTNE_ID), AS_ID='XXXXXXXXXXXXXXXXXXX') (cost=1.10 rows=1) (actual time=0.003..0.003 rows=1 loops=132294)
-> Limit table size: 1 unique row(s)
-> Limit: 1 row(s) (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Filter: (FTI1.AS_ID = 'XXXXXXXXXXXXXXXXXXX') (cost=0.74 rows=0.05) (actual time=0.003..0.003 rows=0 loops=50123)
-> Covering index lookup on FTI1 using AK_MFTI (FT_SRK_ID=<cache>(RCE_NS.MTNE_ID)) (cost=0.74 rows=2) (actual time=0.003..0.003 rows=0 loops=50123)
-> Index lookup on RC_STS using RCE_STS (AID=RCE.AID, R_CON_ID=RCE.R_CON_ID, R_CON_VER_NB=RCE.R_CON_VER_NB) (cost=1.62 rows=2) (actual time=0.013..0.016 rows=1 loops=671352)
-> Single-row index lookup on D_LKP_STS using PRIMARY (D_LKP_NB=RC_STS.R_CON_ESTS_NB) (cost=1.00 rows=1) (actual time=0.000..0.000 rows=1 loops=745483)
-> Single-row index lookup on D_LKP_FRQ using PRIMARY (D_LKP_NB=RC_STS.RR_FRQ_NB) (cost=1.00 rows=1) (actual time=0.000..0.000 rows=1 loops=745483)
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 16 2024 - 12:20:58 CEST