Often we are in a hurry needing just a rough idea about what´s going on in our database right now.
Tanel Poder one of the first OCM in world wrote a very easy explanation that I summarize with his query:
Column sw_event format a20
select
count(*),
CASE WHEN state != ‘WAITING’ THEN ‘WORKING’
ELSE ‘WAITING’
END AS state,
CASE WHEN state != ‘WAITING’ THEN ‘On CPU / runqueue’
ELSE event
END AS sw_event
FROM
v$session
WHERE
type = ‘USER’
AND status = ‘ACTIVE’
GROUP BY
CASE WHEN state != ‘WAITING’ THEN ‘WORKING’
ELSE ‘WAITING’
END,
CASE WHEN state != ‘WAITING’ THEN ‘On CPU / runqueue’
ELSE event
END
ORDER BY
1 DESC, 2 DESC;
Tanel article is available at:
http://blog.tanelpoder.com/2008/08/07/the-simplest-query-for-checking-whats-happening-in-a-database/
Leave a Reply