What SQL Statements are Currently Running in my Oracle Database

We are often times faced with the situation where we need to know what statements are running right now in our Oracle database.  Maybe we are having some sort of issue, and applications are not responding or responding very slowly, and you need to get some information fast about what is happening in your system.  One of the first things I always take a look at is what is happening in the database.  This may just be from experience, but on (too) many occasions, I've found the issue to be a long running, inefficient query that is consuming way too many resources.  In any case, one of the things I always want to do is narrow down what tier of the system the problem might be in.  So by scanning through the results of this query, I can quickly determine if the problem is in Oracle or I need to look somewhere else.

So here is the query:

 SELECT  
     s.sid, s.username, s.osuser,   
     s.machine, s.process, s.program, s.module,   
     q.sql_text, q.optimizer_cost,   
     s.blocking_session, bs.username as blocking_user,   
     bs.machine as blocking_machine, bs.module as blocking_module,  
     bq.sql_text AS blocking_sql, s.event AS wait_event,  
     q.sql_fulltext  
   FROM v$session s  
   INNER JOIN v$sql q  
     ON s.sql_id = q.sql_id  
   LEFT OUTER JOIN v$session bs -- blocking sessions  
     ON s.blocking_session = bs.sid  
   LEFT OUTER JOIN v$sql bq -- blocking queries  
     ON bs.sql_id = bq.sql_id  
   WHERE s.type = 'USER';  

What do I look for when I run this query?  First, statements with a high optimizer cost.  The cost is usually high because the statement is expensive.  So I check those out first.  I also look to see if there is a blocking session for a statement.  I have seen cases where someone created a lock and then everyone piled up behind them bringing an entire environment to a halt (fortunately this was is DEV, but still).  And finally, I'll run this statement a couple of times, and see if there are any statements that are still the same on the second or even third running of the query.  Most statements that are run (in an OLTP environment anyway) execute in sub-second time, so if a statement is sticking around over 10, 15 or even 30 seconds, I know it is trouble.

What I also get from the statement is enough information to find out who is running the statement.  Session id, Oracle user, OS user and machine are all there.  I've seen cases where someone ran a statement in production but forgot a join condition or didn't realize they weren't using an index.  From the data in the above statement, I usually am able to track down who it is so I can give them a call and get them to cancel the statement.

I usually keep a cheat sheet around of about 10 statements like this that I commonly use to diagnose what is going on with an Oracle database, and this is always on of the more frequently used statements in that list.  So put this in your own cheat sheet or bookmark this page so that the next time someone asks "Does anyone know what is going on in the Oracle database right now?" you will be prepared to find the answers.