Ultimate performance optimization for Oracle

The ORACLE tutorial you are looking at is :Oracle performance optimizations. Increasing the SGA already buffered did not seem to improve performance significantly, with a 1.73% increase in load time. Let's increase the size of the SGA redo log:

DB3: Log Buffer
2 K Database Block Size
128 M SGA Buffer Cache
128 M SGA Shared Pool
16 M SGA Redo Cache
16 M Redo Log Files
Tablespaces Dictionary
TPC Results Load Time (Seconds) 41.39
Transactions/Second 10.088

We can see a 17.35% increase in load time and a 9.33% increase in TPS. Since loading and simultaneously inserting, updating, and deleting requires more space than 8M, but it seems that increasing the memory performance does not improve significantly, we increase the block size:

DB4:4 K Block
4 K Database Block Size
128 M SGA Buffer Cache
128 M SGA Shared Pool
16 M SGA Redo Cache
16 M Redo Log Files
Tablespaces Dictionary
TPC Results Load Time (Seconds) 17.35
Transactions/Second 10.179

We saw a 138% increase in load time! There was no significant effect on the value of TPS. The following simple idea is to switch the management of a table space from directory to local:

DB5: Local Tablespaces
4 K Database Block Size
128 M SGA Buffer Cache
128 M SGA Shared Pool
16 M SGA Redo Cache
16 M Redo Log Files
Tablespaces Local
TPC Results Load Time (Seconds) 15.07
Transactions/Second 10.425

Now let's expand the database block to 8K to see the result:

DB6:8 K Block
8 K Database Block Size
128 M SGA Buffer Cache
128 M SGA Shared Pool
16 M SGA Redo Cache
16 M Redo Log Files
Tablespaces Local
TPC Results Load Time (Seconds) 11.42
Transactions/Second 10.683

It seems that the result is not bad, we have no reason to continue to increase the block size, we have not adjusted the corresponding parameters according to the number of CPU, this time we set the number of I/O processes to continue to adjust:

DB7: I/O Slaves
8 K Database Block Size
128 M SGA Buffer Cache
128 M SGA Shared Pool
16 M SGA Redo Cache
16 M Redo Log Files
Tablespaces Local
dbwr_io_slaves 4
lgwr_io_slaves (derived) 4
TPC Results
Load Time (Seconds) 10.48
Transactions/Second 10.717

Our tests were based on Red Hat 6.2 with kernel version 2.2.14-5 smp. For the Linux kernel, there are several hundred parameters that can be adjusted, including the use of the CPU type, SMP support, APIC support, DMA support, IDE DMA default parameters supported by disk quota. According to the documentation of Oracle, the main adjustments we need to make are the size of Shared memory and semaphore. SHMMAX is configured at least 0x13000000, SEMMNI, SEMMSL and SEMOPN at least 100, 512 and 100, respectively. These parameters can be set through the following command:

# echo 0 x13000000 > /proc/sys/kernel/shmmax
# echo 512 32000 100 100 > /proc/sys/kernel/sem

OS1: single kernel and IPC

TPC Results
Load Time (Seconds) 9.54
Transactions/Second 11.511