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

Thread: EXTENTS

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I couldn't agree more with what pando (and some not_enough_well_known_whitepapers) say about fragmentation and extent sizing.

    This fragmentation question is an old and ever-lasting topic among DBA comunity, yet still so many DBAs spend so much energy and time (and money) in defragmenting their tablespaces and belivs in mith that "fewer extents is better for performance, the above limit for number of extents should be 5/10/20....".

    I also like the way how marist89 catches his developers when they sneak some segments into production database. I've heard even better and nasties one. Suppose your largest datafile in a tablespace is 500M. Set the default tablespace storage parameters so that INITIAL and NEXT are larger than this, say 600M. That way developer will not be able to create segment in a tablespace unless he set the storage parameters at segment level which forces him to think about this stuff and maybe even let his DBA to explain how storage parameters work and why are they used ;-)).


    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    '...initial 128K extent size will be multiples of data_block_size * 5 ...'

    Assume db_block_size is 8K

    when we set initial 128K without using minimum extent clause even we have set size to 128K, if you check the actual size it will say 160K, the reason is because by default Oracle allocates data_block_size * 5 which would be 40K in this case but since we requested an initial of 128 it will allocate multiple of 40K round up which would be 160K

    As for number of extents issue I dont think you yield same perfomance with a table of 10 extents with another same table of 1000 extents since the data in 1000 extents would be so dispersed physically. I think this is why cluster yields better perfomance in some cases no? Because the data are physically stored together.
    However I do think that as long as extent size are uniform I wouldnt worry too much about number of extents. Priority one would be to achieve uniform extent sizes.

  3. #13
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I do agree with Pando. As for as Production databases, I would prefer to take off table level stroage clause, which defines the objects @ tablespace storage clause and go with
    small(ext size 128K),Medium(ext size 4M) and large(ext size 128M) tablespaces sa suggested in that tech white paper. Pesonally this is how I will set up our databases.


  4. #14
    Join Date
    Dec 2000
    Posts
    43

    Cool

    I enjoyed the document about SAFE (Simple Algorithm for Frag Elimination)... mentioned earlier in the thread.

    I think that I will try to incorporate those rules in my shop as well (if possible).

    Great document..

    Does everyone agree that 128k should be (in theory) the smallest size extent used?


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