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

[fault handling] enq IV-contention case of queue waiting

1.1 BLOG document structure map

1.2Preface 1.2.1 introduction and precautions for technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) obsession:

Enq IV-contention case of ① queue waiting (focus)

Tips:

① article has been updated synchronously on itpub (http://blog.itpub.net/26736162), blog Garden (http://www.cnblogs.com/lhrbest) and Wechat official account (xiaomaimiaolhr).

All the code used in the ② article, related software, related materials and the pdf version of this article can be downloaded from the cloud disk of wheat seedlings. The cloud disk address of wheat seedlings can be found at: http://blog.itpub.net/26736162/viewspace-1624453/.

③ if the code format of the web page article is out of order, please download the document in pdf format to read.

④ in this BLOG, the output part of the code is generally placed in a table with one row and one column.

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

1.3 Fault analysis and resolution process

1.3.1 introduction of database environment

Project

Source db

Db Typ

RAC

Db version

12.1.0.2.0

Db storage

ASM

OS version and kernel version

SuSE Linux Enterprise Server (SLES 11) 64 bit

1.3.2 AWR analysis

Here is a brief analysis of Up Time (hrs), several other indicators are very familiar. The "Up Time (hrs)" in the table represents the number of hours between the start of the database instance and the end of this snapshot. For example, the start time for database instance 1 in this AWR is "2016-08-11 20:51" and the snapshot end time is "2016-12-14 21:00", so the "Up Time (hrs)" is about 125.006 days, and the conversion to hours is about 3000.14 hours, as shown below:

SYS@lhrdb > SELECT trunc (UP_TIME_D,3), trunc (trunc (UP_TIME_D,3) * 24jue 2) UP_TIME_HRS FROM (SELECT (TO_DATE ('2016-12-14 21 YYYY-MM-DD HH24:MI')-TO_DATE (' 2016-08-11 20 trunc 51), 'YYYY-MM-DD HH24:MI') UP_TIME_D FROM DUAL)

TRUNC (UP_TIME_D,3) UP_TIME_HRS

--

125.006 3000.14

You can see that node 1 has a higher load, while in ADDM, special classes have more wait events. Next, take a look at the part of the waiting event:

You can see that enq: IV-contention and DFS lock handle wait heavily. The name enq: IV-contention needs to be mentioned here. In AWR, both IV and-are preceded by 1 space, while those recorded in the database are followed by 2 spaces, as follows:

Therefore, you need to pay attention when using search.

Based on the value of the p1 parameter in ASH:

SYS@lhrdb > SELECT CHR (BITAND (1230372867,-16777216) / 16777215) | |

2 CHR (BITAND (1230372867, 16711680) / 65535) "LOCK"

3 BITAND (1230372867, 65535) "MODE"

4 FROM DUAL

LO MODE

IV 3

1.3.3 enq: IV-contention to solve SELECT *

FROM V$EVENT_NAME A

WHERE A.NAME LIKE'% enq: IV-contention%'

This wait event is unique to 12c, which has about 500 more wait events than 11g. For this question, refer to MOS:12c RAC DDL Performance Issue: High "enq: IV-contention" etc if CPU Count is Different (document ID 2028503.1)

The fix will be included in future PSU, patch exists for certain platform/version.

The workaround is to set the following parameter to the highest value in the cluster and restart:

_ ges_server_processes

To find out the highest value, run the following grep on each node:

Ps-ef | grep lmd

The wait event is mainly caused by the inconsistency of the variable cpu_count on the two nodes of 12c RAC.

You can see from the AWR that the CPU of node 1 is 48 and the CPU of node 2 is 96.

You can see the change history of the parameter CPU_COUNT from dba_hist_parameter:

SQL > SHOW PARAMETER CPU

NAME TYPE VALUE

-

Cpu_count integer 96

Parallel_threads_per_cpu integer 2

Resource_manager_cpu_allocation integer 96

SQL > select snap_id, INSTANCE_NUMBER,PARAMETER_NAME,VALUE from dba_hist_parameter where PARAMETER_NAME='cpu_count' order by snap_id

SNAP_ID INSTANCE_NUMBER PARAMETER_NAME VALUE

. .

3368 1 cpu_count 48

3369 1 cpu_count 48

3369 2 cpu_count 48

3370 1 cpu_count 48

3371 1 cpu_count 48

3372 1 cpu_count 48

3373 1 cpu_count 48

3374 1 cpu_count 48

3375 2 cpu_count 96

3375 1 cpu_count 48

3376 1 cpu_count 48

3376 2 cpu_count 96

3377 1 cpu_count 48

3377 2 cpu_count 96

3378 2 cpu_count 96

3378 1 cpu_count 48

3379 1 cpu_count 48

3379 2 cpu_count 96

.

Query alarm log: more alert* | grep-I Cpu can also obtain the changes of CPU.

Ask the customer to know that they have adjusted the CPU resources of the system, so that the CPU_COUNT on node 2 changes automatically, causing enq: IV-contention to wait.

If the number of CPU of the host changes, then when the host restarts, the value of the cpu_count parameter of the database will change accordingly, which belongs to the operating system dependent parameter.

After adjusting the number of CPU of the host, the wait event disappears.

1.4 MOS1.4.1 12c RAC DDL Performance Issue: High "enq: IV-contention" etc if CPU Count is Different (document ID 2028503.1)

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Information in this document applies to any platform.

SYMPTOMS

12c RAC database seeing high "enq: IV-contention":

From awr report:

Top 10 Foreground Events by Total Wait Time

= =

Event Waits Total Wait Time (sec) Wait Avg (ms)% DB time Wait Class

Enq: IV-contention 52914 1688.4 31.91 42.8 Other

Row cache lock 44865 896.8 19.99 22.7 Concurrency

Tkprof of 10046 trace of SQL statement shows the same event in the top:

Event waited on Times Max. Wait Total Waited

-- Waited--

Enq: IV-contention 6017 0.32 287.68

Row cache lock 957 0.20 7.48

Library cache lock 631 0.13 15.10

Library cache pin 616 0.11 14.54

CAUSE

Cluster nodes have different CPU count resulting in different number of LMD processes, on one node it has two while on the other it has three.

The issue is due to the following bug:

BUG 21293056-PERFORMANCE DEGRADATION OF GRANT STATEMENT AFTER 12C UPGRADE

Which is closed as duplicate of:

BUG 21395269-ASYMMETRICAL LMDS CONFIGURATION IN A CLUSTER LEADS TO POOR MESSAGE TRANSFER

SOLUTION

The fix will be included in future PSU, patch exists for certain platform/version.

The workaround is to set the following parameter to the highest value in the cluster and restart:

Ps-ef | grep lmd

About Me

.

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/viewspace-2131320/

● article blog park address: http://www.cnblogs.com/lhrbest/p/6218042.html

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (642808185), indicate the reason for adding

● was completed at Agricultural Bank of China from 15:00 on 2016-09-01 to 19:00 on 2016-10-20.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

The mobile phone captain clicks the image below to identify the QR code or the Wechat client scans the following QR code to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, and learn the most practical database technology for free.

Cdn.qqmail.com/zh_CN/htmledition/p_w_picpaths/function/qm_open/ico_mailme_02.png ">

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.