orawin.info

Both an ORACLE-L discussion here and a Stack Exchange post raise essentially the same question which is, paraphrased:

What is the performance overhead of datafile autoextend events.

The conventional wisdom, with which I agree, is that the overhead is insignificant. However the poster in the stack exchange dialog asked how one would determine this empirically. A little bit of googling didn’t find anything so I thought I’d throw out some examples that can (with a little tweaking for file locations and sizes) be run on your system if you are interested.  The script I used is reproduced at the end of the post, but is described here for ease of understanding. The tests described were all conducted on a laptop using a single laptop HDD. As you’ll see from the results the actual hardware you have will impact this test significantly.

The script creates 2 tablespaces – one fixed at 1gb in size and one that starts small and is allowed to grow. The size and growth rate of the small tablespace are determined by the size of each extent in a test table that is created in each tablespace.  I ran sets of tests to determine the elapsed time taken to add a specific volume of data using different extent sizes for the table,(and hence different numbers of auto-extend operations), the setup is such that the growing tablespace should grow once for each time an extent is allocated to the table in the tablespace. The static tablespace is sized so that it does not need to grow at all by comparison.

I made tests at extent sizes (and autogrowth increments) of 1,10,25,50 and 100mb and data volumes from 100mb to 1000mb. Elapsed times are taken from sqlplus set timing on command.  My results are shown in the table below, All elapsed times are in seconds.

I then calculated the elapsed time per extension  and the elapsed time per extent allocation (by using the static tablespace timings). The second of these metrics varied a bit when very few extents were being allocated but was remarkably consistent at the 7-8ms mark. This suggests that the impact of allocating a new extent to an object is about equal to the impact of a single random disk I/O (or measurable but negligible). When it came to object extension then the average elapsed time per file system extension varied pretty much linearly with how much space was being allocated per file extension. On my system the average numbers (and the std deviation was very low though my sample size is small) came out as shown below.

It appears to me that what happens at file extension is pretty likely to be the same as what happens when a new datafile is added – namely space is allocated in the file system and the blocks zeroed out. The ‘overhead’ therefore will vary according to the capabilities of your I/O subsystem.  The data above for me graphs as follows:

Conclusions

There are 2 competing considerations when determining the optimal autoextend size for a datafile, and the balance between the 2 probably varies by I/O subsystem. In the greater cosmic scale of things neither is probably as important as the efficiency of your data import process or the SQL used to populate your tables. These 2 considerations are:

• What are the constraints on elapsed time that any individual transaction cannot exceed.
• Overall how efficient do we want to be.

In my case moving from a 25mb auto-extend size to a 50mb auto-extend size means that overall my dataload is 27% faster, but it does imply an increase in Worst case response time from 1.1 to 1.7s which may not be acceptable in an OLTP environment.

Script

The script I used is reproduced below. It will require editing if you use it for file locations.

set timing off
set verify off

accept extent_size prompt "Enter extent size in MB [default 1)" default 1
accept growth prompt "Enter desired growth in extents [default 100]" default 100
col min_size new_value min_file_size

select (&extent_size. + 1 ) min_size from dual;

drop tablespace ts_&extent_size. including contents and datafiles;
drop tablespace static_ts including contents and datafiles;

create tablespace ts_&extent_size.
datafile 'c:\app\oracle\oradata\niall\ts_&extent_size..dbf' size &min_file_size.m autoextend on next &extent_size.m
extent management local uniform size &extent_size.m;

create tablespace static_ts
datafile 'c:\app\oracle\oradata\niall\static_ts.dbf' size 1g autoextend off
;

create table growing_tab(n1 number, vc1 varchar2(100))
tablespace ts_&extent_size.;

create table static_tab(n1 number, vc1 varchar2(100))
tablespace static_ts;

set timing on

begin
for i in 1..&growth. loop
execute immediate 'alter table growing_tab allocate extent';
end loop;
end;
/

begin
for i in 1..&growth. loop
execute immediate 'alter table static_tab allocate extent';
end loop;
end;
/

drop tablespace ts_&extent_size. including contents and datafiles;
drop tablespace static_ts including contents and datafiles;

Possibly Related Posts:

• Advice from the Internet
• Distinctly Odd (Update)
• Distinctly Odd
• Instrumentation Overhead