8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Memory Management In Oracle9i
Oracle9i introduces a number of new features aimed to make memory management simpler and more flexible.
Related articles.
Dynamic SGA Parameters
In Oracle8i, making changes to the SGA memory structures involved a restart of the server. This made the process of modification
and testing of new SGA configurations almost impossible on production systems. Oracle9i has made more instance parameters
dynamic, including DB_CACHE_SIZE and SHARED_POOL_SIZE.
ALTER SYSTEM SET DB_CACHE_SIZE = 10000000; ALTER SYSTEM SET SHARED_POOL_SIZE = 10000000;
Automatic SQL Execution Memory Management
Prior to Oracle9i optimization of the PGA memory structures could be very time consuming depending on the type of operations the system was performing. Oracle9i allows the DBA to leave configuration of the PGA up to Oracle by setting two initialization parameters.
WORKAREA_SIZE_POLICY = AUTO PGA_AGGREGATE_TARGET = 100000K
The WORKAREA_SIZE_POLICY parameter tell the server that it should take over PGA memory management. The
PGA_AGGREGATE_TARGET parameter specifies the total amount of memory the server can allocate to the PGA. Oracle
quote the following equations as a base for calculating the value of this parameter.
PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% for an OLTP system PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% for a DSS system
Where TOTAL_MEMORY is the total available memory for the system. If multiple applications/instances are running on the machine the values should be adjusted accordingly.
The PGA_AGGREGATE_TARGET parameter provides a soft limit. If necessary, the server can actually use more memory. If a session is performing an operation that is too big to reasonably be performed in the PGA, it will be offloaded into temp segments, which will prevent the operation from failing, but make it slower.
For backwards compatibility Oracle9i allows manual configuration of the PGA using.
WORKAREA_SIZE_POLICY = MANUAL SORT_AREA_SIZE = ??? HASH_AREA_SIZE = ??? BITMAP_MERGE_AREA_SIZE = ??? CREATE_BITMAP_AREA_SIZE = ???
Buffer Cache Advisory
Oracle9i includes a buffer cache advisory to aid configuration of the buffer cache. This advisory relies on an internal simulation based on the current workload to predict the cache "miss" rates for various sizes of the buffer cache ranging from 10% to 200% of the current cache size. The advisor can be started and stopped using the following.
ALTER SYSTEM SET DB_CACHE_ADVICE = <ON/OFF/READY>
By default the advisor is set to off as data collection and cache simulation cause a slight performance overhead. The results of switching the parameter are as follows.
- ON - Data collection and cache simulation is started.
- READY - Data collection is stopped but existing results are maintained.
- OFF - Data collection is stopped and all existing data is deleted.
The results of the advisor can be viewed by querying the V$DB_CACHE_ADVICE view.
For more information see:
Hope this helps. Regards Tim...