Oracle Database Instance CPU Metrics

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

We have an emergency, we are trying to monitor CPU consumption per DB and we want to see these metrics. We deployed the oracle probe and it does not provide us with CPU consumption. We want to know with which probe we can achieve that. This is important for the institution since we are doing a survey to purchase iron from the servers. What we want to achieve is that on a server we have several DBs and we want to know the individual consumption of those DBs. For example, tell us that the DB_1 has 20% CPU utilized and DB_2 has 30% utlized.

Environment

• DX UIM 20.4 or higher
• oracle probe GA version

 

Cause

• Guidance

Resolution

How to create a custom checkpoint for the oracle probe
https://knowledge.broadcom.com/external/article/14959

Use V$OSSTAT or V$SYSMETRIC_HISTORY view to monitor system utilization statistics from the operating system.

Useful statistics contained in V$OSSTAT and V$SYSMETRIC_HISTORY include:

■ Number of CPUs
■ CPU utilization
■ Load
■ Paging
■ Physical memory

You can try using one or more of these Oracle Views:

SELECT * FROM V$OSSTAT

SELECT * FROM V$SYSMETRIC_HISTORY

Yet, creating an Oracle custom checkpoint would be the recommended approach.

You may consider this query shown below as well but first run it successfully to see the output:

https://stackoverflow.com/questions/49248378/query-to-fetch-the-oracle-cpu-usage-for-last-1-hour

select * from (select username,sid, round((cpu_usage/(select sum(value) total_cpu_usage from gv$sesstat t inner join gv$session  s on ( t.sid = s.sid ) inner join gv$statname n on ( t.statistic# = n.statistic#) where n.name like '%CPU used by this session%' and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24))*100,2) cpu_usage_per_cent, module_info,client_info from (select nvl(s.username,'Oracle Internal Proc.') username,s.sid,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info from gv$sesstat t inner join gv$session  s on ( t.sid = s.sid ) inner join gv$statname n on ( t.statistic# = n.statistic# ) where n.name like '%CPU used by this session%' and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24) s1 ) order by cpu_usage_per_cent desc fetch first 10 rows only

Reference:

https://stackoverflow.com/questions/49248378/query-to-fetch-the-oracle-cpu-usage-for-last-1-hour 

If the Oracle DB server is deployed on a Windows OS, you may also consider Windows performance counters but that would require running ntperf probe on a local robot on the DB server, e.g.,

https://docs.oracle.com/cd/E18787_01/doc/win.112/e10845/monitor.htm