FW: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Mar 2011 13:30:29 -0500
Much earlier today, now snipped to fit to list.
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Friday, March 04, 2011 7:40 AM
To: 'deshpande.subodh@xxxxxxxxx'; 'mdinh@xxxxxxxxx'
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Subodh Deshpande
Sent: Friday, March 04, 2011 12:10 AM
To: mdinh@xxxxxxxxx
Cc: Oracle Mailinglist
Subject: Re: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g
UNIFORM if record size is not going to change..
<snip>
Other related posts:
- » FW: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g - Mark W. Farnham
