DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: temp tablespace initial/next

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends,

    I had an Oracle Conultant in my company yesterday and the subject of Temp tablespace cropped up.

    I have my initial extent size set to a multiple of sort area size + 1 block.

    I have my next extent size set to a multiple of sort area size.

    e.g.

    initial : 794624
    next : 786432

    The consultant told me that initial and next should be the same in Temp tablespace.

    He told me that every time a session required a sort that went to disk, the extent size added to the sort segment would take initial_extent first 794624 and then next extent, 786432.
    and this would happen for every sort that went to disk for every session that required a sort, thus heavily fragmenting temp tablespace.

    I always thought that the initial extent size is at the segment level. So an initial extent of 794624 would be created when sort segment is created. and all subsequent extents would be 786432, regardless of whether a different session is requiring a sort or not.

    Someone out there please enlighten me.




    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    I think I have answered my own question.

    According to this query I am right.

    1 select bytes, count(bytes)
    2 from dba_extents
    3 where tablespace_name = 'TEMP'
    4* group by bytes
    SQL> /

    BYTES COUNT(BYTES)
    ---------- ------------
    786432 55


    Database has been up for 2 days, more than 1 session has requested a sort. Maybe I should email him with this.


    [Edited by Sureshy on 06-26-2002 at 06:26 AM]
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Firstly, Create Temporary Tablespace ... Extent Management Local will always be better than Create Tablespace ... Temporary.

    Secondly, setting of Initial = Next and Pctincrease =0 for datafile of temporary tablespace is always a good way to eliminate fragmentation. However, I think a sort segment will seldom deallocate extents, so it will rarely be fragmental even though you set PctIncrease != 0.

    Please correct me if I am wrong.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #4
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116

    Wink

    i 100% agree with Calvin_Qiu

    OCP too

  5. #5
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    I was not asking about how to set up a temporary tablespace or LMT or the advantages of LMT, I am already aware of that!

    I was asking about Dictionary tablespaces and the discussion I had with an Oracle consultant. I was asking whether or not he was correct in his statement, to establish whether or not my company should hire him as a consultant for advice when I am not available.

    I'll repeat what he said, all I want is for someone to agree or disagree with this statement.
    He said :
    Every session that adds extents to the temporary sort segment always adds the value of initial_extent to the chain of current extents and all subsequent extents will be sized at the value of next_extent FOR THAT SESSION. If this is so you would have a sort segment with different extent lengths all the way through it.

    I was saying that initial extent is at the segment level and not the session level, regardless of whether it is a temp tablespace or not! There is only one extent at the value of initial_extent and that is the first one. All subsequent extent sizes are set at the value of next_extent no matter which session causes its creation in temp tablespace.

    If you read Oracle documentation regarding DMT TEMP tablespaces, it states that the extent size of the sort segment should be a multiple of sort_area_size + 1 block. Hence, my initial_extent is a multiple of sort_area_size + 1 block and next_extent is a mulitple of sort_area_size alone.

    P.S. The consultant charges £1200 a day. I want to make sure he knows his stuff.






    [Edited by Sureshy on 06-26-2002 at 10:33 AM]
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I thought it was on an instance basis, not a session basis. However, there seems to be some ambiguity in the documentation. (http://otn.oracle.com/docs/products/...block.htm#2922)


    A single sort allocates its own temporary segment, in the temporary tablespace of the user issuing the statement, and then returns the extents to the tablespace.

    Multiple sorts, however, can use sort segments in a temporary tablespace designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort but remain available for other multiple sorts.


    That being said, I think your idea is closer than his.
    Jeff Hunter

  7. #7
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    agree with marist89. I wanna know what's "single sort"?
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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