Script to find Oracle database performance
Submitted by ashrafb on Mon, 2011-11-21 15:43
articles:
This single script provides the overall picture of the database in terms of Waits events, Active/Inactive killed sessions, Top Processes (physical I/O, logical I/O, memory and CPU processes), Top CPU usage by users, etc.
set serveroutput on declare cursor c1 is select version from v$instance; cursor c2 is select host_name , instance_name , to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime , to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime from v$instance; cursor c4 is select * from (SELECT count(*) cnt, substr(event,1,50) event FROM v$session_wait WHERE wait_time = 0 AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message', 'SQL*Net message from client') GROUP BY event ORDER BY 1 DESC) where rownum <6; cursor c5 is select round(sum(value)/1048576) as sgasize from v$sga; cursor c6 is select round(sum(bytes)/1048576) as dbsize from v$datafile; cursor c7 is select 'top physical i/o process' category, sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io 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 ('physical reads', 'physical writes', 'physical reads direct', 'physical reads direct (lob)', 'physical writes direct', 'physical writes direct (lob)') 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_io from v$statname c, v$sesstat a where a.statistic# = c.statistic# and c.name in ('physical reads', 'physical writes', 'physical reads direct', 'physical reads direct (lob)', 'physical writes direct', 'physical writes direct (lob)')) where rownum < 2 union all select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io 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 ('consistent gets', 'db block gets') 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_io 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 ('consistent gets', 'db block gets')) where rownum < 2 union all select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2) from (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')) where rownum < 2 union all select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2) from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu 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 = 'CPU used by this session' 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_cpu 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 = 'CPU used by this session') where rownum < 2; cursor c8 is select username, sum(VALUE/100) cpu_usage_sec from v$session ss, v$sesstat se, v$statname sn where se.statistic# = sn.statistic# and name like '%CPU used by this session%' and se.sid = ss.sid and username is not null and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') group by username order by 2 desc; begin dbms_output.put_line ('Database Version'); dbms_output.put_line ('-----------------'); for rec in c1 loop dbms_output.put_line(rec.version); end loop; dbms_output.put_line( chr(13) ); dbms_output.put_line('Hostname'); dbms_output.put_line ('----------'); for rec in c2 loop dbms_output.put_line(rec.host_name); end loop; dbms_output.put_line( chr(13) ); dbms_output.put_line('SGA Size (MB)'); dbms_output.put_line ('-------------'); for rec in c5 loop dbms_output.put_line(rec.sgasize); end loop; dbms_output.put_line( chr(13) ); dbms_output.put_line('Database Size (MB)'); dbms_output.put_line ('-----------------'); for rec in c6 loop dbms_output.put_line(rec.dbsize); end loop; dbms_output.put_line( chr(13) ); dbms_output.put_line('Instance start-up time'); dbms_output.put_line ('-----------------------'); for rec in c2 loop dbms_output.put_line( rec.starttime ); end loop; dbms_output.put_line( chr(13) ); for b in (select total, active, inactive, system, killed from (select count(*) total from v$session) , (select count(*) system from v$session where username is null) , (select count(*) active from v$session where status = 'ACTIVE' and username is not null) , (select count(*) inactive from v$session where status = 'INACTIVE') , (select count(*) killed from v$session where status = 'KILLED')) loop dbms_output.put_line('Active Sessions'); dbms_output.put_line ('---------------'); dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed '); end loop; dbms_output.put_line( chr(13) ); dbms_output.put_line( 'Sessions Waiting' ); dbms_output.put_line( chr(13) ); dbms_output.put_line('Count Event Name'); dbms_output.put_line('----- -----------------------------------------------------'); for rec in c4 loop dbms_output.put_line(rec.cnt||' '||rec.event); end loop; dbms_output.put_line( chr(13) ); dbms_output.put_line('----- -----------------------------------------------------'); dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes'); dbms_output.put_line ('---------------'); for rec in c7 loop dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used); end loop; dbms_output.put_line('------------------------------------------------------------------'); dbms_output.put_line('TOP CPU users by usage'); dbms_output.put_line ('---------------'); for rec in c8 loop dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec); dbms_output.put_line ('---------------'); end loop; end;
Please visit the following website for more articles like this:
www.bash-dba.com
»
- ashrafb's blog
- Log in to post comments
Comments
Nice Script
Hi,
Nice script. Thanks for sharing.
Excellent Script
Dear Ashraf,
Thanks for the wonderful script. I love this script.
Regards.
Salih KM
Don't you think running AWR
Don't you think running AWR report will be easier?
Script to find Oracle database performance.
Thanks for sharing the useful script.
Thanks for this
Thanks for this script.........
CPU used
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,
Memory usage - cursor C8
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'))