SORT BLOCK刷新到磁盘的2中方式

Sort operations use up to SORT_AREA_SIZE bytes of memory. If a sort can be performed within

 this amount of memory and the result set fits within SORT_AREA_RETAINED_SIZE then there is no

 need to start writing blocks to disk. If more sortspace is needed it is then necessary to use

 a TEMPORARY segment on disk to accommodate the intermediate sort runs and/or the sort results

 . There are 2 ways sort blocks can be sent to disk:

 

via the buffer cache (and then DBWR)

Using sort direct writes

The first of these is often the default, and in Oracle 7.1 is the only option available. 

Sort blocks are placed into the buffer cache thus aging all other blocks. 

When the sort blocks reach the LRU end of the least recently used list DBWR will flush 

them to disk. This can impact the performance for everyone else as private sort blocks 

(sort blocks are of no use to anyone else) can flood DBWR and age blocks in the cache 

more quickly. 

In Oracle 7.2 the parameter SORT_DIRECT_WRITES can be set to cause processes to write sort blocks direct to disk avoiding the buffer cache. It is generally desirable to set SORT_DIRECT_WRITES to TRUE to ensure sort blocks do not impact the buffer cache.

Note 1: Setting SORT_DIRECT_WRITES=TRUE causes additional memory to be allocated for the session.

Note 2: In a lightly loaded environment SORT_DIRECT_WRITES may cause an individual job to take slightly longer ! Consider that when blocks are placed in the buffer cache it is acting almost like a memory extension to that process as "GETs" of sort blocks may be satisfied from the cache rather than from disk.

Since Oracle 8.1 the parameter SORT_DIRECT_WRITES became obsolete and direct writes are always used for sort operations that do not fit into the sort area size.