The simplest query for checking what´s happening in a Oracle database

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/