active_sess_2
大罗
47次下载
445次浏览
2019-07-24
4.5

脚本内容

--判断活跃会话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 (+);

评论

贡献排行榜