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

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Sun, 25 Nov 2012 23:04:46 -0800 (PST)
Message-ID: <1353913486.5458.YahooMailNeo_at_web161302.mail.bf1.yahoo.com>



It is not possible for cpu consumed by all sql executions to be greater than DB CPU, unless it is either a bug or double counting due to Pl/sql as mentioned by others. Try running below query (assuming no sql aged out of library cache and no invalidations occured, otherwise run the below query at the begining and end of the sample period) to determine the approximate cpu consumption by sql executions versus DB CPU.

select a.cpu1 DB_CPU, b.cpu1 SQL_CPU from
(select value cpu1 from v$sys_time_model where stat_name='DB CPU') a,
(select sum(cpu_time) cpu1 from v$sql where (command_type= 47) or (command_type <> 47 and program_id = 0)) b
/

Keep in mind that most metrics in v$sql wrap over once the number reach 2 power 32, and same numbers carry forward to stats$sql_summary table in statspack. It is recommended to use 15 minute snapshot intervals on OLTP databases.

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 26 2012 - 08:04:46 CET

Original text of this message