Re: Sparse TEMP files

In my experience, your first thought is right.

The first time that temporary blocks are written, Oracle has to acquire them from the operating system - at which point they have to be de-allocated from the O/S map of free blocks. I think that the activity (including disk writes associated with updating the free block map and writing inode and filepointer blocks) is likely to introduce extra time.

The overhead is probably small, though, especially when compared to the cost of a large sort to disc. It might be worth checking, though whether Oracle demands space with high precision (i.e. to the nearest block) or low precision (by extent) when it first requires previously unformatted space. In particular, does it write the file (with nulls) when the sort_segment needs a new extent, or does it write it ONLY if blocks from that extent are pushed to disc.

Perhaps the easiest way to test is to make use of temporary LOBS with the NOCACHE option set. This seems to make Oracle particularly aggressive about writing to the TEMP tablespace. (in 8.1.7)

I don't think there is ever any question of Oracle reading temporary segments before using them, it probably only 'news' them, irrespective of what used to be there.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Karen wrote in message <3D291A93.E6587A0F_at_ureach.com>...


>Actually I think you are right and I was wrong.

>

>It should be actually FASTER for temp files.  For temp files, the OS blocks

do not


>exist, and OS will not have to read them in first!  I do not think that

modifying


>inode pointers or formating the blocks by Oracle will be any significant

overhead,


>as both are in memory operations.

>

>However, Oracle's implementation may turn our speculations upside down.

>

>For my organization, though, the monitoring considerations prevail and I

would


>rather do what I suggested.

>

>

>Yong Huang wrote:

>

>> Karen <abvk_at_ureach.com> wrote in message

news:<3D27E270.D86488D9_at_ureach.com>...


>> > b) the performance

>> > will be slower for the first queries that use the tablespace.

>>

>> I'd like to know why the first use of a sparse tempfile would be

>> slower than if the file were actually filled with data. If it *is*

>> indeed true, it's probably not because the "holes" have to be written

>> with real data; it may be because the inode pointers have to point

>> somewhere instead of nowhere? Do you have a benchmark or a theoretical

>> proof?

>>

>> Yong Huang

>