Oracle Database provides two extent management options: locally managed and dictionary managed. Today, when creating a new database, most DBAs use the former for all tablespaces. This is a good thing. What is less obvious is the choice between uniform extent size and system managed extent size. Both of these options have pros and cons. The tendency is to use uniform extent size only when the size of the segments to be stored in a tablespace is (well) known. Otherwise, it is better to delegate the choice of an “optimal” extent size to the database engine. In fact, the aim of system managed extent size is to adjust the size of the extents to the size of the segments to which they are associated. In other words, a large segment should have large extents, a small segment should have small extents. In this way it should be possible to avoid both fragmentation and very high number of extents. To do so, the size of the extents is normalized to a limited number of sizes: 64KB, 1MB, 8MB or 64MB.
Now, let’s discuss two enhancements that have been (silently?) introduced in 11.1.0.7… Yes, in 11.1.0.6 they are not available!
But, before that, let’s create a tablespace and a view that will be used for the tests I’ll show you…
SQL> CREATE TABLESPACE test
2 DATAFILE '/u01/oradata/DBM11106/test01.dbf' SIZE 1G
3 BLOCKSIZE 8K
4 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
5 SEGMENT SPACE MANAGEMENT AUTO;
SQL> CREATE OR REPLACE VIEW v AS
2 SELECT bytes, extent_id-lag(extent_id,1,-1) OVER (ORDER BY extent_id) AS extents
3 FROM (
4 SELECT bytes, extent_id, decode(bytes,lead(bytes) OVER (ORDER BY extent_id),0,1) AS last
5 FROM user_extents
6 WHERE segment_name = 'T'
7 )
8 WHERE last = 1
9 ORDER BY extent_id;
Improvement #1
Up to 11.1.0.6, INITIAL (the attribute that can be specified in the storage clause) is only partially considered when creating a new segment. In fact, as the following example shows, even if a segment of 1MB is created, sixteen extents of 64KB are associated to it.
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 1M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
65536 16
One might think that INITIAL is not considered at all. However, it is easy to show that it’s actually used. To do so it’s sufficient to create a segment of 2MB. As the following example shows, two extents of 1MB are associated to it. In other words, even if the value of INITIAL is considered, the size of the first extent is not directly derived from it.
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 2M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
1048576 2
As of 11.1.0.7, however, the size of the first extent is directly derived from INITIAL. The only and obvious limitation is that the extent size is normalized to either 64KB, 1MB, 8MB or 64MB. To do so the database engine uses the extent size which is equal or smaller to INITIAL. Therefore, when the same SQL statements as before are executed, for a segment of 1MB only one extent of 1MB is associated to it.
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 1M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
1048576 1
A curios side effect of this new behavior is that it’s much common to see segments for which the first extent is larger than the second one. Such situations are not new… but, they are much more common than before. The following example illustrates this. Notice that, for a 10MB segment, the first extent is 8MB and the subsequent two are only 1MB each.
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 10M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
8388608 1
1048576 2
In summary, as of 11.1.0.7, if you set INITIAL to either 64KB, 1MB, 8MB or 64MB, the first extent will have the specified size. So, if you know what the size of an segment will be, it is not bad to provide a sensible value through INITIAL.
Improvement #2
As you might guess, the second improvement is related to another attribute that can be specified in the storage clause: NEXT. The improvement, however, is a not what you might expect in first place. In fact, NEXT is not simply used to specify the size of any extent allocated after the first one. According to my observations NEXT is only used when a parallel load is performed.
Up to 11.1.0.6, when a parallel load is performed, every parallel slave allocates extents from a temporary segment and loads the data into it. In other words, the parallel slaves do not modify the target table. Then, when the transaction is committed, the temporary segment is merged to the target table. The following example illustrates this behavior. Notice three things. First, NEXT is set to 1MB. Second, the insert loads about 10MB of data. Third, I forced the utilization of two parallel slaves. Since each parallel slave allocated its own extents, every parallel slave allocates sixteen 64KB extents, four 1MB extents and one 832KB extent (for a total of about 6MB per parallel slave). Simply put, the extent allocation for the temporary segment follows the rules we are used to.
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (NEXT 1M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
65536 1
SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
SQL> INSERT INTO t
2 SELECT rownum, rpad('*',1000,'*')
3 FROM dual
4 CONNECT BY level <= 10000;
SQL> SELECT bytes, extent_id-lag(extent_id,1,-1) OVER (ORDER BY extent_id) AS extents
2 FROM (
3 SELECT bytes, extent_id, decode(bytes,lead(bytes) OVER (ORDER BY extent_id),0,1) AS last
4 FROM user_extents
5 WHERE segment_type = 'TEMPORARY'
6 )
7 WHERE last = 1
8 ORDER BY extent_id;
BYTES EXTENTS
---------- ----------
65536 16
1048576 4
851968 1
65536 16
1048576 4
851968 1
SQL> COMMIT;
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
65536 17
1048576 4
851968 1
65536 16
1048576 4
851968 1
As of 11.1.0.7, NEXT is used for sizing the extents of the temporary segment. As a result, if you know that are loading a lot of data, you can avoid the small extents (that might lead to suboptimal performance when the data will be accessed…). Let’s re-execute the same SQL statement as before to see what the difference is. Notice that the only extent of 64KB is the original one that was initially associated to the table.
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (NEXT 1M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
65536 1
SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
SQL> INSERT INTO t
2 SELECT rownum, rpad('*',1000,'*')
3 FROM dual
4 CONNECT BY level <= 10000;
SQL> SELECT bytes, extent_id-lag(extent_id,1,-1) OVER (ORDER BY extent_id) AS extents
2 FROM (
3 SELECT bytes, extent_id, decode(bytes,lead(bytes) OVER (ORDER BY extent_id),0,1) AS last
4 FROM user_extents
5 WHERE segment_type = 'TEMPORARY'
6 )
7 WHERE last = 1
8 ORDER BY extent_id;
BYTES EXTENTS
---------- ----------
1048576 5
786432 1
1048576 5
786432 1
SQL> COMMIT;
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
65536 1
1048576 5
786432 1
1048576 5
786432 1
In summary, as of 11.1.0.7, with NEXT you can control the sizing of the temporary extents used during parallel loads.