Oracle OS calls are used to calculate CPU statistics, which show how much CPU is used in a certain amount of time (i.e. certain quantity of code). The CPU figures indicate cycles consumed, not time, therefore they do not account for any wait time in the run queue for the CPU.
Contrarily, Oracle ASH displays a list of all Oracle sessions that are active from the standpoint of Oracle, meaning that they are neither idle or awaiting a non-idle wait event like I/O. Thus, ASH takes into account both the time spent using CPU burning cycles and the time spent waiting to use the CPU.
As a result, we can take the time "On CPU" from ASH, deduct the CPU consumption from Oracle statistics, and then add back the time that Oracle sessions spent waiting to access the CPU.
You can use below query to check for the CPU usage.
SELECT se.username, ss.sid,ROUND(value/100)"CPU Usage"FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE'%CPU used by this session%'AND se.sid = ss.SIDAND se.username ISNOTNULLORDERBY value DESC;
Oracle OS calls are used to calculate CPU statistics, which show how much CPU is used in a certain amount of time (i.e. certain quantity of code). The CPU figures indicate cycles consumed, not time, therefore they do not account for any wait time in the run queue for the CPU.
Contrarily, Oracle ASH displays a list of all Oracle sessions that are active from the standpoint of Oracle, meaning that they are neither idle or awaiting a non-idle wait event like I/O. Thus, ASH takes into account both the time spent using CPU burning cycles and the time spent waiting to use the CPU.
As a result, we can take the time "On CPU" from ASH, deduct the CPU consumption from Oracle statistics, and then add back the time that Oracle sessions spent waiting to access the CPU.
You can use below query to check for the CPU usage.
SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage" FROM v$session se, v$sesstat ss, v$statname st WHERE ss.statistic# = st.statistic# AND name LIKE '%CPU used by this session%' AND se.sid = ss.SID AND se.username IS NOT NULL ORDER BY value DESC;