I have installed 22.214.171.124 as dedicated server option. I have a few question here. Pls read the below text from Oracle guide.
1. How do I check my Oracle is running in automatic memory management mode?
2. I'm using PGA target, so sort_area is it used?
3. sort_area_retained_size is currently 0. Will performance improve if I increase the size?
4. If I disconnected the session, the freed memory of sort_are_retained_size will be freed right?
Tuning sort operations using SORT_AREA_SIZE is only relevant for configurations running the Oracle shared server option or for configurations not running under the automatic memory management mode. In the later case, Oracle Corporation strongly recommends switching to the automatic memory management mode, because it is easier to manage and often outperforms a manually-tuned system.
Considerations with SORT_AREA_RETAINED_SIZE
With dedicated connections, the freed memory is not released to the operating system, rather the freed memory is made available to the session for reuse.
1. SHOW PARAMETER WORKAREA_SIZE_POLICY
2. SORT_AREA_SIZE is not used when you are using PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY IS TRUE. But there was a bug regarding this (1961633) which was fixed in release 2.
3. Oracle does not recommend use of SORT_AREA_RETAINED_SIZE (with Auto PGA Manage) unless you are using shared server.
4. The memory is released back to UGA not to the OS.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
Click Here to Expand Forum to Full Width