Which of the following items are available for Oracle 9i performance tuning on AIX?

This article describes how to adjust the performance of Oracle 9i on AIX, we all know that memory access conflicts occur when the actual applied memory of a process exceeds the total physical memory of the system, the system exchanges programs and data between memory and hard disk.

Control Memory switching (Paging)

A large number of memory swap operations will greatly affect the performance of the system's Oracle 9i on AIX, 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.

Adjust the File Cache 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. The swap zone can be dynamically added on AIX, or the lsps command can be used to view the usage of the SWAp zone, and the vmstat can be used 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 that can obtain better overall system performance of Oracle 9i on AIX. 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 data block size in the Oracle database for better performance. On AIX, set the DB_BLOCK_SIZE initialization parameter and select a data block from 2KB to 32KB. The default value is 4KB. 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 improve the speed of archiving database logs, but be careful not to affect the performance of the entire system's Oracle 9i on AIX. 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.

Article by: http://database.csdn.net/c_oracle/tag/2