orawin.info

Niall's Oracle Pages – Oracle Opinion since 2004

Proof by extension..

without comments

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.

 Extent Size (MB) Data Allocated MB Elapsed (growth) Elapsed (Static) Extend Ops
1 100

25.78

0.680

100

10 100

5.89

0.110

10

25 100

6.08

0.070

4

50 100

3.95

0.050

2

100 100

3.23

0.004

1

1 500

138.17

3.340

500

10 500

24.33

0.450

50

25 500

20.52

0.170

20

50 500

15.73

0.090

10

100 500

15.55

0.060

5

1 1000

276.68

7.510

1000

10 1000

51.72

0.720

100

25 1000

37.99

0.310

40

50 1000

33.73

0.170

20

100 1000

32.63

0.070

10

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.

Extension Size (MB)

Duration per operation (s)

1

0.27

10

0.53

25

1.17

50

1.74

100

3.20

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:

Written by Niall Litchfield

January 4th, 2012 at 3:06 pm

Posted in Uncategorized

Tagged with ,

Leave a Reply

%d bloggers like this: