OraFAQ Forum: Performance Tuning » UNION causing slow performance

Home » RDBMS Server » Performance Tuning » UNION causing slow performance (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNION causing slow performance [message #331725]

Sat, 05 July 2008 01:07

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

Dumb question.. I have a query that uses a UNION statement, combining 2 individual queries. Both individual queries are almost identical, and both, individually, complete in less than 2-5 seconds. However, when I execute them via a UNION query (which is what I need), it takes 5-8 minutes to complete! This doesn't seem right to me, so I was wondering if there's anything I can do to "force" it to execute the queries separately, then combine them. There will be no duplicates between them, so I really don't need any extra logic in the union, like a distinct, etc. Changing it to UNION ALL to avoid the distinct aspect seems to make it worse, though. Any ideas?

Below is what the query looks like. Please feel free to offer any advice to combine or change it to make it perform better:

select t1.field1, t1.field2, t2.field3
from table1 t1, table2 t2
where t1.ind = 'Y'
  and t1.id = 12345
  and t1.a = t2.a
UNION
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1, table2 t2
where t1.ind = 'N'
  and t1.id = 12345
  and t1.a = t2.a

Thanks!!

Report message to a moderator

Re: UNION causing slow performance [message #331729 is a reply to message #331725]

Sat, 05 July 2008 01:50

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Changing it to UNION ALL to avoid the distinct aspect seems to make it worse,

Prove it, this is most likely wrong.

Regards
Michel

Report message to a moderator

Re: UNION causing slow performance [message #331733 is a reply to message #331729]

Sat, 05 July 2008 02:17

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

My apologies - I re-ran the query, noting the comparisons between the UNION and the UNION ALL, and I've noticed the UNION ALL performs slightly better than the UNION, but just slightly.. I'm now running with a smaller data-set, and the UNION ALL still takes about 15-17 seconds, when each individual query now takes less than 1/2 of a second to execute. I'm still a bit confused as to why this is the case, when I'd expect the overall query to perform in less than a second or two. Thoughts?

Report message to a moderator

Re: UNION causing slow performance [message #331737 is a reply to message #331733]

Sat, 05 July 2008 02:49

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Show an explain plan of each individual query, and then one for the UNION query. Whatever is different will be the thing causing it to run slower.

Ross Leishman

Report message to a moderator

Re: UNION causing slow performance [message #331741 is a reply to message #331733]

Sat, 05 July 2008 02:59

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Execute in SQL*Plus "set autotrace traceonly" and the 4 queries.
Then copy and paste the session.
Don't forget to format it accordingly to forum guidelines.

Regards
Michel

Report message to a moderator

Re: UNION causing slow performance [message #331823 is a reply to message #331725]

Sat, 05 July 2008 14:43

Kriptas
Messages: 118
Registered: June 2008

Senior Member

Oracle can rebuild UNION queries, so without execution plan it is hard to find problem.

please try this query:

select t1.field1, 
       DECODE(t1.ind,'Y',t1.field2,NULL) field2,
       DECODE(t1.ind,'Y',t2.field3,NULL) field3
from table1 t1, table2 t2
where t1.id = 12345
      and t1.a = t2.a

if column t1.ind has more values then 'Y' or 'N' add
      and t1.ind in ('Y','N')

Report message to a moderator

Re: UNION causing slow performance [message #331826 is a reply to message #331741]

Sat, 05 July 2008 15:18

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

Boy I feel really dumb now, I completely overlooked the ORDER BY I had in the overall UNION ALL query, which I failed to include in my psuedo-query above. When I remove the ORDER BY, the overall query runs in less than a second! Unfortunately I DO need the ordering in there.. Is there a way to speed something like that up? I understand I'm potentially dealing with millions of rows having to be sorted here, so it's expected that this should take a little time, but just wanted to see if there are any methods by which that kind of thing could be sped up (Kriptas, I ran multiple tests against a query using DECODEs instead of the UNION ALL, and it actually ran slower :0 ).

Here is the explain plan for the whole query (smaller dataset - 37k records) with the UNION ALL (default is RULE optimizer):

36975 rows selected.

Elapsed: 00:00:19.56

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (ORDER BY)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2'
   4    3         NESTED LOOPS
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1'
   6    5             INDEX (RANGE SCAN) OF 'TBL1NN01' (NON-UNIQUE)
   7    4           INDEX (RANGE SCAN) OF 'TBL2PK00' (UNIQUE)
   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1'
   9    8         INDEX (RANGE SCAN) OF 'TBL1NN01' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
      80324  consistent gets
      18699  physical reads
          0  redo size
    8438104  bytes sent via SQL*Net to client
      30114  bytes received via SQL*Net from client
       2466  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      36975  rows processed

Here is the same query with Optimizer=CHOOSE:

36975 rows selected.

Elapsed: 00:00:13.25

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=61561 Card=38626 Bytes=20626284)
   1    0   SORT (ORDER BY) (Cost=59455 Card=38626 Bytes=20626284)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=3 Card=1 Bytes=306)
   4    3         NESTED LOOPS (Cost=58697 Card=19313 Bytes=13268031)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (Cost=758 Card=19313 Bytes=7358253)
   6    5             INDEX (RANGE SCAN) OF 'TBL1NN01' (NON-UNIQUE) (Cost=94 Card=19313)
   7    4           INDEX (RANGE SCAN) OF 'TBL2PK00' (UNIQUE) (Cost=2 Card=1)
   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (Cost=758 Card=19313 Bytes=7358253)
   9    8         INDEX (RANGE SCAN) OF 'TBL1NN01' (NON-UNIQUE) (Cost=94 Card=19313)

Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
      80324  consistent gets
      18586  physical reads
          0  redo size
    8438104  bytes sent via SQL*Net to client
      30133  bytes received via SQL*Net from client
       2466  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      36975  rows processed

Report message to a moderator

Re: UNION causing slow performance [message #331828 is a reply to message #331725]

Sat, 05 July 2008 15:55

Kriptas
Messages: 118
Registered: June 2008

Senior Member

please try the same query
but with full record tables

and post execution plan and trace of my query
(or post execution plan and trace of my query with the same tables as in last your post)

do you really need to select only one row from t1 table
where t1.id=12345 or it is only for example and you need to select all t1.id ?
if so, that query must be faster then nested loops
and with my qury you can use order by safely Wink

Wink

select /*+ USE_HASH(t1 t2) */
       t1.field1, 
       CASE WHEN t1.ind='Y' WHEN t1.field2 ELSE NULL END field2,
       CASE WHEN t1.ind='Y' WHEN t2.field3 ELSE NULL END field3,
from table1 t1, table2 t2
where t1.a = t2.a

Report message to a moderator

Re: UNION causing slow performance [message #331829 is a reply to message #331725]

Sat, 05 July 2008 15:58

Kriptas
Messages: 118
Registered: June 2008

Senior Member

and if you can. please export data of the smaller tables to text file, and send it to me by email kriptas@gmail.com
and table creation ddl too please Wink

Wink

Report message to a moderator

Re: UNION causing slow performance [message #331832 is a reply to message #331829]

Sat, 05 July 2008 17:24

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

Thanks for the responses. I got rid of the UNION ALL and implemented the CASE statements instead. The resulting query (with no hints) actually runs a bit slower than the UNION ALL, and adding the USE_HASH makes it run forever Sad

Sad

A bit of background, there are about 40 million rows on t1 and 90 million on t2. For t1.id=12345, there are the 35k rows (or I can pick a different ID that has more or less).

This is a better representation of what the query looks like:

select 
    t1.field1, 
    CASE WHEN t1.ind='Y' THEN t2.field2 ELSE NULL END field2,
    CASE WHEN t1.ind='Y' THEN t2.field3 ELSE NULL END field3,
from table1 t1, table2 t2
where t1.a = t2.a
  and t1.id = 12345
  and t2.indicator = 'Y'
order by t1.a

I'll get you the other relavent info asap. I really like the look/feel of the above query a lot better than the UNION ALL one, so I'm hoping we can get this thing running fast with the above logic and the order by in there.

Here is the explain plan with the CASE logic implemented as mentioned above:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=181689 Card=57940 Bytes=39804780)

   1    0   SORT (ORDER BY) (Cost=181689 Card=57940 Bytes=39804780)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=3 Card=1 Bytes=306)

   3    2       NESTED LOOPS (Cost=176084 Card=57940 Bytes=39804780)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1'
          (Cost=2264 Card=57940 Bytes=22075140)

   5    4           INDEX (RANGE SCAN) OF 'TBL1NN01' (NON-UNIQUE) (Cost=274 Card=57940)

   6    3         INDEX (RANGE SCAN) OF 'TBL2PK00' (UNIQUE) (Cost=2 Card=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
      80321  consistent gets
      12327  physical reads
          0  redo size
    8438104  bytes sent via SQL*Net to client
      29404  bytes received via SQL*Net from client
       2466  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      36975  rows processed

Report message to a moderator

Re: UNION causing slow performance [message #331833 is a reply to message #331725]

Sat, 05 July 2008 18:04

Kriptas
Messages: 118
Registered: June 2008

Senior Member

strange...
look at the trace
the query with CASE have less physical reads (about 30% less) and litle bit less concisten gets.

what about HASH JOIN...
it takes some time to calculate hash for the tables, and after ir joins very quickly.. so if you waiting only for first rows hash join will be slower then nested loops.

if you need do calculations for all t1.id then HASH JOIN will be a lot faster then nested loops. if you need to acces all rows in t1 and join them with t2, nested loops is very bad way. better way is full scan t1 and t2 and hash join them.
to join tables of 40 mln rows and 90 rows with hash join can take about 5-10 mins if server is quadcore itanium 3ghz

hash join will take almost the same time to join all rows from t1 with t2.. and 1 row from t1 and coresponding rows from t2.

so if you need seled 1 t1.id from t1 and coresponding rows from t2 then use nested loops,
and is you need to select all t1.id and all coresponding rows from t2 then use HASH JOIN.

to measure run time use create table as statement
then wait till ends 1 query, and do the same with other query.
first rows whitch shows on the screen to not show real time of execution.

Report message to a moderator

Re: UNION causing slow performance [message #331834 is a reply to message #331725]

Sat, 05 July 2008 18:14

Kriptas
Messages: 118
Registered: June 2008

Senior Member

there is some code else...
maybe it help if you need select only 1 t1.id at the time..

select 
    t1.field1, 
    CASE WHEN t1.ind='Y' THEN t2.field2 ELSE NULL END field2,
    CASE WHEN t1.ind='Y' THEN t2.field3 ELSE NULL END field3,
from (select t1.ind, t1.field1, t1.a from table1 t1 where t1.id=12345 order by t1.a ) t3
INNER JOIN table2 t2 ON t3.a = t2.a AND t2.indicator = 'Y'

I'm not sure, but if order t1 by t1.a and then join them the result must be the same...
but oracle will do ordering on lot less records.

Report message to a moderator

Re: UNION causing slow performance [message #331835 is a reply to message #331725]

Sat, 05 July 2008 18:30

Kriptas
Messages: 118
Registered: June 2008

Senior Member

and one more thing...
using HASH JOIN for joining all rows on t1 with all rows with t2 is best then optimiser is CBO and gathered statistics for tables, gathered statistisc for indexes and gathered histograms for table columns Smile

Smile

and block size is 32k and.. so on and on and on Wink

Wink

and one question more... what index is on t2?
one columnt in or two or more ?

[Updated on: Sat, 05 July 2008 19:01]

Report message to a moderator

Re: UNION causing slow performance [message #331837 is a reply to message #331835]

Sat, 05 July 2008 19:48

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

Great idea about separating out the offending query that does the ordering! The overall new query you posted limits the records to only ones that have an t2.indicator of Y, and unfortunately that eliminates a lot of rows we still need returned. I changed it to have a (+) on that line to fix this; is that a good approach to getting them back in there?

For example:

select 
    t1.field1, 
    CASE WHEN t1.ind='Y' THEN t2.field2 ELSE NULL END field2,
    CASE WHEN t1.ind='Y' THEN t2.field3 ELSE NULL END field3,
from (select t1.ind, t1.field1, t1.a from table1 t1 where t1.id=12345 order by t1.a ) t3
INNER JOIN table2 t2 ON t3.a = t2.a AND t2.indicator (+) = 'Y'

Regardless, tests have unfortunately indicated that although the performance is good on the new query (with the (+)), the UNION ALL one still significatly outperforms it in almost every case.. I'm having a hard time understanding why that is. I can post the new explain plan if needed for the new CASE one, unless you can think of a better way to do the above (+) outer join.

To answer your question, on t2, the indexes are as follows:

       Constraints/Indexes:

       Name        Type Index Parameters
       ----------  ---- ----- -------------------------------------------------------------------------------------------
c      @@@@tbl2pk00 pk   yes   (a,seq_n)
c      @@@@tbl2fk01 fk   no    (a) references table1 (a)

Table 1 has primary key of a. Thoughts?

Report message to a moderator

Re: UNION causing slow performance [message #331847 is a reply to message #331837]

Sun, 06 July 2008 00:45

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Hopefully this will clear some things up:

A hash join - in this case - will not be quicker because there is no idenpendent access path on TABLE2. To perform a HASH join, you would need to FULL TABLE SCAN table2, because it does not contain the constraining ID column.

The UNION ALL is faster because it does not have to perform the CASE statements. It probably DOES have to do extra IO (reading TABLE1 rows twice) but those rows are almost certainly cached.

The optimal SQL would probably be:

select t1.field1, t1.field2, t2.field3
from table1 t1, table2 t2
where t1.ind = 'Y'
  and t1.id = 12345
  and t1.a = t2.a
UNION ALL
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
  and t1.id = 12345
ORDER BY 1

I suspect this is the SQL you ran to get the plan shown above. It is advantageous over the CASE version because it does not attempt to join to TABLE2 for the ind=N rows.

Of course, this may not be functionally identical to the UNION version you showed unless all values of "a" exist in TABLE2 and are unique. You be the judge.

Also, DO NOT push the ORDER BY into an inline view. If it is used as the hash table of a subsequent join, the order will be lost.

Ross Leishman

Report message to a moderator

Re: UNION causing slow performance [message #331859 is a reply to message #331725]

Sun, 06 July 2008 01:51

Kriptas
Messages: 118
Registered: June 2008

Senior Member

oh right,I do not notify that secont select in union have only one table. my mistake.

Report message to a moderator

Re: UNION causing slow performance [message #331870 is a reply to message #331847]

Sun, 06 July 2008 03:48

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

Once again, you guys have proven to be incredible. That makes sense why the UNION ALL is running faster, and after re-arranging the WHERE clause lines how you indicated, the query is truly running even slightly faster (you were exactly right about the ordering - tried it my way and yours, and yours runs faster).

The query is now running consistently in under 3-4 minutes for over 2 million records returned. Needless to say, this is a step up from the 30-40 minutes it took before starting this re-write. Thank you all again!!! Razz

Razz

Report message to a moderator

Re: UNION causing slow performance [message #331872 is a reply to message #331725]

Sun, 06 July 2008 04:22

Kriptas
Messages: 118
Registered: June 2008

Senior Member

rleishman,
but your second select in UNION is not corect 100% of cases

select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
  and t1.id = 12345

maybe there is not records in table2 for table1 record,
so your query will output more rows than needed.

and why you use OREDER BY 1 in second subselect?
It is ordering all UNION rows? or only second subselect in UNION output rows?

Report message to a moderator

Re: UNION causing slow performance [message #331876 is a reply to message #331872]

Sun, 06 July 2008 06:11

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Kriptas wrote on Sun, 06 July 2008 19:22

rleishman,
but your second select in UNION is not corect 100% of cases

select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
  and t1.id = 12345

maybe there is not records in table2 for table1 record,
so your query will output more rows than needed.

rleishman wrote on Sun, 06 July 2008 15:45

Of course, this may not be functionally identical to the UNION version you showed unless all values of "a" exist in TABLE2 and are unique. You be the judge.

Kriptas wrote on Sun, 06 July 2008 19:22

and why you use OREDER BY 1 in second subselect?
It is ordering all UNION rows? or only second subselect in UNION output rows?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079 - See section on "position"

Ross Leishman

Report message to a moderator

Re: UNION causing slow performance [message #331877 is a reply to message #331725]

Sun, 06 July 2008 06:22

Kriptas
Messages: 118
Registered: June 2008

Senior Member

sorry I do not read you post wery good.

But about "order by 1" I know what is "order by 1" but I want to know
it orders only

select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
  and t1.id = 12345
ORDER BY 1

or whole

select t1.field1, t1.field2, t2.field3
from table1 t1, table2 t2
where t1.ind = 'Y'
  and t1.id = 12345
  and t1.a = t2.a
UNION ALL
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
  and t1.id = 12345
ORDER BY 1

?
if I read rigth, it is need to order whole output record set.

Report message to a moderator

Re: UNION causing slow performance [message #331880 is a reply to message #331876]

Sun, 06 July 2008 07:21

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

rleishman wrote on Sun, 06 July 2008 21:11

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079 - See section on "position"

In that "Position" section I recommended, there is a link to "Sorting Query Results" that answers your question.

Ross Leishman

Report message to a moderator

Re: UNION causing slow performance [message #331892 is a reply to message #331725]

Sun, 06 July 2008 08:27

Kriptas
Messages: 118
Registered: June 2008

Senior Member

thanks, I do not know that about order by in the last component query in UNION statement.

Report message to a moderator

Previous Topic:	Query taking much time
Next Topic:	Materialized view not getting used

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 10:50:50 CDT 2024