1

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?

enter image description here

How can I find out why this query is in waiting state?

4 Answers 4

2

To the portion of the question regarding time spent on CPU I offer this for your consideration.

NOTE: This has been tested and verified to work in Oracle versions 12c, 18c, & 19c. It does NOT work in versions 11g and below. It has NOT been tested in versions above 19c.

If a session is active it is either waiting on some wait event or is on a CPU processing data. In V$SESSION the combination of (STATUS = 'ACTIVE' and TIME_SINCE_LAST_WAIT_MICRO = 0) tells us the session is currently waiting on some wait event. Inversely, (STATUS = 'ACTIVE' and TIME_SINCE_LAST_WAIT_MICRO > 0) tells us the session is active but not waiting on a wait event and therefore is ON CPU.

That means we can query as seen below to see what our active sessions are waiting on, including time ON CPU.

col DTS format a22
col inst_sid heading "INST_ID|:SID" format a7
col username format a15
col machine format a12
col sql_exec_start   heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col module format a16
col event format a33
col SEQ# format 99990
col wait_sec heading "WAIT|(SEC)" format 99999

select inst_id||chr(58)||sid as inst_sid
   ,username
   ,case
      when machine like '%\%' then substr(machine,(instr(machine,'\',1)+1),length(machine)) -- WINDOWS HOST NAME FORMANT'
      when machine like '%.%' then substr(machine,1,(instr(machine,'.',1)-1))               -- UNIX HOST NAME FORMANT
      else machine
   end machine
   ,(sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start
   ,sql_id
   ,substr(module,1,15) module
   ,substr
      (case time_since_last_wait_micro
         when 0 then (case wait_class when 'Idle' then '*IDLE* '||event else event end)
         else 'ON CPU'
         end
      ,1,33) event
   ,seq#
   ,(case time_since_last_wait_micro
      when 0 then wait_time_micro
      else time_since_last_wait_micro
      end) /1000000 wait_sec
from gv$session
where inst_id||chr(58)||sid <> sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')
   and username is not null
   and status='ACTIVE'
order by sql_exec_start,
   username,
   sql_id,
   machine,
   sid;

                                     SQL
INST_ID                              START                                                                                 WAIT
:SID    USERNAME        MACHINE      D HH:MM:SS  SQL_ID        MODULE           EVENT                               SEQ#  (SEC)
------- --------------- ------------ ----------- ------------- ---------------- --------------------------------- ------ ------
1:5674  KFAP_CTL        895029       +0 00:00:01 7gs76asdf0wrq DBMS_SCHEDULER   ON CPU                              9715      1
2:18067 BILLSMITH       BMG-50       +0 00:00:01 2wdfgfsd1uis6                  control file sequential read       10545      0
1:19743 KFAP_WEB        854072       +0 00:00:09 fjklsd0fw5xm2 D-ZING(732)      PGA memory operation                 852      0
2:3367  DBSNMP          697384       +0 00:00:59 g0dpalwjdfu4w JDBC Thin Client *IDLE* PL/SQL lock timer            4679     59
1:8402  KFAP_CTL        284974       +0 00:02:54 a33ncusmx9u0d DBMS_SCHEDULER   cell single block physical read    56311      0
2:18486 KFAP_RPT        593860       +0 00:22:41 kfismax9rj6y6 KFAP(342,4998)   ON CPU                               728      9

I hope this is helps.

1
  • Thanks this has helped me trying to figure out what's going on with some performance issues. Turned out the I/O subsystem may as well have been using a quill and some parchment.
    – Pete
    Commented Jun 28, 2022 at 14:34
1

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 <= 1000)
select count(*) from g,g,g,g;
1

To see all of the cumulative waits for a session, query V$SESSION_EVENT. This doesn't limit your view to just the current wait.

0

You can query the dynamic performance view V$SESSION_WAIT

Like the following:

select sid, seq#, EVENT,  WAIT_CLASS,  SECONDS_IN_WAIT from v$session_wait where sid=<SESSION_SID>

Take a look at the 'EVENT' field.

1
  • Something's wrong here. SQL dev says seconds in wait is 1000. Your query says seconds in wait is 0. Event says db file sequential read. This doesn't reveal what it is waiting on just wait it should be doing, reading from disk. Commented Jun 5, 2016 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.