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

Thread: extent size for temp tablespace

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi ppl

    There is a formula in Oracle ILT books which shows how to set extent size for temporary tablespaces

    initial=next=(multiple of sort area size)+db_block_size

    anyone know who the extent size must add size of a block?

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Well this is the official reason (taken from DBA 8 exam questions) - to reduce the possibility of fragmentation. As I've been a DBA for all of 1 1/2 days if anyone can explain why I'd be fairly interested!!! :-)

    Oracle8: Database Administration - Managing Temporary Segments
    Oracle8 Database Administration: Manage Storage Structures TBT - Unit 3, Lesson 1, Topic: 3
    Oracle8 DBA Handbook - Physical Database Layouts - 110-111

    Temporary segments are based on the default storage parameters of the tablespace.
    For the TEMPORARY tablespace, INITIAL and NEXT should be equal to each other and a multiple of
    SORT_AREA_SIZE plus DB_BLOCK_SIZE to reduce the possibility of fragmentation.
    PCTINCREASE should always be equal to zero.


  3. #3
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    extent sizing

    hi Pando,

    Because the sort will be written from memory to disc the size has to be a multiple of the sort area size.

    Because the sort segment will need a header block adding one db block makes up for the extra little space needed.

    tycho


  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    One extra block is required for all data files.
    While sizing the data file, always add one extra block.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yes I nderstand that it has to be multiple of sort area size, my question is about the extra block. While as sizing a datafile I understand an extra block is added to avoid useless space at end of the datafile.
    However, the ILT suggests

    INITIAL EXTENT SHOULD BE EQUAL TO NEXT EXTENT
    and the sieze of these extents should be

    MULTIPLE OF SORT AREA SIZE + DATA_BLOCK_SIZE

    it´s suggesting the size of extent has to be multiple plus a block and not the size of the datafile! And this is the part i dont get it

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    the temporary tablespace allocations are overflows of what happens in the sort_area_size, which is why you want the sort_area_size multiple. the extra block you mentioned is for the header block for the temporary segment. at least accoriding to what i've read :)

    [Edited by pwoneill on 11-14-2000 at 11:04 AM]

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    errrrr pwoneill you dont get my point , I know for the segment you need block header but in a temporary tablespace only one segment is created which means you need a block header for the whole segment and not one block for each extent which is the formula suggesting!

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    I see your point now. I have a diagram in front of me (oracle 24x7 page 585 for those who have the book), that seems to imply that each extent has it's own header block, which would justify the formula. Later in the book it mentions the formula you gave, and the justification that I used (page 611).

    However other things I'm looking at ( [url]http://oradoc.photo.net/ora81/DOC/server.815/a67781/c02block.htm[/url] ) say headers are segment level, which would imply the forumla is incorrect and allocating extents larger then they should be.

    Can anyone clear this up?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if every extent need an extra block then all extents in any tablespace would have to use that formula too! Which they dont otherwise the clause minimum extent would be totally useless!

  10. #10
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Extent sizing

    Hi

    Are you sure Pando????
    >in a temporary tablespace only one segment is created.

    Whats in the v$sort_segment view then?

    Naturaly the way space is used in the temporary tablespace is much different the way it is used in other tablespaces.
    So I think you are mistaken here.
    >well if every extent need an extra block then all extents in any tablespace would have to use that formula too!

    Tycho

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