Introduction to Oracle RAC performance adjustment Solution

The adjustment of Oracle RAC performance is mainly to adjust the actual application size through CPU and wait time, as well as the specific adjustment of RAC, and descriptions of the relevant actual operation solutions, the following is the description of the specific content of the article. I hope you will gain some benefits after browsing it.

RAC-reactive adjustment tools mainly include: Specific wait events, system and queue statistics, database control Performance page, statspack, and AWR reports. These statistics are stored in memory, you can view V $ in the dynamic performance view. * AWR snapshots indicates the persistent part of the device.

1. Adjust the size of CPU and wait time

When you adjust the system, it is very important to compare the CPU time and wait time of the system, so as to determine how much is used for effective working time in the corresponding time, the number of resources that are waiting to be occupied by other processes.

According to the general rule, the system that occupies the main part of wait time needs to be adjusted more than the system that occupies the main part of CPU time. On the other hand, a large amount of CPU usage may be caused by poor SQL write operations.

Although the ratio of CPU time to wait time always decreases with the increase of system load, the sharp increase of wait time is a manifestation of conflict and must be effectively processed.

Adding more CPUs to node or adding nodes to cluster is very limited in the resource competition. On the contrary, when the loading system load increases, the CPU time ratio may not be significantly reduced. More benefit may be obtained by adding CPUs or OracleRAC Instances.

Note: If the CPU time ratio is in the first five events, the automatic workload repositoryAWR report displays the CPU time and wait time in the Top 5 Event segments.

2. Specific RAC Adjustment

Although OracleRAC has its own unique adjustment methods, such as interconnected transmission, adjusting each Instance like a single-Instance system will bring about a large benefit. At least it should be the first step of tuning.

Obviously, if there is a serialization problem in the single-Instance environment, this problem will become more serious in RAC.

RAC-reactive adjustment tools mainly include: Specific wait events, system and queue statistics, database control Performance page, statspack and AWR report

RAC-proactive adjustment tool: AWR snapshots, ADDMAutomatic Database Diagnostic Monitor) report

As shown above, the adjustment tool of the OracleRAC is similar to that of the single-Instance system. However, the combination of some special wait events and statistical information is critical to the adjustment of RAC.

3. analyze the impact of cache fusion buffer fusion in RAC.

The impact of accessing blocks in the global buffer and the coherency of maintaining the cache are shown below:

Global buffer service statistics for current and cr blocks: for example, gc Current blocks received and gc cr blocks received.

The global buffer service waits for events to gc the current block 3-way, gc cr grant 2-way, etc)

The response time of cache fusion transmission is determined by the messaging time and processing time used by the physical switching link component, IPC protocol, and GCS protocol.

In addition to related log write operations, it is not affected by disk I/O factors. The cache fusion protocol does not need to perform I/O on data files to ensure the coherency of the buffer. In addition, OracleRAC does not cause more I/O operations than non-clustered instances.

Article by: http://database.51cto.com/art/200703/43583.htm