Quote from a forthcoming book (yeah, mine ;) Bear in mind, this was tested on 9i and 10g only -- but the logic hasn't changed to my knowledge since 8i
<quote>
Extent Trimming and Locally-Managed Tablespaces
Enter locally-managed tablespaces. Here we have 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 solve nicely the problem of the 99MB free space, followed by 1MB used space, followed by 99MB free space, and so on, resulting in lots of free space that cannot be used. However, they each solve it very differently. The UNIFORM SIZE approach obviates extent trimming from consideration all together. When you use UNIFORM SIZEs, Oracle cannot perform extent trimming. All extents are of that single sizenone 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 sizesthat 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.
Lets now look at the differences between the two locally-managed tablespace approaches. To do that, we need a real-life example to work with. Well 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 entirelyyou 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
To get started, we need an external table. Ive 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 '|'
(
id ,owner ,object_name ,subobject_name ,object_id
,data_object_id ,object_type ,created ,last_ddl_time
,timestamp ,status ,temporary ,generated ,secondary
)
We can convert this easily into an external table definition using SQL*Loader itself:
$ sqlldr big_table/big_table big_table.ctl external_table=generate_only
SQL*Loader: Release 10.1.0.3.0 - Production on Mon Jul 11 14:16:20 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Notice the parameter EXTERNAL_TABLE passed to SQL*Loader. It causes SQL*Loader in this case to not load data, but rather to generate a CREATE TABLE statement for us in the log file. This CREATE TABLE statement looked as follows (this is an abridged form; Ive edited out repetitive elements to make the example smaller):
CREATE TABLE "SYS_SQLLDR_X_EXT_BIG_TABLE"
(
"ID" NUMBER,
...
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'big_table.bad'
LOGFILE 'big_table.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY "|",
....
"SECONDARY" CHAR(255)
TERMINATED BY "|"
)
)
location
(
'big_table.dat'
)
)REJECT LIMIT UNLIMITED
All we need to do is edit that a bit to name the external table the way we want; change the directories, perhaps; and so on:
ops$tkyte@ORA10GR1> create or replace directory my_dir as '/tmp/'
2 /
Directory created.
And after that, all we need to do is actually create the table:
ops$tkyte@ORA10GR1> CREATE TABLE "BIG_TABLE_ET"
2 (
3 "ID" NUMBER,
16 "SECONDARY" VARCHAR2(1)
17 )
18 ORGANIZATION external
19 (
20 TYPE oracle_loader
21 DEFAULT DIRECTORY MY_DIR
22 ACCESS PARAMETERS
23 (
24 RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
25 READSIZE 1048576
26 FIELDS TERMINATED BY "|" LDRTRIM
27 REJECT ROWS WITH ALL NULL FIELDS
28 )
29 location
30 (
31 'big_table.dat'
32 )
33 )REJECT LIMIT UNLIMITED
34 /
Table created.
Then we make this table parallel enabled. This is the magic stepthis is what will facilitate an easy parallel direct path load:
ops$tkyte@ORA10GR1> alter table big_table_et PARALLEL;
Table altered.
Note The PARALLEL clause may also be used on the CREATE TABLE statement itself. Right after the REJECT LIMIT UNLIMITED, the keyword PARALLEL could have been added. I used the ALTER statement just to draw attention to the fact that the external table is, in fact, parallel enabled.
Extent Trimming with UNIFORM vs. AUTOALLOCATE Locally-Managed Tablespaces
Thats all we need to do with regard to setting up the load component. Now, we would like to investigate how space is managed in a locally-managed tablespace (LMT) that uses UNIFORM extent sizes, compared to how space is managed in an LMT that AUTOALLOCATEs extents. In this case, well use 100MB extents. First we create LMT_UNIFORM, which uses uniform extent sizes :
ops$tkyte@ORA10GR1> create tablespace lmt_uniform
2 datafile '/u03/ora10gr1/lmt_uniform.dbf' size 1048640K reuse
3 autoextend on next 100m
4 extent management local
5 uniform size 100m;
Tablespace created.
And next we create LMT_AUTO, which uses AUTOALLOCATE to determine extent sizes:
ops$tkyte@ORA10GR1> create tablespace lmt_auto
2 datafile '/u03/ora10gr1/lmt_auto.dbf' size 1048640K reuse
3 autoextend on next 100m
4 extent management local
5 autoallocate;
Tablespace created.
Each tablespace started with a 1GB data file (plus 64KB used by locally-managed tablespaces to manage the storage; it would be 128KB extra instead of 64KB if we were to use a 32KB blocksize). We permit these data files to autoextend 100MB at a time. We are going to load this file:
$ ls -lag big_table.dat
-rw-rw-r-- 1 tkyte 1067107251 Jul 11 13:46 big_table.dat
which is a 10,000,000-record file. It was created using the big_table.sql script found in the Setting Up section at the beginning of this book and then unloaded using the flat.sql script available on http://asktom.oracle.com/~tkyte/flat/index.html Next, we do a parallel direct path load of this file into each tablespace:
ops$tkyte@ORA10GR1> create table uniform_test
2 parallel
3 tablespace lmt_uniform
4 as
5 select * from big_table_et;
Table created.
ops$tkyte@ORA10GR1> create table autoallocate_test
2 parallel
3 tablespace lmt_auto
4 as
5 select * from big_table_et;
Table created.
On my system, which has four CPUs, these CREATE TABLE statements executed with eight parallel execution servers and one coordinator. I verified that was the case by querying one of the dynamic performance views related to parallel execution, V$PX_SESSION, while these statements were running:
sys@ORA10GR1> select sid, serial#, qcsid, qcserial#, degree
2 from v$px_session;
SID SERIAL# QCSID QCSERIAL# DEGREE
---------- ---------- ---------- ---------- ----------
137 17 154 998 8
139 13 154 998 8
141 17 154 998 8
150 945 154 998 8
161 836 154 998 8
138 8 154 998 8
147 15 154 998 8
143 41 154 998 8
154 998 154
9 rows selected.
Note In creating the UNIFORM_TEST and AUTOALLOCATE_TEST tables, we simply specified parallel on each table, with Oracle choosing the degree of parallelism. In this case, I was the sole user of the machine (all resources available) and Oracle defaulted it to 8 based on the number of CPUs (four) and the PARALLEL_THREADS_PER_CPU parameter setting, which defaults to 2.
The SID,SERIAL# are the identifiers of the parallel execution sessions, and the QCSID,QCSERIAL# is the identifier of the query coordinator of the parallel execution. So, with eight parallel execution sessions running, we would like to see how the space was used. A quick query against USER_SEGMENTS gives us a good idea:
ops$tkyte@ORA10GR1> select segment_name, blocks, extents
2 from user_segments
3 where segment_name in ( 'UNIFORM_TEST', 'AUTOALLOCATE_TEST' );
SEGMENT_NAME BLOCKS EXTENTS
--------------- ---------- ----------
UNIFORM_TEST 204800 16
AUTOALLOCATE_TEST 145592 714
Since we were using an 8KB blocksize, that shows a difference of about 462MB, or looking at it from ratio perspective, AUTOALLOCATE_TEST is about 70 percent the size of UNIFORM_TEST as far as allocated space goes. If we look at actual used space
ops$tkyte@ORA10GR1> exec show_space('UNIFORM_TEST' );
Free Blocks............................. 59,224
Total Blocks............................ 204,800
Total Bytes............................. 1,677,721,600
Total MBytes............................ 1,600
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 9
Last Used Block......................... 12,800
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec show_space('AUTOALLOCATE_TEST' );
Free Blocks............................. 16
Total Blocks............................ 145,592
Total Bytes............................. 1,192,689,664
Total MBytes............................ 1,137
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 41
Last Used Block......................... 8
PL/SQL procedure successfully completed.
Note The SHOW_SPACE procedure is described in the Setting Up section at the beginning of this book.
we can see if we take away the blocks on the freelist for UNIFORM_TEST59,224 of themthe tables consume about the same amount of actual space, but the amount of space needed by the UNIFORM tablespace is considerably more. This is all due to the extent trimming that did not take place. It we look at UNIFORM_TEST, we see this clearly:
ops$tkyte@ORA10GR1> select segment_name, extent_id, blocks
2 from user_extents where segment_name = 'UNIFORM_TEST';
SEGMENT_NAME EXTENT_ID BLOCKS
--------------- ---------- ----------
UNIFORM_TEST 0 12800
UNIFORM_TEST 1 12800
UNIFORM_TEST 2 12800
UNIFORM_TEST 3 12800
UNIFORM_TEST 4 12800
UNIFORM_TEST 5 12800
UNIFORM_TEST 6 12800
UNIFORM_TEST 7 12800
UNIFORM_TEST 8 12800
UNIFORM_TEST 9 12800
UNIFORM_TEST 10 12800
UNIFORM_TEST 11 12800
UNIFORM_TEST 12 12800
UNIFORM_TEST 13 12800
UNIFORM_TEST 14 12800
UNIFORM_TEST 15 12800
16 rows selected.
Each extent is 100MB in size. Now, it would be a waste of paper to list all 714 extents, so lets look at them in aggregate:
ops$tkyte@ORA10GR1> select segment_name, blocks, count(*)
2 from user_extents
3 where segment_name = 'AUTOALLOCATE_TEST'
4 group by segment_name, blocks
5 /
SEGMENT_NAME BLOCKS COUNT(*)
----------------- ---------- ----------
AUTOALLOCATE_TEST 8 128
AUTOALLOCATE_TEST 128 504
AUTOALLOCATE_TEST 240 1
AUTOALLOCATE_TEST 392 1
AUTOALLOCATE_TEST 512 1
AUTOALLOCATE_TEST 656 1
AUTOALLOCATE_TEST 752 5
AUTOALLOCATE_TEST 768 1
AUTOALLOCATE_TEST 1024 72
9 rows selected.
This generally fits in with how locally-managed tablespaces with AUTOALLOCATE are observed to allocate space. The 8, 128, and 1,024 block extents are normal; we will observe them all of the time with AUTOALLOCATE. The rest, however, are not normal; we do not usually observe them. They are due to the extent trimming that takes place. Some of the parallel execution servers finished their part of the loadthey took their last 8MB (1,024 blocks) extent and trimmed it, resulting in a spare bit left over. One of the other parallel execution sessions, as it needed space, could use this spare bit. In turn, as these other parallel execution sessions finished processing their own loads, they would trim their last extent and leave spare bits of space.
So, which approach should you use? If your goal is to direct path load in parallel as often as possible, I suggest AUTOALLOCATE as your extent management policy. Parallel direct path operations like this will use not use space under the objects HWMthe space on the freelist. So, unless you do some conventional path inserts into these tables also, UNIFORM allocation will permanently have additional free space in it that it will never use. Unless you can size the extents for the UNIFORM locally-managed tablespace to be much smaller, you will see what I would term excessive wastage over time, and remember that this space is associated with the segment and will be included in a full scan of the table.
To demonstrate this, lets do another parallel direct path load into these existing tables, using the same inputs:
ops$tkyte@ORA10GR1> alter session enable parallel dml;
Session altered.
ops$tkyte@ORA10GR1> insert /*+ append */ into UNIFORM_TEST
2 select * from big_table_et;
10000000 rows created.
ops$tkyte@ORA10GR1> insert /*+ append */ into AUTOALLOCATE_TEST
2 select * from big_table_et;
10000000 rows created.
ops$tkyte@ORA10GR1> commit;
Commit complete.
If we compare the space utilization of the two tables after that operation as follows:
ops$tkyte@ORA10GR1> exec show_space( 'UNIFORM_TEST' );
Free Blocks............................. 118,463
Total Blocks............................ 409,600
Total Bytes............................. 3,355,443,200
Total MBytes............................ 3,200
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 281,609
Last Used Block......................... 12,800
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> exec show_space( 'AUTOALLOCATE_TEST' );
Free Blocks............................. 48
Total Blocks............................ 291,184
Total Bytes............................. 2,385,379,328
Total MBytes............................ 2,274
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 8
Last Used Ext BlockId................... 140,025
Last Used Block......................... 8
PL/SQL procedure successfully completed.
we can see that as we load more and more data into the table UNIFORM_TEST using parallel direct path operations, the space utilization gets worse over time. We would want to use a significantly smaller uniform extent size or use the AUTOALLOCATE. The AUTOALLOCATE may well generate more extents over time, but the space utilization is superior due to the extent trimming that takes place.
</quote>