DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: extent size for temp tablespace

  1. #11
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    Pando -
    I am looking at the ILT Notes for the O8: Performance Tuning Workshop course (Part #M08298, dated March 1999), p. 10-27:

    "Select INITIAL and NEXT values as integer multiples of SORT_AREA_SIZE, allowing an extra block for the segment header."

    I believe this is just another example of poorly worded notes. As the above citation notes, the extra block is for the SEGMENT header, of which there is only one. Therefore, I would recommend you disregard references to the extra block.

    Tycho - Pando is ALMOST right about the one temporary segment per tablespace. There is only one temporary segment created within a TEMPORARY tablespace for each instance mounting the database. So if you are in an OPS environment with three instances mounting the same database, it is possible to have up to three temporary segments in a TEMPORARY tablespace. It is also possible to have more than one TEMPORARY tablespace within a database. Consequently, if you look at V$SORT_SEGMENT, you may see more than one temporary segment listed.

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Tycho
    only one segment is created in a temporary tablespace and this is the beauty of why setting a tablespace temporary and not permanent, extents of the segment is used over and over again which consequently rules out the necessity of allocating extents and deallocating extents in a temporary segment which leads to better perfomance in a sort intensive (Datawarehouse?) enviroment.

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    ops sorry didnt read carp´s post :o
    I havent worked with OPS so... heh :D

  4. #14
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    Okay

    Thank you Pando and Carp,

    I learned something new here.

    BTW I finished my OCP 60 minutes ago passing with 55/62 for Networking.

    Tycho

  5. #15
    Hi!
    Just 2 cents :)
    -A temporary segment has 1 block header
    -the next extents hasn't

    Supose a block_size=1K
    If you define initial 64k and next 64k then the first two extents of this segment will be 65k and 64k, so , you will have 2 sizes of extents in the same tbs, that's why the fragmentation.
    The best way is
    initial 64K next 65K

    Hope I'm clear :D
    Ramon Caballero, DBA, rcaballe@yahoo.com

  6. #16
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I just test that rcaballe, I do see the extents uniform not as you just suggested though, my initial is 64K and next is 64K and the rest are all 64K, i.e uniform size extents. May be it´s because I used minimum extent 64K clause?

  7. #17
    Where do you check the extents?
    In dba_extents or dba_segments, I think is different.
    If you say initial 1M in dba_segments will say 1M, but in dba_extents?
    Ramon Caballero, DBA, rcaballe@yahoo.com

  8. #18
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    tested with tablespace manager

  9. #19
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    rcalle -
    You have it backwards.
    The first block of the first extent is allocated as the segment header. Consequently, if you have the 1K block size and specify
    INITIAL 200K NEXT 200K PCTINCREASE 0
    all of your extents will be 200 blocks long. However, your first extent will only have 199 blocks available for data storage.

  10. #20
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that´s what i though too.... otherwise all initial of any type of segment would require an extra block...

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