Re: DB CPU is much lower than CPU Time Reported by TOP SQL consumers

From: Marcus Mönnig <mm_at_marcusmoennig.de>
Date: Tue, 13 Nov 2012 20:32:52 +0100
Message-ID: <CAFuw4v=EwjLtp-6TGmxK-qw1eWEJd6WoNwFcCU1LuxbO13VJkg_at_mail.gmail.com>



You are likely seeing double counting in the SQL CPU section. Looking at the execution counts, it might well be that e.g. the fourth SQL statement is called from within the first SQL statement. The other execution counts also kind of match to each other. Of course this is nothing but an educated guess and you would need to look at the (PL/)SQL code behind these SQL hash values to verify.

Is the system using scheduler jobs? In that case the anonymous (Oracle internal) PL/SQL code that calls the PL/SQL job action will collect all the CPU time and the called PL/SQL job action might e.g. have one SELECT statement that actually eats the CPU and the CPU time is also counted towards this statement.

Another case is a construct like:

Statement1: SELECT pl_sql_function(column) from ...

Where pl_sql_function is a PL/SQL function that returns a value based on another SQL Statement (Statement 2) and that is executed for each row returned by Statement 1.

Both the CPU times reported for Statement 1 AND 2 will include the CPU times burned by Statement 2.

Marcus

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 13 2012 - 20:32:52 CET

Original text of this message