How to find out why this query is in waiting state? I have a query that has been several minutes in waiting and is not running at all. 

In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds? 



How can I find out why this query is in waiting state?
What makes you think your session is waiting?

When a statement uses the CPU, it is not waiting. There is no wait event that accounts for using the CPU for processing. People often forget this. Your session is most likely using the CPU. There is no indication in your output that your session is waiting.

V$SESSION


  SECONDS_IN_WAIT
  
  If the session is currently waiting, then the value is the amount of
  time waited for the current wait. If the session is not in a wait,
  then the value is the amount of time since the start of the last wait.


You should query WAIT_TIME as well:


  WAIT_TIME
  
  If the session is currently waiting, then the value is 0. ...


Another method to confirm that your session is ON CPU would be querying ASH. 

V$ACTIVE_SESSION_HISTORY

In ASH, the SESSION_STATE column has 2 different possible values:


  SESSION_STATE
  
  Session state:
  
  WAITING 
  
  ON CPU


Another method would be to query V$SESSTAT (joined with V$STATNAME) an check whether the statistic CPU time increases.

You can run any CPU-bound query to test this, for example the below query uses nothing else but CPU. Start it, and you will we see SECONDS_IN_WAIT increasing, while your session is not waiting, but using CPU.

with g as (select * from dual connect by level &lt;= 1000)
select count(*) from g,g,g,g;