FW: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Friday, March 04, 2011 7:40 AM
To: 'deshpande.subodh_at_gmail.com'; 'mdinh_at_xifin.com' Cc: 'Oracle Mailinglist'
Subject: RE: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g  

Please explain a little more of what you mean. I don't think I understand what a record size would have to do with extent size allocation pattern. If you mean dynamic changes in the sum of the vsize of columns of individual rows, I would think percent free and present used settings would be relevant. If you're talking about adding and removing columns, likewise.  

So I'm guessing you mean something different by record size and I can't figure out the relationship to extent size allocation pattern.  

As storage unit costs dropped it became more important for Oracle to minimize the expense managing extent sizes and administrative efforts than to avoid wasting a little bit of acreage on disks here and there. You could fine tune things with "dictionary managed" tablespaces, but you could also create the equivalent of spaghetti code in the sizes and pieces left behind from dropped segments (which you may or may not be able to glue back together depending on whether extents of non-dropped segments), the scalability was gated by the concurrency achievable in the dictionary, and you could get a big surprise if some object grew at a much greater rate than you expected.  

Locally managed tablespaces fixed a lot of that. The choices of management pattern are quite limited, but extent allocation is more scalable and the variability of size of freed extents is reduced. In the case of uniform, freed extents are exactly one size, so they can always be used. With autoallocate Oracle chooses from a limited number of extent sizes with an algorithm designed to keep small things small but ramp up the next extent size used as the number of extents required grows. Read the details in something like the storage clause section of the Oracle Database SQL Language Reference, page 8-51 of the one I'm looking at right now, which is 11g Release 2 (11.2) E10592-04.  

Now as for strategy (and I'm limiting this to locally managed tablespaces as you need a really specific reason to use dictionary managed tablespaces any more, in which case you probably don't need me telling you what to do):  

If you have a bunch of probably medium small objects to store and you really don't know what size they will be, an autoallocate tablespace is your sweetheart. Keep an eye on it and if something starts getting big, then consider the tradeoff between the effort to move it to a uniform tablespace versus the annoyance of it possibly eating up and causing file size growth in the place where you intended to keep medium small stuff.  

If you have a bunch of lookup tables that you know are and will remain very small, it is possibly useful to store them in a tablespace with a small uniform extent size. This tends to be more true than false if you've got a bit of SSD in your system and conserving space on it is needed. Having a tablespace with a small uniform extent size tends to conserve size allocated on the media for small objects. If something starts to get dynamic allocation at a fast rate you can arrange to move it. Small objects can also be handled relatively waste free with autoallocate so this is somewhat a matter of choice. If the count of objects you have that each be stored in a tiny fraction of, say, 1M, is small, then a lot of folks don't think this is worth the trouble of having the extra tablespace. If you're conserving SSD space it tends to be more important, but if you have a lot of databases and database activities to manage with a small number of DBAs it may be more effort than it is worth.  

For anything bigger than medium small, you build one or more tablespaces at the appropriate uniform size, and probably some multiple of 1M or near there that matches up well with your physical disk farm. (Read up on ASM if you're using ASM, and massage what I've written here as needed.)  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Subodh Deshpande
Sent: Friday, March 04, 2011 12:10 AM
To: mdinh_at_xifin.com
Cc: Oracle Mailinglist
Subject: Re: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g    

UNIFORM if record size is not going to change..

<snip>

--
http://www.freelists.org/webpage/oracle-l