Well done on testing the claim, rather than just accepting it. Yes, this is one of those old DBA's tales. It is based on the incorrect assumption that each sort run is equal to the sort_area_size. The intent of the extra block is to allow for the segment header block in the initial extent, and thus avoid a misalignment of sort runs to extent boundaries. The theory was that a misalignment of sort runs to extent boundaries could cause one more extent than necessary to be allocated to each disk sort. If that were the case, and if the temporary tablespace allowed PERMANENT contents, and if there was high utilization of the ST enqueue, then there might be a small performance impact.
In fact, no more than 90% of the sort area is used to build each sort run. The rest is used for write buffers. There are overheads that are introduced as the sort run is written from memory to disk blocks, but even with a 2K block size these are much less than 10%. So the size of sort runs on disk is always less than the sort area size. Also, it is normal for the final sort run to be somewhat smaller than the others. For these reasons, the concern about sort run to extent boundary alignment, and the concern about allocating an extra extent, are both unwarranted.
It is also worth mentioning that now that we can have temporary tablespaces with only TEMPORARY contents, each tablespace only has one temporary segment per instance and thus only one segment header block, and frequent sort extent allocation no longer contributes to ST enqueue contention. So even if these concerns had once had some substance, that would no longer be the case.
Better advice, regardless of your Oracle version or type of temporary tablespace, is to ensure that most disk sorts can be accommodated within a single temporary tablespace extent.
On page 489 of "Oracle Performance Tunning" there is a suggestion that setting your INITAIL and NEXT extent sizes to one block plus a multiple the sort area size will give you some performance improvement. For example:
DB_BLOCK_SIZE = 4K SORT_AREA_SIZE = 64K 4K + (1 * 64K) = 68K 4K + (2 * 64K) = 130K 4K + (3 * 64K) = 196KI have been looking for documentation on the extra block. I have set up some test databases and have tried to prove that you get better performance but all of the statictics look the same. I have also been reading through "Oracle8i Internal Services" thinking this may lead me to the answer with little luck. I have searched through Oracle's MetaLink and have found that they have suggested adding a block to your temp segment, subtracting a block from your temp segment, and keeping your temp segement just a multiple of your sort area size. Any suggestions?