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

Request notifications

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
Posted by Brian Fenton on
I forgot to mention this is on Oracle 8i.
Collapse
Posted by Dirk Gomez on
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
Posted by Brian Fenton on
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
Posted by Dirk Gomez on
Hmm, well then please post the plans of the queries individually.
Collapse
Posted by Brian Fenton on
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
Posted by Brian Fenton on
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
Posted by Brian Fenton on
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
Posted by Dirk Gomez on
Compare your init.oras then. Look for a parameter like complex view merging or something similar...that may be the culprit.
Collapse
Posted by Brian Fenton on
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
Posted by russell muetzelfeldt on
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
Posted by Jade Rubick on
Also, you don't need to use cc_users, most likely. That will save you a few joins.