Oracle Logbook

June 20, 2012

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

Filed under: Uncategorized — heliodias @ 8:08 pm

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/

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: