CPU spikes are tightly coupled with database performance issues because CPU spikes are signs of contention and starvation of system resources.
When we trouble shooting CPU spike issue on database servers, SQL with high CPU time needs to be on the check list
unless we are sure the top CPU consumers come from outside of the database.
SQL with high CPU time could be the root cause of CPU spikes, or could be just the symptom:
- CPU bound SQL statements have the potential to be the root cause of CPU spikes. SQL statements which adopt algorithms
to consume CPU cycles aggressively, such as sort, merge, hash, etc., always show high CPU time and could directly contribute to CPU spikes.
SQL statements with high buffer gets or high parses could also have significant CPU time due to Oracle memory's allocation and management.
Reviewing application logic and ensuring optimal SQL plan are needed to address CPU spike issues caused by this type of SQL statements.
- For those SQL statements which are not CPU bound or donít consumes high CPU time at normal conditions, their CPU time could be extended
dramatically during CPU spikes, which indicates they are the symptoms or victims of contention. Drilling down their blocking sessions or wait events
hold the key to dig out the real offender.