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/

Advertisements

AWR it´s not free and requires license – DBA_FEATURE_USAGE_STATISTICS

Filed under: Uncategorized — heliodias @ 3:18 pm

Be aware that if you use such feature, Oracle will log this, and will fail in an license auditing.

even if you just select some *_HIST views, Oracle will going to log this access into DBA_FEATURE_USAGE_STATISTICS and eventually you will have to pay.

Within DBA_FEATURE_USAGE_STATISTICS have informations regarding , how many times you used that feature, when was the last time.

Blog at WordPress.com.