What to query when SQL_ID is NULL

Sometimes the SQL_ID in V$SESSION will be NULL even though a long running SQL statement appears to be executing. Reason being that something other than the SQL is in fact executing. It could be PL/SQL code, latching activity in the shared pool (which is common for very long running SQL statements) or a COMMIT is currently underway.

One can query the V$ACTIVE_SESSION_HISTORY dictionary view to identify the current elapsed time per wait event for the session;

select    /*+ all_rows */
          count(*)                  as Wait_Event_Count
         ,sum(ah.time_waited)       as Total_Time_Waited
         ,ah.session_id             as SID
         ,ev.name                   as Event_Name
         ,sql_id                    as SQL_ID
         ,ah.session_type           as Session_Type
from     
          v$active_session_history  ah
         ,v$event_name              ev
where     ah.sample_time between sysdate - 30/(24*60) and sysdate
and       ah.event#      = ev.event#(+)
and       ah.session_id  = &sid
group by 
          ev.name
         ,ah.session_id
         ,ah.sql_id
         ,ah.session_type
/

The following statement indicates the sessions that have caused the most load as a percentage of all foreground sessions;

select ah.session_id
      ,ah.session_type
      ,nvl(ah.sql_id,'xx')                     as SQL_ID
      ,count(*)                                as Session_Cnt
      ,round(count(*)/sum(count(*)) over(), 2) as Percent_Load
from
       v$active_session_history ah
where
       ah.sample_time >to_date('31-OCT-11 11:40','dd-MON-yy hh24:mi')
and    ah.session_type='FOREGROUND'
group by
       ah.session_id
      ,ah.session_type
      ,ah.sql_id
order by count(*) desc

/

The following SQL shows information on the PL/SQL units, which might be relevant if PL/SQL is busy executing and the SQL_ID is null;


select    ah.session_id               as SID
         ,ah.session_state            as State
         ,event                       as Event_name
         ,ah.sql_id                   as SQL_ID
         ,ah.top_level_sql_id
         ,ah.plsql_entry_object_id
         ,ah.plsql_entry_subprogram_id
         ,ah.plsql_object_id
         ,ah.plsql_subprogram_id
         ,count(*)                    as Wait_Event_Count
from
          v$active_session_history    ah
where
          ah.sample_time     between sysdate - 60/(24*60) and sysdate
and       ah.session_state   in ('ON CPU', 'WAITING')
and       ah.session_id      = &sid
group by  ah.session_id
         ,ah.session_state
         ,ah.event
         ,ah.sql_id
         ,ah.top_level_sql_id
         ,ah.plsql_entry_object_id
         ,ah.plsql_entry_subprogram_id
         ,ah.plsql_object_id
         ,ah.plsql_subprogram_id
order by
          ah.session_id
/

The following SQL shows operations that are currently executing specific to a session, which will give you an indication of what the wait is for if the SQL_ID is null.


alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
col target format a25
col opname format a40
select sid
      ,opname
      ,target
      ,round(sofar/totalwork*100,2)   as percent_done
      ,start_time
      ,last_update_time
      ,time_remaining
from 
       v$session_longops
where
       sid = &sid
/