Physical memory management for Oracle9i

In versions earlier than Oracle9i, you can only control the UNIX memory used by Oracle when starting the database. Oracle provides some INIT. ORA parameters to check the RAM size of the system global area, SGA. Once the database is started, you cannot change the size and configuration of the SGA.

Oracle's goal of moving towards a database of 24/7 is to adjust the size of the UNIX memory zone without having to stop or restart the database. More importantly, the dynamic SGA feature of Oracle9i allows you to monitor the physical memory usage in the SGA and adjust the SGA memory region according to the requirements of the existing Oracle database.

Oracle9i has also added a new memory management technology that enables you to pre-allocate memory for All program global zones program global area, PGA, and allows Oracle to allocate memory for each connection according to different needs of various types of connections. This is a radical change to the traditional Oracle database, and the SORT_AREA_SIZE and other PGA parameters are discarded.

In addition to allocating SGA, Oracle9i DBA must also be responsible for allocating all the memory for the UNIX Oracle Server and reserve 20% of the memory for UNIX management.

In versions earlier than Oracle9i, it is not uncommon to have several INIT. ORA parameter files, because this allows you to easily adjust the database and reconfigure SGA to change the running mode. For example, for online transaction processing, OLTP), the allocated SGA is very different from the Oracle Data Warehouse processing mode.

Oracle recommends OLTP database and decision support system demo-support system, DSS) applications-such as Oracle Data Warehouse-use different memory configuration solutions. The OLTP system should allocate most of the UNIX memory to SGA, while the data warehouse and DSS application are sensitive to the memory, allocate most of the memory to the PGA connection.

Of course, if a database operates in two ways, you can run a script to dynamically change the memory allocation. For example, if a database runs in OLTP mode during the day and in DSS mode at night, you can run a script to fetch some memory from SGA and re-allocate it to the PGA region.

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 parts 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 dynamically allocates memory by modifying the physical address space of the UNIX memory zone. You can use the malloc () and free () commands on UNIX.

The new dynamic SGA feature enables Oracle SGA to start with a small capacity and gradually increase as needed. To facilitate this process, a new SGA_MAX_SIZE parameter is provided.

(