You may have read my advice not to use sparse tempfiles on Solaris UFS, but did you know that datafiles can be sparse too?

Direct and concurrent I/O is very important for Oracle database performance, so there can be a dramatic impact when database operations switch to buffered and blocking I/O.  (Eg RMAN section size bug).

On Solaris, concurrent direct I/O is disabled to UFS files that are sparse (not all blocks have been allocated in the filesystem).
All direct I/O is disabled when the files are opened with the O_DSYNC flag and the files are sparse.

When datafiles are resized, (either manually or by auto-extension), Oracle grows the file using fcntl to seek past the last allocated block of the file.  At this point, the datafile is considered sparse; it has holes because not all the blocks have been allocated.  The process resizing the datafile will now start filling that hole, and can do so with direct and asynchronous I/O.  Until it has completed the task, other processes’ I/O from/to that datafile is limited by blocking file locks (aka POSIX/inode/single writer locks).  Processes opening the datafile with the O_DSYNC flag are also forced to use OS buffering (large I/Os are broken into single block I/Os).

This situation may not cause significant problems for quiet databases, but for a database that has many concurrent sessions that access the most recent data, (eg right hand indexes and time-based partitions), it can cause grid lock.  For small auto-extension increases, the symptoms may just show as frequent blips.  At the other end of the scale, resizing a 1GB datafile to 20GB would block all sessions wanting to read a single block from that datafile and bring multiple database writers to a halt until the resizing process finishes writing 19GB to the datafile (so that it has 20GB allocated in the filesystem).

The symptoms are varied, but you might see one session waiting on Data file init write while others get stuck on a single db file sequential read, DBWRs on db file parallel write, etc.

Consequent peaks of concurrent activity (dam bursting effect) can have lasting effects, such as index bloating from ITL growth and an increase in 50/50 block splits in what would otherwise be right hand indexes.  In extreme examples the application may experience errors when trying to connect due to hitting the maximum number of processes or concurrent listener/TCP connections.

To avoid this issue, simply pre-allocate space; create datafiles at the size at which you would expect them to eventually grow if left to auto-extend.

Don’t rely on auto-extension to drip feed capacity.  (An auto-extend next size of 8KB or 16KB would also avoid this problem, but could increase overhead and UFS fragmentation).

Be cautious when resizing a used datafile by a large amount in a busy database.


One thought on “Danger: Sparse Datafiles Ahead

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s