Oracle performance optimization

  • 2020-05-09 19:31:34
  • OfStack

The ORACLE tutorial you are looking at is :Oracle under extreme performance optimization. There are reasons to believe that the new kernel version (2.2.16-3 smp) should also have a performance boost:

OS2: Newer minor version kernel TPC Results
Load Time (Seconds) 9.40
Transactions/Second 11.522

Currently, there are 2.4 versions of the kernel, and compared with 2.2, the performance has been greatly improved. We adopt 2.4.1 smp:

OS3: Newer major version kernel TPC Results
Load Time (Seconds) 8.32
Transactions/Second 12.815

The default read operation of Linux updates the last read time, but this is not important to us, so we turn off this option by setting the file properties of noatime. (similar Settings for Windows NT and 2000)

If only relative to Oracle data file Settings, our command is

chattr + A file_name

Implementation of the entire directory: chattr-R +A directory_name

The best way is to modify /etc/fstab and add the noatime keyword for each file system entry.

OS4: noatime file attribute
TPC Results
Load Time (Seconds) 5.58
Transactions/Second 13.884

Another way to adjust Linux I/O is to adjust the virtual memory subsystem, modify /ect/ sysctl.cong file, and add the following line:

vm.bdflush = 100 1200 128 512 15 5000 500 1884 2

According to/usr/src/Linux Documentation/sysctl/vm txt that:

Parameter 1: controls the largest dirty buffer in the buffer. Increasing this value means that Linux can delay disk writes.
Second parameter 1200 ndirty: gives bdflush the maximum dirty buffer that can be written to disk one time.
Third parameter 128 nrefill: the maximum number of buffers bdflush can add to the free buffer when refill_freelist() is called.

refill_freelist() 512: when this number exceeds nref_dirt dirty buffer, bdflush will be awakened.

The fifth 15 and the last two parameters 1884 and 2 are not used by the system, so we do not modify them.

age_buffer 50*HZ, age_super parameter 5*HZ: controls the maximum wait time for Linux to write dirty buffers to disk. The value is expressed in clock ticks (jiffies), which is 100 jiffies per second.

OS5: bdflush settings TPC Results
Load Time (Seconds) 4.43
Transactions/Second 14.988

After the adjustment of series 1 above, the final loading time we obtained was reduced by 1015.35%, while TPS increased by 45.61%.

Related articles: