Investigating a CPU Spike and Resource/cluster Hang in Oracle RAC Environment
Introduction: In a complex Oracle Real Application Clusters (RAC) environment, unexpected issues can arise, leading to system instability and performance degradation. In this blog post, we will delve into a specific incident involving a CPU spike and resource hang that occurred during the execution of a high parallelism SQL job. We will analyze the root causes and explore potential solutions to mitigate such problems in the future. The Incident: On a particular day between 2:00 PM and 6:30 PM IST, an SQL job with a Degree of Parallelism (DoP) set to 200 was initiated on Node#3 around 2:12 PM IST. Soon after, the CPU Idle% dropped to single digits, reaching as low as 1% at 3:10 PM IST. This performance degradation led to Node#6 being evicted following a dirty detach reconfiguration. During the incident, it was observed that the shutdown process remained hung for an extended period, and the system was only restarted at 4:44 PM as per the Syslog. The Cluster Ready Services (CRS) stack cam...