Oracle Sorts Using Single-Threaded Temp File

It appears that sorting in Oracle only writes to one sort segment at a time. If an ORDER BY is executed on a large table, and if it will not fit into memory for the sort, then temp sort segments are used. We have defined multiple temp files using locally managed. While the sort is executing, V$SORT_USAGE shows:

USERNAME	USER	SESSION_ADDR	SESSION_NUM	SQLADDR	SQLHASH	TABLESPACE	CONTENTS	SEGTYPE	SEGFILE#	SEGBLK#	EXTENTS	BLOCKS	SEGRFNO#
BGILLE	BGILLE	762BC688	507	7BB24D84	2656723803	TEST_TEMP	TEMPORARY	SORT	409	448005	753	1927680	1
BGILLE	BGILLE	762BD960	26	7BB24D84	2656723803	TEST_TEMP	TEMPORARY	SORT	411	483845	4	10240	3
BGILLE	BGILLE	762BFF10	20	7BB24D84	2656723803	TEST_TEMP	TEMPORARY	SORT	411	486405	2	5120	3
BGILLE	BGILLE	762C087C	19	7BB24D84	2656723803	TEST_TEMP	TEMPORARY	SORT	409	450565	2	5120	1

Notice that the first entry has 753 extents. Also, watching iostat (Redhat Linux 3) during the sort shows writing to only one mount point at a time. It does seem to slowly move among a couple of mount points, but it is only writing to one at a time.

Is there some way to make (or allow) Oracle to use multiple temp files at once during the sort operation? Someone suggested that when Oracle uses ASM it will do this, but when using regular file system temp files, it will not. Can someone confirm this?

I have tried this on both Oracle 9i and 10g with the same results.

Thanks. Received on Fri Mar 10 2006 - 09:47:35 CST