DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: When does SMON in 9.2 deallocate TEMP segments?

  1. #1
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    When does SMON in 9.2 deallocate TEMP segments?

    Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit - HP-UX 11.0

    Temp Tablespace is 4GB...

    SQL> select * from dba_temp_files;

    FILE_NAME
    --------------------------------------------------------------------------------
    FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
    ---------- ------------------------------ ---------- ---------- ---------
    RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
    ------------ --- ---------- ---------- ------------ ---------- -----------
    /sid/oradata/temp/temp01.dbf
    1 TEMP 2097152000 256000 AVAILABLE
    1 NO 0 0 0 2096103424 255872

    /sid/oradata/temp/temp02.dbf
    2 TEMP 2097152000 256000 AVAILABLE
    2 NO 0 0 0 2096103424 255872


    Get error:

    ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    Query v$sort_usage...

    SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
    from v$sort_segment;

    TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
    ------------------------------- ------------ ----------- -----------
    TEMP 511744 1024 510720

    SQL> SELECT s.saddr, s.username, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks
    FROM v$session s, v$sort_usage u, v$sqlarea a
    WHERE s.saddr=u.session_addr
    AND s.sql_address = a.address; 2 3 4

    SADDR USERNAME TABLESPACE
    ---------------- ------------------------------ -------------------------------
    CONTENTS EXTENTS BLOCKS
    --------- ---------- ----------
    C00000009C3BA240 FRED TEMP
    TEMPORARY 1 128

    C00000009C3BA240 FRED TEMP
    TEMPORARY 1 128

    C00000009C393310 TEST TEMP
    TEMPORARY 1 128

    C00000009C387F80 TEST TEMP
    TEMPORARY 2 256

    Why under 9.2.0.3, doesn't SMON clean it up unused TEMP segments. God, I can remember I coult increase the PCTINCREASE = 1 and then back to 0 and it'd clear. Can't do that in 9.2

    Any suggestions... cause I'm all Metalink'd out.
    OCP 8i, 9i DBA
    Brisbane Australia

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    A very short answer reply...

    It doesn't when instance is running cause there is only ever one sort segment.

    o. The first disk sort (after instance startup) creates a sort segment in the TEMPORARY tablespace.
    o. Free extents in the sort segment are re-used as required by sessions.
    o. The sort segment grows to a steady-state.
    o. Sort extents are not de-allocated whilst the instance is running.
    o. Permanent objects cannot be created in TEMPORARY tablespaces.
    o. There is a maximum of one sort segment per TEMPORARY tablespace


    Still looking into other things though... hmmm... will update with additional info later.

    God I love talking to myself...
    Last edited by grjohnson; 07-23-2003 at 01:05 PM.
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    You need to get out a lot Greg.
    I know next year I'll be going to Australia, hope Nick has moved by then..heh heh...
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    As you pointed out, SMON does not clean up the sort segment space. The only way to clean it up is take the tablespace off line drop and recreate. I doubt the restart of the instance would clean it, I think not.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by sambavan
    I doubt the restart of the instance would clean it, I think not.

    Sam
    A quote from Oracle Docs.

    Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.
    Or Yet another quote


    The sort segment for an instance is dropped on instance startup and re-created when the first sort is performed. Extents are then allocated as needed.
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by sambavan
    As you pointed out, SMON does not clean up the sort segment space. The only way to clean it up is take the tablespace off line drop and recreate. I doubt the restart of the instance would clean it, I think not.

    Sam
    Hey Sam, I'm yet to shutdown the instance due to testing going on, but I agree, I'm sure I've shutdown instances and started it again and the whole tablespace was already allocated... can't be certain though. Must remember when I restart.

    abhaysk , I'm also wondering if there is a restricition on when blocks can be reused in the TEMP space. I suppose it's understandable that sort segment extents cannot be shared (although, strange that Rollback segments (and even rollback segment blocks) can be be shared by multiple transactions). If an extents is 1024K, is there any point in having a SORT_AREA_SIZE_RETAINED < TEMP 1024K (other than zero)? I don't think there is... sorry that just me talking out loud

    Anyway, aprt from SORT_AREA_SIZE_RETAINED, is there any other restriction of the EXTENT being reused (meaning, time based?)

    Tarry , you're going to Oz? For a holiday or to work? Good on ya mate, I'm sure you'll enjoy it, it's just the looooonnnngggg flight that's a killer... oh, and the heat and the spiders and the snakes and the crocadiles and the.... ha ha ha...
    Last edited by grjohnson; 07-24-2003 at 03:54 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Hi greg,

    By any chance event 10061 is set in init.ora ??

    NOTE:166297.1 on metalink says
    "Temporary Tablespace Extents are not Being Deallocated After Restart of Database bevcause Event 10061 Is Set in init.ora"

    Also Event 10061 prevents SMON from performing cleanup of TEMP segments.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Sanjay, no mate, nothing like that is being set. Thanks.

    I've opened an iTar with some queestions to Oracle. I'll see how that goes and let you know how we get on.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by grjohnson
    If an extents is 1024K, is there any point in having a SORT_AREA_SIZE_RETAINED < TEMP 1024K (other than zero)? I don't think there is... sorry that just me talking out loud
    Quoting myself..how lame. Anyway, reversing my think, of course theres a point to setting the sort_area_size < extent size.

    Sorry, just thought I'd correct myself before others did.
    OCP 8i, 9i DBA
    Brisbane Australia

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Abhay,

    Thanx for pointing out my misleading thought. In a 24x7 operation, shuting down the instance is a very scarse opportunity. On those cases you would want to drop the tablespace and recreate it at a very least used time. Yes it has some loses, but its worth compared to a major inventory lose.

    Curtesy of Metalink:
    Code:
     If TEMPORARY type tablespaces are in use then SMON will       
     clean up its segments after startup following a shutdown. 
     In this case large numbers of extents can be a severe problem as
     SMON will not service user "sort segment requests" until the cleanup
     is complete.If the cleanup is to take a long time users will not 
     be able to perform sort operations. In this scenario you can point 
     users at a PERMANENT temporary tablespace while SMON cleans up the
     TEMPORARY temporary tablespace. This is likely to cause ST enqueue
     contention but will allow users sessions to sort on disk when 
     necessary rather then them just blocking.
    Thanx,
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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