rdbms 11.2.0.4
参考文档：
RAC 12c DDL的性能问题：节点的CPU数量不同导致大量的"enq: IV - contention" (Doc ID 2440133.1)
CPU count change, "enq: PS-contention" (Doc ID 2548314.1)
--两个节点的rac，将在虚拟机中，将其中一个节点的cpu设置为3，另一个节点cpu设置为1
将其中一个节点的私网网络限制为租用线路T1（1.544Mbps），另一个节点的私网不限制。
主要是为了测试以下内容：
1 等待事件 enq: PS - contention
2 等待事件 enq: IV - contention
3 AWR中RAC集群中的一些参数变化，比如Global Cache and Enqueue Services - Messaging Statistics、Interconnect Ping Latency Stats等
4 模拟ADDM中的Interconnect Latency延迟 （这里没有模拟出来，因为测试环境没有数据在跑）
-- CPU_COUNT 不同，引起的一些等待事件
在其中的一个节点上 ，可以看到CPU是1个 ，有等待事件enq: PS - contention
WORKLOAD REPOSITORY report forDB Name DB Id InstanceInst Num Startup Time ReleaseRAC------------ ----------- ------------ -------- --------------- ----------- ---TEST2389195792 test22 11-Jan-22 08:57 11.2.0.4.0 YESHost Name PlatformCPUs Cores Sockets Memory(GB)---------------- -------------------------------- ---- ----- ------- ----------o11gr22Linux x86 64-bit11 1 3.73Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Tota Wait % DBEvent Waits Time Avg(ms) time Wait Class------------------------------ ------------ ---- ------- ------ ----------DB CPU 1.4 31.3enq: PS - contention 11 .984 20.5 OtherPX Deq: Slave Session Stats 17 .421 7.9 Otherlibrary cache lock 25 .314 7.5 Concurrenclibrary cache pin 37 .3 8 6.8 ConcurrencIPC send completion sync 17 .317 6.2 Othergc current grant busy22 .2 9 4.2 Clusterdb file sequential read 64 .2 3 4.0 User I/Oenq: WF - contention 3 .258 3.9 Otherrow cache lock
在另一个节点上，可以看到CPU是3 ，有等待事件enq: PS - contention
WORKLOAD REPOSITORY report forDB Name DB Id InstanceInst Num Startup Time ReleaseRAC------------ ----------- ------------ -------- --------------- ----------- ---TEST2389195792 test33 11-Jan-22 08:58 11.2.0.4.0 YESHost Name PlatformCPUs Cores Sockets Memory(GB)---------------- -------------------------------- ---- ----- ------- ----------o11gr23Linux x86 64-bit33 1 3.73Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Tota Wait % DBEvent Waits Time Avg(ms) time Wait Class------------------------------ ------------ ---- ------- ------ ----------db file scattered read4 1.6398 39.3 User I/Odb file sequential read 56 1.323 32.4 User I/OIPC send completion sync 33 .721 17.5 OtherDB CPU.5 12.8name-service call wait2 .290 4.4 Otherenq: PS - contention 9 .112 2.6 Othergc cr multi block request 6 .116 2.4 ClusterSQL*Net break/reset to client 4 .123 2.3 ApplicatioPX Deq: Slave Session Stats 12 .1 8 2.3 Othergc cr block 2-way 13 0 3 1.1 Cluster
-- 查看RAC的统计信息，可以看到Avg global cache cr block receive time (ms):、Avg message sent queue time on ksxp (ms):较大 6ms-8ms之间
Global Cache and Enqueue Services - Workload Characteristics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg global enqueue get time (ms):1.1Avg global cache cr block receive time (ms):18.5Avg global cache current block receive time (ms):25.2Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.0Global cache log flushes for cr blocks served %:0.7Avg global cache cr block flush time (ms):0.0Avg global cache current block pin time (ms):0.0Avg global cache current block send time (ms):0.0Global cache log flushes for current blocks served %:6.4Avg global cache current block flush time (ms):1.3Global Cache and Enqueue Services - Messaging Statistics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg message sent queue time (ms):0.0Avg message sent queue time on ksxp (ms):6.3Avg message received queue time (ms):0.0Avg GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages: 69.45% of indirect sent messages: 30.36% of flow controlled messages:0.19-------------------------------------------------------------Global Cache and Enqueue Services - Workload Characteristics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg global enqueue get time (ms):1.8Avg global cache cr block receive time (ms):8.6Avg global cache current block receive time (ms):21.4Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.0Global cache log flushes for cr blocks served %:0.8Avg global cache cr block flush time (ms):0.0Avg global cache current block pin time (ms):0.0Avg global cache current block send time (ms):0.0Global cache log flushes for current blocks served %:6.6Avg global cache current block flush time (ms):1.4Global Cache and Enqueue Services - Messaging Statistics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg message sent queue time (ms):0.0Avg message sent queue time on ksxp (ms):8.6Avg message received queue time (ms):0.0Avg GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages: 60.48% of indirect sent messages: 38.65% of flow controlled messages:0.87-------------------------------------------------------------
查看网络延迟信息 ，发现节点之间的ping，有较大的值 ，
Interconnect Ping Latency StatsDB/Inst: TEST/test2 Snaps: 8-9-> Ping latency of the roundtrip of a message from this instance to -> target in-> The target instance is identified by an instance number.-> Average and standard deviation of ping latency is given in miliseconds-> for message sizes of 500 bytes and 8K.-> Note that latency of a message from the instance to itself is used as-> control, since message latency can include wait for CPUTarget 500B Pin Avg LatencyStddev 8K Ping Avg LatencyStddevInstance Count 500B msg 500B msg Count8K msg8K msg--------- -------- ----------- ----------- -------- ----------- -----------1 0 .00 .00 0 .00 .002 89 .46 .27 89 .46 .273 89 5.98 18.37 89 8.94 28.77------------------------------------------------------Interconnect Ping Latency StatsDB/Inst: TEST/test3 Snaps: 8-9-> Ping latency of the roundtrip of a message from this instance to -> target in-> The target instance is identified by an instance number.-> Average and standard deviation of ping latency is given in miliseconds-> for message sizes of 500 bytes and 8K.-> Note that latency of a message from the instance to itself is used as-> control, since message latency can include wait for CPUTarget 500B Pin Avg LatencyStddev 8K Ping Avg LatencyStddevInstance Count 500B msg 500B msg Count8K msg8K msg--------- -------- ----------- ----------- -------- ----------- -----------1 0 .00 .00 0 .00 .002 90 4.10 14.19 90 9.75 27.753 90 .18 .15 90 .17 .15------------------------------------------------------
-- 将CPU_COUNT设置为两个节点一样，将网络恢复正常，再次查看awr报告
其中一个节点上，显示一个CPU ，没有enq：PS-contention 之类的等待事件
WORKLOAD REPOSITORY report forDB Name DB Id InstanceInst Num Startup Time ReleaseRAC------------ ----------- ------------ -------- --------------- ----------- ---TEST2389195792 test22 11-Jan-22 08:57 11.2.0.4.0 YESHost Name PlatformCPUs Cores Sockets Memory(GB)---------------- -------------------------------- ---- ----- ------- ----------o11gr22Linux x86 64-bit11 1 3.73Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Tota Wait % DBEvent Waits Time Avg(ms) time Wait Class------------------------------ ------------ ---- ------- ------ ----------DB CPU 1.4 46.1reliable message1 .6564 19.0 Othername-service call wait4 .368 9.1 Othergc current block 2-way 271 .3 1 8.8 Clustergc current grant busy 205 .1 1 4.8 Clustergc cr block 2-way 137 .1 1 4.3 Clusterdirect path write 59 .1 2 3.6 User I/Oundo segment extension6 .1 9 1.8 Configuratgc current block busy19 0 2 1.3 Clusterdb file sequential read 11 0 3 1.3 User I/O
另一个节点上，显示一个CPU，虽然显示是3个。但是在cpu_count上做了限制 。没有enq：PS-contention 之类的等待事件
WORKLOAD REPOSITORY report forDB Name DB Id InstanceInst Num Startup Time ReleaseRAC------------ ----------- ------------ -------- --------------- ----------- ---TEST2389195792 test33 11-Jan-22 08:58 11.2.0.4.0 YESHost Name PlatformCPUs Cores Sockets Memory(GB)---------------- -------------------------------- ---- ----- ------- ----------o11gr23Linux x86 64-bit33 1 3.73SQL> show parameter cpu_couNAME TYPE VALUE------------------------------------ ----------- ------------------------------cpu_count integer1SQL> Top 10 Foreground Events by Total Wait Time~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Tota Wait % DBEvent Waits Time Avg(ms) time Wait Class------------------------------ ------------ ---- ------- ------ ----------DB CPU 413. 98.1name-service call wait4 .371.1 Otheros thread startup2 .2102.0 Concurrencgc current block 2-way 139 .2 1.0 ClusterDFS lock handle 6 .124.0 Otherrow cache lock203 .1 1.0 Concurrenclog file sync 21 .1 6.0 Commitdb file sequential read 8 .114.0 User I/Olibrary cache pin 99 0 0.0 Concurrenclibrary cache lock 79 0 1.0 ConcurrencWait Classes by Total Wait Time
-- 查看RAC的统计信息，可以看到Avg global cache cr block receive time (ms):、Avg message sent queue time on ksxp (ms):较小，小于2ms
Global Cache and Enqueue Services - Workload Characteristics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg global enqueue get time (ms):0.1Avg global cache cr block receive time (ms):0.6Avg global cache current block receive time (ms):0.9Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.0Global cache log flushes for cr blocks served %:15.6Avg global cache cr block flush time (ms):0.8Avg global cache current block pin time (ms):0.0Avg global cache current block send time (ms):0.0Global cache log flushes for current blocks served %:2.8Avg global cache current block flush time (ms):1.3Global Cache and Enqueue Services - Messaging Statistics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg message sent queue time (ms):0.0Avg message sent queue time on ksxp (ms):0.7Avg message received queue time (ms):0.0Avg GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages: 59.54% of indirect sent messages: 39.84% of flow controlled messages:0.61------------------------------------------------------------- Global Cache and Enqueue Services - Workload Characteristics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg global enqueue get time (ms):0.2Avg global cache cr block receive time (ms):1.0Avg global cache current block receive time (ms):1.1Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.0Global cache log flushes for cr blocks served %:1.7Avg global cache cr block flush time (ms):0.0Avg global cache current block pin time (ms):0.0Avg global cache current block send time (ms):0.0Global cache log flushes for current blocks served %:2.7Avg global cache current block flush time (ms):0.6Global Cache and Enqueue Services - Messaging Statistics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avg message sent queue time (ms):0.0Avg message sent queue time on ksxp (ms):0.6Avg message received queue time (ms):0.0Avg GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages: 64.42% of indirect sent messages: 34.47% of flow controlled messages:1.10-------------------------------------------------------------
-- 查看两个节点的网络延迟 ，延迟减小了很多 。
Interconnect Ping Latency Stats DB/Inst: TEST/test2 Snaps: 12-13-> Ping latency of the roundtrip of a message from this instance to -> target in-> The target instance is identified by an instance number.-> Average and standard deviation of ping latency is given in miliseconds-> for message sizes of 500 bytes and 8K.-> Note that latency of a message from the instance to itself is used as-> control, since message latency can include wait for CPUTarget 500B Pin Avg LatencyStddev 8K Ping Avg LatencyStddevInstance Count 500B msg 500B msg Count8K msg8K msg--------- -------- ----------- ----------- -------- ----------- -----------1 0 .00 .00 0 .00 .002107 .54 .38107 .53 .383107 .77 1.98107 .77 1.98------------------------------------------------------Interconnect Ping Latency Stats DB/Inst: TEST/test3 Snaps: 12-13-> Ping latency of the roundtrip of a message from this instance to -> target in-> The target instance is identified by an instance number.-> Average and standard deviation of ping latency is given in miliseconds-> for message sizes of 500 bytes and 8K.-> Note that latency of a message from the instance to itself is used as-> control, since message latency can include wait for CPUTarget 500B Pin Avg LatencyStddev 8K Ping Avg LatencyStddevInstance Count 500B msg 500B msg Count8K msg8K msg--------- -------- ----------- ----------- -------- ----------- -----------1 0 .00 .00 0 .00 .002107 1.13 5.42107 1.22 5.443107 .18 .14107 .17 .14------------------------------------------------------
总结
1 rac之间对于节点之间的硬件要求比较严格，否则会引起一些等待事件（enq: PS-contention、enq: IV - contention等）
2 rac节点之间的网络问题，也可以导致ADDM中大量的Interconnect Latency建议，以及rac节点之间的一些其他等待事件（比如gc之类的）
END
如果觉得《enq: IV - contention enq: PS-contention》对你有帮助，请点赞、收藏，并留下你的观点哦！
