Useful Scripts.
Below are some useful scripts that i found in one of my old hard drives. If you want to gather stat reports for the oracle ser sessions you could use the following scripts. Right before posting this i tried them on 8i, 9i, and 10g.
To find total cpu usage by each/all sessions, you could use the following sql statement.
Select nvl ( sess.username, 'oracle_process' ) username
, sess_stat.sid session_id, value cpu_usage
From v$session sess
, v$sesstat sess_stat
, v$statname stat_name
Where sess_stat.statistic# = stat_name.statistic#
And name like '%cpu used by this session%'
And sess_stat.sid = sess.sid
And value > 0
Order by value desc
/
To find total resource usage by each user, you could use the following sql statement.
Select sess.sid, nvl ( sess.username, 'oracle_process' ) username
, sess_stat.sid session_id, stat_name.name statistic, value cpu_usage
From v$session sess
, v$statname stat_name
, v$sesstat sess_stat
Where sess_stat.sid = sess.sid
And stat_name.statistic# = sess_stat.statistic#
And value != 0
Order by value desc
, sess.username
, sess.sid
, stat_name.name
/
To find session i/o activity by each user, you could use the following sql statement.
Select nvl ( sessions.username, 'oracle_process' ) username, osuser os_user
, process pid, sessions.sid sid, serial#, physical_reads, block_gets
, consistent_gets, block_changes, consistent_gets, block_changes
, consistent_changes
From v$session sessions
, v$sess_io session_io
Where sessions.sid = session_io.sid
Order by physical_reads desc
, sessions.username
/
hope this helps…
Kirtan
This entry was posted on Wednesday, March 28th, 2007 at 2:22 pm and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.