Reasons for a session got executing the same SQL for hours

oracle

I've found a situation where some sessions got executing the same sql_id (select) for hours (the most was 32 hours). all sessions was executing the same sql_id.. Looking in the database I didn't find any wait event holding the execution. Is this a application issue? or most likely in database side?
enter image description here

Best Answer

This is just too generic. From what you provided us, it is impossible to tell.

A few possible reasons:

  • network wait (waiting for a dblink connection that is already dead)
  • blocking wait (another transaction blocks these sessions)
  • programmed wait (DBMS_LOCK.SLEEP)
  • or session is not in wait at all but uses the CPU (inefficient PL/SQL code or SQL code/execution plan or session is simply spinning due to bug)