Forum OpenACS Q&A: Strange query performance when using <b>UNION</b>

Collapse
1: Strange query performance when using <b>UNION</b>
Posted by Brian Fenton on 03/23/05 04:00 PM
Hello all.
I'm trying to tune a query which has me scratching my head a little. It's a UNION between 2 queries (which are both fast), but the overall query is quite slow. Does a UNION normally slow things down this much? Removing the order by makes no difference

This is the query:
select s.staff_id as instructor_id,
acs_object.name(s.staff_id) as instructor_name,
lower(acs_object.name(s.staff_id)) as lower_instructor_name
from staff s, cc_users c
where s.staff_id = c.user_id
and c.member_state not in ('deleted', 'banned')
union
select -1 as instructor, ' none' as instructor_name,
' none' as lower_instructor_name from dual
order by lower_instructor_name

The query takes about 6 seconds to run on a production site but the 2 individual querys take about 0.2 seconds each. Removing the order by still takes about 5 or 6 seconds.

This is the explain plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=42 Bytes=74)
1 0 SORT (UNIQUE) (Cost=12 Card=42 Bytes=74)
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost=3 Card=1 Bytes=74)
4 3 NESTED LOOPS (Cost=3 Card=1 Bytes=69)
5 4 NESTED LOOPS (Cost=3 Card=1 Bytes=64)
6 5 NESTED LOOPS (Cost=3 Card=1 Bytes=59)
7 6 NESTED LOOPS (Cost=2 Card=1 Bytes=46)
8 7 NESTED LOOPS (Cost=2 Card=2 Bytes=82)
9 8 INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX_PK' (UNIQUE) (Cost=2 Card=2 Bytes=72)

10 8 INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
11 7 INDEX (UNIQUE SCAN) OF 'STA_PK' (UNIQUE)
12 6 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERSHIP_RELS' (Cost=1 Card=115 Bytes=1495)
13 12 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (UNIQUE)
14 5 INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE)
15 4 INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE)
16 3 INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE)
17 2 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)

Can anybody explain this behaviour?

Collapse
2: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/23/05 04:05 PM
I forgot to mention this is on Oracle 8i.
Collapse
3: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Dirk Gomez on 03/23/05 04:07 PM
A union weeds out duplicates - for this to do Oracle needs to perform an implicit sort by. Can you rule out duplicates? If yes, try union all which does not remove duplicates.
Collapse
4: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/23/05 04:21 PM
Thanks for the reply Dirk. Changing to UNION ALL makes no difference to the performance. Anyway I need to remove duplicates so I need to use UNION.

It's very odd, isn't it?!

Brian

Collapse
5: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Dirk Gomez on 03/23/05 04:36 PM
Hmm, well then please post the plans of the queries individually.
Collapse
6: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/23/05 05:05 PM
select staff_id as instructor_id,
acs_object.name(staff_id) as instructor_name,
lower(acs_object.name(staff_id)) as lower_instructor_name
from staff s, cc_users c
where s.staff_id = c.user_id
and c.member_state not in ('deleted', 'banned')

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=296 Card=8 Bytes=584)
1 0 NESTED LOOPS (Cost=296 Card=8 Bytes=584)
2 1 NESTED LOOPS (Cost=288 Card=8 Bytes=544)
3 2 NESTED LOOPS (Cost=280 Card=8 Bytes=504)
4 3 NESTED LOOPS (Cost=272 Card=8 Bytes=464)
5 4 NESTED LOOPS (Cost=264 Card=8 Bytes=360)
6 5 NESTED LOOPS (Cost=264 Card=1098 Bytes=43920)
7 6 INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX_PK' (UNIQUE) (Cost=264 Card=1098 Bytes=38430)
8 6 INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
9 5 INDEX (UNIQUE SCAN) OF 'STA_PK' (UNIQUE)
10 4 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERSHIP_RELS' (Cost=1 Card=18273 Bytes=237549)
11 10 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (UNIQUE)
12 3 INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE) (Cost=1 Card=149050 Bytes=745250)
13 2 INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE) (Cost=1 Card=149190 Bytes=745950)
14 1 INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE) (Cost=1 Card=221850 Bytes=1109250)

select -1 as instructor, ' none' as instructor_name, ' none' as lower_instructor_name from dual
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'

Collapse
7: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/24/05 12:01 PM
Another interesting fact is that this query is fast on both the development and the release databases - it's only slow on the live database. The main difference between these systems is that live has a much large CC_USERS with over 36000 records. This CC_USERS view is quite slow - just doing a SELECT COUNT(*) from it takes 5 seconds on live. The STAFF table has 247 records on live and 122 on the release database. When STAFF and CC_USERS are joined it correctly drives off the staff_id.

So, it's almost as though using the UNION causes a full table scan of CC_USERS (even though the explain plan didn't show that). I'm going to go check that explain plan again.

Curiouser and curiouser!

Collapse
8: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/24/05 12:10 PM
Yes, that's definitely the correct explain plan. 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=294 Card=44 Bytes=222)
   1    0   SORT (UNIQUE) (Cost=289 Card=44 Bytes=222)
   2    1     UNION-ALL
   3    2       NESTED LOOPS (Cost=276 Card=3 Bytes=222)
   4    3         NESTED LOOPS (Cost=273 Card=3 Bytes=207)
   5    4           NESTED LOOPS (Cost=270 Card=3 Bytes=192)
   6    5             NESTED LOOPS (Cost=267 Card=3 Bytes=177)
   7    6               NESTED LOOPS (Cost=264 Card=3 Bytes=138)
   8    7                 NESTED LOOPS (Cost=264 Card=609 Bytes=24969)
   9    8                   INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX_PK' (UNIQUE) (Cost=264 Card=609 Bytes=21924)
  10    8                   INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
  11    7                 INDEX (UNIQUE SCAN) OF 'STA_PK' (UNIQUE)
  12    6               TABLE ACCESS (BY INDEX ROWID) OF 'MEMBERSHIP_RELS' (Cost=1 Card=18165 Bytes=236145)
  13   12                 INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (UNIQUE)
  14    5             INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE) (Cost=1 Card=149340 Bytes=746700)
  15    4           INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE) (Cost=1 Card=149720 Bytes=748600)
  16    3         INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE) (Cost=1 Card=222490 Bytes=1112450)
  17    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)




Statistics
----------------------------------------------------------
      74048  recursive calls
         10  db block gets
     261044  consistent gets
          0  physical reads
          0  redo size
       8319  bytes sent via SQL*Net to client
       1424  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
        139  rows processed
Collapse
9: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Dirk Gomez on 03/24/05 01:32 PM
Compare your init.oras then. Look for a parameter like complex view merging or something similar...that may be the culprit.
Collapse
10: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Brian Fenton on 03/24/05 04:33 PM
Hmmm.. looks like you could be onto something there. AskTom has an article about this
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3561623983484

In 8i the parameter is called _COMPLEX_VIEW_MERGING and it's undocumented. I'll play around a bit with this on test before touching production. This is starting to look quite hairy indeed....

Thanks for the wisdom Dirk!

Collapse
11: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by russ m on 03/29/05 05:43 AM
depending on how many rows you're returning, it might also help to replace the calls to acs_object.name() with explicit references to the user name columns - I haven't used Oracle in ages, but ISTR that context switches between SQL and PL/SQL are expensive... plus acs_object.name() isn't exactly lightweight itself...

not that this would do as much as having the optimizer do the right thing, but it should help a bit...

Collapse
12: Re: Strange query performance when using <b>UNION</b> (response to 1)
Posted by Jade Rubick on 03/29/05 05:53 AM
Also, you don't need to use cc_users, most likely. That will save you a few joins.