A recurring question I see around the ‘net has to do with the extent allocation in tablespaces with multiple data files. The question is, does oracle completely fill one data file before beginning to use the next, or does it use the files in a balanced, round-robin fashion? Various resources give conflicting answers. A few years ago I put together a test to discover for myself how Oracle handles this. I’ve pulled that test (which was originally run on Oracle 9.2) out of my archives and ran it again on 12.1, with the same results.
This article will present the results of those tests.
Methodology
The test methodology is as follows:
Step 1 – create a tablespace with three small datafiles.
Step 2 – create a table with sufficiently large rows as to force multiple extents with a relatively small number of rows.
Step 3 – insert enough rows into the table to force a sufficient number of extents to force multiple extents
Step 4 – execute queries to map the allocation of the extents.
Trial 1
First, we’ll create a tablespace with three small datafiles.
SQL> create SMALLFILE tablespace bubba_ts 2 datafile '/oradata/tulsa/bubbatbs_01.dbf' 3 size 1m autoextend off, 4 '/oradata/tulsa/bubbatbs_02.dbf' 5 size 1m autoextend off, 6 '/oradata/tulsa/bubbatbs_03.dbf' 7 size 1m autoextend off 8 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K 9 ; Tablespace created.
Next, create a table with a large row size, so as to easily force additional extents. While it is not normally recommended to use CHAR instead of VARCHAR2, in this case it is a convenient way of insuring that every row takes a large amount of space, regardless of the actual data.
SQL> create table bubba.rowid_test 2 ( 3 key_col number, 4 big_col1 char(2000), 5 big_col2 char(2000), 6 big_col3 char(2000) 7 ) 8 ; Table created.
Next, insert enough rows to force the additional extents. In actuality, this took several trials to come up with a number of rows that was both large enough to produce the desired result and yet small enough to remain convenient for the demonstration.
SQL> insert into bubba.rowid_test ( 2 key_col, 3 big_col1, 4 big_col2, 5 big_col3 6 ) 7 select level, 8 'xxxxx', 9 'xxxxx', 10 'xxxxx' 11 from dual 12 connect by level select 'row num: '||a.key_col|| 2 ' file: '|| f.file_name as "File List" 3 from bubba.rowid_test a 4 join dba_data_files f 5 on f.file_id = dbms_rowid.rowid_to_absolute_fno(a.rowid,'BUBBA','ROWID_TEST') 6 order by a.key_col 7 ; File List -------------------------------------------------------------------------------- row num: 1 file: /oradata/tulsa/bubbatbs_02.dbf row num: 2 file: /oradata/tulsa/bubbatbs_02.dbf row num: 3 file: /oradata/tulsa/bubbatbs_02.dbf row num: 4 file: /oradata/tulsa/bubbatbs_02.dbf row num: 5 file: /oradata/tulsa/bubbatbs_02.dbf row num: 6 file: /oradata/tulsa/bubbatbs_01.dbf row num: 7 file: /oradata/tulsa/bubbatbs_01.dbf row num: 8 file: /oradata/tulsa/bubbatbs_01.dbf row num: 9 file: /oradata/tulsa/bubbatbs_01.dbf row num: 10 file: /oradata/tulsa/bubbatbs_01.dbf row num: 11 file: /oradata/tulsa/bubbatbs_01.dbf row num: 12 file: /oradata/tulsa/bubbatbs_01.dbf row num: 13 file: /oradata/tulsa/bubbatbs_01.dbf row num: 14 file: /oradata/tulsa/bubbatbs_03.dbf row num: 15 file: /oradata/tulsa/bubbatbs_03.dbf row num: 16 file: /oradata/tulsa/bubbatbs_03.dbf row num: 17 file: /oradata/tulsa/bubbatbs_03.dbf row num: 18 file: /oradata/tulsa/bubbatbs_03.dbf row num: 19 file: /oradata/tulsa/bubbatbs_03.dbf row num: 20 file: /oradata/tulsa/bubbatbs_03.dbf row num: 21 file: /oradata/tulsa/bubbatbs_02.dbf row num: 22 file: /oradata/tulsa/bubbatbs_02.dbf row num: 23 file: /oradata/tulsa/bubbatbs_02.dbf row num: 24 file: /oradata/tulsa/bubbatbs_02.dbf row num: 25 file: /oradata/tulsa/bubbatbs_02.dbf row num: 26 file: /oradata/tulsa/bubbatbs_02.dbf row num: 27 file: /oradata/tulsa/bubbatbs_02.dbf row num: 28 file: /oradata/tulsa/bubbatbs_02.dbf row num: 29 file: /oradata/tulsa/bubbatbs_01.dbf row num: 30 file: /oradata/tulsa/bubbatbs_01.dbf row num: 31 file: /oradata/tulsa/bubbatbs_01.dbf row num: 32 file: /oradata/tulsa/bubbatbs_01.dbf row num: 33 file: /oradata/tulsa/bubbatbs_01.dbf row num: 34 file: /oradata/tulsa/bubbatbs_01.dbf row num: 35 file: /oradata/tulsa/bubbatbs_01.dbf row num: 36 file: /oradata/tulsa/bubbatbs_03.dbf row num: 37 file: /oradata/tulsa/bubbatbs_03.dbf row num: 38 file: /oradata/tulsa/bubbatbs_03.dbf row num: 39 file: /oradata/tulsa/bubbatbs_03.dbf row num: 40 file: /oradata/tulsa/bubbatbs_03.dbf 40 rows selected.
And finally, another check of simply the extents allocation. Again, it is easy to see the round-robin usage/allocation pattern
SQL> col file_name for a30
SQL> select e.extent_id,
2 e.file_id,
3 f.file_name
4 from dba_extents e,
5 dba_data_files f
6 where e.owner = 'BUBBA'
7 and e.segment_name = 'ROWID_TEST'
8 and e.file_id = f.file_id
9 order by e.extent_id
10 ;
EXTENT_ID FILE_ID FILE_NAME
---------- ---------- ------------------------------
0 5 /oradata/tulsa/bubbatbs_01.dbf
1 8 /oradata/tulsa/bubbatbs_03.dbf
2 7 /oradata/tulsa/bubbatbs_02.dbf
3 5 /oradata/tulsa/bubbatbs_01.dbf
4 8 /oradata/tulsa/bubbatbs_03.dbf
5 7 /oradata/tulsa/bubbatbs_02.dbf
6 rows selected.
Trial 2
In the previous trial I created the tablespace with “EXTENT MANAGEMENT LOCAL UNIFORM SIZE”. Let’s change that to “EXTENT MANAGEMENT LOCAL AUTOALLOCATE” and observe the difference:
SQL> create smallfile tablespace bubba_ts 2 datafile '/oradata/tulsa/bubbatbs_01.dbf' 3 size 1m autoextend off, 4 '/oradata/tulsa/bubbatbs_02.dbf' 5 size 1m autoextend off, 6 '/oradata/tulsa/bubbatbs_03.dbf' 7 size 1m autoextend off 8 extent management local autoallocate 9 ; Tablespace created.
After repeating the same data load procedure as before, the queries to see the usage pattern now show we’ve only used one datafile:
SQL> select 'row num: '||a.key_col||
2 ' file: '|| f.file_name as "File List"
3 from bubba.rowid_test a
4 join dba_data_files f
5 on f.file_id = dbms_rowid.rowid_to_absolute_fno(a.rowid,'BUBBA','ROWID_TEST')
6 order by a.key_col
7 ;
File List
--------------------------------------------------------------------------------
row num: 1 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 2 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 3 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 4 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 5 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 6 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 7 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 8 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 9 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 10 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 11 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 12 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 13 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 14 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 15 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 16 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 17 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 18 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 19 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 20 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 21 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 22 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 23 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 24 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 25 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 26 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 27 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 28 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 29 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 30 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 31 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 32 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 33 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 34 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 35 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 36 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 37 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 38 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 39 file: /oradata/tulsa/bubbatbs_02.dbf
row num: 40 file: /oradata/tulsa/bubbatbs_02.dbf
40 rows selected.
SQL> select e.extent_id,
2 e.file_id,
3 f.file_name
4 from dba_extents e,
5 dba_data_files f
6 where e.owner = 'BUBBA'
7 and e.segment_name = 'ROWID_TEST'
8 and e.file_id = f.file_id
9 order by e.extent_id
10 ;
EXTENT_ID FILE_ID FILE_NAME
---------- ---------- ------------------------------
0 7 /oradata/tulsa/bubbatbs_02.dbf
1 7 /oradata/tulsa/bubbatbs_02.dbf
2 7 /oradata/tulsa/bubbatbs_02.dbf
3 7 /oradata/tulsa/bubbatbs_02.dbf
4 7 /oradata/tulsa/bubbatbs_02.dbf
5 7 /oradata/tulsa/bubbatbs_02.dbf
6 rows selected.
Conclusions
So, in this case, as in most things, the answer is “It depends”. At least with the variables I was controlling for, it depends on if the tablespace is defined as UNIFORM extents, or AUTOALLCOATE extents. I have not explored the behavior with dictionary managed tablespaces simply because they were rendered obsolete by the use of locally managed tablespaces. Anyone still using dictionary managed tablespaces needs to be more concerned about migrating to locally managed than how oracle manages those obsolete structures.
I think it also interesting that in both cases the first extents were allocated in the second file of the tablespace. This seemed consistent across multiple trials. I cannot explain this behavior.
Finally, what is the practical value of knowing this? What administrative decision will you make based on these results? Honestly, nothing comes to mind. But for some reason this question comes up now and again. And when it does the responses seem to be based on speculation or an overly simplified “proof”. So I decided to try a more thorough test to at least prove the behavior, if not the reasoning. In the 12c Concepts manual, the section on extents (here) mentions that while a given extent must be in a single data file, different extents of the same segment may be in different data files but it does not explain how that decision is made.
Ed Stevens, DBA 