如何确定SORT_AREA_SIZE的大小！

尽管oracle 已经不建议使用SORT_AREA_SIZE参数了，oracle建议通过设置PGA_AGGREGATE_TARGET来激活自动工作区管理。不过在doc上看到了，随手记录一下！[@more@]

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.

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.

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.

   Note: 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:

ALTER SESSION SET SORT_AREA_SIZE = 80000000;

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

转载于:http://blog.itpub.net/19602/viewspace-1003456/