--判断活跃会话1
select count(*) ACTIVE_SESSION_COUNT,sum(last_call_et) TOTAL_ACTIVE_TIME ,max(last_call_et) MAX_ACTIVE_TIME,
nvl(event,'==grouping==')event, nvl(sql_id,'==grouping==') sql_id
from v$session
where status = 'ACTIVE' and 
not ( type = 'BACKGROUND' and state='WAITING' and  wait_class='Idle' )
group by cube(event,sql_id)
having count(*)>1 or (grouping(event)+grouping(sql_id)=0)
order by 1
/

--判断活跃会话2（PL/SQL只考虑当前SQL)
select count(*) ACTIVE_SESSION_COUNT ,sum(sysdate-sql_exec_start)*86400 TOTAL_ACTIVE_TIME ,
max(sysdate-sql_exec_start)*86400 MAX_ACTIVE_TIME,
nvl(event,'==grouping==')event, nvl(sql_id,'==grouping==') sql_id
from v$session
where status = 'ACTIVE' and 
not ( type = 'BACKGROUND' and state='WAITING' and  wait_class='Idle' )
group by cube(event,sql_id)
having count(*)>1 or (grouping(event)+grouping(sql_id)=0)
order by 1
/

--找到会话对应PL/SQL 对象
select p.object_name||'.'||p.procedure_name plsql_name--,...
 from v$session s , dba_procedures p
where status = 'ACTIVE' and 
  not ( type = 'BACKGROUND' and state='WAITING' and  wait_class='Idle' )
  and s.plsql_object_id = p.object_id (+)
  and s.plsql_subprogram_id= p.subprogram_id (+);
  
--找到会话对应的等待对象
select o.owner||'.'||o.object_name waiting_object_name
 from v$session s , dba_objects o
where s.status = 'ACTIVE' and 
  not ( s.type = 'BACKGROUND' and state='WAITING' and  wait_class='Idle' )
  and s.row_wait_obj# = o.object_id (+);