-- v$sysstat Query

SELECT * FROM v$sysstat
WHERE name IN ('parse time cpu', 'parse time elapsed', 'parse count (hard)');
Sysstat

-- v$sesstat and v$session Queries

SELECT s.* FROM v$sesstat s WHERE s.statistic# = 12 ORDER BY s.value;


SELECT SUBSTR(ss.username,1,20) user_name, st.*
FROM v$sesstat st,  v$session ss
WHERE ss.sid = st.sid
AND   st.statistic# = 12 
AND   ss.username IS NOT NULL
ORDER BY st.value;

SELECT s.sid, n.name,s.value, n.class
FROM v$statname n,V$sesstat s
WHERE n.statistic# = s.statistic#
AND   s.value > 0
AND   s.sid IN
 (SELECT a.sid 
  FROM v$process p,v$session a
  WHERE p.addr =a.paddr
  AND   a.terminal = USERENV('terminal'))
ORDER BY n.class,n.name;


Select S.Statistic#, n.name, n.class,
 Sum(s.Value) memory_bytes,
 ROUND(Sum(s.Value)/1024/1024,3) memory_bytes
From V$sesstat S, V$statname n
Where S.Statistic# = n.Statistic#
AND   INSTR(n.name,'memory') > 0
GROUP BY S.Statistic#, n.name, n.class
ORDER BY s.statistic#;
Typical Memory

-- v$metric Query

SELECT * FROM v$metric;

SELECT * FROM v$metric
WHERE INSTR(metric_name,'CPU') > 0
ORDER BY end_time
;

-- v$process and v$process_Memory Query

SELECT p.program, p.spid, pm.category,
 pm.allocated, NVL(pm.used,0) used, pm.max_allocated
FROM v$process P, v$process_Memory pm
WHERE p.pid = pm.pid;

-- v$osstat Query

SELECT * FROM v$osstat;
osstat

SELECT name FROM v$osstat 
WHERE stat_name = 'NUM_CPU_CORES'
;

-- Host CPU Utilization (%) => Busy/(Busy+Idle)

SELECT
 TO_CHAR(begin_time,'mm/dd/yyyy') begin_date,
 ROUND(AVG(average),3) "AVG_CPU_% (Busy/(Busy+Idle))" 
FROM dba_hist_sysmetric_summary
WHERE          ----  'Host CPU Utilization (%)'
   INSTR(metric_name,'Host CPU Utilization') > 0
GROUP BY TO_CHAR(begin_time,'mm/dd/yyyy')
ORDER BY TO_CHAR(begin_time,'mm/dd/yyyy')
;

-- Host CPU Utilization (%) in last 24 hours greater than 80%

SELECT
 metric_name,
 TO_CHAR(begin_time,'mm/dd/yyyy hh24:mi') begin_time,
 TO_CHAR(end_time,'mm/dd/yyyy hh24:mi') end_time,
 ROUND(maxval,3) maxval,
 ROUND(average,3) average
FROM  dba_hist_sysmetric_summary
WHERE INSTR(metric_name,'Host CPU Utilization') > 0
AND  begin_time > SYSDATE-1
AND  maxval > 80
ORDER BY  begin_time
;