Re: DB CPU is much lower than CPU Time Reported by TOP SQL consumers
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: free <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 25 Nov 2012 23:04:46 -0800 (PST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
