Little Statspack Performance monitoring SQL

Well, you know Average Active Sessions, if you don’t have AWR than this is a way how to get some information, use Statspack. The query displays how many users were working(active) in a second.

select t.snap_time,round(t.dbtime/t.elapsed,2) as "AvgActiveSessions" from (
nvl(round((l.value-lag(l.value) over (order by l.snap_id))/1000000,2),0) as dbtime,
nvl((s.snap_time-lag(s.snap_time) over (order by s.snap_id asc))*24*60*60,0) as elapsed
from stats$snapshot s, v$sys_time_model m, stats$sys_time_model l where
m.stat_id=l.stat_id and s.snap_id=l.snap_id
and m.stat_name='DB Time'
and snap_time between &timelow and &timehigh
order by 1 asc ) t
where t.dbtime!=0

The same query can be used for the statname DB CPU, so it means it should be smaller than the AAS. It says how much CPU seconds were used in a second.

select g.snap_time,round(g.cpu/g.elapsed,2) from (
round((n.value-lag(n.value,1,n.value) over (order by n.snap_id)),0)/100 cpu,
nvl((t.snap_time-lag(t.snap_time) over (order by t.snap_id asc))*24*60*60,0) as elapsed
from stats$sysstat n,stats$snapshot t
where n.snap_id in (select snap_id from stats$snapshot where snap_time between &timelow and &timehigh )
and ='CPU used by this session' and t.snap_id=n.snap_id order by 1,2 ) g
where g.cpu!=0

This query shows the same information about the CPU usage, of course it’s not the same as the underlying tables are not refreshed in the same time (v$sysstat and v$sys_time_model – I know these are views, but I mean the underlying tables)

AAS = 1,2
DB CPU = 0,8

There were 1,2 active sessions per second and they used 0,8 seconds per Second on CPU. Great.. now if you divide this 0,8 with CPU_COUNT*100 you get the CPU usage in %


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s