SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE

SORT_AREA_SIZE

SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

SORT_AREA_SIZE定义了Oracle用来排序最大的可用空间。在排序完成之后，排序结果返回之前，Oracle会释放分配给排序的空间，但SORT_AREA_RETAINED_SIZE定义的空间不释放。当最后一行排序结果返回后，Oracle释放剩余的内存空间

Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never occur. Only one memory area of SORT_AREA_SIZE exists for each user process at any time.

增加SORT_AREA_SIZE参数可以改善比较大的排序的效率。任何时间用户进程只会使用一块SORT_AREA_SIZE的内存空间。

SORT_AREA_SIZE is also used for inserts and updates to bitmap indexes. Setting this value appropriately results in a bitmap segment being updated only once for each DML operation, even if more than one row in that segment changes.

SORT_AREA_SIZE也被用来增加和修改位图索引。

Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs.

SORT_AREA_SIZE参数值较大可以允许多个排序在内存中进行。如果排序还需要更大的内存空间，临时段将用来保存中间排序的结果

 

SORT_AREA_SIZE通常分配在PGA中

 

SORT_AREA_RETAINED_SIZE

SORT_AREA_RETAINED_SIZE specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.

SORT_AREA_RETAINED_SIZE定义了排序完成后UGA中保留的最大的内存空间。这部分内存将释放给UGA，而不是操作系统

Oracle may allocate multiple sort spaces of this size for each query. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, however, additional concurrent sorts are required, and each sort keeps its own memory area. If the shared server is used, allocation is to the SGA until the value in SORT_AREA_RETAINED_SIZE is reached. The difference between SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE is allocated to the PGA.

Oracle可以为每个query分配多个SORT_AREA_RETAINED_SIZE定义的空间

与 SORT_AREA_SIZE不同的就是SORT_AREA_SIZE分配在PGA中

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22111412/viewspace-611174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22111412/viewspace-611174/