Oracle | MySQL | PostgreSQL |
|---|---|---|
sort_area_size | sort_buffer_size | |
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. | The key cost here is reduced server speed from setting this too high. Many common recommendations to use several megabytes or more have been made in a wide range of published sources and these are harmful for OLTP workloads. that normally benefit most from 32k or other small values. Do not set this to significantly larger values such as above 256k unless you see very excessive numbers of Sort_merge_passes - many hundreds or thousands per second on busy servers. Even then, it is far better to adjust the setting only in the connection of the few queries that will benefit from the larger size. In cases where it is impossible to adjust settings at the session level and when the workload is mixed it can be useful to use higher than ideal OLTP values to address the needs of the mixture of queries. | Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries. |
Oracle不建议手动设置此值，使用AMM或ASMM来管理内存。 如果是自动管理PGA内存，那么对于OLTP系统，建议设置PAG=（机器内存 × 80% ）× 20% | 此值设置的过大会降低服务器性能。 在许多公开的地方，有些人建议将此设置为几兆或更大，这其实是错误的，对OLTP系统的负载是有害的。 一般设置为32k就非常合适。 除非看到每秒由很多新增的sort_merge_passes（几百、成千的），否则不要将其设置超过256k。 即便如此，对于特定的session单独设置为更大值是更好的方式。 | 该值默认为四个兆字节（4MB）。请注意，对于复杂的查询，可能会并行运行多个排序或哈希操作。在开始将数据写入临时文件之前，每个操作将被允许使用此值指定的内存量。同样，几个正在运行的会话可能会同时进行此类操作。因此，使用的总内存可能是work_mem的许多倍；选择值时必须牢记这一事实。排序操作用于ORDER BY，DISTINCT和合并联接。哈希表用于IN子查询的哈希联接，基于哈希的聚合和基于哈希的处理。 |
https://docs.oracle.com/database/121/REFRN/GUID-A343E04E-B484-4791-8B01-12E182AA00C7.htm#REFRN10206 | https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=uktdx9vo8_80&id=1531329.1 | https://www.postgresql.org/docs/9.4/runtime-config-resource.html |