在使用TOP命令來查看CPU或內存使用率異常的進程，再根據PID查找對應的oracle session
select a.sid,a.serial#,a.sql_id,a.machine,a.program,a.status,c.sql_text
from gv$session a, gv$process b, gv$sql c where a.paddr=b.addr and b.spid in (xxx,xxx) and a.sql_id = c.sql_id;
可以用來查詢數據庫當前的等待事件
select inst_id,event,program,machine,sql_id from gv$session where wait_class <> 'Idle' order by event;
如果某一等待事件造成了數據庫運行嚴重緩慢，那麼在執行alter system kill session命令可以無法成功，這時候就需要在OS層面來殺死進程
select 'kill -9 ' || spid from v$process where addr in (select paddr from v$session where event='latch: buffer cache chains'));
在v$session視圖中無法查看連接用戶的IP，可以通過下面的方法實現這一目的
方法一
create table login_history
(
username varchar2(60),
machine varchar2(60),
event varchar2(60),
program varchar2(100),
sql_id varchar2(40),
login_time date,
ip varchar2(50)
);
create or replace trigger login_log
after logon on database
begin
insert into login_history select username, machine, event, program,sql_id, sysdate, sys_context('userenv', 'ip_address')
from v$session
where audsid = userenv('sessionid');
commit;
end;
/
方法二
select utl_inaddr.get_host_address(t.machine),t.* FROM v$session t;
通過dba_hist_active_sess_history視圖，來查詢過去某個時間段內發生某個等待事件的信息，主要是查詢歷史等待事件的sql_id
select SESSION_ID,SESSION_TYPE,MACHINE,PROGRAM,sql_id,BLOCKING_SESSION,BLOCKING_SESSION_STATUS
from dba_hist_active_sess_history
where sample_time > to_date('2020-06-23 09:00:00','yyyy-mm-dd hh24:mi:ss')
and sample_time < to_date('2020-06-23 09:30:00','yyyy-mm-dd hh24:mi:ss')
and wait_class<>'Idle'
and event like 'latch: cache buffers chains%'
group by SESSION_ID,SESSION_TYPE,MACHINE,PROGRAM,sql_id,BLOCKING_SESSION,BLOCKING_SESSION_STATUS order by SESSION_ID,BLOCKING_SESSION;