oracle memory management

[Former Member]

hi gurus,

anybody pls give me good explanetion about oracle memory management.

regards,

reddy

[Former Member]

Hi Balaram reddy

The following link will help u:

http://dblab.ewha.ac.kr/hsyong/teach/dbgrad/grad-education/paper-list-2003-2/SQL-Oracle9i.pdf

Memory Management In Oracle9i:

Oracle9i introduces a number of new features aimed to make memory management simpler and more flexible:

1)Dynamic SGA Parameters

2)Automatic SQL Execution Memory Management

3)Buffer Cache Advisory

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;

2)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 = 100000KThe 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 systemWhere 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.

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 = ???

3)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:

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:

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.

Reward if useful to u