See my book "Effective Oracle by Design", I spent a large section on this showing
a) how it works
b) how it typically sizes the extents
c) how you'll end up with about 300 or so extents for a 10 gig segment (very reasonable)
Here is the excerpt actually
<quote>
(2)Use System-Managed LMTs When You Do Not Know How Big Your Objects Will Become
With a system-managed LMT, the system figures out exactly what the extent sizes will be for a table. Oracle will use an internal, undocumented algorithm to allocate space for every object in the tablespace. With this method, the first couple of extents will be small (64KB), and then the extents will get larger and larger over time. They will all be multiples of each other. That fact precludes free space fragmentation in these tablespaces, because every chunk of free space is potentially usable by any other object. This is in contrast to a DMT, where an object will request arbitrary extent sizes, and if there is not a contiguous free extent large enough to satisfy the request, the request will fail. It is true that in a system managed LMT, since there is more than one extent size, a request for space may fail even if there is existing free space. It is however many times less likely - due mostly to the fact that there is a very limited number of extent sizes. It would be rare to have free space that is not usable in a System managed LMT and in general, the space will be very small.
In the beginning, I was a little cautious about using this type of tablespace. It just didn't feel right. I was always taught that we should size our objects, watch their space usage like a hawk-micromanage the tablespace. Maybe I'm getting lazy, but I would prefer not to work that way if I can avoid it. With system-managed LMTs. I'm not kept awake at night wondering if a PCTINCREASE is going to go crazy on me or if my tablespace will be fragmented like Swiss cheese, with a lot of different-sized holes in it. Objects grow in a sensible fashion, without a lot of watching or handholding.
To see how space might be allocated in such a tablespace, let's use BIG_TABLE again (as noted in the appendix on "setting up", this is a table created from ALL_OBJECTS and duplicated over and over to have quite a few rows). For this demonstration, I made a copy of this table in an auto-allocate LMT.
big_table@ORA920> create tablespace SYSTEM_MANAGED
2 extent management local;
Tablespace created.
big_table@ORA920> create table big_table_copy
2 tablespace SYSTEM_MANAGED
3 as
4 select * from big_table;
Table created.
big_table@ORA920> select tablespace_name, extent_id, bytes/1024, blocks
2 from user_extents
3 where segment_name = ' BIG_TABLE_COPY'
4 /
TABLESPACE_NAME EXTENT_ID BYTES/1024 BLOCKS
------------------------------ ---------- ---------- ----------
SYSTEM_MANAGED 0 64 8
SYSTEM_MANAGED 1 64 8
SYSTEM_MANAGED 14 64 8
SYSTEM_MANAGED 15 64 8
SYSTEM_MANAGED 16 1024 128
SYSTEM_MANAGED 17 1024 128
SYSTEM_MANAGED 77 1024 128
SYSTEM_MANAGED 78 1024 128
SYSTEM_MANAGED 79 8192 1024
SYSTEM_MANAGED 80 8192 1024
SYSTEM_MANAGED 91 8192 1024
SYSTEM_MANAGED 92 8192 1024
93 rows selected.
As you can see in this example, the first 16 extents each are 64KB (but don't be surprised if you see something slightly different). The next 63 are each 1MB, and the remaining 14 are 8MB. As the object grew, the extents grew as well. That is a total of about 180MB of space in 93 extents, which is perfectly acceptable. For those who believe you must have your objects in one extent, I hope that this Oracle-supported algorithm, which promotes and encourages multiple extents, puts that notion to rest.
In this example, when we quadrupled the size of the BIG_TABLE_COPY to about 650MB, Oracle added another 64 extents, each 8MB, for a total of 512MB more. That table was just taking care of its space needs.
When you do not know how big your objects will become, system-managed LMTs, are the way to go. This type of space management is most useful when you are creating objects for an application, and depending on how the people using the application configure it, some tables might be empty, some might be 100MB, and some might be 2GB. On another installation, the tables that were 2GB are empty, the tables that were empty are 100MB, and the tables that were 100MB tables are 2GB. In other words, you have no idea how big these tables are going to be in the real world. Here, having each object start at 64KB and stay that way for the first couple of extents lets the tables that are nearly empty stay very small. The tables that are going to get large will get large fast.
If you are using Oracle9i Release 2 with an LMT, you'll find that it uses system-managed extent sizing for the SYSTEM tablespace. For the SYSTEM tablespace, this extent-sizing strategy is the best thing ever. It will prevent dictionary tables from growing exponentially (as PCTINCREASE would have them doing after a while; even a small PCTINCREASE would cause a table to grow by huge amounts after a short period of time) and keep the number of extents at a reasonable maximum. Consider that if you install Oracle and use a ton of PL/SQL, your SYS.SOURCE$ table and the tables that hold the compiled PL/SQL will be huge. On the other hand, your friend may install Oracle and not write any lines of PL/SQL code. With the system-managed approach, Oracle will let the database figure out how big the extents should be.
System-managed LMTs work well, as long as the objects are destined to be 10GB or less. At 10GB, you would be using about 300 extents for the object in this type of tablespace, which is fine. Segments that exceed 10GB are fairly rare. If you are using partitioning, the individual partitions are the segments; 10GB partitions or smaller would be a good size. For tables and indexes that are getting into the 10GB size range, consider partitioning them into smaller, more manageable segments. For segments larger than 10GB, or for those that you prefer to size yourself for some reason, consider using LMTs with uniform extent sizes.
(2)Use Uniform Extent Sizes When You Know the Ultimate Size of an Object
The other type of LMT is one that supports uniformly sized extents. Using this strategy, each and every extent in the tablespace will be exactly the same size as every other extent in that tablespace. To see how this works, let's use the same example we did in the previous section with BIG_TABLE, but with a 5MB uniform extent size.
big_table@ORA920> create tablespace uniform_size
2 extent management local
3 uniform size 5m
4 /
Tablespace created.
big_table@ORA920> create table big_table_copy
2 tablespace uniform_size
3 as
4 select * from big_table
5 /
Table created.
big_table@ORA920> select tablespace_name, extent_id, bytes/1024, blocks
2 from user_extents
3 where segment_name = ' BIG_TABLE_COPY'
4 /
TABLESPACE_NAME EXTENT_ID BYTES/1024 BLOCKS
------------------------------ ---------- ---------- ----------
UNIFORM_SIZE 0 5120 640
UNIFORM_SIZE 1 5120 640
UNIFORM_SIZE 34 5120 640
35 rows selected.
As expected, every extent that was and will ever be allocated in that tablespace will be 5MB-not a byte more, not a byte less.
So, when is this type of tablespace useful? It works well when you know the ultimate size of the object. For example, when you are loading a data warehouse, you have a good idea of what size the objects will be. You know that table will have fifty 5GB partitions.
The trick here is to pick an extent size that will hold your object with the least amount of waste. If this segment is 5GB + 1 byte, it will need to extend one more time for that last byte, effectively wasting .499999GB of storage. In a large data warehouse, that is a drop in the bucket, but every bit (or byte) counts. You might be willing to set a target of 500 extents, which allows you to use a 10MB extent and waste at most 10MB of space.
Another factor to consider when deciding on an extent size is the desired monitoring granularity. An extent size that implies a new extent each month makes abnormal growth easy to spot. If you were to size the extents so that they grow by dozens every day, this spot check would be more difficult.
(2)Some LMT Caveats
Here, I will point out some caveats I've discovered along the way using LMTs. None of these issues are earth-shattering; none would make me stop using LMTs. I just wanted to make sure you are aware of these points.
(3)The Magic Number for Uniformly Sized Extents Is 64KB
There was a common myth when LMTs first came out that an LMT with a uniform extent size would waste its first extent for the bitmap used to manage the file; that is, if you used a 1MB uniform extent size, the first 1MB of the tablespace would be taken over by Oracle. People thought, "Oh my gosh, if I use a 100MB uniform size, I'll waste 100MB of data!" Well, there is overhead, but it is only 64KB, not the size of the extent.
The problem was that most people were creating their datafiles as an integral multiple of the uniform extent size. For example, if they used a 5MB extent size, they would use 100MB as a datafile size (20 5 = 100). Oracle would look at this 100MB that was just given to it, take away 64KB for itself, divide what was left by 5MB, and truncate the result (no partial extents here). Thus, there were only 19 extents available in this tablespace right after creation! Here is a simple example showing the issue and then how to fix it. We'll start by creating a tablespace with uniform extent sizes of 5MB and a datafile of 100MB.
ops$tkyte@ORA920> create tablespace five_meg
2 datafile size 100m
3 uniform size 5m
4 /
Tablespace created.
Now, we will query how much free space is available in this newly created tablespace.
ops$tkyte@ORA920> select sum(bytes/1024/1024) free_space
2 from dba_free_space
3 where tablespace_name = ' FIVE_MEG'
4 /
FREE_SPACE
----------
95
So, it would appear that 5MB of overhead was taken, but this is not really the case. Let's increase that file by a measly 64KB.
ops$tkyte@ORA920> column file_name new_val f
ops$tkyte@ORA920> select file_name from dba_data_files
2 where tablespace_name = 'FIVE_MEG';
FILE_NAME
------------------------------
/usr/oracle/ora920/OraHome1/or
adata/ora920/o1_mf_five_meg_zc
54bj5l_.dbf
ops$tkyte@ORA920> alter database
2 datafile '&f' resize 102464k;
old 2: datafile '&f' resize 102464k
new 2: datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/o1_mf_five_meg_zc54bj5l_.dbf'
resize 102464k
Database altered.
ops$tkyte@ORA920> select sum(bytes/1024/1024) free_space
2 from dba_free_space
3 where tablespace_name = 'FIVE_MEG';
FREE_SPACE
----------
100
And there you go. We have all of our space. I'm not sure how many DBAs this feature scared off, but I hope they will come around after seeing this!
Note that this 64KB rule need not apply to system-managed extents! If you ran the above test without the uniform size 5m clause, you would find that there is initially 99.9375MB of available free space, and all but 64KB of it would be usable.
(3)System-Managed LMT Allocates from Files Differently
Ask Tom
"How can you achieve 'poor-man's striping' of a table. That is, without using striping software at the operating-system level and without using partitioning, how would you stripe a table across multiple devices?"
To accomplish this, create a tablespace with many datafiles. Use an LMT, of course. Use a uniform extent size that will cause the table to go into 100 or so extents. Create the table in that tablespace. Allocate extents for it in a round-robin fashion, spreading the data out.
I had tested this in the past using a DMT, and Oracle always tended to go round-robin when allocating space. I had also tested this with an LMT using uniform extent sizes, and it worked the same. However, when the user tried to apply this technique, he used system-managed extents! The results were quite different than what I had experienced. But, after further testing, it seems that the round-robin technique will still kick in when the object gets large enough to mandate striping.
End Ask Tom
To see how the different types of LMT allocate from files, we will consider a small example. We will start by creating two tablespaces, each with four datafiles that are 64KB larger than 2MB (the extra 64KB you need for the bitmap). One will use 64KB uniform extents; the other will be system-managed.
ops$tkyte@ORA920> create tablespace uniform_extents
2 datafile size 2112k, size 2112k, size 2112k, size 2112k
3 uniform size 64k
4 /
Tablespace created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create tablespace system_managed
2 datafile size 2112k, size 2112k, size 2112k, size 2112k
3 /
Tablespace created.
Next, we'll create a table in each tablespace.
ops$tkyte@ORA920> create table uniform_size ( x int, y char(2000) )
2 tablespace uniform_extents;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table system_size ( x int, y char(2000) )
2 tablespace system_managed;
Table created.
Now, we'll fill each table to capacity using a loop. Note that the following is an example to illustrate a particular point: how the LMTs will allocate space from multiple files. The goal is to cause the table to extend and grow to fill all available space. In order to accomplish that, we use simple row-at-a-time inserts, followed by a commit. Real production code would use bulk inserts and commit only after all of the inserts were performed, of course. Unfortunately, in order to demonstrate a point, we need to use two extremely bad practices.
ops$tkyte@ORA920> begin
2 loop
3 insert into uniform_size values( 1, 'x' );
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table OPS$TKYTE.UNIFORM_SIZE
by 8 in tablespace UNIFORM_EXTENTS
ORA-06512: at line 3
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
2 loop
3 insert into system_size values( 1, 'x' );
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table OPS$TKYTE.SYSTEM_SIZE
by 128 in tablespace SYSTEM_MANAGED
ORA-06512: at line
Now, when we inspect how the extents were allocated, we'll see something very different in the algorithm:
ops$tkyte@ORA920> select segment_name, extent_id, blocks, file_id
2 from dba_extents
3 where segment_name in ( 'UNIFORM_SIZE' , 'SYSTEM_SIZE' )
4 and owner = user
5 order by segment_name, extent_id
6 /
SEGMENT_NAME EXTENT_ID BLOCKS FILE_ID
------------------------------ ---------- ---------- ----------
SYSTEM_SIZE 0 8 22
SYSTEM_SIZE 1 8 22
SYSTEM_SIZE 2 8 22
note extent_id 3..14 were all in FILE_ID 22
SYSTEM_SIZE 15 8 22
SYSTEM_SIZE 16 128 23
SYSTEM_SIZE 17 128 24
SYSTEM_SIZE 18 128 21
SYSTEM_SIZE 19 128 22
SYSTEM_SIZE 20 128 23
SYSTEM_SIZE 21 128 24
SYSTEM_SIZE 22 128 21
The output shows the first 16 extents for the system-allocated tablespace all came from the same file (FILE_ID 22 in this case). Oracle did this in order to keep all of the small extents together, to avoid allocating a lot of small extents in many different files in an attempt to reduce any potential fragmentation that might occur if we dropped an object from this tablespace. Only when the extents jumped from 8 blocks (64KB) to 128 blocks (1MB) did the round-robin algorithm come into play. Once we hit 1MB extents, the allocation went to file 23, 24, 21, 22, 23, and so on.
However, with the uniformly sized extents, fragmentation is not a concern, so this method uses round-robin allocation from the start.
UNIFORM_SIZE 0 8 18
UNIFORM_SIZE 1 8 19
UNIFORM_SIZE 2 8 20
UNIFORM_SIZE 3 8 17
UNIFORM_SIZE 4 8 18
UNIFORM_SIZE 5 8 19
UNIFORM_SIZE 6 8 20
UNIFORM_SIZE 7 8 17
UNIFORM_SIZE 8 8 18
UNIFORM_SIZE 122 8 20
UNIFORM_SIZE 123 8 17
UNIFORM_SIZE 124 8 18
UNIFORM_SIZE 125 8 19
UNIFORM_SIZE 126 8 20
UNIFORM_SIZE 127 8 17
151 rows selected.
Most of the time, this difference in allocation doesn't really matter. The round-robin technique will kick in when the object gets large enough to mandate striping. I point it out mostly to show that the round-robin allocation will happen, but a simple test might not make that clear to you. If you allocate fewer than 16 extents in a test, you might be under the impression that it will never round-robin. (In fact, I was under that impression myself; it was only when making a larger test that I discovered the truth.)
</quote>