查看等待事件

Select b.logon_time "logon_time",
       /*代码信息，需要修正*/
       /*
       decode(a.program_id,0,to_char(null),
       (select obj.object_type || '       ' || obj.owner || '.' || obj.object_name 
         from dba_objects obj where obj.object_id = a.program_id and rownum = 1 )) as program,
       a.program_line# "line#",
       */
       --b.osuser,
       c.spid "spid",
       b.sid "sid",
       b.username "username", 
       a.sql_id "sql_id", 
       a.sql_text "sql_text",
       a.sql_fulltext "sql_fulltext", 
       round(buffer_gets/decode(a.executions,0,1,a.executions))  "buffer/exe" ,
       round(disk_reads/decode(a.executions,0,1,a.executions)) "disk/exe" ,
       trunc( a.rows_processed/case when a.executions =0 then 1 else a.executions  end) as "rows/exe" ,
       a.executions "ex",
       a.rows_processed "row_proc",
       --a.first_load_time,
       a.last_active_time "last_active_time",
       b.machine "machine",
       b.BLOCKING_SESSION,
       --b.module, 
       --audsid,
       b.event "event",
       b.state,
       b.wait_time,
       b.seconds_in_wait,
       c.pga_alloc_mem,
       b.service_name,
       'alter system kill session ''' || to_char(b.sid) || ',' ||
       to_char(b.serial#) || '''; ' killsess,
       --'instance info:'|| (select 'no:'||instance_number||',name:'||instance_name from v$instance) "KILLSE"
       'select * from table(dbms_xplan.display_cursor(''' || a.sql_id || ''',' || a.child_number|| ',''all'')); --iostats memstats allstats last runstats_tot runstats_last outline '||chr(10)
       ||'select * from gv$session_longops where sql_id = '''||a.sql_id||''';'||chr(10)
       ||'begin '||Chr(10)
       ||'    dbms_shared_pool.purge('''|| (Select address||','||hash_value From v$sqlarea Where sql_id = a.sql_id)  ||''', ''C''); '||Chr(10)
       ||'end;'||Chr(10)
       ||'/' as "TROUBLESHOOTING_SQL"
  from v$sql a,  v$session b,  v$process c 
 where a.sql_id = b.sql_id
   and b.paddr = c.addr
   And a.child_number = b.sql_child_number
   And b.status='ACTIVE'
   --and a.sql_id in ( '3nmrkc1g4j0b8', '97k52xyuukf9c')
   and b.sid<>(select sid from v$mystat where rownum<2) 
 order by b.username, a.sql_text;








SELECT *
  FROM v$session
 WHERE status = 'ACTIVE'
   AND username = 'H2'
   AND sid <> (SELECT sid FROM v$mystat WHERE rownum < 2);