Database Reference
In-Depth Information
UNIFORM_TEST 10 12800
UNIFORM_TEST 11 12800
UNIFORM_TEST 12 12800
13 rows selected.
Each extent is 100MB in size. Now, it would be a waste of paper to list all 131 extents allocated to the
AUTOALLOCATE_TEST
tablespace, so let's look at them in aggregate:
EODA@ORA12CR1> select segment_name, blocks, count(*)
2 from user_extents
3 where segment_name = 'AUTOALLOCATE_TEST'
4 group by segment_name, blocks
5 order by blocks;
SEGMENT_NAME BLOCKS COUNT(*)
-------------------- ---------- ----------
AUTOALLOCATE_TEST 1024 128
AUTOALLOCATE_TEST 6240 1
AUTOALLOCATE_TEST 8192 2
This generally fits in with how locally-managed tablespaces with
AUTOALLOCATE
are observed to allocate space
(the results of the prior query will vary depending on the amount of data and the version of Oracle). Values such as the
1,024 and 8,192 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 load—they took their last 64MB (8,192 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 not use space under
the object's HWM—the 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, let's do another parallel direct path load into these existing tables, using the same inputs:
EODA@ORA12CR1> alter session enable parallel dml;
Session altered.
EODA@ORA12CR1> insert /*+ append */ into UNIFORM_TEST
2 select * from big_table_et;
10000000 rows created.
EODA@ORA12CR1> insert /*+ append */ into AUTOALLOCATE_TEST
2 select * from big_table_et;
10000000 rows created.
EODA@ORA12CR1> commit;
Commit complete.
