Script to find Oracle database performance

articles: 

Comments

Hi,

Nice script. Thanks for sharing.

Dear Ashraf,

Thanks for the wonderful script. I love this script.

Regards.
Salih KM

Don't you think running AWR report will be easier?

Thanks for sharing the useful script.

Thanks for this script.........

Hi:

I want some help in obtaining %CPU used by Oracle. I want to plot a graph every 1 hour
interval.

I providing below two SQLs, but I do not understand why CPU + BCPU
is different from CPU_ORA_CONSUMED.

Thanks in advance,

Caesar
-------------------------------------------------------------------------------------------------

SELECT
     TO_CHAR(TRUNC(sample_time,'MI'),'DD-MON-YYYY HH24:MI:SS') AS sample_time,
     SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',0,1),0))/60 AS cpu,
     SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',1,0),0))/60 AS bcpu
FROM v$active_session_history
   WHERE sample_time>sysdate- INTERVAL '1' HOUR
   AND sample_time<=TRUNC(SYSDATE,'MI')
   GROUP BY TO_CHAR(TRUNC(sample_time,'MI'),'DD-MON-YYYY HH24:MI:SS')
order by 1 asc
/


SELECT
     TO_CHAR(TRUNC(begin_time,'MI'),'DD-MON-YYYY HH24:MI:SS') AS sample_time,
     VALUE/100 AS cpu_ora_consumed
   FROM v$sysmetric_history
   WHERE GROUP_ID=2
   AND metric_name='CPU Usage Per Sec'
order by 1 asc

Hi:

I do not understand that why in the second SQL, the V$BGPROCESS is not considered. Kindly clarify.

Thanks in advance - caesar

--------------------------------
select b.sid sid, nvl(b.username, p.name) username,
sum(value) total_user_mem
from v$statname c, v$sesstat a,
v$session b, v$bgprocess p
where a.statistic# = c.statistic#
and p.paddr (+) = b.paddr
and b.sid = a.sid
and c.name in ('session pga memory', 'session uga memory')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by b.sid, nvl(b.username, p.name)
order by 3 desc),
(select sum(value) total_mem
from v$statname c, v$sesstat a
where a.statistic# = c.statistic#
and c.name in ('session pga memory', 'session uga memory'))