The Oracle tutorial you are looking at is Oracle performance optimization. Increasing the SGA already buffered appears to be less significant for performance, with only a 1.73% increase in loading time. Below we increase the size of the SGA redo log:
Db3:log Buffer
Database Block Size 2K
SGA Buffer Cache 128M
SGA Shared Pool 128M
SGA Redo Cache 16M
Redo Log Files 16M
Tablespaces Dictionary
TPC Results Load Time (Seconds) 41.39
Transactions/second 10.088
We can see that the loading time has increased the 17.35%,tps also 9.33%. Because loading and simultaneous inserts, updates, deletes need to be larger than 8M of space, but it appears that increased memory performance has not significantly improved, we increase the block size:
DB4:4K Block
Database Block Size 4K
SGA Buffer Cache 128M
SGA Shared Pool 128M
SGA Redo Cache 16M
Redo Log Files 16M
Tablespaces Dictionary
TPC Results Load Time (Seconds) 17.35
Transactions/second 10.179
We see a 138% increase in loading time! It has no significant effect on the TPS value. The following simple idea is that the management of table spaces is switched from directory to Local:
Db5:local tablespaces
Database Block Size 4K
SGA Buffer Cache 128M
SGA Shared Pool 128M
SGA Redo Cache 16M
Redo Log Files 16M
Tablespaces Local
TPC Results Load Time (Seconds) 15.07
Transactions/second 10.425
Below we add the database block to 8 K to see the result:
db6:8k Block
Database Block Size 8K
SGA Buffer Cache 128M
SGA Shared Pool 128M
SGA Redo Cache 16M
Redo Log Files 16M
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 size of the block, we have not adjusted according to the number of CPU parameters, this time we set the number of I/O process to continue to adjust:
DB7:I/O Slaves
Database Block Size 8K
SGA Buffer Cache 128M
SGA Shared Pool 128M
SGA Redo Cache 16M
Redo Log Files 16M
Tablespaces Local
Dbwr_io_slaves 4
Lgwr_io_slaves (derived) 4
TPC Results
Load time (Seconds) 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 the Linux kernel, there are nearly hundreds of parameters that can be adjusted, including CPU type, SMP support, APIC support, DMA support, and the use of the IDE DMA defaults for disk quota support. According to Oracle's documentation, the main adjustment we have to make is the size of shared memory and semaphore, SHMMAX minimum configuration 0x13000000,semmni, SEMMSL and SEMOPN are set at least 100, 512, 100 respectively. The settings for these parameters can be implemented by using the following command:
# echo 0x13000000 >/proc/sys/kernel/shmmax
# echo 32000 >/proc/sys/kernel/sem
OS1: Single-Core and IPC
TPC Results
Load time (Seconds) 9.54
Transactions/second 11.511
<
