Session hangs waiting for sort segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Session hangs waiting for sort segment

  1. #1
    Join Date
    May 2001
    Posts
    6

    Question Session hangs waiting for sort segment

    When a statement needs the temporary tablespace it hangs. In v$session_wait I get Sort Segment Request. Then I get other session in v$session_wait waiting for SS enqueue, Is there any place to see what the P1RAW field means. I thougt SS was a wait for a (Row-S) lock in exclusive mode but this time I guess it is sort segment wait.

    If I truss the process waiting for a sort segment I get the following output:
    /1: semtimedop(3342339, 0xFFBE6B34, 1, 0xFFBE6B2C) Err#11 EAGAIN
    /1: semtimedop(3342339, 0xFFBE6B34, 1, 0xFFBE6B2C) (sleeping...)

    I have tried to create a new Temporary tablespace and the same problem occur. Why isn't the temp tablespace used?

    When I select from v$sort_segment I get no rows!
    Any ideas?

  2. #2
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    A question: How big is your SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE (?) parameters in your INIT.ORA/SPFILE.ORA?

    They may be too small.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #3
    Join Date
    May 2001
    Posts
    6
    I'm using pga_aggregate_target=500M and workarea_size_policy=auto.
    The problem occurs when I have used my sortarea in memory and are supposed to use the temp tablespace.

  4. #4
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    OS Error 11, EAGAIN, means you are out of process space at the OS level, not the Oracle level.

    You need to either increase the amount of memory available to Oracle or the number of processes the Oracle account can run simultaneously. Probably the number of processes.

    It must be a pretty large sort.

    By the way, Oracle will try to use memory to sort in SORT_AREA_SIZE chunks, using the TEMP tablespace when the sort set is too large to fit in one chunk, and possibly data files if too large for temp space (I am vague on this, but I seem to remember the 'files' thing from the documentation.)
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

Posting Permissions

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


Click Here to Expand Forum to Full Width