View/SQL for temp segment usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: View/SQL for temp segment usage

  1. #1
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52
    If I would like to look at which session is currently using temporary segments, which view would I use? I did a select * from V$sort_usage but I am not sure what each of the columns represents. My tablespace is the same size on both of the databases (which are identical), but one of the databases shows the temp tablespace as being full. I want to find out what/who is holding those temp segments, and furthermore find out why the usage has not dropped in days(which it should do when the process/SQL statement completes, correct?) Could an orphaned SQL statement cause the temp tablespace to fill up and not deallocate temp segments?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try this:

    SELECT s.username , u.TABLESPACE ,
    u.segtype ,u.blocks, u.segblk#
    FROM v$session s, v$sort_usage u
    WHERE s.saddr = u.session_addr;

  3. #3
    Join Date
    May 2000
    Location
    Huntsville, AL. USA
    Posts
    52

    Still allocated temp segments

    I am running 8.1.6 on NTS, and I used a similar query: select a.username, b.contents, b.extents from v$session a, v$sort_usage b where a.saddr = b.session_addr; and the query returned no rows. Therefore it would seem that nothing is performing a large sort. I cannot explain why the tablespace is 2GB! and it is completely used (99.7%). It has been this way for weeks. No objects were created in the temp tablespace, so I would have guessed it could only be temp segments allocated taking up that much space. My concern now is when someone tries to do a sort, they may not be able to if the space is all used up. What can I check?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You should not worry at all. If temp tablespace is configured as it should be it would yield simmilar picture as you are getting. In temp tablespace extents should not get deallocated after the sorting is done (so implicit or explicit coalescing on temp tablespace should never occure).

    When sorting is over, the extents remain there and are available for subsequent sorts. Only when no "free" extents (that is, extents that are not in used by current sorts) are available will Oracle allocate next extents. So for example, if one of your create_a_realy_big_index commands allocate 2GB woth of temp extents, all those extents remai available for next sorts. Only when you create_an_even_bigger_index or perform tons of concurent sorts some new temp extents will get allocated there.

    This saves the need for quite costy "allocate new extent" operation and dramaticaly increase the efficiency of sorts.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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