SQL with high CPU time - Merge & Sort Operations

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.

 Case Study Summary

The following example describes a case scenario of how to troubleshooting when CPU spikes happened on a database host and a SQL statement with merge & sort operations was found out to be the root cause of CPU spikes.

The list of steps include:

 Step 1 : Check dimension 'SQL' to identify SQL with high CPU time

 Step 2 : Relate dimension 'SQL' to dimension 'wait event' to confirm

 Step 3 : Check SQL stats to identify the pattern

The disk reads was higher than buffer gets, which can be explained that the sorting of large amount of data involves writing from PGA to TEMP tablespace directly.

 Step 4 : Check SQL text and SQL plan to confirm

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us