A case of improper configuration of Oracle Database memory management parameters

Original article:

Http://qa.taobao.com /? P = 7887

 

Since Oracle introduced the automatic management of memory parameters for 10 Gb, it seems that the problem of sga pga parameter settings that had plagued DBAs has been gradually ignored. Yes. Theoretically, we only need to allocate about 80% of the system's memory to the dB, and leave the remaining memory to the OS and other applications. This is a very good practice principle and is also recognized by the practice. However, the computer world often tells us that this automation intelligence is often misused in some extreme scenarios...

In the IOT optimization project, the previous test environment is as follows:

32 GB physical memory, allocated to PGA 1.5 GB, sga_max_size = sga_target = 18.8 GB.

Theoretically, SGA should be 32 * 80% * 80% = 20.48g; PGA should be 32 * 80% * 20% = 5.12g

That is to say, for common OLTP applications, the SGA value of the system is basically reasonable, and the PGA should be moderately increased.

During the test, it was found that the TPs of 200 concurrent select/insert operations (read/write ratio) was stable, and the performance of server side was reasonable.

If you increase the concurrency to 500, the TPS will plummet, server side will be suspended, and load will reach more than 200 in one minute!

For a box with 4 CPUs and 32 GB memory, the results can only be said to be terrible. As a result, the system resources are monitored in real time during the scenario, and the CPU/IO resources are reasonably displayed when the load surges, while the memory reaches the bottleneck and requires tuning.

With the help of DBA, increase PGA to 4 GB, sga_max_size to 16 GB, sga_target to 12 GB, and re-run the test. Everything is normal.

Conclusion 1: Do not use superstitious formulas. After understanding the principles, you can use Tuning Based on the phenomenon and the cause of its occurrence to solve the problem.

Conclusion 2: For Intranet test environments, we recommend that you do not set sga_max_size and sga_target to the same value because of the many test scenarios. The production system should choose the same value :)