SQL with high CPU time - PL/SQL Function
 Case Study Summary

The following describes an example case scenario where a SQL statement using PL/SQL function caused CPU spikes on the database server.

The list of steps include:

 Step 1 : Identify top SQL with high CPU time

 Step 2 : Time analysis on SQL Stats shows high PL/SQL execution time

The assumption that the elapsed time is the sum of other subcomponents doesn’t hold well here. Oracle double counted PL/SQL execution time as part of CPU time.

 Step 3 : Check SQL text and identify the problem PL/SQL function

As we can see here, a PL/SQL function was applied to all rows in the data set. Due to the fact that the data set contained a large amount of data, the usage of PL/SQL function caused a considerable amount of CPU time.

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