Oracle9i's physical memory management

In previous versions of Oracle9i, you could only control the UNIX memory used by Oracle when you started the database. Oracle provides some Init.ora parameters to detect the amount of RAM in the system global Zone (AREA,SGA). Once the database is started, you will no longer be able to change the size and configuration of the SGA.

Oracle's goal of moving to a 24/7 database proposes the need to adjust the size of the UNIX memory area without having to stop and restart the database. More importantly, Oracle9i's dynamic SGA feature allows you to monitor the physical memory usage within the SGA and adjust the SGA memory area based on the needs of the existing Oracle database.

Oracle9i also adds a new memory management technology that allows you to allocate memory for all program global areas (Programs global AREA,PGA) and allow Oracle to allocate memory for each connection according to the different requirements of various connections. This is a fairly radical change to the traditional Oracle database and makes sort_area_size and other PGA parameters obsolete.

Not only do you assign sga,oracle9i DBAs must also assume responsibility for allocating all the memory for UNIX Oracle servers, while reserving 20% of the memory for UNIX management.

In previous versions of Oracle9i, it was not uncommon to have several Init.ora parameter files because it was easy to adjust the database and reconfigure the SGA to change the operating mode. For example, the SGA and Oracle Data Warehouse processing modes allocated for online transaction processing (transaction PROCESSING,OLTP) are very different.

Oracle recommends using different memory configuration scenarios for OLTP databases and decision support systems (decision support SYSTEM,DSS) applications, such as Oracle data warehouses. OLTP systems should allocate much of the UNIX memory to the SGA, while data warehouses and DSS applications are more sensitive to memory, and most of the memory should be allocated to the PGA connection.

Of course, if a database works in two ways, you can run a script that dynamically alters the memory allocation. For example, if a database runs in OLTP mode during the day and runs in DSS mode at night, you can run a script to get some memory out of the SGA and reassign it to the PGA area.

alter system set Sga_memory_max = 3g;
alter system set Pga_aggregate_target = 3g;

In Oracle9i, Oracle provides the ability to increase or decrease the size of the following portions of the SGA memory:

Data buffer Size-alter system set db_cache_size=300m;
Shared Pool Size-alter system set shared_pool_size=200m;
Total PGA RAM Memory Size-alter system set pga_aggregate_target=2000m;

On UNIX, Oracle achieves the purpose of dynamic memory allocation by modifying the physical address space of the UNIX memory area. You can do it with the malloc () and free () commands on UNIX.

The new dynamic SGA feature enables the Oracle SGA to start with a smaller capacity and incrementally increase as needed. A new sga_max_size parameter is also provided to facilitate this process.