Oracle 9i performance adjustments on AIX

Today, we will discuss with you the performance adjustments of Oracle 9i on AIX and the emergence of Memory Access conflicts, when the memory requested by the process exceeds the total physical memory in the system, the system exchanges the program and data between the memory and the hard disk to handle this situation.

Control Memory switching (Paging)

A large number of memory swap operations will greatly affect the system performance, especially when database files are created on the file system (JFS and JFS2 ). In this case, frequently accessed data exists in the SGA and also in the file cache. If the same data is cached twice in the memory, the memory usage efficiency is reduced, so that the memory is frequently exchanged, resulting in system I/O bottlenecks, reduces the performance of the entire system.

Performance adjustments for Oracle 9i on AIX

On AIX, The MINPERM and MAXPERM parameters in the vmtune command can be used to adjust the System File Cache to Control Memory swap operations. The adjustment of MINPERM and MAXPERM parameters should follow the following principles:

When the File Cache page in the memory is below MINPERM, The Memory Page switching algorithm exchanges both the File Cache page and the program page (computational pages ).

When the File Cache pages in the memory are larger than MAXPERM, The Memory Page switching algorithm only exchanges File Cache pages.

When the File Cache pages in the memory are between MINPERM and MAXPERM, The Memory Page switching algorithm usually only exchanges File Cache pages. However, if the File Cache page exchange rate is higher than the program page, the program page is also exchanged.

Default values of MINPERM and MAXPERM parameters:

 
 

  
  
MINPERM = ((number of memory page) - 1024)*0.2  
  
  
MAXPERM = ((number of memory page) - 1024)*0.8 
 
 

You can use the following command to set MINPERM to 5% memory pages, while MAXPERM to 20% memory pages.

# The default values of vmtune-p 5-P 20 are 20% and 80%, respectively.

In the Oracle database application environment, MINPERM and MAXPERM can be set to 5%, 20%, or even smaller, so that the memory is more used for the SGA of Oracle than the System File Cache.

Configure enough swap Areas

If the swap area space is insufficient, the system response speed slows down or even suspends. The size of the swap area depends on the system memory size and the memory requirements of applications. Oracle 9i can dynamically add swap areas on AIX, or use the lsps command to view the usage of swap areas, and use vmstat to monitor system memory page swap operations.

Usually, the size of the swap area must be greater than the actual memory of the system, which depends mainly on the memory requirements of the application. You need to monitor the usage of the swap area and increase the space of the swap area as needed. A large swap space may be required for running Oracle Application or Oracle development tools.

Priority of memory requirements

If the system memory is insufficient, you need to sort the memory usage priority of the process and allocate the memory Priority to the process for better overall system performance. The general order is as follows:

1. Core processes of AIX and Oracle

2. Application Process

3. Redo log buffer of Oracle

4. PGA and shared pool)

5. Oracle data cache (block buffer cache)

For example, when you query the dynamic performance table of Oracle, you find that more memory needs to be configured for both the shared pool and block buffer cache, the free memory in your system is limited. Therefore, it is better to allocate the free memory to the shared pool to the data cache area.

Size of data blocks

You can select the size of data blocks in the Oracle database for better performance. On AIX, Oracle 9i sets the DB_BLOCK_SIZE initialization parameter and selects data blocks from 2 kb to 32 KB. The default value is 4 kb. If the data file is on the file system, the size of the Oracle data block should be an integral multiple of 4 kb of the file system data block. If the data file is on a bare system device, the size of the Oracle data block should be an integer multiple of the system data block 512B.

Oracle recommends using 2 kb or 4 KB data blocks for online transaction applications (OLTP) or hybrid application environments. For BI applications or Decision Support Systems (DSS), use larger data blocks, such as 8 KB, 16 kb or 32 KB.

Adjust Log Archive Buffers)

Configuring a large cache can increase the speed of archiving database logs, but do not affect the performance of the entire system. You can increase LOG_ARCHIVE_BUFFER_SIZE to 20% KB (the default value is 4 kb), which improves the performance of database archiving operations from 0.

Adjust the SGA size

For Oracle SGA, switching out of the system's memory management program to the swap zone is certainly the most undesirable situation for users. This requires the system to have enough memory. We recommend that you configure a large SGA area for applications that are online to a large number of users simultaneously. The most influential parameters for SGA are DB_BLOCK_BUFFERS and SHARED_POOL_SIZE. Increasing these two parameters depends on the size of the SGA memory you want to allocate. Increasing these parameters will also increase the CPU utilization.