Database Reference
In-Depth Information
This sounds all right at first, but in a data warehouse environment, this can lead to wastage after a large load.
Let's say you want to load 1,010MB of data (about 1GB), and you are using a tablespace with 100MB extents. You
decide to use ten parallel execution servers to load this data. Each would start by allocating its own 100MB extent
(there will be ten of them in all) and filling it up. Since each has 101MB of data to load, they would fill up their first
extent and then proceed to allocate another 100MB extent, of which they would use 1MB. You now have 20 extents
(10 of which are full, and 10 of which have 1MB each) and the remaining 990MB is “allocated but not used.” This
space could be used the next time you load more data, but right now you have 990MB of dead space. This is where
extent trimming comes in. Oracle will attempt to take the last extent of each parallel execution server and trim it
back to the smallest size possible.
Extent Trimming and Dictionary-Managed Tablespaces
If you are using legacy dictionary-managed tablespaces, then Oracle will be able to convert each of the 100MB extents
that contain just 1MB of data into 1MB extents. Unfortunately, that would (in dictionary-managed tablespaces) tend
to leave ten noncontiguous 99MB extents free, and since your allocation scheme was for 100MB extents, this 990MB of
space would not be very useful! The next allocation of 100MB would likely
not
be able to use the existing space, since
it would be 99MB of free space, followed by 1MB of allocated space, followed by 99MB of free space, and so on. We will
not review the dictionary-managed approach further in this topic.
Extent Trimming and Locally-Managed Tablespaces
Enter locally-managed tablespaces. There are two types:
UNIFORM SIZE
, whereby every extent in the tablespace is
always precisely the same size, and
AUTOALLOCATE
, whereby Oracle decides how big each extent should be using
an internal algorithm. Both of these approaches nicely solve the
99MB of free space/followed by 1MB of used space/
followed by 99MB of free space
problem. However, they each solve it very differently. The
UNIFORM SIZE
approach
obviates extent trimming from consideration all together. When you use
UNIFORM SIZE
s, Oracle cannot perform
extent trimming. All extents are of that single size—none can be smaller (or larger) than that single size.
AUTOALLOCATE
extents, on the other hand, do support extent trimming, but in an intelligent fashion. They use a few specific sizes
of extents and have the ability to use space of different sizes—that is, the algorithm permits the use of all free space
over time in the tablespace. Unlike the dictionary-managed tablespace, where if you request a 100MB extent, Oracle
will fail the request if it can find only 99MB free extents (so close, yet so far), a locally-managed tablespace with
AUTOALLOCATE
extents can be more flexible. It may reduce the size of the request it was making in order to attempt to
use all of the free space.
Let's now look at the differences between the two locally-managed tablespace approaches. To do that, we need
a real-life example to work with. We'll set up an external table capable of being used in a parallel direct path load
situation, which is something that we do frequently. Even if you are still using SQL*Loader to parallel direct path
load data, this section applies entirely—you just have manual scripting to do to actually load the data. So, in order to
investigate extent trimming, we need to set up our example load and then perform the loads under varying conditions
and examine the results.
Setting Up for Locally-Managed Tablespaces
To get started, we need an external table. I've found time and time again that I have a legacy control file from
SQL*Loader that I used to use to load data, one that looks like this, for example:
LOAD DATA
INFILE '/tmp/big_table.dat'
INTO TABLE big_table
REPLACE
FIELDS TERMINATED BY '|'
