Re: Sparse TEMP files

Without getting into much technical detail, Oracle apparently creates tempfiles by moving the file pointer to the desired size and doing a write there. This is instantaneous but creates zeroes in the inode's pointers structure.

Two downsides of this are: a) df command does not notice the changes, and as the blocks are acquired at arbitrary times, according to the actual usage of the file by Oracle, which jeopardizes the filesystem monitoring; b) the performance will be slower for the first queries that use the tablespace.

The advantages are fast creation and reduced backup time/storage.

To deal with both, have Oracle acquire the blocks as soon as the tempfiles are created. This will, however, eliminate the benefit of fast creation. If you do not care about the mentioned drawbacks, then just enjoy the benefits.

Liggs wrote:

> Anybody else notice that Oracle 8i+ creates TEMP files as sparse files
> ?
> It also adds it very quickly and oracle sees it as, eg 500mb, the 'ls -l'
> Unix command sees it as 500Mb,
> but the 'ls -s' doesn't, possibly saying 10Mb instead.
>
> When someone does a query that needs all the TEMP tablespace, it expands it,
> possibly to the full 500Mb.
> Which is fine, unless you've filled the filesystem up in the meantime.
>
> an OS 'cp' to 'cp' changes it to a normal file, but its still a bit naff and
> dangerous.
>
> Alun
Received on Sun Jul 07 2002 - 01:41:42 CDT