首页 > 应用开发 > IT综合 > 如何确定SORT_AREA_SIZE的大小!


原创 IT综合 作者:warehouse 时间:2008-05-06 10:51:17 0 删除 编辑

Specifying Memory for SQL Work Area with SORT_AREA_SIZE

The memory for the SQL work area can also be controlled with the SORT_AREA_SIZE initialization parameter.


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 value of the SORT_AREA_SIZE parameter should be set using the following rules:

  1. Find the amount of available memory by subtracting the size of the SGA and the size of the operating system from the total system memory.
  2. Divide this amount by the number of parallel slaves that you will use; this is typically the same as the number of CPUs.
  3. Subtract a process overhead, typically a five to ten megabytes, to get the value for SORT_AREA_SIZE.


    You can also save time on index creating operations, or fast rebuilds, with on the fly statistics generation.

Example 13-5 is an example of setting the SORT_AREA_SIZE parameter.

Example 13-5 Example of Creating Indexes Efficiently

A system with 512 Mb memory runs an Oracle instance with a 100 Mb SGA, and the operating system uses 50 Mb. The memory available for sorting is 362 Mb, which equals 512 minus 50 minus 100. If the system has four CPUs running with four parallel slaves, then each of these will have 90 Mb available. 10 Mb is set aside for process overhead, so SORT_AREA_SIZE should be set to 80 Mb. This can be done either in the initialization file or for each session with the following statement:


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量