cursor: pin S waits, sporadic CPU spikes and systematic troubleshooting | Tanel Poder Consulting

[giscus[bot]]

cursor: pin S waits, sporadic CPU spikes and systematic troubleshooting | Tanel Poder Consulting

I recently consulted one big telecom and helped to solve their sporadic performance problem which had troubled them for some months. It was an interesting case as it happened in the Oracle / OS touchpoint and it was a product of multiple “root causes”, not just one, an early Oracle mutex design bug and a Unix scheduling issue – that’s why it had been hard to resolve earlier despite multiple SRs opened etc. - Linux, Oracle, SQL performance tuning and troubleshooting - consulting & training.

https://tanelpoder.com/2010/04/21/cursor-pin-s-waits-sporadic-cpu-spikes-and-systematic-troubleshooting/

[osk-8]

Hello, great article!
We have similar problems in our database cluster Exadata Cloud at Customer (ExaCC) v10 with Oracle Database 19.22.0.0.0. We are experiencing problems with the LREG process reaching 100% CPU. Another apparent behavior is the lack of new sessions established with the suspended instance. It appears that the Load Balancer is redirecting all traffic to the other database server instance.

[tanelpoder]

Yeah, I've heard about the LREG problem too, probably a bug, some object leak, uncontrolled structure growth bug in Oracle LREG's codepath... (one symptom reported is a high number of service stats entries used by informing listeners about an instance's busyness, perhaps Oracle is not cleaning these up as needed and LREG ends up being 100% on cpu as scanning these longer lists takes longer as the leak grows)