CPU usage by Oracle, performance metrics

We all know that Oracle has a statistics to calculate the Oracle usage of CPU with "v$sysstat.name=’CPU used by this session’"

But this statistic value got a problem, since the value will not be accounted/cumulated until the call on the cpu completed, For example, for a PL/SQL program if it takes one hour to complete the call, until that one hour the cpu usage will show zero only.

Rather, using ASH and some other metrics will give you the accurate usage of CPU by Oracle, by OS, CPU Demand.

So lets find out what are the metrics that are useful

1.Oracle CPU used
2.System CPU used
3.Oracle demand for CPU

col metric_name for a25
col metric_unit for a25
select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2;
METRIC_NAME                         VALUE METRIC_UNIT
------------------------------ ---------- ------------------------------
CPU Usage Per Sec              251.067016 CentiSeconds Per Second
CPU Usage Per Txn              5025.52477 CentiSeconds Per Txn
Host CPU Utilization (%)       11.6985845 % Busy/(Idle+Busy)
Database CPU Time Ratio        76.3291033 % Cpu/DB_Time

Converting them to into an useful meaningful numbers, gives accurate picture of the CPU usage by Oracle or by OS or how much oracle is waiting for CPU.

A script from Kyle Hailey's blog, very very useful, thanks to him.

Download Script from here:- Oracle_CPU

Output,

SQL> /

 CPU_TOTAL     CPU_OS    CPU_ORA CPU_ORA_WAIT     COMMIT     READIO       WAIT
---------- ---------- ---------- ------------ ---------- ---------- ----------
     4.247       .727      2.807         .713          0      1.049       .019

SQL>

Here my database is having 24 cpu cores and using 4.247 core usage in total,

Out of that, 
.727 burning for OS
2.807 consumed by Oracle
.713 running on CPU
0 for commit processing
1.049 for USER I/O processing
0.19 is other wait

Hope this helps in understanding the CPU Usage by oracle and whether and where the CPU used in the databases. This also provides a clue on how much the cpu utilized on the total count of the CPU in the server per database. Ofcourse you should run this script every hour and store the details in a table to picturise the same.

-Thanks
Geek DBA