orawin.info

Yesterday I talked about some of the things to consider when adding space to a tablespace using auto-extend for datafiles. This of course isn’t the only way to add space to a tablespace. You could simply add more datafiles. Indeed nearly a decade ago (scary!) I wrote

The final reason for multiple datafiles (to spread IO) depends entirely on
how many disks you have which you don’t tell us, but in general small
datafiles spread across multiple disks will give better performance than
large datafiles on single disks.

At the time this sort of recommendation (use multiple datafiles, manually striped) was pretty common. These days of course with the widespread adoption of SAN technology, ASM, LVM and so on the micromanagement of disk I/O is a disappearing art (and mostly rightly so). The same underlying goal, to spread I/O, is achieved in hardware or o/s level volume management. Never the less, one might wonder how the exercise in growing a table would fare in a database where we added datafiles manually and in a controlled fashion, rather than just allowing auto-extend to do its job with a reasonable value for NEXT

The test script for this is even simpler than the one produced yesterday, because all that is in fact needed is a test that allocates N number of datafiles all of the same size. I reproduce my script for this below:

set timing off
set verify off

accept file_size prompt "Enter file size in MB [default 1)" default 1
accept files_to_add prompt "Enter #files to add [default 100]" default 100

create tablespace ts_&file_size.
datafile 'c:\app\oracle\oradata\niall\ts_&file_size..dbf' size &file_size.m autoextend off;

set timing on

begin
 for i in 1..&files_to_add. loop
   execute immediate 'alter tablespace  ts_&file_size. add datafile ''c:\app\oracle\oradata\niall\ts_&file_size.'||i||'.dbf'' size &file_size.m';
 end loop;
end;
/

drop tablespace ts_&file_size. including contents and datafiles;

This compares per operation with yesterday as follows :- The overhead column is just how much longer it takes to add a datafile.

However it is worth considering two more things before concluding that adding a datafile is a complete waste of time.

First, the session waiting on an add datafile event is the DBA session and not a user session. Consider an OLTP application where you know that you will need to add another 1gb of data per day (so we can reuse my figures). Imagine that you’ve decided that you don’t want any session to be delayed more than 1/2 a second by datafile extension. With my data from yesterday that would mean picking an autoextend size of about 10mb (0.52s per extension), all in all we’d waste about 52s of end-user time autoextending. If we were implementing multiple files per database then we’d almost certainly add a single 1gb datafile (in fact we’d likely have a standard size to add). This would end up having 2 advantages.

• The wait would fall on the administrator and not the user
• The time waited would be less

To illustrate the second point, and the fact that you can make objective predictions by gathering data like this, I’ve created an equivalent graph to yesterdays for file addition rather than file extension using the data from the table above. The bottom line is the quicker file extension, the top the time taken to add a single file of a specified size. I’ve also included the best fit linear trend line for the data as estimated by Excel.

If we plugin 1000mb for x to the formula given above we get an estimate for the time taken to add this datafile of 32.32 seconds.  When I reran my test script plugging in a single 1gb file, I got an actual time of

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.26

More Conclusions

For nearly all people the strategy of adopting an autoextend size of a few 10s of MB and not worrying about precise file sizes will be perfectly sensible. Bigfile tablespaces with autoextend may indeed be the way for many people who would otherwise have lots of 32gb datafiles, especially now RMAN can back these up in chunks. For some people however, either who have extremely tight performance requirements or who have excellent understanding of data growth patterns the old strategy of adding defined standard size datafiles in order to manage space may still be advantageous.

Possibly Related Posts:

• Proof by extension..
• Advice from the Internet
• Random Thoughts
• Distinctly Odd (Update)
• Distinctly Odd