Shulou(Shulou.com)06/01 Report--

By: Jane Zhang | Senior Support Manager

Gc cr multi block request' is a common wait event in RAC database. When performing full table scan (Full Table Scan) or full index scan (Index Fast Full Scan) on RAC, it is easy to produce such multi-block read wait.

The main reasons for this wait are:

1. The database parameter db_file_multiblock_read or db_block_size is set too large, resulting in too much GC transmission when multiple blocks are read

2. The parameter setting related to UDP on OS is not large enough, which causes the cache area of receiving and sending UDP to overflow.

3. Private network performance

4. The LMS setting problem (insufficient number or not running in real time (real time)) results in insufficient processing capacity of LMS and failure to transmit global cache data/message in time.

There is less Bug in this area. There was a BUG 8464597 before 11.2.When db_block_size = 32k, a large number of "gc cr multi block request" were triggered and the performance degraded, and this Bug was fixed in 11.2.

In many cases, lowering the DB_FILE_MULTIBLOCK_READ_COUNT and increasing the OS UDP-related parameters will greatly ease the gc cr multi block request' wait.

Recently, a problem was dealt with. A large number of'gc cr multi block request' waits' were generated after upgrading from 10.2 to 11.2.0.3. It was found that the parameters of DB_FILE_MULTIBLOCK_READ_COUNT and UDP did not change, but the number of LMS varied among different instances and decreased a lot after the upgrade. Later, the number of LMS was increased and the values of each instance were kept consistent, and the problem was solved.

Avg

Wait DB

Event Waits Time (s) (ms) time Wait Class

Gc cr multi block request 632923 32441 51 35.5 Cluster

DB CPU 13518 14.8

Gc cr grant 2-way 327717 10900 33 11.9 Cluster

Gc current grant 2-way 190856 6855 36 7.5 Cluster

Gc current block 2-way 101154 3792 37 4.1 Cluster

If you find that there is a'gc cr multi block request' in the AWR TOP5 wait and its Avg wait (ms) is high, please refer to the following diagnostic steps:

The first step is to view the db_file_multiblock_read_count and db_block_size parameter settings.

SQL > show parameter db_block_size

SQL > show parameter db_file_multiblock_read_count

Db_block_size is generally 8192 and db_file_multiblock_read_count is 16. 5%.

The second step is to see the relevant parameter settings of OS udp. The parameters of udp are different on different OS. These parameters will set the size of receive cache and send cache of UDP. Generally speaking, receive cache > = send cache. If you modify these parameters in the production repository, it is best to consult the OS vendor for considerations.

On AIX:

# no-a

Udp_recvspace

Udp_sendspace

O set udp_sendspace > = [(DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT) + 4096], but not less than 65536.

For example, DB_BLOCK_SIZE=8192, DB_FILE_MULTIBLOCK_READ_COUNT=16, then udp_sendspace > = (8192 * 16) + 4096 135168. Note that this value is only the minimum value, and it is not required by Oracle to be so large.

O set udp_recvspace to 4 to 10 times udp_sendpace

O because sb_max must be > = udp_recvspaceIf, you may need to increase the value of sb_max (default is 1048576)

O if the parameter setting of udp is unreasonable, "socket buffer overflows" may be generated. If this value is not 0, you need to increase udp_recvspace:

Netstat-s | grep "socket buffer overflows"

O reference: http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883

Minimum Software Versions and Patches Required to Support Oracle Products on IBM Power Systems (Doc ID 282036.1)

On Linux:

# More / etc/sysctl.conf

Net.core.rmem_default

Net.core.rmem_max

Net.core.wmem_default

Net.core.wmem_max

The minimum required on the official documentation:

Http://docs.oracle.com/cd/E11882_01/install.112/e24321/pre_install.htm#BABDAEDB

Oracle Database Installation Guide

11g Release 2 (11.2) for Linux

E24321-07

Rmem_default 262144

Rmem_max 4194304

Wmem_default 262144

Wmem_max 1048576

You can set all of these values to 4k:

Net.core.rmem_default = 4194304

Net.core.rmem_max = 4194304

Net.core.wmem_default = 4194304

Net.core.wmem_max = 4194304

On HP:

Check that the UDP setting is large enough:

$/ bin/ndd-get / dev/sockets socket_udp_rcvbuf_default

$/ bin/ndd-get / dev/sockets socket_udp_sndbuf_default

Make sure that the socket_udp_rcvbuf_default is at least twice that of the socket_udp_sndbuf_default.

Sun:

You can view the UDP settings with the following command:

Ndd / dev/udp udp_xmit_hiwat

Ndd / dev/udp udp_recv_hiwat

Ndd / dev/udp udp_max_buf

You can set these two values to the maximum allowed by OS with the following command:

Ndd-set / dev/udp udp_xmit_hiwat

Ndd-set / dev/udp udp_recv_hiwat

Ndd-set / dev/udp udp_max_buf

For more information, please refer to the MOS documentation:

Tuning Inter-Instance Performance in RAC and OPS (Doc ID 181489.1)

The third step is to check the network.

For example, use the netstat-s command to see if there are bad data length, bad checksums, incomplete headers, socket buffer overflows, etc. Note that these values are cumulative, and you need to see if they increase when problems occur.

You can also check to see if there is a "Global Cache Blocks Lost" in AWR. Ideally, this value is 0, and if there is a large block lost, there is something wrong with the network (check the network according to MOS document 563566.1).

You can also ask the network administrator to check the performance of the private network.

Step 4, check the LMS.

1. Check the trace file of LMS to see if there are any exceptions.

two。 Check whether the priority of the LMS process is real-time (real time)?

For example, AIX:

# ps-elf | grep lms

Ps-elf | grep lms

F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD

240103 An oracle 4719002 1 5 39-- 8ae40b590 248856 Jul 28-570 Jul 45 ora_lms0_rac1

The smaller the priority, the higher the priority. If the PRI is less than 40, it is Real Time:

Http://aix4admins.blogspot.co.uk/2011/08/commands-and-processes-process-you-use.html

3. Check the number of LMS:

SQL > show parameter GCS_SERVER_PROCESSES

This value determines the number of LMS.

This value depends on the number of CPU (cpu_count), according to the 11.2 official documentation:

Http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams094.htm#REFRN10259

Default value

If 1-3 CPUS, then 1

If 4-15 CPUs, then 2

If 16 or more CPUs, then 2 + (CPUs / 32). If the result includes a fraction, then the fraction is disregarded. For example, if you had 20 CPUs, then 2 + (20 / 32) would equal 2 GCS processes.

If CLUSTER_DATABASE is set to false, then 0

If ASM, then 1

On AIX, sometimes CPU may increase dynamically, so be sure to check that the number of LMS processes has been adjusted accordingly.

The discussion on this issue can be carried out in Chinese community posts on how to diagnose the'gc cr multi block request'? in the RAC system.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.