Aug 12 2009
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.
August 13th, 2009 at 3:57 am
One thing about uniform size and initial segments I hate is, that an empty segment (lob column) always uses up one extend. This wastes a lot of storage if the col is unused. And as a software vendor you never know when a customer will use the segment (and the customer does not want to decide which segments not to use). With system sized extends this is less a problem (unless you specify a large initial).
August 13th, 2009 at 9:35 am
Hello Christian,
i am wondering about the NEXT clause in your SQL statements and the LMT tablespace with allocation type system.
I know the following allocation algorithm in that kind of tablespace:
SEG < 1M = Extent Size 64k
1M <= SEG < 64M = Extent Size 1M
64M <= SEG < 1G = Extent Size 8M
1G CREATE TABLE t (id NUMBER, pad VARCHAR2(1000))
2 TABLESPACE test
3 STORAGE (INITIAL 10M NEXT 10M);
Just to clarify, becuase of maybe some guys are wondering about that.
Regards
Stefan
August 13th, 2009 at 9:56 am
Hi Stefan
> i am wondering about the NEXT clause in your SQL statements
I did the wrong copy/paste… My bad… The result does not change with or without the NEXT clause. I did plenty of tests to find out in which cases it might be relevant. And, at the end, as I wrote in the post, I never saw a case in which NEXT is used (except in case of parallel load, of course). I updated the post accordingly.
> I know the following allocation algorithm in that kind of tablespace:
Be careful that there are exceptions to it… For example, when MSSM is used. Here an example:
SQL> CREATE TABLESPACE test 2 DATAFILE '/u00/oradata/DBA111/test01.dbf' SIZE 1G 3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE 4 SEGMENT SPACE MANAGEMENT MANUAL; SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000)) 2 TABLESPACE test 3 STORAGE (FREELIST GROUPS 6); SQL> SELECT * FROM v; BYTES EXTENTS ---------- ---------- 65536 1 SQL> CREATE TABLE t (id NUMBER, pad VARCHAR2(1000)) 2 TABLESPACE test 3 STORAGE (FREELIST GROUPS 7); SQL> SELECT * FROM v; BYTES EXTENTS ---------- ---------- 1048576 1This is because all freelist blocks must be in the first extent. The database engine recognize the “problem” and, therefore, increases the size of the first extent.
Cheers,
Chris
August 13th, 2009 at 1:25 pm
Hi Chris:
Very good explanation. So starting 11.1.0.7 you can limit the number of extents if you know the volume of data.
But since really number of extents dont effect performance, how would this be beneficial?
Thank you
Kumar
August 13th, 2009 at 2:33 pm
Hi Kumar
The number of extents itself is not important for performance. What is important for performance is the size of the extents. In fact, the database engine is not able to perform an I/O operation that spans several extents. As a result, if a lot of small extents have to be read, a lot of small I/O operations have to be performed. Hence, it is much better to avoid small extents…
Cheers,
Chris
August 14th, 2009 at 4:38 pm
Hi Chris,
Very neat explanation! From 11.1.0.7 on, is there really a reason to continue using
“uniform extent size”? It looks like “system managed extent size” with accurate
INITIAL and NEXT values is the way to go. At least I have some difficutlties to see the cons.
Thanks, and have a nice weekend
Philipp
August 14th, 2009 at 5:47 pm
[...] Christian Antognini -System Managed Extent Size – 11g Improvements [...]
August 14th, 2009 at 6:40 pm
[...] On Striving for Optimal Performance, Christian Antognini discusses 11g’s improvements to system managed extent size. [...]
August 16th, 2009 at 7:43 pm
[...] Antognini nos cuenta unos cambios en como Oracle 11g maneja los tamaños de los extents en System Managed Extent Size – 11g Improvements. Interesante, espero que 11g llegue pronto a SAP (pero todavía hay que esperar unos [...]
August 16th, 2009 at 11:00 pm
Hi Philipp
Provided that sensible values for INITIAL and NEXT are specified, I also do not see why uniform extent size tablespaces should be used. So, the main con is that you have to specify them… And, if you look around, I see lot of people that are no longer willing to specify such parameters at the table level.
Cheers,
Chris
December 31st, 2009 at 4:39 pm
Just wanted to point out that “Improvement #1″ is not a new feature of 11.1.0.7.
The behaviour is the same in 11g.
Initial size is taken into consideration to determine the number of extents that will be allocated.
The total number of extents allocated will depend if the tablespace is using SYSTEM or UNIFORM size.
The size of the extents that are allocated will depend on the size specified for initial clause.
The following test case show this.
SQL> create tablespace test1 datafile ‘/u02/oradata/ordb/dbfiles/test02.dbf’ size 5m extent management local uniform size 65536;
Tablespace created.
SQL> create table mytable1 (empno number(2), ename varchar2(100)) tablespace test1 storage (initial 2M);
Table created.
SQL> select file_id, extent_id, block_id, blocks, bytes from dba_extents where segment_name = ‘MYTABLE1′;
FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
———- ———- ———- ———- ———-
7 0 9 8 65536
7 1 17 8 65536
7 2 25 8 65536
…….
…….
7 30 249 8 65536
7 31 257 8 65536
SQL> create tablespace test2 datafile ‘/u02/oradata/ordb/dbfiles/test03.dbf’ size 5m;
Tablespace created.
SQL> create table mytable2 (empno number(2), ename varchar2(100)) tablespace test2 storage (initial 2M);
Table created.
SQL> select file_id, extent_id, block_id, blocks, bytes from dba_extents where segment_name = ‘MYTABLE2′;
FILE_ID EXTENT_ID BLOCK_ID BLOCKS BYTES
———- ———- ———- ———- ———-
8 0 9 128 1048576
8 1 137 128 1048576
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production
January 25th, 2010 at 4:44 pm
Hi Merwyn
Sorry, but I don’t understand what do you want to show with your test case. Where is the difference with mine? As I pointed out in the post, the value of the INITIAL parameter is only “fully” used as of 11.1.0.7. At least this is what I see on my test databases… In your case, what happens if you create the table with the following SQL statement? (1M instead of 2M)
Cheers,
Chris
July 13th, 2010 at 9:33 pm
[...] I wrote three years ago about some of the anomalies of I/O sizes when running parallel query, and a related enhancement in 11g described by Christian Antognini a couple of years [...]
July 20th, 2010 at 6:42 pm
Chris,
Thanks again for another wonderful (albeit a bit old) blog post that I am just running across. I’m used to the 10g way of System managed extents and could find no documentation of 1M being the NEXT extent size. I’m used to seeing NEXT be NULL in *_tables, but in 11.1.0.7, it shows at 1048576 for NEXT and 65536 for Initial (upon creation of a table without specifying any storage parameters).
The impact I see here is why application that create 1000s of tables and indexes but only use a a handful. Sure, everything starts out at 64K but as soon as an extent is needed, the segment shoots up to 1M. Maybe this is desirable behavior on Oracle’s part? It is just a change from what I know of 10g and System managed extents. I guess I’ll have to wait until we get to 11gR2 and deferred segment creation :(
July 27th, 2010 at 8:18 am
[...] 假如说,每个区间都只能是64K,这会限制我们将发起的“db file multiblock read”请求的大小吗或者这些请求可以跨越区间边界读取吗?如果这个表空间是有两个(或多个)数据文件组成,而这些区间又是以“轮流”在两个文件之间分配的,这会影响读操作的方式吗?如果我们尝试进行并行表扫描,这些限制在“direct path read”上会不会有所不同呢?如果你的运行系统是一个数据仓库,需要花费大量的时间运行这种操作,那么这些就是你需要回答的问题.(例如,参见我3年前写的关于运行并行查询时的部分IO异常的记录,以及Christian Antognini在大约几年后描述的Oracle 11g中的一个相关改进.) [...]