I have a weird performance issue with a relatively
simple query with INTERSECT and need advice.
The query in question looks like this
(subquery A)
intersect
(subquery B)
independently subquery A runs to completion in 2 seconds and returns 487 rows
independently subquery B runs to completion in 6 seconds and returns 2440 rows
the combined query (with INTERSECT) returns 15 matches
(correct), but runs for a whooping 1 minute 6 seconds
Considering the relatively small size of INTERSECT,
I cannot understand why the performance degrades so much.
Either subquery returns a result set of strings
20-30 characters long. Both subqueries contain nothing
exceptional - very conventional SQL. The size of
intersected results is relatively small, so no memory
issues should be responsible (I could intersect
this results with Perl in less then a second).
The only possible catch is that the strings
returned generally differ only in the last few
characters - could this affect sorting/hashing?
For all
means and purposes Oracle cannot run INTERSECT for
almost 1 minute (we are talking 2 MHz/512MB machine
running Oracle 9i)
So I presume it must be a query optimizer fault.
Nevertheless, playing with QUERY_REWRITE_ENABLED does not solve
anything, likewise COST hint.
Could anyone advice to me how to tackle this problem
or at least explain what is happening?
Anatoli Krassavine
toly@intellidos.com
Intellidos Limited
Have you looked at the explain plan of ...
* subquery A on it's own
* subquery B on it's own
* the two subquerys when intersected
... to see if there is a difference in how they are being executed when intersected?
Re: Very slow INTERSECT under 9i - optimizer mistake?
Originally posted by anatoli Hello,
I have a weird performance issue with a relatively
simple query with INTERSECT and need advice.
The query in question looks like this
(subquery A)
intersect
(subquery B)
independently subquery A runs to completion in 2 seconds and returns 487 rows
independently subquery B runs to completion in 6 seconds and returns 2440 rows
the combined query (with INTERSECT) returns 15 matches
(correct), but runs for a whooping 1 minute 6 seconds
Both subquery independently don't use any addition sorting.
Bot if u try to get intersection of the queries, Oracle has to execute addition step for each of them:
SORT UNIQUE
This is reason, why intersection more slowly, then two subqueries
separately.
Re: Re: Very slow INTERSECT under 9i - optimizer mistake?
Originally posted by Shestakov Both subquery independently don't use any addition sorting.
Bot if u try to get intersection of the queries, Oracle has to execute addition step for each of them:
SORT UNIQUE
This is reason, why intersection more slowly, then two subqueries
separately.
Given the number of rows involved, i don't believe that this explains the differencein execution time.
This is real query (or near real, because view based on this query).
This query use MINUS (it's like INTERSECT).
U can see execution plan if u don't belive that this explains the difference.
Code:
select a.period, center, ent, 1 as source_id
from dtax_model.TM_CC_ENT_LKUP a
, dtax_mlt.MLT_ENT b
WHERE a.period = b.period
AND a.ent_id = b.ENT_ID
AND a.active ='Y'
union all
(
select a.period, center, ent, 2
from dtax_mlt.MLT_CC_ENT_LKUP a
, dtax_mlt.MLT_ENT b
WHERE a.period = b.period
AND a.ent_id = b.ENT_ID
minus
select a.period, center, ent, 2
from dtax_model.TM_CC_ENT_LKUP a
, dtax_mlt.MLT_ENT b
WHERE a.period = b.period
AND a.ent_id = b.ENT_ID
AND a.active = 'Y'
)
;
Plan :
Code:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=52 Bytes=884)
1 0 VIEW OF 'V_TM_CC_ENT_LKUP_NEW2' (Cost=9 Card=52 Bytes=884)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TM_CC_ENT_LKUP' (Cost=2 Card=10334 Bytes=175678)
4 3 NESTED LOOPS (Cost=3 Card=13 Bytes=390)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
6 5 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
7 4 INDEX (RANGE SCAN) OF 'IDX_1_ENT_CC' (NON-UNIQUE) (Cost=1 Card=10334)
8 2 MINUS
9 8 SORT (UNIQUE)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'MLT_CC_ENT_LKUP' (Cost=1 Card=40900 Bytes=613500)
11 10 NESTED LOOPS (Cost=3 Card=26 Bytes=728)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
13 12 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
14 11 INDEX (RANGE SCAN) OF 'IDX_ENT' (NON-UNIQUE)
15 8 SORT (UNIQUE)
16 15 TABLE ACCESS (BY INDEX ROWID) OF 'TM_CC_ENT_LKUP'(Cost=2 Card=10334 Bytes=175678)
17 16 NESTED LOOPS (Cost=3 Card=13 Bytes=390)
18 17 TABLE ACCESS (BY INDEX ROWID) OF 'MLT_ENT' (Cost=2 Card=1 Bytes=13)
19 18 INDEX (RANGE SCAN) OF 'IDX_ENT_PERIOD' (NON-UNIQUE) (Cost=1 Card=1)
20 17 INDEX (RANGE SCAN) OF 'IDX_1_ENT_CC' (NON-UNIQUE) (Cost=1 Card=10334)
Steps :
9 SORT (UNIQUE)
15 SORT (UNIQUE)
show extra sort operations.
Thank you for your responses. I looked at the execution plan. I would have expected the combined query to largely inherit original subquery plans and combine them using UNIQUE SORT, etc. Instead, the first subquery subplan is completely rewritten (the second subquery subplan is identical).
From looking at the new plan I could understand why it runs slow - but why Oracle decides to behave so differently with effectively identical SQL code? What causes it and how could I prevent it?
The plans for independent subqueries and a combined query are attached. I realize that without seeing the original SQL they are not great use, nevertheless they illustrate the point. In a big combined query the optimizer suddenly decides to go for a very simple recursive condition check on several large tables.
Cheers,
Toly
Anatoli Krassavine
Intellidos Limited
Anatoli Krassavine
toly@intellidos.com
Intellidos Limited
The reasons belong to the optimizer alone. Based on the 5 dozen different variables it looked at, it decided that out of the 5000 ways it could solve a query of that size, it *thought* the best way was actually *not* to wait until the very last step to do an intgersect. It re-wrote the intersect out of the query (which it always does) and simply came up with a different plan than you expected. All I can tell you is that the optimizer makes mistakes.
How do you stop it? Well, if you mean how do I force the intersect to be the last step when I do exactly this type of statement, the answer is easy, add AND ROWNUM > 0 to both sub-queries. This will force the optimizer to solve the queries independently, thus forcing the intersect to be done last. You could also use a lot of hints to do the same thing, and it would probably be 'more correct', but it would also be more difficult. The rownum thing is easier.
If you mean 'How do I stop the optimizer from making mistakes and/or doing things that I don't expect? Well, all I can say is can you please let the rest of us know when you figure that one out, eh?
Lesson of the day? Never assume you know what the optimizer is or *should* be doing. *Always* check the plan.
Please accept my deepest appreciation for your advice - it works and solves the problem at hand. The query now runs in under 4 seconds.
I have some related questions to clarify the situation for me, but they are not urgent any more.
I was not naive enough to ask how to make Oracle always optimize correctly, :-). My biggest frustration was to see Oracle optimize/run independent subqueries without a glitch and then suddenly go complete bonkers when I start applying trivial set operations to their results.
1) What are the practical drawbacks of using ROWNUM > 0 in terms of performance, etc compared with a properly optimized query?
2) Is there a global way to force Oracle to solve the queries involved in an INTERSECT independently? I.e. is there a way to treat INTERSECT as kind of a "hard break" for query optimization?
Thank you again,
Toly
Anatoli Krassavine
toly@intellidos.com
Intellidos Limited