Oracle Performance Optimization

Oracle Performance Optimization

The ORACLE tutorial is: Excellent Oracle performance. Increasing the SGA buffer seems to have not significantly improved the performance, and the loading time has only increased by 1.73%. Next we will increase the size of the SGA redo log:

DB3: Log Buffer

Database Block Size 2 K

SGA Buffer Cache 128 M

SGA Shared Pool 128 M

SGA Redo Cache 16 M

Redo Log Files 16 M

Tablespaces Dictionary

TPC Results Load Time (second) 41.39

Transactions/Second 10.088

We can see that the loading time has increased by 17.35%, And the TPS has also increased by 9.33%. Because loading, simultaneous insertion, update, and deletion require a larger space than 8 Mb, but it seems that the increase in memory performance does not significantly improve. we increase the block size:

DB4: 4 K Block

Database Block Size 4 K

SGA Buffer Cache 128 M

SGA Shared Pool 128 M

SGA Redo Cache 16 M

Redo Log Files 16 M

Tablespaces Dictionary

TPC Results Load Time (second) 17.35

Transactions/Second 10.179

We can see that the loading time has increased by 138%! The TPS value is not significantly affected. The following simple idea is to switch tablespace management from directory to local:

DB5: Local Tablespaces

Database Block Size 4 K

SGA Buffer Cache 128 M

SGA Shared Pool 128 M

SGA Redo Cache 16 M

Redo Log Files 16 M

Tablespaces Local

TPC Results Load Time (second) 15.07

Transactions/Second 10.425

Next we will increase the database block to 8 K to see the result:

DB6: 8 K Block

Database Block Size 8 K

SGA Buffer Cache 128 M

SGA Shared Pool 128 M

SGA Redo Cache 16 M

Redo Log Files 16 M

Tablespaces Local

TPC Results Load Time (second) 11.42

Transactions/Second 10.683

It seems that the results are not bad. We have no reason to continue to increase the block size. We have not adjusted the corresponding parameters based on the number of cpus. This time we set the number of I/O processes to continue to adjust:

DB7: I/O Slaves

Database Block Size 8 K

SGA Buffer Cache 128 M

SGA Shared Pool 128 M

SGA Redo Cache 16 M

Redo Log Files 16 M

Tablespaces Local

Dbwr_io_slaves 4

Lgwr_io_slaves (derived) 4

TPC Results

Load Time (second) 10.48

Transactions/Second 10.717

Our tests are based on Red Hat 6.2 and the kernel version is 2.2.14-5 smp. For Linux kernels, there are hundreds of parameters that can be adjusted, including CPU type, SMP support, APIC support, DMA support, and the use of default ide dma parameters is supported by the disk quota. According to the Oracle document, the main adjustment we need to do is the size of the shared memory and semaphore. SHMMAX should be configured with at least 0x13000000, SEMMNI, SEMMSL, and SEMOPN should be set to at least 100,512,100 respectively. You can use the following command to set these parameters:

# Echo 0x13000000>/proc/sys/kernel/shmmax

# Echo 512 32000 100 100>/proc/sys/kernel/sem

OS1: single kernel and IPC

TPC Results

Load Time (second) 9.54

Transactions/Second 11.511