Oracle 参数 SORT_AREA_SIZE 官方解释，作用，如何配置最优化建议
本站中文解释
：
SORT_AREA_SIZE参数指定了每个会话可以使用的排序内存大小，单位为字节。
排序操作是Oracle在实施SQL语句过程中必不可少的步骤，如果SORT_AREA_SIZE设置的值太小，将导致排序操作的效率下降，从而影响SQL语句的执行效率，以及数据库的性能。
正确设置SORT_AREA_SIZE参数的方法：
1、首先，确定SORT_AREA_SIZE参数的值，默认值为2M，此参数也可以在实际应用中调整，如果使用排序的SQL语句执行效率不高，可以考虑对此参数增加值；
2、针对某个会话的等待事件，可以检查当前会话的SORT_AREA_SIZE大小是否合理。如果当前SORT_AREA_SIZE太小，没有足够的内存来完成排序操作，可以尝试去增加SORT_AREA_SIZE的值；
3、根据实际的表大小和服务器硬件配置，可以计算出合理的参数，一般来说，使用以下公式对SORT_AREA_SIZE参数值进行设置：
max_sort_size = log_block_size * 10 * avg_row_len / 1024.
这里log_block_size是由DB_BLOCK_SIZE参数定义，并且不能大于SORT_AREA_SIZE，而avg_row_len是排序表存储的数据的平均行的大小；
4、其次，可以考虑使用替换排序和哈希排序来替代全表排序，减少排序时间；
5、最后，如果表比较大，考虑使用更大容量的固态硬盘作为表空间存放，也能够缩短排序过程中的访存时间。
官方英文解释
SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort.
| Property | Description |
|---|---|
|
Parameter type |
Integer |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
Minimum: the value equivalent of six database blocks Maximum: operating system-dependent |
|
Basic |
No |
Note:
Oracle does not recommend using the SORT_AREA_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_SIZE is retained for backward compatibility.
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.
Increasing SORT_AREA_SIZE size improves the efficiency of large sorts.
Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.
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.
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.
The default is adequate for most OLTP operations. You might want to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.
See Also:
-
Oracle Database
Concepts for information on sort areas -
Your operating system-specific Oracle documentation for the default value on your system
-
“SORT_AREA_RETAINED_SIZE”
