=================================================
Website      : http://www.arshltd.com
Author       : Arsh Ltd
Category     : Database Administration
Title        : What is happening in your database NOW
Description  : Lists queries which can be used to find current state of database
=================================================

Background – Often when monitoring your databases, you come across situations where you need to know what is going on in the databases at the moment. This could be due to your SYSADMIN asking you to check something is not right or someone informing you that performance of the system has gone down etc. The critical thing in all these kind of analysis is what is actually causing all this load. Unless you know that, you cannot take any appropriate action e.g. to kill that session or to attempt to tune the query etc.

You are then required to find out which SQL statements are running at the moment, which users are active, which scheduler jobs are running etc. Following list down the queries that will help you trace the issue.

Queries - For the following queries to work, you need to connect as a user which has access to dba_% objects.

--Oracle Scheduler Jobs running at the moment and some details
SELECT 
  owner, 
  job_name, 
  job_action, 
  repeat_interval, 
  enabled, 
  state, 
last_start_date
FROM dba_scheduler_jobs
WHERE state = 'RUNNING';

--List queries which are consuming most disk reads (hence resources)
SELECT 
  sa.sql_id, 
  sa.executions, 
  sa.buffer_gets, 
  sa.parse_calls, 
  ROUND(sa.disk_reads/sa.executions,0) diskreads_per_exec, 
  sa.disk_reads, 
  sa.sorts, 
  sa.sql_text
FROM  v$sqlarea sa
WHERE sa.executions > 0
ORDER BY 5 DESC;

--List all Outstanding Long Operations going on in database at the moment
SELECT 
  sess.SID, 
  sess.serial#, 
  sess.machine, 
  sess.schemaname, 
  sess.terminal,
  ROUND(lops.elapsed_seconds/60) || 'min ' || 
    MOD(lops.elapsed_seconds,60) || 'secs ' time_elapsed,
  ROUND(lops.time_remaining/60) || 'min ' || 
    MOD(lops.time_remaining,60) || 'secs ' est_time_remaining,
  ROUND(lops.sofar/lops.totalwork*100, 2) pct_completed
FROM  v$session sess, v$session_longops lops
WHERE sess.SID     = lops.SID
AND   sess.serial# = lops.serial#
AND   lops.totalwork <> 0
AND   lops.sofar <> lops.totalwork;

--List current connected sessions and what statements are being executed from those sessions
SELECT 
  sess.SID,
  sess.serial#,
  sess.username,
  sess.schemaname,
  sess.status,
  sess.osuser,
  sess.blocking_session,
  sa.sql_text
FROM v$session sess, v$sqlarea sa
WHERE sess.sql_hash_value = sa.hash_value
AND   sess.sql_address = sa.address;

Note that the previous query also would inform you if that session is being blocked by some other session and hence hanging for some resources to be freed etc.

Also above query lists current connect sessions, but all of them may not be active at the moment, so to find out current connected ACTIVE sessions, just add this filter “status = 'ACTIVE'”, so it will look like –

 
--List connected ACTIVE sessions and SQL's being executed from those sessions
SELECT 
  sess.SID,
  sess.serial#,
  sess.username,
  sess.schemaname,
  sess.status,
  sess.osuser,
  sess.blocking_session,
  sa.sql_text
FROM  v$session sess, v$sqlarea sa
WHERE sess.SQL_HASH_VALUE = sa.HASH_VALUE
AND   sess.SQL_ADDRESS = sa.ADDRESS
AND   status = 'ACTIVE';

--List all the SQL queries that are executing NOW
SELECT 
  PARSING_USER_ID,
  (
   SELECT username 
   FROM dba_users 
   WHERE user_id = sa.PARSING_USER_ID
  ) parsing_usernm,
  USERS_OPENING,
  EXECUTIONS,
  USERS_EXECUTING,
  SQL_TEXT,
  LOADS, 
  FIRST_LOAD_TIME, 
  INVALIDATIONS, 
  PARSE_CALLS, 
  DISK_READS,
  BUFFER_GETS, 
  CPU_TIME, 
  ELAPSED_TIME,
  SORTS
FROM v$sqlarea sa
WHERE Parsing_User_ID <> 0 
AND users_executing <> 0 
ORDER BY TO_DATE(first_load_time,'yyyy-mm-dd/hh24:mi:ss');

As you can see from previous query you can use it for identifying various things like which query is doing lot of disk_reads etc.

Also please note from above –

1. To exclude SYS related queries fired in response to user queries from coming up in our list, the condition “Parsing_User_ID != 0” has been put.
2. The above lists all SQL queries running NOW, and if you want to have a list of queries that have run before (historically), comment this condition “users_executing <> 0”. Also if you want to find only queries that have executed in previous day, then add this filter as well –
   AND TO_DATE(first_load_time,'yyyy-mm-dd/hh24:mi:ss') >= TRUNC(SYSDATE - 1)
3. To get a list of SQL queries against a particular table, use this filter condition –
   AND INSTR(sql_text,'') > 0