Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: select with multiple unions hangs

    Hey All -

    I have a user that's writing a huge query with over 10 unions. If I run each select separately (IE: no unions), each seems to finish in a reasonable time. However, if I go past about 3 unions the query seems to hang forever. Any idea what might be making it hang? TEMP space? or something else?

    Thanks,

    Amy

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    what kind of union are they doing. If it is "union", oracle will perform a scan of all preceding unions every time to remove duplicates. If it is "union all" it will simply run each query and append the results.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    an EXPLAIN_PLAN might provide a clue or two, but no guarentee.
    A better way to gain visibility would be to enable SQL_TRACE (AT LEVEL 12).
    If on *nix use of truss, strace or equivalent might reveal additional clues.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2006
    Posts
    28
    It's definitely a "union" and not "union all". I'm assuming that when it scans of all preceding unions, this is put in temp tablespace? Could I be running out of temp tablespace?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Could I be running out of temp tablespace?
    An error would be thrown if/when that occurs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What may be happening is that when you go for more than 3 queries it switches from an in memory sort (v fast) to a disk sort (much slower). An increased PGA may help avoid this.

    You might want to look into merging the sql queries without using union if its possible.

    Alan

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >An increased PGA may help avoid this.
    As opposed to
    ALTER SESSION SET SORT_AREA_SIZE=1000000000?
    Last edited by anacedent; 04-13-07 at 18:55.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Tuning memory usage will depend on the Oracle version. There is a good section about it in the Performance Tuning Guide.

  9. #9
    Join Date
    Jul 2006
    Posts
    28
    Thank you all - I'll try some of these suggestions today.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    if you do not need to suppress duplicates, I would really suggest that you change the union to union all. It will greatly speed up your query.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •