Oracle数据库提供了两种extent管理选项：本地管理和数据字典管理。目前，当创建一个新数据库，大多数DBA会使用前者来创建所有的表空间。这是一个好事。但不太明显的是在统一extent大小和系统管理extent大小之间的选择。这两种选择各有利弊。只有当存储在表空间中的段的大小已知时,才倾向于使用统一的区段大小。否则，最好是将选择最佳的extent尺寸的事情交给数据库。实际上，系统管理extent大小的目的是根据相关段的大小调整extent的大小。换言之，大段应该有大尺寸的extent，小段应该有尺寸的extent。通过这种方式，应该有可能避免碎片和过多的extent。为此，extent的大小被规范化为有限的几个尺寸：64KB、1MB、8MB或64MB。
现在让我们讨论两个自11.1.0.7被引入（悄悄的）的功能增强，而11.1.0.6时还不可用。
但是，在此之前，让我们创建一个表空间和一个视图，用于我将向您展示的测试…
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;
改进 #1
至11.1.0.6时，创建新段时，INITIAL（在STORAGE子句指定）参数仅会被部分考虑。实际上，如下例所示，即便创建了1MB的段，也会有16个64KB的extent与之关联。
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 1M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
65536 16
有人可能认为根本不考虑INITIAL值。然而，很容易证明它是实际使用的。要做到这一点，创建一个2MB的段就足够了。如下例所示，有两个1MB的extent与之关联。换言之，即使考虑了INITIAL值，第一个extent的大小也不是直接从它派生的。
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 2M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
1048576 2
然而，到了11.1.0.7，第一个extent的大小是直接由INITIAL值派生的。唯一可见的限制是extent的尺寸被规范化为64KB, 1MB, 8MB或64MB。数据库会使用小于等于INITIAL值的那种extent尺寸。因此，当执行与前面相同的SQL语句时，对于1MB段，只有一个1MB的extent与之关联。
SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 1M);
SQL> SELECT * FROM v;
BYTES EXTENTS
---------- ----------
1048576 1
这个行为的副作用是你会经常看到第一个extent的大小会比第二个extent大。虽然这不是什么新情况，但比以前更普遍了。下面的例子说明了这一点。注意，对于一个10MB的段，第一个extent是8MB，随后的两个extent各只有1MB。
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
总之，从11.1.0.7开始，如果将INITIAL设置为64KB、1MB、8MB或64MB，则第一个extent将具有指定的大小。所以，如果您知道段的大小，那么通过INITIAL提供一个合理的值也是不错的。
改进 #2
如您所料，第二个改进与可以在storage子句中指定的另一个参数有关：NEXT。然而，这种改进可能并不是你最初所期望的。实际上，NEXT并不是简单地用于指定第一个extent之后分配的任何extent的大小。根据我的观察，NEXT仅在执行并行加载时使用。
直到11.1.0.6，当执行并行加载时，每一个并行子进程从临时段中分配extents,并将数据写入其中。换言之，并行子进程并不会修改目标表。等事务提交时，临时段被合并到目标表中。如下的例子演示了此行为。注意三件事。首先，将NEXT设置为1MB。其次，insert大约10MB的数据。第三，我强制使用两个并行的子进程。由于每个并行子进程分配了自己的数据块，因此每个并行子进程分配了16个64KB数据块、4个1MB数据块和1个832KB数据块（每个并行子进程总共处理约6MB）。简单地说，临时段的extent分配遵循我们习惯的规则。
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
而11.1.0.7下，NEXT用于调整临时段的extent尺寸。因此，如果您知道正在加载大量数据，则可以避免小extent（这可能会导致在访问数据时性能不佳…）。让我们重新执行与之前相同的SQL语句，看看有什么不同。请注意，唯一的64KB的extent是最初建表时分配的原始extent。
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
总之，11.1.0.7时，使用NEXT可以控制并行加载期间,使用的临时段中extent的大小。
以下附原文链接和内容：
https://antognini.ch/2009/08/system-managed-extent-size-11g-improvements/
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.
