Oracle 参数 SORT_AREA_RETAINED_SIZE 官方解释，作用，如何配置最优化建议
本站中文解释
sort_area_retained_size参数表示空间排序保持区的大小，这也是Oracle排序运算时使用的最大内存空间，即当排序数据超过此空间时，会写出到临时表空间。
正确设置方式：
1.首先要考虑“SORT_AREA_SIZE”和“SORT_AREA_RETAINED_SIZE”参数之间关系，如果开发者设置“SORT_AREA_SIZE”参数值太大，则基本是用不到“SORT_AREA_RETAINED_SIZE”参数，因为元素可以在这个区域内连续排序。
2.此外，如果SORT_AREA_SIZE设置的值比较小，而SORT_AREA_RETAINED_SIZE值大，则系统会越来越慢，因为当内存已满时，排序的元素会被写到临时表中，而临时表的访问会非常低效。
3.因此，正确设置方式为：通过观察查询使用的最大排序空间，来确定“SORT_AREA_RETAINED_SIZE ”和“SORT_AREA_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.
| Property | Description |
|---|---|
|
Parameter type |
Integer |
|
Default value |
Derived from |
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
From the value equivalent of two database blocks to the value of |
|
Basic |
No |
Note:
Oracle does not recommend using the SORT_AREA_RETAINED_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_RETAINED_SIZE is retained for backward compatibility.
Oracle may allocate multiple sort spaces of this size for each query. Usually, only one or two sorts occur simultaneously, 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.
Note:
The default value as reflected in the V$PARAMETER dynamic performance view is 0. However, if you do not explicitly set this parameter, Oracle actually uses the value of the SORT_AREA_SIZE parameter.
See Also:
-
“SORT_AREA_SIZE”
-
Oracle Database
Performance Tuning Guide for information on setting the values of this parameter and theSORT_AREA_SIZEparameter to tune sort operations using shared servers
