( select value cpu_count from v$parameter where name='cpu_count' ) parameter ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt, Group by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait') Sum(round(m.time_waited/m.INTSIZE_CSEC,3)) AAS I like to wrap all this up into a query with all the wait classes to see the overall load on Oracle including CPU consumed by Oracle, CPU demanded by Oracle and CPU used at the OS level: selectĭecode(n.wait_class,'User I/O','User I/O', The demand alert us to CPU starvation on a busy system. Now the demand for CPU is coming from ASH which is sampled so the accuracy is weak, but in larger sample sets or busier systems it’s pretty darn good. So the demand for CPU was higher than the amount consumed. ( select value cpu_count from v$parameter Round((prcnt.busy*parameter.cpu_count)/100,3) AAS We can only add that, AFAIK, by joing in ASH: select 'CPU_ORA_CONSUMED' CLASS, Not a very active system, and we can look at an active system later, but what I wanted to point out is that this query is missing an important statistic: the demand for CPU by Oracle. Where metric_name='Host CPU Utilization (%)'Īn AAS of 1 is equivalent to 100% of a core, so, OS CPU is about 2% of a core and of that Oracle used 0.2% of a core. Round((prcnt.busy*parameter.cpu_count)/100,3) sAAS Now the question is how do we convert these to something useful? For me I put it into the equivalent of AAS and compare it to the core count: select 'CPU_ORA_CONSUMED' CLASS, Select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2 ĬPU Usage Per Sec 251.067016 CentiSeconds Per SecondĬPU Usage Per Txn 5025.52477 CentiSeconds Per Txn This is awesome as we can see how busy the system is and how much of the CPU Oracle is responsible for: col metric_name for a25 Starting in 10g Oracle records both the CPU used by the instance as well as the load on the system in v$sysmetric. There are 3 kinds of CPU in the Oracle stats.